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:

| 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:

| 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:


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:




I hope this Azure Management trick was useful for you!

4 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: Logo

You are commenting using your 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.