Home

Azure "conditional" searches in Log Analitycs

%3CLINGO-SUB%20id%3D%22lingo-sub-320207%22%20slang%3D%22en-US%22%3EAzure%20%22conditional%22%20searches%20in%20Log%20Analitycs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-320207%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20my%20situation.%20I%20have%20a%26nbsp%3Bscheduled%20task%20writing%20a%20keepalive%20event%20in%20the%20registry%20each%2015%20minutes%20in%20several%20Windows%20servers.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20trace%20them%20(or%20any%20other%20pointing%20to%20a%20problem)%20with%20something%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3EEvent%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20EventLog%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22System%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20Source%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22MyEvtOrigin%22%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20TimeGenerated%20%26gt%3B%20now()-20m%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20TimeGenerated%20%26lt%3B%20now()-%3C%2FSPAN%3E%3CSPAN%3E5%3C%2FSPAN%3E%3CSPAN%3Em%3C%2FSPAN%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%20events_count%3D%3C%2FSPAN%3E%3CSPAN%3Ecount%3C%2FSPAN%3E%3CSPAN%3E()%20%3C%2FSPAN%3E%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%20Computer%2C%20EventID%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3EWith%20this%20I%20can%20get%20the%20servers%20that%20are%20running%20in%20the%20same%20time%20lapse.%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3EHeartbeat%3CBR%20%2F%3E%7C%20where%20OSType%20%3D%3D%20'Windows'%3CBR%20%2F%3E%7C%20where%20TimeGenerated%20%26gt%3B%20now()-20m%20and%20TimeGenerated%20%26lt%3B%20now()-5m%26nbsp%3B%3CBR%20%2F%3E%7C%20summarize%20arg_max(TimeGenerated%2C%20*)%20by%20SourceComputerId%20%2F%2F%20que%20est%C3%A9n%20arrancadas%3CBR%20%2F%3E%7C%20top%20500000%20by%20Computer%20asc%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3EHow%20can%20I%20query%20the%20%3CSPAN%3Eevents_count%20in%20case%20there%20are%20none%20(meaning%20my%20task%20is%20not%20running%20anymore)%3C%2FSPAN%3E%26nbsp%3Bto%20trigger%20an%20alert%20but%20only%20considering%20those%20servers%20thar%20are%20running%3F%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3EWhen%20I%20try%20to%20do%20a%20join%20like%20this%3A%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EHeartbeat%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20where%20OSType%20%3D%3D%20'Windows'%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20where%20TimeGenerated%20%26gt%3B%20now()-20m%20and%20TimeGenerated%20%26lt%3B%20now()-5m%26nbsp%3B%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20summarize%20arg_max(TimeGenerated%2C%20*)%20by%20SourceComputerId%26nbsp%3B%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20top%20500000%20by%20Computer%20asc%3C%2FSPAN%3E%3CBR%20%2F%3E%7C%20join%20kind%3D%20inner%20(%3CBR%20%2F%3E%3CDIV%3E%3CSPAN%3EEvent%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BEventLog%20%3D%3D%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%22System%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BSource%20%3D%3D%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%22MyEvtOrigin%22%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BTimeGenerated%20%26gt%3B%20now()-20m%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BTimeGenerated%20%26lt%3B%20now()-%3C%2FSPAN%3E%3CSPAN%3E5%3C%2FSPAN%3E%3CSPAN%3Em%3C%2FSPAN%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3Bevents_count%3D%3C%2FSPAN%3E%3CSPAN%3Ecount%3C%2FSPAN%3E%3CSPAN%3E()%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BComputer%2C%20EventID%3C%2FSPAN%3E%3C%2FDIV%3E%7C%20where%20events_count%20%26lt%3B%201%26nbsp%3B%3CBR%20%2F%3E%7C%20sort%20by%20TimeGenerated%20asc%20nulls%20last%3CBR%20%2F%3E)%20on%20Computer%3CBR%20%2F%3E%7C%20summarize%20arg_max(TimeGenerated%2C%20*)by%20Computer%2C%20EventID%20%2Cevents_count%2C%20SourceComputerId%3CBR%20%2F%3E%7C%20top%20500000%20by%20Computer%20asc%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3EThe%20resulting%20alarm%20is%20triggeres%20both%20if%20the%20job%20fails%20and%20when%20the%20server%20is%20down.%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-320207%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-320733%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20%22conditional%22%20searches%20in%20Log%20Analitycs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-320733%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20I%20understand%20correctly%20you%20will%20need%20to%20reverse%20the%20logic%20than%3A%3C%2FP%3E%0A%3CPRE%3Elet%20LiveServers%20%3D%20Event%0A%7C%20where%20EventLog%20%3D%3D%20%22System%22%0A%7C%20where%20Source%20%3D%3D%20%22MyEvtOrigin%22%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A%7C%20where%20TimeGenerated%20%26gt%3B%20now()-20m%20and%20TimeGenerated%20%26lt%3B%20now()-5m%20%20%20%20%20%20%20%20%20%20%20%20%20%0A%7C%20summarize%20events_count%3Dcount()%20by%20Computer%2C%20EventID%0A%7C%20distinct%20Computer%3B%0AHeartbeat%0A%7C%20where%20TimeGenerated%20%26gt%3B%20now()-20m%20and%20TimeGenerated%20%26lt%3B%20now()-5m%0A%7C%20where%20OSType%20%3D%3D%20'Windows'%0A%7C%20where%20Computer%20notin%20(LiveServers)%20%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20logic%20for%20the%20above%20query%20is%3A%3C%2FP%3E%0A%3CP%3E-%20Find%20me%20all%20computers%20that%20have%20my%20live%20event%20for%20certain%20period%20and%20put%20them%20into%20table%3C%2FP%3E%0A%3CP%3E-%20Find%20me%20all%20Windows%20computers%20that%20are%20producing%20heartbeat%20events%20and%20filter%20to%20show%20me%20those%20that%20are%20not%20in%20the%20above%20table%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20will%20have%20to%20figure%20out%20the%20timings%20on%20your%20own.%20I%20usually%20restrict%20time%20only%20from%20a%20time%20in%20the%20past%20until%20now.%20Especially%20for%20alerts%20as%20there%20you%20specify%20the%20time%20frame%20in%20the%20alert%20properties.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-320729%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20%22conditional%22%20searches%20in%20Log%20Analitycs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-320729%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20your%20swift%20answer.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20works%20but%20only%20partialy.%3C%2FP%3E%3CP%3EI%20have%20learnt%20that%20there%20is%20another%20problem%2C%20related%20to%20the%20usage%20o%26nbsp%3B%3C%2FP%3E%3CPRE%3Eevents_count%3Dcount()%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FPRE%3E%3CP%3Eas%20my%20task%20runs%20each%2015%20min%2C%20in%20an%20hour%20you%20get%204%20events%2C%20perfect.%20But%20as%20it%20is%20meant%20to%20be%20a%20kind%20of%20%22keep%20alive%22%20if%20I%20disable%20the%20task%20%22events_count%3Dcount()%22%20instead%20of%20returning%20a%200%20value%20for%20a%20particilar%20server%20makes%20the%20line%20not%20to%20appear%20and%20this%20way%20wouldn't%20trigger%20the%20alert.%20Is%20there%20any%20way%20to%26nbsp%3Bcapture%26nbsp%3B0%26nbsp%3Bresults%26nbsp%3Bin%26nbsp%3B%3CSPAN%3E%22events_count%3Dcount()%22%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-320635%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20%22conditional%22%20searches%20in%20Log%20Analitycs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-320635%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3Ethe%20query%20would%20be%3C%2FP%3E%0A%3CPRE%3Elet%20LiveServers%20%3D%20Heartbeat%0A%7C%20where%20OSType%20%3D%3D%20'Windows'%0A%7C%20where%20TimeGenerated%20%26gt%3B%20now()-20m%20and%20TimeGenerated%20%26lt%3B%20now()-5m%20%0A%7C%20distinct%20Computer%3B%0AEvent%0A%7C%20where%20Computer%20in%20(LiveServers)%0A%7C%20where%20EventLog%20%3D%3D%20%22System%22%0A%7C%20where%20Source%20%3D%3D%20%22MyEvtOrigin%22%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A%7C%20where%20TimeGenerated%20%26gt%3B%20now()-20m%20and%20TimeGenerated%20%26lt%3B%20now()-5m%20%20%20%20%20%20%20%20%20%20%20%20%20%0A%7C%20summarize%20events_count%3Dcount()%20by%20Computer%2C%20EventID%3C%2FPRE%3E%0A%3CP%3EAs%20Event%20does%20not%20have%20SourceComputerId%20so%20we%20use%20Computer%20as%20unique%20name.%20We%20put%20all%20the%20names%20of%20servers%20from%20the%20first%20query%20into%20a%20table%20and%20than%20we%20use%20that%20table%20to%20filter%20the%20second%20query.%20I%20think%20this%20will%20work%20for%20your%20case.%20On%20the%20first%20query%20I%20would%20not%20use%20such%20precise%20scope%20for%20TimeGenerated.%20I%20would%20rather%20use%20ago(20m)%20for%20example.%20If%20you%20will%20create%20alert%20out%20of%20this%20I%20would%20remove%20the%20TImeGenerated%20scope%20completely%20as%20the%20time%20frame%20you%20set%20in%20the%20alert%20properties.%20Also%20have%20a%20look%20at%20this%20on%20alerting%20on%20more%20than%20one%20column%3A%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fcloudadministrator.net%2F2018%2F06%2F08%2Faggregate-on-more-than-one-column-for-azure-log-search-alerts%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fcloudadministrator.net%2F2018%2F06%2F08%2Faggregate-on-more-than-one-column-for-azure-log-search-alerts%2F%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Matasiete
New Contributor

Hi:

 

This is my situation. I have a scheduled task writing a keepalive event in the registry each 15 minutes in several Windows servers.

 

I can trace them (or any other pointing to a problem) with something like this:

 

Event
| where EventLog == "System"
| where Source == "MyEvtOrigin"                                             
| where TimeGenerated > now()-20m and TimeGenerated < now()-5m             
| summarize events_count=count() by Computer, EventID
 
With this I can get the servers that are running in the same time lapse.
 
Heartbeat
| where OSType == 'Windows'
| where TimeGenerated > now()-20m and TimeGenerated < now()-5m 
| summarize arg_max(TimeGenerated, *) by SourceComputerId // que estén arrancadas
| top 500000 by Computer asc
 
How can I query the events_count in case there are none (meaning my task is not running anymore) to trigger an alert but only considering those servers thar are running?
 
When I try to do a join like this:
 
 
Heartbeat
| where OSType == 'Windows'
| where TimeGenerated > now()-20m and TimeGenerated < now()-5m 
| summarize arg_max(TimeGenerated, *) by SourceComputerId 
| top 500000 by Computer asc
| join kind= inner (
Event
where EventLog == "System"
where Source == "MyEvtOrigin"                                             
where TimeGenerated > now()-20m and TimeGenerated < now()-5m             
summarize events_count=count() by Computer, EventID
| where events_count < 1 
| sort by TimeGenerated asc nulls last
) on Computer
| summarize arg_max(TimeGenerated, *)by Computer, EventID ,events_count, SourceComputerId
| top 500000 by Computer asc
 
The resulting alarm is triggeres both if the job fails and when the server is down.
 
 
3 Replies

Hi,

the query would be

let LiveServers = Heartbeat
| where OSType == 'Windows'
| where TimeGenerated > now()-20m and TimeGenerated < now()-5m 
| distinct Computer;
Event
| where Computer in (LiveServers)
| where EventLog == "System"
| where Source == "MyEvtOrigin"                                             
| where TimeGenerated > now()-20m and TimeGenerated < now()-5m             
| summarize events_count=count() by Computer, EventID

As Event does not have SourceComputerId so we use Computer as unique name. We put all the names of servers from the first query into a table and than we use that table to filter the second query. I think this will work for your case. On the first query I would not use such precise scope for TimeGenerated. I would rather use ago(20m) for example. If you will create alert out of this I would remove the TImeGenerated scope completely as the time frame you set in the alert properties. Also have a look at this on alerting on more than one column:

https://cloudadministrator.net/2018/06/08/aggregate-on-more-than-one-column-for-azure-log-search-ale...

 

Thanks for your swift answer.

 

It works but only partialy.

I have learnt that there is another problem, related to the usage o 

events_count=count()

as my task runs each 15 min, in an hour you get 4 events, perfect. But as it is meant to be a kind of "keep alive" if I disable the task "events_count=count()" instead of returning a 0 value for a particilar server makes the line not to appear and this way wouldn't trigger the alert. Is there any way to capture 0 results in "events_count=count()"?

 

If I understand correctly you will need to reverse the logic than:

let LiveServers = Event
| where EventLog == "System"
| where Source == "MyEvtOrigin"                                             
| where TimeGenerated > now()-20m and TimeGenerated < now()-5m             
| summarize events_count=count() by Computer, EventID
| distinct Computer;
Heartbeat
| where TimeGenerated > now()-20m and TimeGenerated < now()-5m
| where OSType == 'Windows'
| where Computer notin (LiveServers) 

 

The logic for the above query is:

- Find me all computers that have my live event for certain period and put them into table

- Find me all Windows computers that are producing heartbeat events and filter to show me those that are not in the above table

 

You will have to figure out the timings on your own. I usually restrict time only from a time in the past until now. Especially for alerts as there you specify the time frame in the alert properties.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies