Lesson Learned #392:Querying Extended Events and Sending Results via Email using PowerShell

In this article, we will explore how to leverage PowerShell to query extended events in SQL Server and send the results via email. Extended events provide valuable insights into Azure SQL performance and activity, and automating the process of querying and receiving the results can greatly enhance monitoring and analysis capabilities. We used to have this questions about how to receive an email when a live query took more than specific duration time. 

Prerequisites:

• Azure SQL Database with Extended Events enabled
• PowerShell
• SMTP server credentials for sending emails

Extended Event Definition: Before diving into the PowerShell script, let's first define the extended event session that we'll be querying. Execute the following script in Azure SQL Database to create the event session:

CREATE EVENT SESSION QueryMonitoring
ON DATABASE
ADD EVENT sqlserver.sql_statement_completed
(
    ACTION (sqlserver.client_app_name, sqlserver.database_id, sqlserver.query_hash)
    WHERE duration > 1500000
)
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY = 4096 KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS)
GO

ALTER EVENT SESSION QueryMonitoring ON DATABASE STATE = start

This event session, named "QueryMonitoring," captures the sql_statement_completed event when the duration of the executed statement is greater than (1.5 seconds). It includes actions to capture the client application name, database ID, and query hash. The captured events are stored in the ring buffer target.

Script Overview: The PowerShell script performs the following tasks:

1. Configures the SMTP server settings, email addresses, and other parameters.
2. Retrieves the last execution date from a JSON file, which serves as a checkpoint to filter duplicate events.
3. Constructs the query to retrieve extended event data from the database, filtered by the last execution date.
4. Establishes a database connection and executes the query.
5. Checks if there are any results to send via email.
6. Creates an email body with the queried results.
7. Sends the email using the configured SMTP server.
8. Saves the current execution date in the JSON file for future executions.

Script Code:

# Configuration
$smtpServer = "your_STMP_server_Name"
$smtpPort = 25
$smtpUsername = "your_user_name"
$smtpPassword = "your_password"
$fromAddress = "your_from_email"
$toAddress = "your_to_email"
$subject = "Extended Event Query - Results"
$jsonFilePath = "c:\temp\last_execution_date.json"

# Retrieve the last execution date from the JSON file
$lastExecutionDate = [DateTime]::ParseExact('01/01/1900', "dd/MM/yyyy", $null)
if (Test-Path $jsonFilePath) {
    $jsonContent = Get-Content $jsonFilePath -Raw | ConvertFrom-Json
    $lastExecutionDate = $jsonContent.LastExecutionDate.value
}

# Convert the last execution date to the appropriate format for the query
$lastExecutionDateString = $lastExecutionDate.ToString("yyyy-MM-dd HH:mm:ss")

# Extended Event Query
$query = @"
DECLARE @LastExecutionDate DATETIME = '$lastExecutionDateString';

DROP TABLE IF EXISTS #EventBuffer;
CREATE TABLE #EventBuffer (event_data XML);

INSERT INTO #EventBuffer (event_data)
SELECT CAST(target_data AS XML)
FROM sys.dm_xe_database_session_targets AS dst
JOIN sys.dm_xe_database_sessions AS ds ON ds.address = dst.event_session_address
WHERE ds.name = 'QueryMonitoring' -- Extended Event session name
    
SELECT
    event_data.value('(RingBufferTarget/event/@timestamp)[1]', 'datetime') AS EventTimestamp,
    event_data.value('(RingBufferTarget/event/action[@name="client_app_name"]/value)[1]', 'nvarchar(max)') AS ClientAppName,
    event_data.value('(RingBufferTarget/event/action[@name="database_id"]/value)[1]', 'nvarchar(max)') AS DatabaseId,
    event_data.value('(RingBufferTarget/event/action[@name="query_hash"]/value)[1]', 'nvarchar(max)') AS QueryHash,
    event_data.value('(RingBufferTarget/event/data[@name="statement"]/value)[1]', 'nvarchar(max)') AS Statements,
    event_data.value('(RingBufferTarget/event/data[@name="duration"]/value)[1]', 'bigint') AS Duration
FROM #EventBuffer
WHERE event_data.value('(RingBufferTarget/event/@timestamp)[1]', 'datetime') >= @LastExecutionDate;
"@

# Database Connection
$connectionString = "Data Source=servername.database.windows.net;Initial Catalog=dbname;User ID=username;Password=password;"
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
$command = New-Object System.Data.SqlClient.SqlCommand($query, $connection)
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter($command)
$dataTable = New-Object System.Data.DataTable

$connection.Open()
$adapter.Fill($dataTable)
$connection.Close()

# Check if there are results to send via email
if ($dataTable.Rows.Count -gt 0) {
    # Create the email body with the results
    $body = ""
    foreach ($row in $dataTable.Rows) {
        $body += "Event Timestamp: $($row["EventTimestamp"])
Client App Name: $($row["ClientAppName"])
Database ID: $($row["DatabaseId"])
Query Hash: $($row["QueryHash"])
Statements: $($row["Statements"])
Duration: $($row["Duration"])

"
    }

    # Send the email
    $smtp = New-Object Net.Mail.SmtpClient($smtpServer, $smtpPort)
    $smtp.EnableSsl = $true
    $smtp.Credentials = New-Object System.Net.NetworkCredential($smtpUsername, $smtpPassword)
    $mailMessage = New-Object Net.Mail.MailMessage($fromAddress, $toAddress)
    $mailMessage.Subject = $subject
    $mailMessage.Body = $body

    $smtp.Send($mailMessage)
}
# Save the current execution date in the JSON file
$lastExecutionDateObject = [PSCustomObject]@{
    LastExecutionDate = (Get-Date)
}
$lastExecutionDateObject | ConvertTo-Json | Set-Content $jsonFilePath

Conclusion: By combining PowerShell and Azure SQL extended events, we have created a powerful automation script that queries event data and sends the results via email. This enables efficient monitoring and analysis of Azure SQL activity and performance. Feel free to customize the script further based on your specific requirements and extend its functionality to suit your needs.

With the ability to query extended events and receive the results in your inbox, you can stay on top of critical events and make informed decisions to optimize your Azure SQL environment.