Best practices for queries used in log alert rules

Queries can start with a table name like SecurityEvent and Perf, or with “search” and “union” operators that can provide a multi-table/multi-column search experience. These operators are useful during data exploration and for searching terms in the entire data model. However, these operators are not efficient for productization in alerts. Log alert rule queries in Log Analytics and Application Insights should always start with a table to define a clear scope for the query execution. It improves both query performance and the relevance of the results.

Note that using cross-resource queries in log alert 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 this query. These examples show how you can modify your queries and avoid “search” and “union *” operators.

Example 1

You want to create a 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 the Perf table.

2. Since the properties used in the query are from the 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 a 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 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 the 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 property in the second part of the query also belongs to the Perf table.

2. Since the properties used in the query are from the Perf table, both outer and inner queries shoud start with it and scope the query execution to this 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