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!
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
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
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.
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
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
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
The eventcount is always 2 so this will always cut the total time in half but the overlap will still happen.