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

[t9jdc]

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!

[blues1990]

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

[somesoni2]

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

[t9jdc]

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.

[Stevelim]

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

[t9jdc]

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

[richgalloway]

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

[t9jdc]

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