Splunk Search

How to edit my search to filter out all but 1 concurrent event?

t9jdc
Engager

In my current run, if two estops / jams are active at the same time, it will count count every minute they are both in alarm as 2 minutes for the "MinutesInAlarm" field. I need them to count as 1 real minute, no matter how many are active in that field at that time

This is my current search:

index=4_ip_sql source=CNVIP101 Priority=4 Quality=192 date_hour>=11 date_hour<24 (Message="*jam*" OR Message="*stop*") 
| eval AlarmType=if(like(Message,"%Jam%"),"Jam","E-Stop") 
| dedup EventID 
| eval PLC=mvindex(split(SourceName," "),0) 
| transaction SourceName startswith="Active=1" endswith="Active=0" maxevents=2
| stats count as Alarms, sum(eval(duration/60)) as MinutesInAlarm by PLC AlarmType
| eval MinutesInAlarm=round(MinutesInAlarm)
| sort PLC

Thanks!

0 Karma

blues1990
Explorer
index=4_ip_sql source=CNVIP101 Priority=4 Quality=192 (Message="*jam*" OR Message="*stop*")  | eval AlarmType=if(like(Message,"%Jam%"),"Jam","E-Stop") | dedup EventID   | eval PLC=mvindex(split(SourceName," "),0)   | 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>=11 date_hour<24  | 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

somesoni2
SplunkTrust
SplunkTrust

Give this a try. For alert started on same minute, it will only consider the duration of alarm with max duration, completely removing overlapping duration.

index=4_ip_sql source=CNVIP101 Priority=4 Quality=192 date_hour>=11 date_hour<24 (Message="*jam*" OR Message="*stop*") 
 | eval AlarmType=if(like(Message,"%Jam%"),"Jam","E-Stop") 
 | dedup EventID 
 | eval PLC=mvindex(split(SourceName," "),0) 
 | transaction SourceName startswith="Active=1" endswith="Active=0" maxevents=2
 | bucket span=1m _time | eventstats max(duration) as max by _time PLC AlarmType
 | where duration=max
 | stats count as Alarms, sum(eval(duration/60)) as MinutesInAlarm by PLC AlarmType
 | eval MinutesInAlarm=round(MinutesInAlarm)
 | sort PLC
0 Karma

t9jdc
Engager

the overlaps are still counting on top of coexisting times, unfortunately. For example, if a jam occurs at 1:15 and another jam occurs in the same PLC area at 1:18 and they are not resolved until 1:30, it will say the time of jam is 28 minutes instead of the desired, and correct, 15.

0 Karma

Stevelim
Communicator

Any reason why you are using transaction on the SourceName instead of the PLC that you stripped off?

From my understanding:

Base search for machine message stop or Jam

| Create the respective alarm since either the machine jams or is E stopped by operator
| dedup eventID <== to remove duplicates, im assuming the machine sends multiple messages with various IDs
| Assign and identify the PLCs which I assumed you are reading off using the sourcenames
| Group the events by Sourcename which contains the names of the PLC, the 2 events where it was first On and off
| analytics command for charting the number of alarms, the total duration according to the PLC Alarm (Jam or Estopped)
| Round off the minutes
| Sort by PLC

0 Karma

t9jdc
Engager

So I wanted to connect 2 events (one where the alarm turns on and one where it turns off). So the SourceName is unique to a given alarm, where as each PLC has hundreds of different alarms. So if you join the transaction on PLC it could join together different alarms like this:

PLC1_Alarm1 on
PLC1_Alarm2 off

Since they have the same PLC

0 Karma

richgalloway
SplunkTrust
SplunkTrust

How about this?

index=4_ip_sql source=CNVIP101 Priority=4 Quality=192 date_hour>=11 date_hour<24 (Message="*jam*" OR Message="*stop*") 
| eval AlarmType=if(like(Message,"%Jam%"),"Jam","E-Stop") 
| dedup EventID 
| eval PLC=mvindex(split(SourceName," "),0) 
| transaction SourceName startswith="Active=1" endswith="Active=0" maxevents=2
| stats count as Alarms, sum(eval(duration/60/eventcount)) as MinutesInAlarm by PLC AlarmType
| eval MinutesInAlarm=round(MinutesInAlarm)
| sort PLC
---
If this reply helps you, Karma would be appreciated.
0 Karma

t9jdc
Engager

The eventcount is always 2 so this will always cut the total time in half but the overlap will still happen.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

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 ...