Splunk Search

How would I add in a column to list the average duration of an event?

blues1990
Explorer

For this query:

index=4_ip_sql source=CNVIP101 Priority=4 Quality=192 (Message="jam" OR Message="stop" OR Message="halt" OR Message="fault") date_wday!="Saturday" date_wday!="Sunday" | eval date_hour=strftime(_time,"%H") | eval AlarmType=case(like(Message,"%Jam%"),"Jam",like(Message,"%Fault%"),"Fault",like(Message,"%Halt%"),"Halt",like(Message,"%Stop%"),"E-Stop") | dedup EventID | rex field=SourceName "(?MCP[0-9A-Za-z]*)\s" | eval Area=case( PLC="MCP01","Receiving", PLC="MCP02A",case(like(Message,"%@ Bins Lvl 2 East%"),"Bins Lvl 2 East",like(Message,"%Tote%"),"Tote Recycle",1=1,"Bin Entry"), PLC="MCP02B","Bins Lvl 2 West", PLC="MCP03A","ACA", PLC="MCP03B","ACA", PLC="MCP04A","Small and Large Pack East", PLC="MCP04B","Small Pack West", PLC="MCP05A",case(like(Message,"%Bins Lvl 1 East%"),"Bins Lvl 1 East",1=1,"Rackline Exit"), PLC="MCP05B",case(like(Message,"5%"),"Bins Lvl 2 West",1=1,"Bins Lvl 2 East"), PLC="MCP06",case(like(Message,"%Bins Lvl 1 West%"),"Bins Lvl 1 West",1=1,"Bins Lvl 1 East"), PLC="MCP07A",case(like(Message,"%Shipping%"),"Shipping",1=1,"Large Autoscale"), PLC="MCP07B","Small Autoscale") | transaction SourceName startswith="Active=1" endswith="Active=0" maxevents=2 | search duration>0 (date_hour>=8 date_hour<17 OR (date_hour=17 AND date_minute<45)) | eval endTime=_time+duration | sort 0 Area AlarmType -_time | delta _time as startToNextStart p=1 | delta endTime as endToNextEnd p=1 | fillnull startToNextStart value=duration | eval endToNextEnd=if(endToNextEnd>0,1,-endToNextEnd) | eval startToNextStart=if(startToNextStart>0,duration,-startToNextStart) | eval duration=if(duration<=startToNextStart,duration,if(endToNextEnd>0,startToNextStart,startToNextStart-endToNextEnd)) | stats count as TotalAlarms, sum(eval(duration/60)) as "MinutesInAlarm" by Area AlarmType | eval MinutesInAlarm=round(MinutesInAlarm,0) | sort AlarmType Area

0 Karma

sundareshr
Legend

When you say average duration by event, I assume you mean average duration for all events by alarm_type. You could just add avg(duration) to your statscommand. Like this

| stats count as TotalAlarms, sum(eval(duration/60)) as "MinutesInAlarm" avg(duration) as avgduration by Area AlarmType
0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...