Exploring Anomalies with Log Analytics using KQL

Published Apr 05 2021 06:00 AM 3,670 Views
Microsoft

Detecting anomalies in your data can be a very powerful and desired functionality. Within Azure Monitor we provide a really easy method to alert on Anomalies if they are coming from Metrics (Creating Alerts with Dynamic Thresholds in Azure Monitor - Azure Monitor | Microsoft Docs). But what if the anomalies you want to detect are not a metric but sit in Application Insights or Log Analytics. Well, that’s where the Kusto query language comes to the rescue.

 

Detecting Anomalies with Kusto

Kusto has anomaly detection built in using series_decompose_anomalies.

 

series_decompose_anomalies() - Azure Data Explorer | Microsoft Docs

 

Now I’m not going to lie, the first time I read the above article I came away a little confused. But once you’ve built a query a few time using this then it becomes fairly simple.

 

Some of the key things you need to do to utilize this is:

  • You need to pull the data that you want to detect anomalies on
  • You need to order the results.
  • You need to then create either a list or series before you use the series_decompose_anomalies
  • Create a new column that detect the anomalies.

I think the best way to show this is to walk through a scenario.

 

Scenario

We want to look at the number of events occurring on each of our servers in the System event log. We want to detect any anomalies where more events than normal happen on a server. To make this query even more useful we’ll take the list of servers that have had anomalies and chart them by eventid.

 

Step 1: Pulling the Data

Step one is to get the data that you want to detect anomalies on. What the below query will do is filter to only event in the “System” log and then create a count of events for each server in 30 minute aggregates.

 

Event
| where TimeGenerated >= ago(7d)
| where EventLog == 'System'
| summarize EventCount=count() by Computer, bin(TimeGenerated,30m)

 

So the output from just this query would look something like this:

bwatts670_0-1617478804224.png

 

Step 2: Order the results

Before we create a list or series we need to order the results by the time generated. This is the simplest step but essential if you want accurate results! Just add the following line to your query:

 

| order by TimeGenerated

 

So now our query looks like this.

 

Event
| where TimeGenerated >= ago(7d)
| where EventLog == 'System'
| summarize EventCount=count() by Computer, bin(TimeGenerated,30m)
| order by TimeGenerated

 

Step 3: Make a List

Now we have everything ready to create the list in Kusto. Below is the line you need to add to your query. This will make a list of both the TimeGenerated field and the EventCount field. So what we’ll end up with is a single line for each server with a list of the TImeGenerated and EventCount fields.

 

| summarize EventCount=make_list(EventCount),TimeGenerated=make_list(TimeGenerated) by Computer

 

So the query looks like this

 

Event
| where TimeGenerated >= ago(7d)
| where EventLog == 'System'
| summarize EventCount=count() by Computer, bin(TimeGenerated,30m)
| order by TimeGenerated
| summarize EventCount=make_list(EventCount),TimeGenerated=make_list(TimeGenerated) by Computer

 

And the output should look something like this:

bwatts670_1-1617478804229.png

 

Step 4: Detect Anomalies

We have our query setup to detect the anomalies, we just need to pass the “EventCount” field to series_decompose_anomalies and create a new column with the results:

 

| extend outliers=series_decompose_anomalies(EventCount)

 

So the outliers filed will contain 3 values

              0=Normal

              -1=less events than normal

              1=more events than normal

So our query would now look like this:

 

Event
| where TimeGenerated >= ago(7d)
| where EventLog == 'System'
| summarize EventCount=count() by Computer, bin(TimeGenerated,30m)
| order by TimeGenerated
| summarize EventCount=make_list(EventCount),TimeGenerated=make_list(TimeGenerated) by Computer
| extend outliers=series_decompose_anomalies(EventCount)

 

And the output should look something like this:

bwatts670_2-1617478804235.png

As you can see, we have some anomalies that got detected.

 

Extra Credit

When I get to this point I always like to look at where the anomalies were detected and make sure that I would consider them anomalies. I won’t go into it in this article but you can adjust how sensitive the calculations. For me to easily see where the anomalies were detected I’ll use mvexpand on any of the list that we’ve made:

 

| mv-expand TimeGenerated, EventCount, outliers

 

Then look at the results to make sure where I see a 1 (we’ll later filter to only positive anomalies) it makes sense:

bwatts670_3-1617478804240.png

 

I’d also check on the ones that stay at 0 to make sure the EventCounts are pretty close:

bwatts670_4-1617478804244.png

 

Step 5: Get Useful Information

Not saying what we have right now isn’t useful but I like to use the anomalies info to get more detailed information. So in this case we detected anomalies for the number of events on a server. Now that we know the server had an anomaly maybe we want to graph that by the EventId to determine what caused the anomaly. Lets start by expanding the list like before but then filtering to only outliers that equal 1

 

| mv-expand TimeGenerated, EventCount, outliers
| where outliers == 1

 

This will give us every outlier detected by each server. What I really need is a list of each server that has had at least one anomaly. So I just at the following:

 

| distinct Computer

 

I want to feed the results into another query so let me set a variable called “Computers” using the let command. So our completed initial query looks like this:

 

let Computers=Event
| where TimeGenerated >= ago(7d)
| where EventLog == 'System'
| summarize EventCount=count() by Computer, bin(TimeGenerated,30m)
| order by TimeGenerated
| summarize EventCount=make_list(EventCount),TimeGenerated=make_list(TimeGenerated) by Computer
| extend outliers=series_decompose_anomalies(EventCount)
| mv-expand TimeGenerated, EventCount, outliers
| where outliers == 1
| distinct Computer;

 

So now I can in my next query I can filter down to just these computers using “| where Computer in (Computers)” where Computers is fed in from the above query.

 

….(prev query)
Event
| where TimeGenerated >= ago(7d)
| where EventLog == 'System'
| where Computer in (Computers)
| extend ChartName = strcat(Computer,'-',EventID)
| summarize EventCount=count() by ChartName,bin(TimeGenerated, 30m)
| render timechart

 

The only thing different I did here is create a “ChartName” field that is a combination of the Computer and the EventID. I’m using the same time period (7d) and the same aggregation (30m) for both the anomaly detection and this second query. That way you’re looking at the same data for both.

 

Below is the complete query:

 

 

let Computers=Event
| where TimeGenerated >= ago(7d)
| where EventLog == 'System'
| summarize EventCount=count() by Computer, bin(TimeGenerated,30m)
| order by TimeGenerated
| summarize EventCount=make_list(EventCount),TimeGenerated=make_list(TimeGenerated) by Computer
| extend outliers=series_decompose_anomalies(EventCount)
| mv-expand TimeGenerated, EventCount, outliers
| where outliers == 1
| distinct Computer;
Event
| where TimeGenerated >= ago(7d)
| where EventLog == 'System'
| where Computer in (Computers)
| extend ChartName = strcat(Computer,'-',EventID)
| summarize EventCount=count() by ChartName,bin(TimeGenerated, 30m)
| render timechart

 

 Here are the results in my environment.

 

bwatts670_5-1617478804259.png

 

 

I just have one server in my environment so charting it makes sense. I would probably analyze the data in a table if I had a bunch of servers. But you can see really quickly I can see that EventID 7036 is the one that is causing the anomalies for this server. The rest of the events are staying stable, but that event varies a good bit on the server.

 

If you’re interested in another scenario where this same process can be useful check out my previous blog about “Detecting Azure Cost Anomalies.”

 

Summary

Once you get the hand of “series_decompose_anomalies” it can be a very useful tool in your toolbelt. I covered using this to visualize anomalies in the number of events occurring in the System event log. As long as you can follow the steps above (Get the data you want, order the data, make a list or series, and then detect anomalies) you can explore your data for anomalies. Just like we can visualize the data we can also setup alerts through Azure Monitor.

1 Comment
Senior Member

Very, very good.

%3CLINGO-SUB%20id%3D%22lingo-sub-2252477%22%20slang%3D%22en-US%22%3EExploring%20Anomalies%20with%20Log%20Analytics%20using%20KQL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2252477%22%20slang%3D%22en-US%22%3E%3CP%3EDetecting%20anomalies%20in%20your%20data%20can%20be%20a%20very%20powerful%20and%20desired%20functionality.%20Within%20Azure%20Monitor%20we%20provide%20a%20really%20easy%20method%20to%20alert%20on%20Anomalies%20if%20they%20are%20coming%20from%20Metrics%20(%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-monitor%2Falerts%2Falerts-dynamic-thresholds%23%3A~%3Atext%3DDynamic%2520Thresholds%2520alerts%2520rule%2520can%2520create%2520tailored%2520thresholds%2Clower%2520threshold%2520(default)%2520Greater%2520than%2520the%2520upper%2520threshold%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ECreating%20Alerts%20with%20Dynamic%20Thresholds%20in%20Azure%20Monitor%20-%20Azure%20Monitor%20%7C%20Microsoft%20Docs%3C%2FA%3E).%20But%20what%20if%20the%20anomalies%20you%20want%20to%20detect%20are%20not%20a%20metric%20but%20sit%20in%20Application%20Insights%20or%20Log%20Analytics.%20Well%2C%20that%E2%80%99s%20where%20the%20Kusto%20query%20language%20comes%20to%20the%20rescue.%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId--495652297%22%20id%3D%22toc-hId--495590736%22%20id%3D%22toc-hId--495590736%22%20id%3D%22toc-hId--495590736%22%20id%3D%22toc-hId--495590736%22%20id%3D%22toc-hId--495590736%22%3E%26nbsp%3B%3C%2FH2%3E%0A%3CH2%20id%3D%22toc-hId-1991860536%22%20id%3D%22toc-hId-1991922097%22%20id%3D%22toc-hId-1991922097%22%20id%3D%22toc-hId-1991922097%22%20id%3D%22toc-hId-1991922097%22%20id%3D%22toc-hId-1991922097%22%3EDetecting%20Anomalies%20with%20Kusto%3C%2FH2%3E%0A%3CP%3EKusto%20has%20anomaly%20detection%20built%20in%20using%20series_decompose_anomalies.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fseries-decompose-anomaliesfunction%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Eseries_decompose_anomalies()%20-%20Azure%20Data%20Explorer%20%7C%20Microsoft%20Docs%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENow%20I%E2%80%99m%20not%20going%20to%20lie%2C%20the%20first%20time%20I%20read%20the%20above%20article%20I%20came%20away%20a%20little%20confused.%20But%20once%20you%E2%80%99ve%20built%20a%20query%20a%20few%20time%20using%20this%20then%20it%20becomes%20fairly%20simple.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESome%20of%20the%20key%20things%20you%20need%20to%20do%20to%20utilize%20this%20is%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EYou%20need%20to%20pull%20the%20data%20that%20you%20want%20to%20detect%20anomalies%20on%3C%2FLI%3E%0A%3CLI%3EYou%20need%20to%20order%20the%20results.%3C%2FLI%3E%0A%3CLI%3EYou%20need%20to%20then%20create%20either%20a%20list%20or%20series%20before%20you%20use%20the%20series_decompose_anomalies%3C%2FLI%3E%0A%3CLI%3ECreate%20a%20new%20column%20that%20detect%20the%20anomalies.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3EI%20think%20the%20best%20way%20to%20show%20this%20is%20to%20walk%20through%20a%20scenario.%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId-184406073%22%20id%3D%22toc-hId-184467634%22%20id%3D%22toc-hId-184467634%22%20id%3D%22toc-hId-184467634%22%20id%3D%22toc-hId-184467634%22%20id%3D%22toc-hId-184467634%22%3E%26nbsp%3B%3C%2FH2%3E%0A%3CH2%20id%3D%22toc-hId--1623048390%22%20id%3D%22toc-hId--1622986829%22%20id%3D%22toc-hId--1622986829%22%20id%3D%22toc-hId--1622986829%22%20id%3D%22toc-hId--1622986829%22%20id%3D%22toc-hId--1622986829%22%3EScenario%3C%2FH2%3E%0A%3CP%3EWe%20want%20to%20look%20at%20the%20number%20of%20events%20occurring%20on%20each%20of%20our%20servers%20in%20the%20System%20event%20log.%20We%20want%20to%20detect%20any%20anomalies%20where%20more%20events%20than%20normal%20happen%20on%20a%20server.%20To%20make%20this%20query%20even%20more%20useful%20we%E2%80%99ll%20take%20the%20list%20of%20servers%20that%20have%20had%20anomalies%20and%20chart%20them%20by%20eventid.%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId-864464443%22%20id%3D%22toc-hId-864526004%22%20id%3D%22toc-hId-864526004%22%20id%3D%22toc-hId-864526004%22%20id%3D%22toc-hId-864526004%22%20id%3D%22toc-hId-864526004%22%3E%26nbsp%3B%3C%2FH2%3E%0A%3CH2%20id%3D%22toc-hId--942990020%22%20id%3D%22toc-hId--942928459%22%20id%3D%22toc-hId--942928459%22%20id%3D%22toc-hId--942928459%22%20id%3D%22toc-hId--942928459%22%20id%3D%22toc-hId--942928459%22%3EStep%201%3A%20Pulling%20the%20Data%3C%2FH2%3E%0A%3CP%3EStep%20one%20is%20to%20get%20the%20data%20that%20you%20want%20to%20detect%20anomalies%20on.%20What%20the%20below%20query%20will%20do%20is%20filter%20to%20only%20event%20in%20the%20%E2%80%9CSystem%E2%80%9D%20log%20and%20then%20create%20a%20count%20of%20events%20for%20each%20server%20in%2030%20minute%20aggregates.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EEvent%0A%7C%20where%20TimeGenerated%20%26gt%3B%3D%20ago(7d)%0A%7C%20where%20EventLog%20%3D%3D%20'System'%0A%7C%20summarize%20EventCount%3Dcount()%20by%20Computer%2C%20bin(TimeGenerated%2C30m)%0A%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESo%20the%20output%20from%20just%20this%20query%20would%20look%20something%20like%20this%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22bwatts670_0-1617478804224.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F269349i4FC03B5946F13639%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22bwatts670_0-1617478804224.png%22%20alt%3D%22bwatts670_0-1617478804224.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId-1544522813%22%20id%3D%22toc-hId-1544584374%22%20id%3D%22toc-hId-1544584374%22%20id%3D%22toc-hId-1544584374%22%20id%3D%22toc-hId-1544584374%22%20id%3D%22toc-hId-1544584374%22%3EStep%202%3A%20Order%20the%20results%3C%2FH2%3E%0A%3CP%3EBefore%20we%20create%20a%20list%20or%20series%20we%20need%20to%20order%20the%20results%20by%20the%20time%20generated.%20This%20is%20the%20simplest%20step%20but%20essential%20if%20you%20want%20accurate%20results!%20Just%20add%20the%20following%20line%20to%20your%20query%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%7C%20order%20by%20TimeGenerated%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESo%20now%20our%20query%20looks%20like%20this.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EEvent%0A%7C%20where%20TimeGenerated%20%26gt%3B%3D%20ago(7d)%0A%7C%20where%20EventLog%20%3D%3D%20'System'%0A%7C%20summarize%20EventCount%3Dcount()%20by%20Computer%2C%20bin(TimeGenerated%2C30m)%0A%7C%20order%20by%20TimeGenerated%0A%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId--262931650%22%20id%3D%22toc-hId--262870089%22%20id%3D%22toc-hId--262870089%22%20id%3D%22toc-hId--262870089%22%20id%3D%22toc-hId--262870089%22%20id%3D%22toc-hId--262870089%22%3EStep%203%3A%20Make%20a%20List%3C%2FH2%3E%0A%3CP%3ENow%20we%20have%20everything%20ready%20to%20create%20the%20list%20in%20Kusto.%20Below%20is%20the%20line%20you%20need%20to%20add%20to%20your%20query.%20This%20will%20make%20a%20list%20of%20both%20the%20TimeGenerated%20field%20and%20the%20EventCount%20field.%20So%20what%20we%E2%80%99ll%20end%20up%20with%20is%20a%20single%20line%20for%20each%20server%20with%20a%20list%20of%20the%20TImeGenerated%20and%20EventCount%20fields.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%7C%20summarize%20EventCount%3Dmake_list(EventCount)%2CTimeGenerated%3Dmake_list(TimeGenerated)%20by%20Computer%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESo%20the%20query%20looks%20like%20this%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EEvent%0A%7C%20where%20TimeGenerated%20%26gt%3B%3D%20ago(7d)%0A%7C%20where%20EventLog%20%3D%3D%20'System'%0A%7C%20summarize%20EventCount%3Dcount()%20by%20Computer%2C%20bin(TimeGenerated%2C30m)%0A%7C%20order%20by%20TimeGenerated%0A%7C%20summarize%20EventCount%3Dmake_list(EventCount)%2CTimeGenerated%3Dmake_list(TimeGenerated)%20by%20Computer%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnd%20the%20output%20should%20look%20something%20like%20this%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22bwatts670_1-1617478804229.png%22%20style%3D%22width%3A%20678px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F269348i874021DBAC1D34D5%2Fimage-dimensions%2F678x43%3Fv%3Dv2%22%20width%3D%22678%22%20height%3D%2243%22%20role%3D%22button%22%20title%3D%22bwatts670_1-1617478804229.png%22%20alt%3D%22bwatts670_1-1617478804229.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId--2070386113%22%20id%3D%22toc-hId--2070324552%22%20id%3D%22toc-hId--2070324552%22%20id%3D%22toc-hId--2070324552%22%20id%3D%22toc-hId--2070324552%22%20id%3D%22toc-hId--2070324552%22%3E%26nbsp%3B%3C%2FH2%3E%0A%3CH2%20id%3D%22toc-hId-417126720%22%20id%3D%22toc-hId-417188281%22%20id%3D%22toc-hId-417188281%22%20id%3D%22toc-hId-417188281%22%20id%3D%22toc-hId-417188281%22%20id%3D%22toc-hId-417188281%22%3EStep%204%3A%20Detect%20Anomalies%3C%2FH2%3E%0A%3CP%3EWe%20have%20our%20query%20setup%20to%20detect%20the%20anomalies%2C%20we%20just%20need%20to%20pass%20the%20%E2%80%9CEventCount%E2%80%9D%20field%20to%20series_decompose_anomalies%20and%20create%20a%20new%20column%20with%20the%20results%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%7C%20extend%20outliers%3Dseries_decompose_anomalies(EventCount)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESo%20the%20outliers%20filed%20will%20contain%203%20values%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%200%3DNormal%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20-1%3Dless%20events%20than%20normal%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%3Dmore%20events%20than%20normal%3C%2FP%3E%0A%3CP%3ESo%20our%20query%20would%20now%20look%20like%20this%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EEvent%0A%7C%20where%20TimeGenerated%20%26gt%3B%3D%20ago(7d)%0A%7C%20where%20EventLog%20%3D%3D%20'System'%0A%7C%20summarize%20EventCount%3Dcount()%20by%20Computer%2C%20bin(TimeGenerated%2C30m)%0A%7C%20order%20by%20TimeGenerated%0A%7C%20summarize%20EventCount%3Dmake_list(EventCount)%2CTimeGenerated%3Dmake_list(TimeGenerated)%20by%20Computer%0A%7C%20extend%20outliers%3Dseries_decompose_anomalies(EventCount)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnd%20the%20output%20should%20look%20something%20like%20this%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22bwatts670_2-1617478804235.png%22%20style%3D%22width%3A%20783px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F269350iB816FFA54C7DDFDF%2Fimage-dimensions%2F783x47%3Fv%3Dv2%22%20width%3D%22783%22%20height%3D%2247%22%20role%3D%22button%22%20title%3D%22bwatts670_2-1617478804235.png%22%20alt%3D%22bwatts670_2-1617478804235.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EAs%20you%20can%20see%2C%20we%20have%20some%20anomalies%20that%20got%20detected.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId-283441996%22%20id%3D%22toc-hId-283503557%22%20id%3D%22toc-hId-283503557%22%20id%3D%22toc-hId-283503557%22%20id%3D%22toc-hId-283503557%22%20id%3D%22toc-hId-283503557%22%3EExtra%20Credit%3C%2FH2%3E%0A%3CP%3EWhen%20I%20get%20to%20this%20point%20I%20always%20like%20to%20look%20at%20where%20the%20anomalies%20were%20detected%20and%20make%20sure%20that%20I%20would%20consider%20them%20anomalies.%20I%20won%E2%80%99t%20go%20into%20it%20in%20this%20article%20but%20you%20can%20adjust%20how%20sensitive%20the%20calculations.%20For%20me%20to%20easily%20see%20where%20the%20anomalies%20were%20detected%20I%E2%80%99ll%20use%20mvexpand%20on%20any%20of%20the%20list%20that%20we%E2%80%99ve%20made%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%7C%20mv-expand%20TimeGenerated%2C%20EventCount%2C%20outliers%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThen%20look%20at%20the%20results%20to%20make%20sure%20where%20I%20see%20a%201%20(we%E2%80%99ll%20later%20filter%20to%20only%20positive%20anomalies)%20it%20makes%20sense%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22bwatts670_3-1617478804240.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F269353i3378347FFE9C30A3%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22bwatts670_3-1617478804240.png%22%20alt%3D%22bwatts670_3-1617478804240.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%E2%80%99d%20also%20check%20on%20the%20ones%20that%20stay%20at%200%20to%20make%20sure%20the%20EventCounts%20are%20pretty%20close%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22bwatts670_4-1617478804244.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F269351iB7420B157818777F%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22bwatts670_4-1617478804244.png%22%20alt%3D%22bwatts670_4-1617478804244.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId--1524012467%22%20id%3D%22toc-hId--1523950906%22%20id%3D%22toc-hId--1523950906%22%20id%3D%22toc-hId--1523950906%22%20id%3D%22toc-hId--1523950906%22%20id%3D%22toc-hId--1523950906%22%3EStep%205%3A%20Get%20Useful%20Information%3C%2FH2%3E%0A%3CP%3ENot%20saying%20what%20we%20have%20right%20now%20isn%E2%80%99t%20useful%20but%20I%20like%20to%20use%20the%20anomalies%20info%20to%20get%20more%20detailed%20information.%20So%20in%20this%20case%20we%20detected%20anomalies%20for%20the%20number%20of%20events%20on%20a%20server.%20Now%20that%20we%20know%20the%20server%20had%20an%20anomaly%20maybe%20we%20want%20to%20graph%20that%20by%20the%20EventId%20to%20determine%20what%20caused%20the%20anomaly.%20Lets%20start%20by%20expanding%20the%20list%20like%20before%20but%20then%20filtering%20to%20only%20outliers%20that%20equal%201%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%7C%20mv-expand%20TimeGenerated%2C%20EventCount%2C%20outliers%0A%7C%20where%20outliers%20%3D%3D%201%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20will%20give%20us%20every%20outlier%20detected%20by%20each%20server.%20What%20I%20really%20need%20is%20a%20list%20of%20each%20server%20that%20has%20had%20at%20least%20one%20anomaly.%20So%20I%20just%20at%20the%20following%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%7C%20distinct%20Computer%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20want%20to%20feed%20the%20results%20into%20another%20query%20so%20let%20me%20set%20a%20variable%20called%20%E2%80%9CComputers%E2%80%9D%20using%20the%20let%20command.%20So%20our%20completed%20initial%20query%20looks%20like%20this%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3Elet%20Computers%3DEvent%0A%7C%20where%20TimeGenerated%20%26gt%3B%3D%20ago(7d)%0A%7C%20where%20EventLog%20%3D%3D%20'System'%0A%7C%20summarize%20EventCount%3Dcount()%20by%20Computer%2C%20bin(TimeGenerated%2C30m)%0A%7C%20order%20by%20TimeGenerated%0A%7C%20summarize%20EventCount%3Dmake_list(EventCount)%2CTimeGenerated%3Dmake_list(TimeGenerated)%20by%20Computer%0A%7C%20extend%20outliers%3Dseries_decompose_anomalies(EventCount)%0A%7C%20mv-expand%20TimeGenerated%2C%20EventCount%2C%20outliers%0A%7C%20where%20outliers%20%3D%3D%201%0A%7C%20distinct%20Computer%3B%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESo%20now%20I%20can%20in%20my%20next%20query%20I%20can%20filter%20down%20to%20just%20these%20computers%20using%20%E2%80%9C%7C%20where%20Computer%20in%20(Computers)%E2%80%9D%20where%20Computers%20is%20fed%20in%20from%20the%20above%20query.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%E2%80%A6.(prev%20query)%0AEvent%0A%7C%20where%20TimeGenerated%20%26gt%3B%3D%20ago(7d)%0A%7C%20where%20EventLog%20%3D%3D%20'System'%0A%7C%20where%20Computer%20in%20(Computers)%0A%7C%20extend%20ChartName%20%3D%20strcat(Computer%2C'-'%2CEventID)%0A%7C%20summarize%20EventCount%3Dcount()%20by%20ChartName%2Cbin(TimeGenerated%2C%2030m)%0A%7C%20render%20timechart%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20only%20thing%20different%20I%20did%20here%20is%20create%20a%20%E2%80%9CChartName%E2%80%9D%20field%20that%20is%20a%20combination%20of%20the%20Computer%20and%20the%20EventID.%20I%E2%80%99m%20using%20the%20same%20time%20period%20(7d)%20and%20the%20same%20aggregation%20(30m)%20for%20both%20the%20anomaly%20detection%20and%20this%20second%20query.%20That%20way%20you%E2%80%99re%20looking%20at%20the%20same%20data%20for%20both.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBelow%20is%20the%20complete%20query%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3Elet%20Computers%3DEvent%0A%7C%20where%20TimeGenerated%20%26gt%3B%3D%20ago(7d)%0A%7C%20where%20EventLog%20%3D%3D%20'System'%0A%7C%20summarize%20EventCount%3Dcount()%20by%20Computer%2C%20bin(TimeGenerated%2C30m)%0A%7C%20order%20by%20TimeGenerated%0A%7C%20summarize%20EventCount%3Dmake_list(EventCount)%2CTimeGenerated%3Dmake_list(TimeGenerated)%20by%20Computer%0A%7C%20extend%20outliers%3Dseries_decompose_anomalies(EventCount)%0A%7C%20mv-expand%20TimeGenerated%2C%20EventCount%2C%20outliers%0A%7C%20where%20outliers%20%3D%3D%201%0A%7C%20distinct%20Computer%3B%0AEvent%0A%7C%20where%20TimeGenerated%20%26gt%3B%3D%20ago(7d)%0A%7C%20where%20EventLog%20%3D%3D%20'System'%0A%7C%20where%20Computer%20in%20(Computers)%0A%7C%20extend%20ChartName%20%3D%20strcat(Computer%2C'-'%2CEventID)%0A%7C%20summarize%20EventCount%3Dcount()%20by%20ChartName%2Cbin(TimeGenerated%2C%2030m)%0A%7C%20render%20timechart%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3BHere%20are%20the%20results%20in%20my%20environment.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22bwatts670_5-1617478804259.png%22%20style%3D%22width%3A%20747px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F269352iABA09C21C76681C5%2Fimage-dimensions%2F747x232%3Fv%3Dv2%22%20width%3D%22747%22%20height%3D%22232%22%20role%3D%22button%22%20title%3D%22bwatts670_5-1617478804259.png%22%20alt%3D%22bwatts670_5-1617478804259.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20just%20have%20one%20server%20in%20my%20environment%20so%20charting%20it%20makes%20sense.%20I%20would%20probably%20analyze%20the%20data%20in%20a%20table%20if%20I%20had%20a%20bunch%20of%20servers.%20But%20you%20can%20see%20really%20quickly%20I%20can%20see%20that%20EventID%207036%20is%20the%20one%20that%20is%20causing%20the%20anomalies%20for%20this%20server.%20The%20rest%20of%20the%20events%20are%20staying%20stable%2C%20but%20that%20event%20varies%20a%20good%20bit%20on%20the%20server.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%E2%80%99re%20interested%20in%20another%20scenario%20where%20this%20same%20process%20can%20be%20useful%20check%20out%20my%20previous%20blog%20about%20%E2%80%9C%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fcore-infrastructure-and-security%2Fdetecting-azure-cost-anomalies%2Fba-p%2F2020591%22%20target%3D%22_blank%22%3EDetecting%20Azure%20Cost%20Anomalies%3C%2FA%3E.%E2%80%9D%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId-963500366%22%20id%3D%22toc-hId-963561927%22%20id%3D%22toc-hId-963561927%22%20id%3D%22toc-hId-963561927%22%20id%3D%22toc-hId-963561927%22%20id%3D%22toc-hId-963561927%22%3ESummary%3C%2FH2%3E%0A%3CP%3EOnce%20you%20get%20the%20hand%20of%20%E2%80%9Cseries_decompose_anomalies%E2%80%9D%20it%20can%20be%20a%20very%20useful%20tool%20in%20your%20toolbelt.%20I%20covered%20using%20this%20to%20visualize%20anomalies%20in%20the%20number%20of%20events%20occurring%20in%20the%20System%20event%20log.%20As%20long%20as%20you%20can%20follow%20the%20steps%20above%20(Get%20the%20data%20you%20want%2C%20order%20the%20data%2C%20make%20a%20list%20or%20series%2C%20and%20then%20detect%20anomalies)%20you%20can%20explore%20your%20data%20for%20anomalies.%20Just%20like%20we%20can%20visualize%20the%20data%20we%20can%20also%20setup%20alerts%20through%20Azure%20Monitor.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-2252477%22%20slang%3D%22en-US%22%3E%3CP%3EDetecting%20anomalies%20in%20your%20data%20can%20be%20a%20powerful%20tool.%20In%20this%20article%20we%20examine%20how%20to%20do%20exactly%20this%20with%20data%20in%20Log%20Analytics%20by%20using%20KQL.%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2252477%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBradWatts%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2273267%22%20slang%3D%22en-US%22%3ERe%3A%20Exploring%20Anomalies%20with%20Log%20Analytics%20using%20KQL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2273267%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there.%20I%20have%20developed%20an%20Azure-friendly%20Excel%20365%20template%20that%20will%20automatically%20detect%20outliers%20in%20a%20single%20column%20of%20data.%20It%20will%20identify%2C%20score%20and%20then%20visualize%20outliers%20at%20the%20click%20of%20one%20button.%20You%20can%20try%20a%20free%20version%20in%20my%20online%20store%20at%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fsellfy.com%2Fcool-number-crunching-template%2Fp%2F95npu9%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EFREE%20PODCAST%20TRIAL%20-%20Find%20Unusual%20Data%3A%20Fraud%2C%20Poor%20Cu%20-%20Cool%20Number%20Crunching%20Templates%20(sellfy.com)%3C%2FA%3E.%20And%2C%20I%20also%20have%20more%20advanced%20versions%20that%20will%20automatically%20create%20boxplots%20and%20pivots%20tables%20with%20slicers%20so%20you%20can%20explore%20outliers%20vs.%20the%20rest%20of%20your%20dataset.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fsellfy.com%2Fcool-number-crunching-template%2Fp%2Ffind-unsual-data-explore-your-outliers%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EFind%20Unusual%20Data%3A%20Explore%20Your%20Outliers%20-%20Cool%20Number%20Crunching%20Templates%20(sellfy.com)%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2275482%22%20slang%3D%22en-US%22%3ERe%3A%20Exploring%20Anomalies%20with%20Log%20Analytics%20using%20KQL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2275482%22%20slang%3D%22en-US%22%3E%3CP%3EVery%2C%20very%20good.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Co-Authors
Version history
Last update:
‎Apr 05 2021 06:00 AM
Updated by: