Splunk Search

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

lagle123
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

niketn
Legend

@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

niketn
Legend

@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!!!"
0 Karma

lagle123
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

DalJeanis
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

lagle123
New Member

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Use stats instead of eventstats

0 Karma

lagle123
New Member

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

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...