Extract SQL Auditing data into Power BI (4248553)

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.

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. 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.

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

   

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