Managed Instance - Weekly report of logins

Question

What's the best way to get a weekly report from an Azure Managed Instance giving all logon information (User, date, IP, success, etc)? I want all SQL logins and Entra ID logins.

I've followed the steps detailed here: https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/auditing-configure?view=azuresql and have played with the sys.fn_get_audit_file function. This could be used in a Power BI subscription that gets emailed.

I'm using distinct as it seems to be a lot of entries and I'm not sure if this is overkill or not. I basicaly want to validate the IP addresses as I've set up my NSG to only allow certain ranges and want this as something I can check to make sure.

SELECT DISTINCT CAST(event_time AS DATE) AS EventDate,
	server_principal_name,
	client_ip,
    succeeded
FROM (
SELECT *
FROM sys.fn_get_audit_file('https://MyBlobStorage/MyContainer/MyServer/master/',DEFAULT,DEFAULT)
) a

Answer 1

To efficiently generate a weekly report for all logon information from an Azure SQL Managed Instance and handle large data volumes, consider the following steps:

1. Create a Data Warehouse Table

Create a table to store the weekly logon data. This approach will help in managing and querying large datasets more efficiently.

CREATE TABLE WeeklyLogonReport (

    EventDate DATE,

    ServerPrincipalName NVARCHAR(128),

    ClientIP NVARCHAR(45),

    Succeeded BIT,

    DatabaseName NVARCHAR(128),

    EventTime DATETIME,

    Statement NVARCHAR(MAX),

    SessionID INT,

    ServerInstanceName NVARCHAR(128),

    ApplicationName NVARCHAR(128)

);

2. Insert Weekly Data into the Table

Modify your query to insert the weekly data into the WeeklyLogonReport table. Schedule this insertion using a SQL Server Agent job.

INSERT INTO WeeklyLogonReport

SELECT 

    CAST(event_time AS DATE) AS EventDate,

    server_principal_name,

    client_ip,

    succeeded,

    database_name,

    event_time,

    statement,

    session_id,

    server_instance_name,

    application_name

FROM 

    sys.fn_get_audit_file('https://MyBlobStorage.blob.core.windows.net/MyContainer/MyServer/', DEFAULT, DEFAULT)

WHERE 

    event_time >= DATEADD(day, -7, GETDATE()) -- Filter for the last week

    AND action_id IN ('LGIS', 'LGOI') -- Logon events (LGIS: Login Successful, LGOI: Login Failed)

ORDER BY 

    event_time DESC;

3. Automate Data Collection

Set up a SQL Server Agent job to run the above insertion script weekly. This ensures your WeeklyLogonReport table is always up to date with the latest logon information.

1. Open SQL Server Management Studio (SSMS).
2. Navigate to SQL Server Agent.
3. Create a new job:
   • Name: Weekly Logon Report
   • Steps: Add a new step to execute the insertion script.
   • Schedule: Set the schedule to run weekly.

4. Create a Power BI Report

1. Connect to Azure SQL Database:
   • Open Power BI Desktop.
   • Click on Get Data and select Azure SQL Database.
   • Enter the connection details to your Azure SQL Managed Instance.
2. Import Data:
   • Use the WeeklyLogonReport table as your data source.
3. Create Visualizations:
   • Design your report in Power BI, including tables, charts, and filters to visualize the logon data.
4. Set Up Refresh:
   • Publish the report to Power BI Service.
   • Configure a data refresh schedule in Power BI Service to ensure the report is updated weekly.

5. Email Notifications

Set up email subscriptions in Power BI to receive the report regularly:

1. Open the report in Power BI Service.
2. Click on Subscribe.
3. Set up the subscription:
   • Choose the frequency (weekly).
   • Enter the email addresses of recipients.

6. Monitor and Validate IP Addresses

Use custom visuals and alerts in Power BI to monitor and validate IP addresses:

• Create filters to narrow down the data to specific IP ranges or users.
• Set up alerts in Power BI to notify you if any unauthorized IPs attempt to access the database.

7. Optimize Query Performance

To further optimize the performance, you can partition the WeeklyLogonReport table by EventDate and create indexes on frequently queried columns such as EventDate, ServerPrincipalName, and ClientIP.

CREATE INDEX IDX_WeeklyLogonReport_EventDate ON WeeklyLogonReport(EventDate);

CREATE INDEX IDX_WeeklyLogonReport_ServerPrincipalName ON WeeklyLogonReport(ServerPrincipalName);

CREATE INDEX IDX_WeeklyLogonReport_ClientIP ON WeeklyLogonReport(ClientIP);

By following these steps, you can efficiently generate, store, and report on weekly logon information from your Azure SQL Managed Instance. This method not only helps manage large volumes of data but also integrates seamlessly with Power BI for visualization and monitoring purposes.