SOLVED

How to get last status of the service in Event Logs without selecting TimeRange

%3CLINGO-SUB%20id%3D%22lingo-sub-2174338%22%20slang%3D%22en-US%22%3EHow%20to%20get%20last%20status%20of%20the%20service%20in%20Event%20Logs%20without%20selecting%20TimeRange%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2174338%22%20slang%3D%22en-US%22%3E%3CP%3EHI%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20requirement%20is%20to%20find%20the%20status%20of%20few%20windows%20services%20whether%20its%20running%2Fstopped%2Fstarted.%3C%2FP%3E%3CP%3EEvents%20will%20capture%20only%20the%20last%20state%20of%20the%20service%20so%26nbsp%3B%20cannot%20see%20logs%20if%20there%20is%20no%20change%20in%20the%20current%20state%20.%3C%2FP%3E%3CP%3EFor%20Eg%3A%3C%2FP%3E%3CP%3EEvent%3CBR%20%2F%3E%7C%20where%20EventLog%20%3D%3D%20'System'%20and%20EventID%20%3D%3D%207036%20and%20Source%20%3D%3D%20'Service%20Control%20Manager'%20and%20RenderedDescription%20startswith_cs%20'cisco'%20%7C%20parse%20kind%3Drelaxed%20EventData%20with%20*%20'%3CDATA%20name%3D%22%26quot%3Bparam1%26quot%3B%22%3E'%20Windows_Service_Name%20'%3C%2FDATA%3E%3CDATA%20name%3D%22%26quot%3Bparam2%26quot%3B%22%3E'%20Windows_Service_State%20'%3C%2FDATA%3E'%20*%3CBR%20%2F%3E%7C%20sort%20by%20TimeGenerated%20desc%3CBR%20%2F%3E%7C%20project%20Computer%2C%20Windows_Service_Name%2C%20Windows_Service_State%2C%20TimeGenerated%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAbove%20query%20returns%20the%20status%20of%20all%20the%20services%20that%20starts%20with%20Cisco%20within%20a%20time%20range%20that%20is%20selected.%20if%20there%20is%20no%20change%20of%20state%20within%20that%20time%20frame%20then%20it%20don't%20return%20those%20result%20sets.%3C%2FP%3E%3CP%3EBut%20How%20to%20find%20the%20last%20status%20of%20the%20service%20in%20Events%20%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2175440%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20get%20last%20status%20of%20the%20service%20in%20Event%20Logs%20without%20selecting%20TimeRange%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2175440%22%20slang%3D%22en-US%22%3EThis%20would%20return%20the%20last%20row%20of%20data%20-%20using%20arg_max()%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EEvent%3CBR%20%2F%3E%7C%20where%20EventLog%20%3D%3D%20'System'%20and%20EventID%20%3D%3D%207036%20%3CBR%20%2F%3E%7C%20summarize%20arg_max(TimeGenerated%2C*)%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2176830%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20get%20last%20status%20of%20the%20service%20in%20Event%20Logs%20without%20selecting%20TimeRange%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2176830%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3BThanks%20for%20the%20reply.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20modified%20my%20query%20to%20below%20one%26nbsp%3B%3C%2FP%3E%3CP%3EEvent%3CBR%20%2F%3E%7C%20where%20TimeGenerated%20%26lt%3B%20ago(3m)%20%2F%2F%20last%203%20months%26nbsp%3B%3CBR%20%2F%3E%7C%20where%20Computer%20%3D%3D%20''%20%2F%2F%20VM%20instance%20name%3CBR%20%2F%3E%7C%20where%20EventLog%20%3D%3D%20'System'%20and%20EventID%20%3D%3D%207036%20and%20Source%20%3D%3D%20'cisco'%20and%20RenderedDescription%20startswith_cs%20'cisco'%3CBR%20%2F%3E%7C%20parse%20kind%3Drelaxed%20EventData%20with%20*%20'%3CDATA%20name%3D%22%26quot%3Bparam1%26quot%3B%22%3E'%20Windows_Service_Name%20'%3C%2FDATA%3E%3CDATA%20name%3D%22%26quot%3Bparam2%26quot%3B%22%3E'%20Windows_Service_State%20'%3C%2FDATA%3E'%20*%3CBR%20%2F%3E%7C%20project%20TimeGenerated%2C%20Computer%2C%20Windows_Service_Name%2C%20Windows_Service_State%3CBR%20%2F%3E%7C%20summarize%20arg_max(TimeGenerated%2C%20*)%20by%20Windows_Service_Name%3CBR%20%2F%3E%7C%20sort%20by%20TimeGenerated%20desc%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20this%20query%20is%20executed%20without%20'%7C%20where%20TimeGenerated%20%26lt%3B%20ago(3m)%20'%20it%20by%20default%20takes%2024%20hours.%3C%2FP%3E%3CP%3ESo%20modified%20query%20to%20check%20status%20for%20last%203%20months%20.%20Its%20working%20as%20expected%26nbsp%3B%20in%20query%20explorer.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20when%20this%20is%20pinned%20to%26nbsp%3B%20dashboard%20%2C%20it's%20not%20retuning%20the%20result%20as%20it%20still%20takes%20the%20TimeRange%20from%20the%20dashboard%20and%20when%20i%20cannot%20override%20to%20check%20for%20last%203%20months%20.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20to%20display%20this%20result%20in%20dashboard%20%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

HI,

 

My requirement is to find the status of few windows services whether its running/stopped/started.

Events will capture only the last state of the service so  cannot see logs if there is no change in the current state .

For Eg:

Event
| where EventLog == 'System' and EventID == 7036 and Source == 'Service Control Manager' and RenderedDescription startswith_cs 'cisco' | parse kind=relaxed EventData with * '<Data Name="param1">' Windows_Service_Name '</Data><Data Name="param2">' Windows_Service_State '</Data>' *
| sort by TimeGenerated desc
| project Computer, Windows_Service_Name, Windows_Service_State, TimeGenerated

 

Above query returns the status of all the services that starts with Cisco within a time range that is selected. if there is no change of state within that time frame then it don't return those result sets.

But How to find the last status of the service in Events ?

7 Replies
best response confirmed by Racheal200 (New Contributor)
Solution
This would return the last row of data - using arg_max()


Event
| where EventLog == 'System' and EventID == 7036
| summarize arg_max(TimeGenerated,*)

@Clive Watson Thanks for the reply.

 

I have modified my query to below one 

Event
| where TimeGenerated < ago(3m) // last 3 months 
| where Computer == '' // VM instance name
| where EventLog == 'System' and EventID == 7036 and Source == 'cisco' and RenderedDescription startswith_cs 'cisco'
| parse kind=relaxed EventData with * '<Data Name="param1">' Windows_Service_Name '</Data><Data Name="param2">' Windows_Service_State '</Data>' *
| project TimeGenerated, Computer, Windows_Service_Name, Windows_Service_State
| summarize arg_max(TimeGenerated, *) by Windows_Service_Name
| sort by TimeGenerated desc

 

If this query is executed without '| where TimeGenerated < ago(3m) ' it by default takes 24 hours.

So modified query to check status for last 3 months . Its working as expected  in query explorer.

 

But when this is pinned to  dashboard , it's not retuning the result as it still takes the TimeRange from the dashboard and when i cannot override to check for last 3 months .

 

How to display this result in dashboard ?

 

@Racheal200 

Make sure you "set in Query" in the Dashboard.  Also 3m == 3 minutes, so you would need 90d for 3months The timespan data type - Azure Data Explorer | Microsoft Docs. 

When using ago()  ago() - Azure Data Explorer | Microsoft Docs
use ">" rather than "<"

Screenshot 2021-03-02 085142.jpg

@Clive Watson , Have modified my query as u suggested.

 

In dashboard , I don't have similar option like yours. when I click that icon it opens edit query box like the image below and there's not much option.

 

aMDB.png

@Clive Watson Thanks !  I already tried this option but not useful to my scenerio.

Again  here there is no option to select last 3 Months .I have to choose a time range and cannot use the query as it is which already have time range to check last 3 months data.