How to create the below alert?

[abhi04]

I have below two events for a host which shows eventcode=6005 meaning PC ON and evencode=6006 meaning PC OFF. I want to create an alert for sending an alert if the host or computer is Off for more than two hours. So basically, it should take the latest event by host and check if eventcode=6006 for off and then subtract that time from now and if greater than 2 hours should send an alert for this host or computer being OFF. How can I do that.

6/25/18
6:09:23.000 AM  
06/25/2018 05:09:23 AM
LogName=System
SourceName=EventLog
EventCode=6005
EventType=4
Type=Information
ComputerName=USOLPWDW7361HNK.NAO.global.gmacfs.com
TaskCategory=None
OpCode=None
RecordNumber=358246
Keywords=Classic
Message=The Event log service was started.


6/25/18
6:08:14.000 AM  
06/25/2018 05:08:14 AM
LogName=System
SourceName=EventLog
EventCode=6006
EventType=4
Type=Information
ComputerName=USOLPWDW7361HNK.NAO.global.gmacfs.com
TaskCategory=None
OpCode=None
RecordNumber=358233
Keywords=Classic
Message=The Event log service was stopped.

[abhi04 · Accepted solution]

The below query works:

index=wineventlog EventCode=6005 OR EventCode=6006 |stats latest(EventCode) as EventCode, latest(_time) as _time by host|eval Time_Now=strftime(now(),"%Y-%m-%d %H:%M:%S")|
eval PC_OFF_Time=strftime(_time,"%Y-%m-%d %H:%M:%S")|eval Time_Since_PC_IS_OFF_IN_Hours=(strptime(Time_Now,"%Y-%m-%d %H:%M:%S")-strptime(PC_OFF_Time,"%Y-%m-%d %H:%M:%S"))/3600|
eval Message=case(EventCode="6005","PC ON",EventCode="6006","PC OFF")|table host, Message, PC_OFF_Time, Time_Now, Time_Since_PC_IS_OFF_IN_Hours|where Time_Since_PC_IS_OFF_IN_Hours>2 AND (Message="PC OFF")

[renjith_nair]

why do you need to convert time to a readable format and again convert it back to epoch format for substraction?

[abhi04]

I have to display the the time PC was off and the now time as well in a table format

[renjith_nair]

Hi @abhi04,

Try

    your base search |stats latest(EventCode) as EventCode,latest(_time) as _time |eval diff=(now()-_time)/3600
|where EventCode="6006" AND diff>2

[abhi04]

I was buiding the below query and I dont find result fro Time_Diff.

index=wineventlog EventCode=6005 OR EventCode=6006 |dedup host|eval Message=case(EventCode="6005","PC ON",EventCode="6006","PC OFF")
|eval Time_Now=strftime(now(),"%Y-%m-%d %H:%M:%S")|eval Time_Diff=Time_Now - _time|table host, Message, _time, Time_Now, Time_Diff

Althought format of _time and Time_Now are same.Any idea why?

[renjith_nair]

Hi @abhi04,
No, _time is in epoch format though Splunk shows as a readable in the output. So you could directy use eval Time_Diff=now()-_time where both are in epoch formats. Also dedup host deletes duplicate entries based on host. So if you have both event codes for a host , only one will be displayed