Splunk Search

How to add event duration time for multiple events into one row?

New Member

Hello,

I'm trying to find the correct syntax to get the total time a device was in an alert status. The events have a start and stop time but occur multiple times. I've been able to get the total time of each occurrence which produces multiple rows, I want a single row indicating total time the device was in an alert status

index = base search
| transaction exporter_ip alarmID startswith=eval alarm_status="start") endswith=eval(alarm_status="end") 
| eval duration = tostring(duration,"duration") 
| eventstats sum(duration) by device_name |table device_name ,duration

oh so close but not close enough.

0 Karma
1 Solution

SplunkTrust
SplunkTrust

@lagle123, you might have to elaborate on "total time a device was in an alert status". You should also add sample data for multiple start stop condition. For example does following hold good... (1)start (2)start (3)stop (4)stop (5)start ... etc as consecutive events for the same device? Can you share your current output and then exemplify what is missing as per your requirement?

You should check out feasibility of stats instead of transaction and eventstats. Based on your query seems like exporter_ip and alarmID are the key to find unique device.

  <YourBaseSearch> alarm_status="start" OR alarm_status="end" 
 | stats values(alarm_status) as alarm_status count as eventcount min(_time) as FirstTime max(_time) as LastTime by exporter_ip alarmID device_name
 | search eventcount>1 alarm_status="start" alarm_status="end" 
 | eval duration = LastTime-FirstTime
 | eval _time=FirstTime 
 | table device_name ,duration
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

@lagle123, you might have to elaborate on "total time a device was in an alert status". You should also add sample data for multiple start stop condition. For example does following hold good... (1)start (2)start (3)stop (4)stop (5)start ... etc as consecutive events for the same device? Can you share your current output and then exemplify what is missing as per your requirement?

You should check out feasibility of stats instead of transaction and eventstats. Based on your query seems like exporter_ip and alarmID are the key to find unique device.

  <YourBaseSearch> alarm_status="start" OR alarm_status="end" 
 | stats values(alarm_status) as alarm_status count as eventcount min(_time) as FirstTime max(_time) as LastTime by exporter_ip alarmID device_name
 | search eventcount>1 alarm_status="start" alarm_status="end" 
 | eval duration = LastTime-FirstTime
 | eval _time=FirstTime 
 | table device_name ,duration
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

0 Karma

New Member

Thank you for the assistance, I dropped the transaction command out and went with the eventstats along with your recommended search parameters but got the same results. The total event(alert) time is presented but in multiple rows. I would like to total the duration time in one row by device name

current out put
device_name duration
router1 5
router1 3
router1 2

desired output
device_name duration
router1 10

Thanks for taking the time to assist.

0 Karma

SplunkTrust
SplunkTrust

@lagle123 - Did the corrected code niketnilay posted, using stats, solve your problem? If so, please accept the answer to close the question.

0 Karma

New Member

Thank you that search you provided worked, thank you for the assistance.

0 Karma

SplunkTrust
SplunkTrust

Use stats instead of eventstats

0 Karma

New Member

Thank you that search you provided worked, thank you for the assistance.

0 Karma