Managed Instance - Weekly report of logins

HSB 45 Reputation points
2024-05-28T19:29:46.55+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,122 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Ali Varzeshi 80 Reputation points
    2024-06-14T10:15:43.07+00:00

    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.

    0 comments No comments