-
Notifications
You must be signed in to change notification settings - Fork 8.8k
/
DataSyncLogPowerShellRunbook.ps1
214 lines (177 loc) · 7.98 KB
/
DataSyncLogPowerShellRunbook.ps1
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
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
#Requires -Modules @{ ModuleName="Az.Sql"; ModuleVersion="3.5.1" }
#Data Sync OMS Integration Runbook
#To use this script Change all the strings below to reflect your information.
#Setup a System Managed Identity in the Automation Account, with Reader permissions to the monitored SQL targets
#Also allow the managed identity access to the Automation Account itself to read/write the Variable used
#Information for Sync Group 1
#If you want to use all the sync groups in your subscription keep the $DS_xxxx fields empty.
#If you want to use all sync groups in a Resource Group define the $DS_ResourceGroupName.
#If you want to use all sync groups in a Server define $DS_ResourceGroupName and $DS_ServerName.
#If you want to use all sync groups in a Database define $DS_ResourceGroupName, $DS_ServerName and $DS_DatabaseName.
#If you want to use a specific sync group define $DS_ResourceGroupName, $DS_ServerName, $DS_DatabaseName and $DS_SyncGroupName.
$SubscriptionId = "SubscriptionId"
$DS_ResourceGroupName = ""
$DS_ServerName = ""
$DS_DatabaseName = ""
$DS_SyncGroupName = ""
# Insert your Automation Account ResourceGroup and Account names
$AC_ResourceGroupName = "ResourceGroupName"
$AC_AccountName = "AutomationAccountName"
# Insert the name of the DateTime variable in the Automation Account for storing the last synctime
$AC_LastUpdatedTimeVariableName = "DataSyncLogLastUpdatedTime"
# Replace with your OMS Workspace ID (Log Analytics Workspace ID)
$CustomerId = "OMSCustomerID"
# Replace with your OMS Primary Key (Log Analytics Primary Key)
$SharedKey = "SharedKey"
# Specify the name of the record type that you'll be creating
$LogType = "DataSyncLog"
# Specify a field with the created time for the records
$TimeStampField = "DateValue"
# Ensures you do not inherit an AzContext in your runbook
Disable-AzContextAutosave -Scope Process
# Connect to Azure with system-assigned managed identity
$AzureContext = (Connect-AzAccount -Identity).context
# set and store context
$AzureContext = Set-AzContext -SubscriptionName $AzureContext.Subscription -DefaultProfile $AzureContext
# 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-OMSData($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 `
-fileName $fileName `
-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
}
#Get Log Data
#Set the endtime, get StartTime-filter from Automation Account variable
$endtime =[System.DateTime]::UtcNow
$StartTime = Get-AzAutomationVariable -ResourceGroupName $AC_ResourceGroupName `
–AutomationAccountName $AC_AccountName `
-Name $AC_LastUpdatedTimeVariableName | Select -ExpandProperty Value
#Get Log
Write-Output "Getting Data Sync Log from $StartTime to $EndTime"
if ($DS_ResourceGroupName -eq "")
{
$ResourceGroupName = Get-AzResourceGroup | select -ExpandProperty ResourceGroupName
}
else
{
$ResourceGroupName = $DS_ResourceGroupName
}
foreach ($ResourceGroup in $ResourceGroupName)
{
if ($DS_ServerName -eq "")
{
$ServerName = Get-AzSqlServer -ResourceGroupName $ResourceGroup | select -ExpandProperty ServerName
}
else
{
$ServerName = $DS_ServerName
}
foreach ($Server in $ServerName)
{
if ($DS_DatabaseName -eq "")
{
$DatabaseName = Get-AzSqlDatabase -ResourceGroupName $ResourceGroup -ServerName $Server | select -ExpandProperty DatabaseName
}
else
{
$DatabaseName = $DS_DatabaseName
}
foreach ($Database in $DatabaseName)
{
if ($Database -eq "master")
{
continue;
}
if ($DS_SyncGroupName -eq "")
{
$SyncGroupName = Get-AzSqlSyncGroup -ResourceGroupName $ResourceGroup -ServerName $Server -DatabaseName $Database | select -ExpandProperty SyncGroupName
}
else
{
$SyncGroupName = $DS_SyncGroupName
}
foreach ($SyncGroup in $SyncGroupName)
{
$Logs = Get-AzSqlSyncGroupLog -ResourceGroupName $ResourceGroup `
-ServerName $Server `
-DatabaseName $Database `
-SyncGroupName $SyncGroup `
-starttime $StartTime `
-endtime $EndTime;
if ($Logs.Length -gt 0)
{
foreach ($Log in $Logs)
{
$Log | Add-Member -Name "SubscriptionId" -Value $SubscriptionId -MemberType NoteProperty
$Log | Add-Member -Name "ResourceGroupName" -Value $ResourceGroup -MemberType NoteProperty
$Log | Add-Member -Name "ServerName" -Value $Server -MemberType NoteProperty
$Log | Add-Member -Name "HubDatabaseName" -Value $Database -MemberType NoteProperty
$Log | Add-Member -Name "SyncGroupName" -Value $SyncGroup -MemberType NoteProperty
#Filter out Successes to Reduce Data Volume to OMS
#Include the 5 commented out line below to enable the filter
#For($i=0; $i -lt $Log.Length; $i++ ) {
# if($Log[$i].LogLevel -eq "Success") {
# $Log[$i] =""
# }
# }
}
$json = ConvertTo-JSON $logs
$result = Post-OMSData -customerId $customerId -sharedKey $sharedKey -body ([System.Text.Encoding]::UTF8.GetBytes($json)) -logType $logType
if ($result -eq 200)
{
Write-Host "Success"
}
if ($result -ne 200)
{
throw
@"
Posting to OMS Failed
Runbook Name: DataSyncOMSIntegration
"@
}
}
}
}
}
}
# Write runtime into Automation Account variable
Set-AzAutomationVariable -ResourceGroupName $AC_ResourceGroupName `
–AutomationAccountName $AC_AccountName `
-DefaultProfile $AzureContext `
-Name $AC_LastUpdatedTimeVariableName `
-Value $EndTime `
-Encrypted $False