Getting Data In

Multiple events - datetime field

Path Finder

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

0 Karma
1 Solution

SplunkTrust
SplunkTrust

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") | ...
---
If this reply helps you, an upvote would be appreciated.

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

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") | ...
---
If this reply helps you, an upvote would be appreciated.

View solution in original post

0 Karma

Path Finder

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

0 Karma

Path Finder

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,

0 Karma

Path Finder

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

0 Karma

SplunkTrust
SplunkTrust

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") | ...

---
If this reply helps you, an upvote would be appreciated.
0 Karma

Path Finder

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

0 Karma