Measuring inactivity in equipment according to x y z position values

[Roy1]

Hello I would like to make a query in which i can see how long my equipment has been inactive and when it was inactive preferably in a timechart.
I would like to define inactive in 2 ways. One is when x y and z have the same value +/-50 for 10 seconds or more
In these events 1000=950/1050 for the sake of inactivity

The second way is when there has been no new event from a piece of equipment for more than 10 seconds

Any help would be very much appriciated.

Below are some sample events and how long the equipment is active/inactive

12:00:10 x=1000 y=500 z=300 equipmentID=1
12:00:15 x=1000 y=500 z=300 equipmentID=1
12:00:20 x=1025 y=525 z=275 equipmentID=1
12:00:25 x=1000 y=500 z=300 equipmentID=1
(20 seconds of inactivity)
12:00:30 x=1600 y=850 z=60 equipmentID=1
12:00:35 x=1600 y=850 z=60 equipmentID=1
(15 seconds of activity)
12:03:00 x=1650 y=950 z=300 equipmentID=1
(135 seconds of inactivity)
12:03:05 x=1850 y=500 z=650 equipmentID=1
12:03:10 x=2500 y=950 z=800 equipmentID=1
12:03:15 x=2500 y=950 z=400 equipmentID=1
12:03:20 x=2500 y=950 z=150 equipmentID=1
(15 seconds of activity)

[bowesmana · Accepted solution]

@Roy1 Can I question your first (15 seconds of activity) where there are only 2 events

12:00:30 x=1600 y=850 z=60 equipmentID=1
12:00:35 x=1600 y=850 z=60 equipmentID=1

where x,y and z are the same, so I am not clear on how you get 15 seconds active. It seems like 5 or 10 seconds.  The 135 also seems like it should be 145

Here is an example using your data that gives you a timechart - NB: It's only done with a single equipmentID, so needs to be tested with more than one Id

| makeresults
| fields - _time
| eval d=split(replace("12:00:10 x=1000 y=500 z=300 equipmentID=1
12:00:15 x=1000 y=500 z=300 equipmentID=1
12:00:20 x=1025 y=525 z=275 equipmentID=1
12:00:25 x=1000 y=500 z=300 equipmentID=1
(20 seconds of inactivity)
12:00:30 x=1600 y=850 z=60 equipmentID=1
12:00:35 x=1600 y=850 z=60 equipmentID=1
(15 seconds of activity)
12:03:00 x=1650 y=950 z=300 equipmentID=1
(135 seconds of inactivity)
12:03:05 x=1850 y=500 z=650 equipmentID=1
12:03:10 x=2500 y=950 z=800 equipmentID=1
12:03:15 x=2500 y=950 z=400 equipmentID=1
12:03:20 x=2500 y=950 z=150 equipmentID=1
(15 seconds of activity)", "
","##"),"##")
| mvexpand d
| rename d as _raw
| extract
| rex "(?<time>\d+:\d+:\d+)"
| eval _time=strptime(time, "%H:%M:%S")
| eval description=if(isnull(_time), _raw, null())
| fields - _raw time
| where isnotnull(_time)
| sort _time
``` The above is just data setup ```
| streamstats time_window=10s count range(x) as r_x range(y) as r_y range(z) as r_z by equipmentID
| eval isIdleByCoord=if(r_x<=50 AND r_y<=50 AND r_z<=50, 1, 0)
| streamstats window=2 global=f range(_time) as r by equipmentID
| timechart fixedrange=f sum(eval(if(isIdleByCoord=1, r, null()))) as InActive by equipmentID

It calculates isIdleByCoord is calculated in a 10 second window. If there is no activity for a piece of equipment then it will not have a data point, so the 'r' calculation will show the range of the previous point.

Let me know if this helps

[Roy1]

Thanks for your reply. I think you are right on the example data I made some mistakes there. and the query works even with multiple ID's so thank you very much.