Multiple events - datetime field

[eholz1]

Hello, I am trying to find out how to calculate the duration between a device returning from
a "DOWN" state. My search looks for UP/DOWN events on a per Device ID
I can have multiple up/down events for a given device ID. I have a DateTime field for when the device went down, and when it came
back up via Status=UP or Status=DOWN
here is my search: index=device_index sourcetype=device-syslog-ng DeviceID=626 | dedup DateTime
this search returns two UP/DOWN events for this ID - I would like to see a duration = deviceUP-deviceDown, my date time field is
2019/04/20 18:05:03, etc the device could be down for a day or two, so I need the date field there. I have seen transaction and stats usage but cannot see how to relate the datetime for the event based on status of up/down. The _time value does not represent the actual time the device went up or down, but the time entered into splunk.. I can have multiple UP/DOWN events for one device, etc.

any suggestions?
Thanks so much,
eholz1

[richgalloway · Accepted solution]

Date time strings cannot be subtracted from each other. You have to convert them into integers using strptime first. This will give you the down time in seconds.

... | eval duration = strptime(deviceUP, "%Y/%m/%d %H:%M:%S") - strptime(deviceDown, "%Y/%m/%d %H:%M:%S") | ...

[eholz1]

another question:
my search gives me 4 lines for my device, 4 events, cycling from Down to Up for the devices. I have the time via strptime formatting
but I cannot see how to subtract the time from Down event from the time for UP event, Here is the search:
index=cacti_index sourcetype=cacti-syslog-ng DeviceID=626 Status="UP" OR Status="DOWN" | dedup DateTime | rename DateTime as time2 | eval timeRng = strptime(time2,"%Y/%m/%d %H:%M:%S")

I dedup the DateTime because for this device I has 3 up events and 2 down events

Thanks,
Eholz1

[eholz1]

Hello Rich,

Wow, thanks for this! I do have one question: my deviceUP and my deviceDown are on separate lines in the output of the search,
would I use a "where" clause to grab the UP - Down times? my initial search returns two lines (events) one for the UP, and one for the DOWN.

I will do the conversion, etc.

Thanks again,
Eholz1,

[eholz1]

Hello, Thanks for this, I can get the times, but I am having trouble correlating the time from the "DOWN" event and the "UP" event. They are on separate lines in the output of search. I will check your next post,
thanks

[richgalloway]

Try stats.

... | stats latest(deviceUP) as deviceUP, latest(deviceDown) as deviceDown by DeviceID | eval duration = strptime(deviceUP, "%Y/%m/%d %H:%M:%S") - strptime(deviceDown, "%Y/%m/%d %H:%M:%S") | ...

[eholz1]

Thanks again,
Here is my basic search
index=cacti_index sourcetype=cacti-syslog-ng DeviceID=626 | dedup DateTime | rename DateTime as time2 | eval timeRng = strptime(time2,"%Y/%m/%d %H:%M:%S")
| eval time1=if(Status="DOWN",strptime(time2,"%Y/%m/%d %H:%M:%S"),"")

time1 is the time went down, timeRng (for the up device) is the time it went "UP". I am not seeing how to relate the two time values, up/down