Injecting Simulated Records for the SQL Assessment Solution into an OMS Workspace

  • Article

In this post, we look at how we can leverage the OMS HTTP Collector API to introduce a Custom Log Type that simulates the record type of the SQL Assessment Solution – SQLAssessmentRecommendation, and injecting sample records for it into an OMS Workspace. With the right set of sample data and the right search queries adapted from the ones used to power the visualization of the SQL Assessment Solution, a simulated assessment and visualization result can be reconstructed in any OMS workspace for exploratory purposes. However, there are some views and dashboards with rich data visualization and contextual search capabilities used in some OMS Solutions that cannot be recreated with the View Designer at this time. These capabilities are only available and packaged with specific OMS Solutions in order to provide deeper insights pivoted around the problem areas they address.

image

Important Note:
This simulation method should only be used in test environments for demo or POC purposes as sample data SHOULD NOT be injected into a production environment and be mixed with live production data.

The OMS SQL Assessment Solution can be used to assess the risk and health of your SQL server environment and provides a prioritized list of recommendations specific to your deployed server infrastructure. The recommendations are categorized across six focus areas which help you quickly understand the risk and take action. Assessment data is collected and sent to an OMS Workspace by the OMS Agent and stored as records of type SQLAssessmentRecommendation. Here is an example of an SQLAssessmentRecommendation type record:

image 

There are 2 types of fields for this record type:

  • Type 1 Fields: Common fields:
    • SourceSystem
    • MG
    • id
    • Type
  • Type 2 Fields: Solution level fields:
    • TimeGenerated
    • AssessmentId
    • AssessmentName
    • RecommendationId
    • Recommendation
    • Description
    • RecommendationResult
    • FocusAreaId
    • FocusArea
    • ActionAreaId
    • ActionArea
    • RecommendationScore          <—double
    • RecommendationWeight        <—double
    • Computer
    • AffectedObjectType
    • AffectedObjectName
    • AffectedObjectUniqueName
    • AffectedObjectResult
    • SqlInstanceName
    • DatabaseName

Lets name the Custom Log Type that we are creating to simulate the SQLAssessmentRecommendation record type as MySQLAssessmentRecommendation.
Since the values for the Type 1 fields are automatically assigned to ALL records upon ingestion by the OMS Service, the sample records of type MySQLAssessmentRecommendation will have these fields populated as well. Hence only the Type 2 fields and their corresponding sample values are required to be added into the JSON payload for MySQLAssessmentRecommendation records to be injected into an OMS Workspace of interest.

Here is a sample PowerShell script that converts a CSV file with sample SQLAssessmentRecommendation type data into JSON data and injecting it into an OMS Workspace with the Log Analytics HTTP Data Collector API in a JSON payload. The sample data will appear as records of type MySQLAssessmentRecommendation_CL in the targeted OMS Workspace.

This PowerShell script consist of 4 main parts:

Part 1: Specify the key information for authentication, custom log type name and record time stamp.

  # Replace with your Workspace ID
 $CustomerId = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx" 
  
 # Replace with your Primary Key
 $SharedKey = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
  
 #Specify the name of the record type that we'll be creating.
 $LogType = "MySQLAssessmentRecommendation"
 
#Specify a time in the format YYYY-MM-DDThh:mm:ssZ to specify a created time for the records.
$TimeStampField = ""

Part 2: Prepare the standard functions to create the authorization signature, and to create and post the request (Copied from the snippets provided in the standard API Documentation):

 # 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
}

# 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

}

Part 3: Convert CSV data to JSON payload and convert data to correct type.
Note that the HTTP Data Collector API automatically determines the data type of the custom fields for the Custom Log Type based on their values in the JSON payload. For more information on record types and properties, refer to the API Documentation.
However, there may still be some fields in the CSV file that contain numerical values that are intended to be processed as fields with double type data but instead could be recognized as fields with string type data by the Collector API.
Hence, the while loop in the following PowerShell snippet goes through each record imported from the CSV file and converts the values of specific fields into values of type double for the Collector API to recognize their field types correctly.

 $SQLAssessmentData = Import-Csv -Path <<Path\SampleSQLAssessmentData.csv>> | select AssessmentId,AssessmentName,RecommendationId,Recommendation,Description,RecommendationResult,FocusAreaId,FocusArea,ActionAreaId,ActionArea,RecommendationScore,RecommendationWeight,Computer,AffectedObjectType,AffectedObjectName,AffectedObjectUniqueName,AffectedObjectResult,SqlInstanceName,DatabaseName

#Convert to correct types
$index = 0
$numberOfRecords = $SQLAssessmentData.Count

While($numberOfRecords -gt $index)
{
    $SQLAssessmentData[$index].RecommendationScore  = [double]$SQLAssessmentData[$index].RecommendationScore
    $SQLAssessmentData[$index].RecommendationWeight = [double]$SQLAssessmentData[$index].RecommendationWeight      
    
    $index++     
}
    $SQLAssessmentDataFixed = ConvertTo-Json -InputObject @($SQLAssessmentData)

The fields selected for the custom MySQLAssessmentRecommendation record type in order to simulate SQL Assessment Solution records in an OMS Workspace are:
AssessmentId, AssessmentName, RecommendationId, Recommendation, Description, RecommendationResult, FocusAreaId, FocusArea, ActionAreaId, ActionArea, RecommendationScore, RecommendationWeight, Computer, AffectedObjectType, AffectedObjectName, AffectedObjectUniqueName, AffectedObjectResult, SqlInstanceName, DatabaseName

Here is an example of a sample record imported from the CSV file and converted to JSON format to be submitted to the HTTP Data Collector API:

     {
        "AssessmentId":  "8ac39b27-8cff-4cff-98af-03ddb0cdb473",
        "AssessmentName":  "SQL",
        "RecommendationId":  "3ddb789f-c69e-4548-8703-4b52b238dd42",
        "Recommendation":  "Enable Remote Desktop on virtual machines.",
        "Description":  "One or more server instances are not configured to allow Remote Desktop connections. Without a Remote Desktop connection  you will be unable to connect
 to and manage Microsoft Azure-hosted virtual machines.",
        "RecommendationResult":  "Passed",
        "FocusAreaId":  "76f7f2bc-3fad-4e78-89ef-c1d2b03ef89b",
        "FocusArea":  "Operations and Monitoring",
        "ActionAreaId":  "3b2ec63a-1f31-4f3b-bc68-6329b3efab83",
        "ActionArea":  "Routine Maintenance",
        "RecommendationScore":  63,
        "RecommendationWeight":  0.1586902,
        "Computer":  "MyDBServer.contoso.com",
        "AffectedObjectType":  "Microsoft.Windows.Computer",
        "AffectedObjectName":  "MyDBServer.CONTOSO.COM",
        "AffectedObjectUniqueName":  "Server_MyDBServer.CONTOSO.COM",
        "AffectedObjectResult":  "Passed",
        "SqlInstanceName":  "",
        "DatabaseName":  ""
    },

Part 4: Submit the data to the API endpoint.

 
# Submit the data to the API endpoint
Post-OMSData -customerId $customerId -sharedKey $sharedKey -body ([System.Text.Encoding]::UTF8.GetBytes($SQLAssessmentDataFixed)) -logType $logType

So, if Part 1 + Part 2 + Part 3 + Part 4 of the sample PowerShell script returns a Status Code of 202, it means that the JSON Payload has been accepted by the HTTP Data Collector API and the simulated records should appear in the targeted OMS Workspace within a few minutes time.
To list all records of the MySQLAssessmentRecommendation type that were successfully injected into a targeted workspace, run the Type=MySQLAssessmentRecommendation_CL search query. The records returned from the search result should appear as follows with the field data types identified accordingly as indicated by their suffix:

image

With these sample records, we can view all recommendations using the OMS log search, correlate them with other existing record types, or create custom views and dashboards of a simulated SQL Assessment Solution. Here is an example of a dashboard created with the View Designer using log search queries adapted from the actual SQL Assessment Solution :

image

Notice the cool tooltip feature where the full recommendation name appears when hovering the pointer above the line item in the List view.
Here are the queries for each visualization and navigation component of each View in the dashboard above:

 ##Security and Compliance
#Donut Query
Type=MySQLAssessmentRecommendation_CL TimeGenerated>=NOW-7DAYS FocusAreaId_g="f14449df-1309-43e6-8f02-2d373d89daa8"|  Measure Countdistinct(RecommendationId_g) by RecommendationResult_s
#List Query
Type=MySQLAssessmentRecommendation_CL TimeGenerated>=NOW-7DAYS FocusAreaId_g="f14449df-1309-43e6-8f02-2d373d89daa8" RecommendationResult_s=Failed | Measure Countdistinct(RecommendationId_g) by RecommendationScore_d,Recommendation_s | sort RecommendationScore_d desc
#Navigation Query
Type=SQLAssessmentRecommendation_CL TimeGenerated>=NOW-7DAYS FocusAreaId_g="f14449df-1309-43e6-8f02-2d373d89daa8"| Select RecommendationScore_d,Recommendation_s | sort RecommendationScore_d desc

##Availability and Business Continuity
#Donut Query
Type=MySQLAssessmentRecommendation_CL TimeGenerated>=NOW-7DAYS FocusAreaId_g="d617a649-5756-40a5-ac41-481e66e6200b"|  Measure Countdistinct(RecommendationId_g) by RecommendationResult_s
#List Query
Type=MySQLAssessmentRecommendation_CL TimeGenerated>=NOW-7DAYS FocusAreaId_g="d617a649-5756-40a5-ac41-481e66e6200b" RecommendationResult_s=Failed | Measure Countdistinct(RecommendationId_g) by RecommendationScore_d,Recommendation_s | sort RecommendationScore_d desc
#Navigation Query
Type=SQLAssessmentRecommendation_CL TimeGenerated>=NOW-7DAYS FocusAreaId_g="d617a649-5756-40a5-ac41-481e66e6200b"| Select RecommendationScore_d,Recommendation_s | sort RecommendationScore_d desc

##Performance and Scalability
#Donut Query
Type=MySQLAssessmentRecommendation_CL TimeGenerated>=NOW-7DAYS FocusAreaId_g="13a1c8d4-aa8e-4c2d-96e4-50d2e4ce9849"|  Measure Countdistinct(RecommendationId_g) by RecommendationResult_s
#List Query
Type=MySQLAssessmentRecommendation_CL TimeGenerated>=NOW-7DAYS FocusAreaId_g="13a1c8d4-aa8e-4c2d-96e4-50d2e4ce9849" RecommendationResult_s=Failed | Measure Countdistinct(RecommendationId_g) by RecommendationScore_d,Recommendation_s | sort RecommendationScore_d desc
#Navigation Query
Type=SQLAssessmentRecommendation_CL TimeGenerated>=NOW-7DAYS FocusAreaId_g="13a1c8d4-aa8e-4c2d-96e4-50d2e4ce9849"| Select RecommendationScore_d,Recommendation_s | sort RecommendationScore_d desc

##Upgrade, Migration and Deployment
#Donut Query
Type=MySQLAssessmentRecommendation_CL TimeGenerated>=NOW-7DAYS FocusAreaId_g="6c3d7177-68b0-4283-89da-43c8d1364324"|  Measure Countdistinct(RecommendationId_g) by RecommendationResult_s
#List Query
Type=MySQLAssessmentRecommendation_CL TimeGenerated>=NOW-7DAYS FocusAreaId_g="6c3d7177-68b0-4283-89da-43c8d1364324" RecommendationResult_s=Failed | Measure Countdistinct(RecommendationId_g) by RecommendationScore_d,Recommendation_s | sort RecommendationScore_d desc
#Navigation Query
Type=SQLAssessmentRecommendation_CL TimeGenerated>=NOW-7DAYS FocusAreaId_g="6c3d7177-68b0-4283-89da-43c8d1364324"| Select RecommendationScore_d,Recommendation_s | sort RecommendationScore_d desc

##Operations and Monitoring
#Donut Query
Type=MySQLAssessmentRecommendation_CL TimeGenerated>=NOW-7DAYS FocusAreaId_g="76f7f2bc-3fad-4e78-89ef-c1d2b03ef89b"|  Measure Countdistinct(RecommendationId_g) by RecommendationResult_s
#List Query
Type=MySQLAssessmentRecommendation_CL TimeGenerated>=NOW-7DAYS FocusAreaId_g="76f7f2bc-3fad-4e78-89ef-c1d2b03ef89b" RecommendationResult_s=Failed | Measure Countdistinct(RecommendationId_g) by RecommendationScore_d,Recommendation_s | sort RecommendationScore_d desc
#Navigation Query
Type=SQLAssessmentRecommendation_CL TimeGenerated>=NOW-7DAYS FocusAreaId_g="76f7f2bc-3fad-4e78-89ef-c1d2b03ef89b"| Select RecommendationScore_d,Recommendation_s | sort RecommendationScore_d desc

##Change and Configuration Management
#Donut Query
Type=MySQLAssessmentRecommendation_CL TimeGenerated>=NOW-7DAYS FocusAreaId_g="dbc43886-cf26-483e-ac46-030a002a50ea"|  Measure Countdistinct(RecommendationId_g) by RecommendationResult_s
#List Query
Type=MySQLAssessmentRecommendation_CL TimeGenerated>=NOW-7DAYS FocusAreaId_g="dbc43886-cf26-483e-ac46-030a002a50ea" RecommendationResult_s=Failed | Measure Countdistinct(RecommendationId_g) by RecommendationScore_d,Recommendation_s | sort RecommendationScore_d desc
#Navigation Query
Type=SQLAssessmentRecommendation_CL TimeGenerated>=NOW-7DAYS FocusAreaId_g="dbc43886-cf26-483e-ac46-030a002a50ea"| Select RecommendationScore_d,Recommendation_s | sort RecommendationScore_d desc

Cleaning Up:

To clean up the Custom Fields of this Custom Log Type from an OMS Workspace, go to Settings –> Data –> Custom Fields –> Manage custom fields, and click Remove to remove them:
image

 

Additional Resources:

Log Analytics HTTP Data Collector API by Brian Wren
https://azure.microsoft.com/en-us/documentation/articles/log-analytics-data-collector-api/

HTTP Data Collector API: Send us data from space… or anywhere! by Evan Hissey
https://blogs.technet.microsoft.com/msoms/2016/08/30/http-data-collector-api-send-us-data-from-space-or-anywhere/

Optimize your environment with the SQL Assessment solution in Log Analytics by Bill Anderson
https://azure.microsoft.com/en-us/documentation/articles/log-analytics-sql-assessment/

 

 

Disclaimer:
All information on this blog is provided on an as-is basis with no warranties and for informational purposes only. Use at your own risk. The opinions and views expressed in this blog are those of the author and do not necessarily state or reflect those of my employer.