-
Notifications
You must be signed in to change notification settings - Fork 2
/
Copy pathAzure Synapse SQL DW Log Analytics Session Custom Metadata Logging
171 lines (137 loc) · 7.41 KB
/
Azure Synapse SQL DW Log Analytics Session Custom Metadata Logging
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
##Fully Functional For Azure Function
# Input bindings are passed in via param block.
param($Timer)
# Get the current universal time in the default string format.
$currentUTCtime = (Get-Date).ToUniversalTime()
# The 'IsPastDue' property is 'true' when the current function invocation is later than scheduled.
if ($Timer.IsPastDue) {
Write-Host "PowerShell timer is running late!"
}
# Write an information log with the current time.
Write-Host "PowerShell timer trigger function ran! TIME: $currentUTCtime"
try {
###Context no longer needed as we will get the Synapse SQL Pool instance name from the config parameter.###
### Set-AzContext -SubscriptionId $env:azpocsub
$SQLDW=@($env:AzureSynapse1);
##You can remove the below in Prod if you like after testing#####
Write-Host $SQLDW
Write-Host $env:dwdb1
##Write-Host $env:azpocsub
################################################
###You can use a foreach loop if there are multiple SQL DWs that require querying, you will have to set the instance and DB for every foreach call###
###The below is using managed identity of the Azure Function, ensure correct permissions is provided to the function in the GRANT VIEW DATABASE STATE TO [functionnamehere]###
###Calls to synapse DW should not incur any concurrency slots of resource usage when quiring DMVs###
$resourceURI = "https://database.windows.net/"
$tokenAuthURI = $env:MSI_ENDPOINT + "?resource=$resourceURI&api-version=2017-09-01"
$tokenResponse = Invoke-RestMethod -Method Get -Headers @{"Secret"="$env:MSI_SECRET"} -Uri $tokenAuthURI
$accessToken = $tokenResponse.access_token
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=tcp:$SQLDW,1433;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Initial Catalog=$env:dwdb1;"
$SqlConnection.AccessToken = $AccessToken
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "SELECT Count(1) AS TOTAL `
from sys.dm_pdw_exec_sessions pwsess join `
sys.dm_pdw_exec_requests pwrequ `
on pwsess.session_id=pwrequ.session_id `
where pwrequ.submit_time >= DATEADD(second,-30,getdate()) `
AND pwrequ.session_id <> session_id() "
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$dataset = New-Object System.Data.DataSet
$SqlAdapter.Fill($dataset)
$SqlConnection.Close()
$SynapseSess=($DataSet.Tables[0]).TOTAL
if ($SynapseSess -ge 1)
{
# Replace with your Workspace ID From Log Analytics
$CustomerId = $env:workspaceidsynapse1
Write-Host $CustomerId
# Replace with your Primary Key From Log Analytics
$SharedKey = $env:workspacekeysynapse
##Do not run the below write host in Prod for security reasons, here for testing purposes.
###Write-Host $SharedKey
# Specify the name of the record type that you'll be creating For This case it is Blocking which will create a blocking table in the workspace to query
$LogType = "SynapseSessionDW"
# You can use an optional field to specify the timestamp from the data. If the time field is not specified, Azure Monitor assumes the time is the message ingestion time
$TimeStampField = ""
# The below metadata will be added to the workspace if the condition is met. There is an initial check above before this section executes to not waste resources
$resourceURI = "https://database.windows.net/"
$tokenAuthURI = $env:MSI_ENDPOINT + "?resource=$resourceURI&api-version=2017-09-01"
$tokenResponse = Invoke-RestMethod -Method Get -Headers @{"Secret"="$env:MSI_SECRET"} -Uri $tokenAuthURI
$accessToken = $tokenResponse.access_token
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=tcp:$SQLDW,1433;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Initial Catalog=$env:dwdb1;"
$SqlConnection.AccessToken = $AccessToken
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "select pwsess.session_id AS [Session_Id_s], pwsess.status AS [Status_s], pwsess.Login_Name, pwsess.Login_Time, `
pwsess.Client_Id, pwsess.App_Name, pwsess.Sql_Spid, pwrequ.Request_Id AS [RequestId], pwrequ.Session_Id AS [Session_id_r], `
pwrequ.status AS [Status_r], pwrequ.start_time, pwrequ.end_time, `
pwrequ.total_elapsed_time, pwrequ.Error_Id, pwrequ.Command `
from sys.dm_pdw_exec_sessions pwsess join `
sys.dm_pdw_exec_requests pwrequ `
on pwsess.session_id=pwrequ.session_id `
where pwrequ.submit_time >= DATEADD(second,-30,getdate()) `
AND pwrequ.session_id <> session_id() "
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$dataset = New-Object System.Data.DataTable
$SqlAdapter.Fill($dataset)
$SqlConnection.Close()
###Convert the data to JSon directly and select the specific objects needed from the above query, all objects are selected in this case, but you can omit any if needed###
$SynapsePOC=$dataset | Select-Object Session_id_s, status_s, login_name, login_time, client_id , app_name, sql_spid, RequestId, Session_id_r, status_r, start_time, end_time, total_elapsed_time, error_id, command |ConvertTo-Json
# Create the function to create the authorization signature
Function Build-Signature ($customerId, $sharedKey, $date, $contentLength, $method, $contentType, $resource)
{
$xHeaders = "x-ms-date:" + $date
$stringToHash = $method + "`n" + $contentLength + "`n" + $contentType + "`n" + $xHeaders + "`n" + $resource
$bytesToHash = [Text.Encoding]::UTF8.GetBytes($stringToHash)
$keyBytes = [Convert]::FromBase64String($sharedKey)
$sha256 = New-Object System.Security.Cryptography.HMACSHA256
$sha256.Key = $keyBytes
$calculatedHash = $sha256.ComputeHash($bytesToHash)
$encodedHash = [Convert]::ToBase64String($calculatedHash)
$authorization = 'SharedKey {0}:{1}' -f $customerId,$encodedHash
return $authorization
}
# Create the function to create and post the request
Function Post-LogAnalyticsData($customerId, $sharedKey, $body, $logType)
{
$method = "POST"
$contentType = "application/json"
$resource = "/api/logs"
$rfc1123date = [DateTime]::UtcNow.ToString("r")
$contentLength = $body.Length
$signature = Build-Signature `
-customerId $customerId `
-sharedKey $sharedKey `
-date $rfc1123date `
-contentLength $contentLength `
-method $method `
-contentType $contentType `
-resource $resource
$uri = "https://" + $customerId + ".ods.opinsights.azure.com" + $resource + "?api-version=2016-04-01"
$headers = @{
"Authorization" = $signature;
"Log-Type" = $logType;
"x-ms-date" = $rfc1123date;
"time-generated-field" = $TimeStampField;
}
$response = Invoke-WebRequest -Uri $uri -Method $method -ContentType $contentType -Headers $headers -Body $body -UseBasicParsing
return $response.StatusCode
}
# Submit the data to the API endpoint
Post-LogAnalyticsData -customerId $customerId -sharedKey $sharedKey -body ([System.Text.Encoding]::UTF8.GetBytes($SynapsePOC)) -logType $logType
}
} catch {
###########Catch Exception if there is an error###########
$Exception = $_.Exception.Message
###########Send Email of the exception###########
Write-Error -Exception $Exception
} finally {
###########Close any potential open connection###########
if ($SqlConnection.State -eq 'Open') {
$SqlConnection.Close()
}
}