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

[lagle123]

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.

[niketn · Accepted solution]

@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

[lagle123]

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.

[DalJeanis]

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

[lagle123]

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

[somesoni2]

Use stats instead of eventstats

[lagle123]

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