Splunk Search

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


For this query:

index=4ipsql source=CNVIP101 Priority=4 Quality=192 (Message="jam" OR Message="stop" OR Message="halt" OR Message="fault") datewday!="Saturday" datewday!="Sunday" | eval datehour=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 (datehour>=8 datehour<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


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