Monitor your Azure SQL Database Auditing activity with Power BI
Azure SQL Database Auditing logs tracks database events, enabling you to retain, report and analyze the activities in the instances you monitor. These logs can be used to help gain insight into discrepancies and anomalies that could indicate business concerns or suspected security violations.
With the latest update to Power BI, you can connect to the data logged by SQL Database Auditing with a set of out of box reports and a customized dashboard. This initial version of the content pack makes it easy to find suspicious events, unusual activity, and trends, based on a dataset that has been created for your reporting.
In this post we’ll cover how to get better insights to your SQL Database Auditing data using Power BI. For additional details on how to connect and get started, jump to the Connecting to SQL Database Auditing section below.
Exploring your SQL Database Auditing data
Power BI offers a set of out of box content that connects to your Azure SQL Database Audit logs.
This content is based off of tables with “AuditLogs” in the name and includes data from the most recent 250k Audit Log Records. After connecting, you’ll land on a customized dashboard for tracking your events. The dashboard show the number of monitored databases, the number of highlighted events, event types by database and much more.
Your dashboard can be customized however you like, ensuring you have the data that is most important to you. The data will automatically be refreshed daily, keeping your dashboard up to date.
Behind each tile there are additional insights. Many lead to the three page report that comes with the dashboard. This report provides details on Event Type Distribution, Highlighted Events and a Drill Down into the events.
To see more about any of the reports, select a tile such as “Events by Weekday, Event Type”. This leads to the Event Type Distribution report, where you can find details about events by type across a variety of dimensions such as month, day, hour, and more. More details about interacting with reports are available here.
Any of the visuals can be added to your dashboard, customizing it for your scenario. Use the navigation at the bottom to browse the other pages of the report, including Highlighted Events and a Drill Down report.
These reports are interactive, selecting a particular value will cross filter the other related visuals. You can also use the filter pane on the right hand side, to change filters on the individual visual or the entire report page.
If you’d like to customize your reports, by adding additional fields or changing existing visuals, you can edit the report. Select Edit Report in the top left corner to show the field list, allowing you to access any of values and calculations included in this out of box data set. In this mode you can additional visuals, change the fields in a particular visual and even add a filter to a visual or the entire report. This dataset contains a large table with details from the Audit Logs, including Action Status, the count of databases and servers being monitored, the statements that were executed during the event and much more.
Back on the dashboard, you can also ask questions about your audit log data using the question box. Try questions such as “total events by database” or “events by date where event type is DataAccess”. The results of the questions can also be pinned to your dashboard.
Connecting to Azure SQL Database Auditing
The content pack imports data from all tables that contain “AuditLogs” in their name and append it to a single data model table named “AuditLogs”. The last 250k Audit Logs will be included and the data will be refreshed once per day, ensuring your monitoring the most recent information!
To get started, click Get Data at the top of the navigation pane.
Select the SQL Database Auditing connector and click Connect. You can use the Search box to help narrow down the list.
In order to connect, enter the Azure Table Storage account name where your logs are stored.
Using the Key Authentication Method, enter the Account Key to connect.
After hitting “Sign In”, Power BI will begin connecting to your account and populating the out of box content including your own dashboard.
We’re always interested in hearing your feedback – please leverage https://support.powerbi.com to let the team know how your experience was and if there’s anything we can do better. We look forward to your feedback!
