Azure DB sync logs in log analytics workspace

Question

I have an Azure SQL database sync group that is scheduled to run each hour. Question: Can i sent this logs to a log analytics workspace by enabling the diagnostic settings? If yes, what will be the best way to filter them out?

I can successfully get the logs from powershell, but my end goal here is to create an alert based on the sync logs.

Thanks you in advance!

Jim Xu Jim Xu · Accepted Answer · 2020-12-10T08:02:36

If you want to send Azure SQL database sync group to a log analytics workspace, you can implement it with HTTP Data Collector API.

For example

$SubscriptionId = "SubscriptionId" 
$DS_ResourceGroupName = ""
$DS_ServerName =  "" 
$DS_DatabaseName = "" 
$DS_SyncGroupName = "" 


# Replace with your OMS Workspace ID
$CustomerId = "OMSCustomerID"  

# Replace with your OMS 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"

Connect-AzureRmAccount
select-azurermsubscription -SubscriptionId $SubscriptionId
#get log
$endtime =[System.DateTime]::UtcNow
$StartTime = ""

$Logs = Get-AzureRmSqlSyncGroupLog -ResourceGroupName $DS_ResourceGroupName `
                                                  -ServerName $DS_ServerName `
                                                  -DatabaseName $DS_DatabaseName `
                                                  -SyncGroupName $DS_SyncGroupName `
                                                  -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 $DS_ResourceGroupName -MemberType NoteProperty
    $Log | Add-Member -Name "ServerName" -Value $DS_ServerName -MemberType NoteProperty
    $Log | Add-Member -Name "HubDatabaseName" -Value $DS_DatabaseName -MemberType NoteProperty
    $Log | Add-Member -Name "SyncGroupName" -Value $DS_SyncGroupName -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         
"@
}
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 `
        -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

}

After doing that, you can alert vai log search in Azure log analysis. For more detail, please refer to the blog.

Azure DB sync logs in log analytics workspace

1 Answers