Extract SQL Auditing data into Power BI

Growing datasets and information in the modern digital age become considerable businesses’ assets as data drives many important decisions for organizations. Therefore, many companies are challenged with keeping a close eye on data governance processes that expand together with the amount of data being accumulated. To an extent, SQL auditing data is not an exception, and it might play an important role while screening for potential data breaches, learning about activity trends and data flows.

In this article, we will explore the possibilities of how to collect audit data and put it to use in the context of data analytics using MS Power BI, create valuable information, and learn about databases activities and data access.

Extracting data using native auditing techniques

There are fewer options to collect, store, and organize auditing data using the native SQL auditing techniques. In this article, we’ll be using Extended Events sessions for demonstration purposes. Let us quickly jump into creating a session file to track access, more particularly SELECT statements against the database using SQL Server Management Studio (SSMS) user interface.

Create new Extended Events session

1. Connect to a SQL Server using SSMS

2. In the Object Explorer, expand the Management node > Extended Events > Right click on the Sessions node and choose New Session:

   

3. In the New Session wizard, inside the General tab give your session a familiar name:

   

4. In the Events tab we will configure the policy that will tell SQL Server what type of auditing data we are collecting, and to track SELECT statements:
   • Navigate to Events tab and into Events library search for sp_statement_completed
   • Highlight it in the grid and choose right arrow button “>” to move the event to the Selected events box:

   

5. Staying on the Events tab, highlight the sql_statement_completed from the Selected events grid and choose Configure:

   

6. Events configuration options allows to be more specific on what information will be captured during the SQL audit process, for this demo, inside the Global Fields we’ve chosen:
   • Client application name
   • Client hostname
   • System time
   • Database name
   • SQL text, and
   • Username

   

7. In the Filter (Predicate) tab, add the new clause and select sqlserver.sql_text field, in the operator choose like_i_sql_unicode_string and put “%SELECT%” as a value:

   

8. Now that we’ve set up the fields and filters, continue by navigating to the Data storage tab
   • Select event file for the storage type
   • Configure the file properties to instantiate the files on a filesystem location of your choice and aspiration for maximum file size and rollover policy:

   

9. Choose OK to finish. The event session should be instantiated in the Extended Events session:

   

Prepare auditing data output for Power BI use

SQL audit data collection is in commence the moment a session is started. The data is being accumulated in the form of a flat-file on the file system per the configuration used in this demo. Moving forward, to get some sample data it is required to roll some SELECT queries against the database on the server to collect the data.
Considering that we have data inside our session files ready, let’s put it to use. Auditing by nature can grow datasets really fast for the highly transactional databases and systems, and we might have a bunch of files storing our data on the file system. To consolidate and prepare data for the output, SSMS provides the ability to merge data files as follows:

1. Go to File -> Open -> Merge Extended Events files…:

   

2. In the Merge Extended Events Files dialog choose Add and select files from the location chosen to store session files on the file system:

   

3. Upon files load, SSMS navigation bar is expanded with additional drop-down “Extended Events” that provides the Export option

   • Power BI can load from any Export options, for this demo we are using export to csv file:

     

Import Extended events data into Power BI

Power BI yet as freeware is powerful enough for data processing to create understandable and upgradable reports for modern business needs nowadays. As a comprehensive tool, it provides the ability to import data from various ranges and types of data sources, including the CSV file which we’ve created from the SQL auditing source.

Import audit data using the Get data option from the main application ribbon in Power BI desktop application as follows:

1. In the Get Data dialog select File tab and choose Text/CSV:

   

2. The data can be loaded as-is, or transformed prior to the load from the data preview dialog
   • Regardless of the fact that we’ve chosen a subset of global fields in Extended Events session configuration, there are yet several system-related information in our data set, and to remove the irrelevant data, continue by choosing Transform data button

   

3. In the transform data working pane, select all the irrelevant columns and from the context menu choose Remove columns:

   

4. After the data set is transformed to match the expectations and configuration setup continue with Close & Apply option from the ribbon:

   

5. Upon application, the data is ready for visualization, with unlimited options to filter and present data in the Power BI. In the below screenshot we’ve created the graph that shows numbers of databases access from the various applications:

   

To learn more about the Power BI capabilities, reporting visualization options you can explore the Get started creating in the Power BI service article

Extracting audit data using third-party software

ApexSQL Audit is a comprehensive third-party software for SQL auditing that is focused on ease of configuration, use, and making compliance with many auditing regulations easy. As a solution, it provides functionalities for a wide range of scenarios during the auditing process, a tamper-evident central repository database for data keeping, automated reporting, and alerting features.

Configuring auditing policy

ApexSQL Audit is developed to easy set up and configure auditing policies through an interactive user interface for databases and SQL Server instances alike, regardless of the edition or version. Let us dive deeper into how to configure and put auditing policy into motion using ApexSQL Audit interface:

1. Run ApexSQL Audit and go to the Configure tab
   • If not already done, add SQL Server to the list

2. Add database for auditing on Add database button in the server pane. Select the database(s) from the databases list and choose Add

   

3. Select the database and choose desired SQL operations for auditing

   

4. Upon Apply, the auditing policy will be configured, and the data collected per defined configuration which can be examined in the summary configuration pane:

   

Creating report template

Now when the configuration is applied, and data collection is active, the audit reports can be easily generated into a preview grid, exported file, or retrieved via the API calls. To create a meaningful and expected SQL audit report, the Reports feature provides the ability to filter data output using a subset of options to filter by event data properties, including the database name, logins, applications, time filters, operations, and more:

Reporting in the ApexSQL Audit provides a wide range of options and the ability to create, manage and automate data output from the SQL audit trail, as it is detailed in the Create report definitions, output and schedule article. Report data output is based on the filter conditions used; besides the ability to create reports from a pre-defined report template list, data output can be filtered per high-granularity custom filters that are available in the tool.

Importing audit data into Power BI using API call

Once the reporting template and filter conditions are configured, the data output can be consumed by Power BI using the API call. Before we get into detailed workflow, let’s examine the specifics and pre-requisites on how to setup and use API web server in ApexSQL Audit as described in Using API to generate SQL auditing reports.

As the web API server is up and running, we can utilize the Reports option to get raw SQL audit data via the API call, as follows:

1. Go to Reports tab, highlight the report template and choose API call from the Generate drop-down list:

   

2. Copy the API call string in the Report API call pop-up:

   

3. Open the Power BI console, choose Get data and in the opened dialog continue with the Web option from the Other tab:

   

4. In the From Web dialog, paste the API call string in the URL field and choose OK:

   

5. To access SQL Audit report it is required to authenticate using Windows credentials. Due to application-level security features in ApexSQL Audit, choose the account that has access to auditing data trail:

   

6. The Connect button will run the API call and load the data information in the Power Query Editor. From here, open the Events list to convert it to table in the next step:

   

7. Continue with To Table option from the Convert tab of the Power Query Editor’s main ribbon menu:

   

8. Inside the To Table dialog, use the default values for delimiter and extra columns:

   

9. Use the header columns button to expand the columns from the table controls:

   

   • Optionally, change data type for date column so you can create time-lined visualizations:

     

10. Complete data loading by choosing the Close and Apply button in the main ribbon menu:

    

The data is loaded and ready to be consumed for data analytics, visualization of data access trends, and variety range of information that can be reconstructed from the audit data. Below is an example of the number of operations per operation and per login from the SQL audit data report:

Conclusion

Extra data visibility and consumption is the number one priority for data-driven businesses, however, the road to get the consumable, easy understand and trace-able data is challenging without an effective approach to collect and prepare data samples when creating information. With that in mind, using native SQL auditing techniques is hard to efficiently implement on large-scale environments from configuration, use, security, and maintainability perspectives. Whilst native auditing is suitable for small and low-transactional systems and databases to a degree, ApexSQL Audit is a considerable solution for both, big and small, low and high transactional systems, as one can easily configure auditing policies and use pre-defined regulatory oriented templates to collect and report data fast and easy.