[Powershell + OMS HTTP Data Collection API] - Get & Send data from a MS-SQLServer to OMS Log Analytics

Earlier in my post about using the OMS Data Collector API, i promissed that i would post something about it but in a Powershell way of doing it - today is the day.
Somewhere in the past i had the need to put some data stored in a on-prem database to OMS so we could use it's own Analysis engine.
So, obviously, from where you run this, you need to have internet connection or configured HTTP proxy - just sayin'! :)

In my case i needed to have a timestamp stored for the last time i queried data so i don't have duplicated data in OMS, so analysis could be more accurate - this Powershell script version doesn't contain that logical part of code, it's more raw so you could understand how it's done and after it, you could obvisouly change it's logic as i did, or add some other logic.

Before we go to the script itself, let me just explain the part of how we store data and then convert it to JSON.

As you might see i store data from MSSQL in a 'System.Data.DataTable' (variable $table).
So the right to way to change it to JSON is making - $table | select $table.Columns.columnname | ConvertTo-Json
As so, you'll have data in the above and correct format :

{
"property1": "value1",
"property2": "value2"
"property3": "value3",
"property4": "value4"
}

Where "property1", "property2" and so on is your table column names. This ways you only have to concern about the query fields you want/like.

So, this is the powershell i came up with - it's been great to have something like this to do some magic.

# Database
$SQLServer = "YOUR_MSSQL_SERVER"
$SQLDBName = "YOUR_DATABASE"
$SqlQuery = "YOUR_QUERY"

#OMS
$customerId = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
$SharedKey = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
$dataType = "YOUR_OMS_CUSTOM_LOG_FILE_NAME"
# Function to create the authorization signature
Function New-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 -TypeName System.Security.Cryptography.HMACSHA256
  $sha256.Key = $keyBytes
  $calculatedHash = $sha256.ComputeHash($bytesToHash)
  $encodedHash = [Convert]::ToBase64String($calculatedHash)
  $authorization = 'SharedKey {0}:{1}' -f $customerId, $encodedHash
  return $authorization
}
#Send data to OMS
Function Send-OMSData($customerId, $sharedKey, $body, $logType)
{
 $method = 'POST'
  $contentType = 'application/json'
  $resource = '/api/logs'
  $rfc1123date = [DateTime]::UtcNow.ToString('r')
  $contentLength = $body.Length
  $signature = New-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
  }

  $response = Invoke-WebRequest -Uri $uri -Method $method -ContentType $contentType -Headers $headers -Body $body -UseBasicParsing
  return $response.StatusCode
}

Try {
    $connString = "Data Source=$SQLServer;Initial Catalog=$SQLDBName;user=YOUR_SQL_SERVER_USERNAME;password=YOUR_SQL_SERVER_PASSWORD" 
    $connection = New-Object System.Data.SqlClient.SqlConnection($connString)
    $connection.Open()
    $sqlcmd = $connection.CreateCommand()
    $sqlcmd.CommandText = $SqlQuery
    $results = $sqlcmd.ExecuteReader()
    $table = new-object “System.Data.DataTable”
    $table.Load($results)
    $connection.Close()
}

Catch [Exception] {
Write-Host $_.exception.message
    exit
}
$payload = $table | select $table.Columns.columnname | ConvertTo-Json
#send Data to OMS
Send-OMSData -customerId $customerId -sharedKey $sharedKey -body ([System.Text.Encoding]::UTF8.GetBytes($payload)) -logType $dataType

Hope you all enjoy and give it some use.

Cheers,