Aggregate on More Than One Column for Azure Log Search Alerts


Log Analytics alerts aka Azure Log Search Alerts allows you to create a single alert and trigger alert instances per a column. This is possible by creating metric measurement alert. Unfortunately these types of alerts also have a limitation which might be blocking in certain situation. The classical example for that limitation is to create a single alert that will separate instance for free disk space threshold for each computer and disk. The query representation of that looks like this:

Perf
| where ObjectName == "LogicalDisk" or ObjectName == "Logical Disk"
| where CounterName == "% Free Space" 
| where InstanceName <> "_Total" 
| summarize AggregatedValue =  avg(CounterValue) by Computer, InstanceName, bin(TimeGenerated, 5m)  | render timechart

but as you can see we aggregating on more than one field – Computer and InstanceName and such alert if created will not function properly. Basically will ignore the InstanceName column and produce incorrect results.
Fear not because of the power of Log Analytics query language there is a workaround. What we can do is basically merge Computer and InstanceName columns into one and aggregated on that merged column. This is easy done by the extend operator. The query we will have will look like this:

Perf
| where ObjectName == "LogicalDisk" or ObjectName == "Logical Disk"
| where CounterName == "% Free Space" 
| where InstanceName <> "_Total" 
| extend ComputerDrive = strcat(Computer, ' - ', InstanceName)
| summarize AggregatedValue = avg(CounterValue) by ComputerDrive, bin(TimeGenerated, 5m)  

After that we can proceed with creating the alert with that query:

alert

Note the alert is configured intentionally with such threshold to generate alerts just for demo purposes.

Once the alerts are triggered as you can see via the e-mails below that separate e-mails are generated for each computer and drive:

alert1

alert2

alert3

I hope this Azure Management trick was useful for you!

3 thoughts on “Aggregate on More Than One Column for Azure Log Search Alerts

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.