Best practices for queries used in log alerts rules

Queries can start with either a table name like “search” or “union *” operators. These commands are useful during data exploration and for searching terms over the entire data model. However, these operators are not efficient for productization in alerts. Log alerts rules queries in Log Analytics and Application Insights should always start with table(s), this is to define a clear scope for the query execution to specific table(s). It also improves both query performance and relevance of the results. You can learn more by visiting our documentation, “Query best practices.”

Note that using cross-resource queries in log alerts rules is not considered inefficient although “union” operator is used. The “union” in cross-resource queries is scoped to specific resources and tables as shown in this example, while the query scope for “union *” is the entire data model.

Union

app('Contoso-app1').requests,

app('Contoso-app2').requests,

workspace('Contoso-workspace1').Perf

After data exploration and query authoring, you may want to create a log alert using that query. These examples show how you can modify queries and avoid “search” and “union *” commands.

Example 1

You want to create log alert on the following query.

search ObjectName == 'Memory' and (CounterName == '% Committed Bytes In Use' or CounterName == '% Used Memory') and TimeGenerated > ago(5m)

| summarize Avg_Memory_Usage =avg(CounterValue) by Computer

| where Avg_Memory_Usage between(90 .. 95)

| count

To author a valid alert query without the use of “search” operator, follow these steps:

1. Identify the table that the properties are hosted in.

search ObjectName == 'Memory' and (CounterName == '% Committed Bytes In Use' or CounterName == '% Used Memory')

| summarize by $table

The result indicates that these properties belong to Perf table.

 

 

 

2. Since the properties used in the query are from Perf table, the query should start with it and scope the query execution to that table.

Perf

| where ObjectName == 'Memory' and (CounterName == '% Committed Bytes In Use' or CounterName == '% Used Memory') and TimeGenerated > ago(5m)

| summarize Avg_Memory_Usage=avg(CounterValue) by Computer

| where Avg_Memory_Usage between(90 .. 95)

| count

Example 2

You want to create log alert on the following query.

search (ObjectName == 'Processor' and CounterName == '% Idle Time' and InstanceName == '_Total')

| where Computer !in ((union * | where CounterName == '% Processor Utility' | summarize by Computer)) | summarize Avg_Idle_Time = avg(CounterValue) by Computer, CounterPath | where Avg_Idle_Time < 5 | count

To modify the query, follow these steps:

1. Since the query makes a use of both “search” and “union *” operators, you need to identify the tables hosting the properties in two stages.

search (ObjectName == 'Processor' and CounterName == '% Idle Time' and InstanceName == '_Total')

| summarize by $table

The properties of the first part of the query belong to Perf table.

 

 

 

Note, the “withsource = table” command adds a column that designates the table name that hosts the property.

union withsource = table * | where CounterName == '% Processor Utility'

| summarize by table

The properties of the second part of the query also belong to Perf table.

 

2. Since the properties used in the query are from Perf table, both outer and inner queries start with Perf table and scope the query execution to that table.

Perf

| where ObjectName == 'Processor' and CounterName == '% Idle Time' and InstanceName == '_Total'

| where Computer !in ((Perf | where CounterName == '% Processor Utility' | summarize by Computer))

| summarize Avg_Idle_Time = avg(CounterValue) by Computer, CounterPath

| where Avg_Idle_Time < 5

| count

Source: Azure Blog Feed