How do I display latest event after comparing descriptions from event log?

[nareshkumarg]

Hello,

I am working on a query to check multiple service status from multiple servers and trying to display the current status of each service using windows event log 7036. Event ID 7036 captures the event for both services stopped and started.  My requirement is on a given point of time service might restart multiple time and I don't want to list all restart state instead want to display the current status by comparing the data for each service against the current State.

index IN (wineventappsys_*) EventCode=7036 host IN (ABC,DEF,GHI)

| stats count by _time, host, EventCode, SourceName, LogName, Message
| lookup service_list Message OUTPUT Short_Description Severity
| eval State =if(match(Message,"running state"),"CLOSED","OPEN")
| stats latest(_time) as Date by host State Short_Description | sort - host Date ShortDescription 

Here it still lists both open and closed events. I am trying to display data only with the last state for each service for each server.

Any help is greatly appreciated.

Naresh

[nareshkumarg · Accepted solution]

The following query helps me to achieve my requirement.

index=wineventappsys_* source="WinEventLog:System" EventCode=7036 host IN (ABC,DEF,GHI)
| stats latest_time(Message) as _time by host Message
| lookup service_list Message OUTPUT ShortDescription Severity
| eval State =if(match(Message,"running state"),"CLOSED","OPEN")
| eval combostate=_time.State
| transaction host ShortDescription keeporphans=true delim="," | nomv combostate
| eval condition=if((like(combostate,"%CLOSED%OPEN") AND duration>0) OR eventcount=1,1,0)
| search condition=1

[thambisetty]

index IN (wineventappsys_*) EventCode=7036 host IN (ABC,DEF,GHI) 
| stats count by _time,Message, host,EventCode, SourceName, LogName 
| fields - count
| rex field="Message" "The\s+(?<service>.+)\s+service\s+entered\s+the\s+(?<state>\w+)\s+state"
| eventstats latest(_time) as latest_time by service, host
| where _time=latest_time 
| fields - latest_time
| search state=stopped
| lookup service_list Message OUTPUT Short_Description Severity 
| eval State="OPEN"
| sort - host Date ShortDescription

if you are okay with latest service state to be running or closed, you can remove search state=stopped. there could be latest service state running also for some services. if you remove search state=stopped then you need to add your old eval 

| eval State =if(match(Message,"running state"),"CLOSED","OPEN") 

[thambisetty]

index IN (wineventappsys_*) EventCode=7036 host IN (ABC,DEF,GHI) 
| stats count by _time,Message, host,EventCode, SourceName, LogName 
| fields - count
| rex field="Message" "The\s+(?<service>.+)\s+service\s+entered\s+the\s+(?<state>\w+)\s+state"
| eventstats latest(_time) as latest_time by service, host
| where _time=latest_time 
| fields - latest_time
| search state=stopped
| lookup service_list Message OUTPUT Short_Description Severity 
| eval State="OPEN"
| sort - host Date ShortDescription

[ITWhisperer]

I don't know these logs or events but from your description it seems to me that you need the value of State for the latest event for each Message (which you have already looked up) so how about trying this

| stats latest(_time) as Date values(State) as State by host Short_Description | sort - host Date ShortDescription 

Could you also do the lookup after the stats so there is less to look up? 

[thambisetty]

try below

index IN (wineventappsys_*) EventCode=7036 host IN (ABC,DEF,GHI) 
| stats latest(Message) as Message latest(_time) as Date by  host, EventCode, SourceName, LogName 
| lookup service_list Message OUTPUT Short_Description Severity 
| eval State =if(match(Message,"running state"),"CLOSED","OPEN") 
| sort - host Date ShortDescription

like answer if it solves your problem.

[nareshkumarg]

@thambisetty I tried the query but it displays only one service from the host even though I see more than 1 service restarted at the same time range. Is there a way to modify the query to display all services with the event ID 7036 with the latest status. Also if the recent message for service is closed then I need only that state not both open and closed.

Regards,

Naresh