Hi all,
This article has been created for a customer that wants to be able to create an alert for customized thresholds for each existing server and performance counter. If there is no specific server, the alert need to use a generic threshold.
The are some ways that you can take to achieve this goal. This allows you to put all the logic in the Kusto query. The solution is composed by:
- CSV file
- PowerShell script
- Custom log table
- Amazing Log Kusto query
Those are the main steps:
- CSV file : this file will contain all the details of threshold, server name, counter name , threshold and the value .
This is an example:
Generic means that if the server name isn’t in the csv file, the value will be taken as threshold for the alert.
You can position this file where you want, in a storage account or on you filesystem, it depends where you want to run the upload script.
- PowerShell Script : we need to upload our custom threshold csv in Azure, there are many way to that, in this case we use a PowerShell script. This script take inspiration from this sample script. Azure Monitor HTTP Data Collector API - Azure Monitor | Microsoft Learn
Basically, you have three parameters that you need to set up in the script:
$CustomerId : Replace with your Workspace ID
$SharedKey : Replace with your Primary Key
$LogType : Specify the name of the custom log that you'll be creating.
$CSVPath : Path of the csv file
Another part that was modified is:
$csvs = Import-Csv -LiteralPath $CSVPath
foreach ($csv in $csvs){
$json = @"
[
{
"Computer": "$($csv.Computer)",
"CounterName": "$($csv.CounterName)",
"InstanceName": "$($csv.InstanceName)",
"value": "$($csv.value)"
}
]
"@;
In this section we parse the csv and put the value in Json and upload to Log Analytics workspace. After 10/15 minutes, you’ll find a table called “LogType_CL”.
NB. a table in log analytics has a log retention policy, basically after a number of day that you can set, the record will be deleted. Due to that, you need to run the script at least monthly (the minimum retention is 30 days). After the table’s creation you will find in the “Table” section the name of your custom table, click on Manage table and you’ll find the retention.
The retention could be extended.
- Kusto query: the query needs to put together you threshold and your live data that comes from your resources. We analyze each row separately to understand each phase of the query.
Perf // the table where our monitoring data are stored.
| summarize arg_max(TimeGenerated,*) by Computer, CounterName, InstanceName // we take the last value of all countername of the KPI (example : CPU / DISK / RAM etc.)
| join kind = leftouter ( Threshold | summarize arg_max(Date, *) by Computer,CounterName, InstanceName ) on $left.Computer==$right.Computer, $left.CounterName==$right.CounterName, $left.InstanceName == $right.InstanceName // with this join we put together the collected metrics and the threshold
| extend Computer1 = iff(isempty(Computer1), "Generic", Computer1) // the meaning of iff is like if, in this case if the field Computer1 is empty, it puts the value “Generic”
| join kind = leftouter ( Threshold | summarize arg_max(Date, *) by Computer,CounterName, InstanceName ) on $left.Computer1==$right.Computer, $left.CounterName==$right.CounterName, $left.InstanceName == $right.InstanceName | where isnotempty( Computer2) // we make another join for the generic threshold
| project TimeGenerated,Computer,CounterName, InstanceName, CounterValue,value1, test = iff(CounterValue < value1, "OK", "KO") // at the end we evaluate if the value is greater or less than threshold
Remember: in this case we are consider ALL counters that are available for the selected resources, if you want to create an alert for a specific resource you need to specify a Counter Name after the first line.
- Creation of the Alert
The last part of implementation is create the Alert. You can use this link as a general guide. Tutorial - Create a log query alert for an Azure resource - Azure Monitor | Microsoft Learn
In this case, we paste our query in LAW, and click on Create Alert
In this section you can specify what is the frequency of evaluation, basically how many time the query runs. You can keep the menu as you can see below.
Now you can follow the setup, to link an action groups and start you monitoring!
See you on the next article!