Display running difference between multiple event counts

[pjbuchan596]

Hello all, I have two sets of sensors, and want to display a line graph for each of the set of sensors over time, giving whether or not they detect anything. I have 4 different event types, an "on" and "off" for each of the sensor sets that tell when the sensors start detecting something (on) and when they stop detecting something (off). I would like a line graph with a line depicting each of the sensor sets, going between "on" and "off" for each line to show activity.

As an example for what I'm trying / my thinking, I have this, though I'm getting an error with the streamstats command and recognize this implementation is likely far off.

index=main (SM_C.key="PresenceMonitor.highConfidenceUsage" OR SM_C.key="PresenceMonitor.highConfidenceUsage.start" OR SM_C.key="RoomMonitor.Occupied" OR SM_C.key="RoomMonitor.UnOccupied") SM_C.value.data.room!="error"
| streamstats eval(count(SM_C.key="RoomMonitor.Occupied") - count(SM_C.key="RoomMonitor.UnOccupied")) as A_in_use eval(count(SM_C.key="PresenceMonitor.highConfidenceUsage.start") - count(SM_C.key="PresenceMonitor.highConfidenceUsage")) as B_in_use
| eval fan=mvrange(1, 2.0001)
| mvexpand fan
| eval Usage=case(fan=1,"A", fan=2,"B")
| eval Value=case(fan=1,A_in_use, fan=2,B_in_use)
| chart values(Value) by Usage

The PresenceMonitor and the RoomMonitor are the two given sensor sets, with the occupied and start events giving the detection start (respectively) and the UnOccupied / Usage events giving the detection end (respectively). The use of mvexpand and case was an attempt to consolidate the 4 different events into 2 for charting the line graph.

Thank you for any help!

[micahkemp · Accepted solution]

Streamstats can only do aggregation functions, not mathematical. And your count/eval were switched around. To accomplish what that streamstats line appears to be attempting, you'd do:

| streamstats count(eval(SM_C.key="RoomMonitor.Occupied")) AS occupied_count, count(SM_C.key="RoomMonitor.UnOccupied") AS unoccupied_count count(eval(SM_C.key="PresenceMonitor.highConfidenceUsage.start")) AS start_count, count(SM_C.key="PresenceMonitor.highConfidenceUsage") AS high_confidence_count
| eval A_in_use=occupied_count-unoccupied_count, B_in_use=start_count-high_confidence_count

[pjbuchan596]

Definitely closer, I added two uses of the "eval" function inside two of the count calls. Looked like it wasn't attempting to match the key otherwise. Now looks as follows:

index=main (SM_C.key="PresenceMonitor.highConfidenceUsage" OR SM_C.key="PresenceMonitor.highConfidenceUsage.start" OR SM_C.key="RoomMonitor.Occupied" OR SM_C.key="RoomMonitor.UnOccupied") SM_C.value.data.room!="error"
| streamstats count(eval(SM_C.key="RoomMonitor.Occupied")) AS occupied_count, count(eval(SM_C.key="RoomMonitor.UnOccupied")) AS unoccupied_count count(eval(SM_C.key="PresenceMonitor.highConfidenceUsage.start")) AS start_count, count(eval(SM_C.key="PresenceMonitor.highConfidenceUsage")) AS high_confidence_count
| eval A_in_use=occupied_count-unoccupied_count, B_in_use=start_count-high_confidence_count
| table novum_in_use, occupied_count, unoccupied_count

I've added the table to view the output created. So far all of the "in_use" values and count values are reporting a count of 0, though I've verified that there are events they should be counting that match the criteria. This leads me to believe that the "eval(SM_C.key=X)" never return true. Currently investigating.

[micahkemp]

Can you explain why you want to use streamstats here? I'm not clear what it is you're trying to accomplish with that specific command.

[pjbuchan596]

I'm attempting to correlate 4 different events (a start and stop for each sensor set) into 2 line graphs (one for each sensor) on a single panel. As there should only ever be a difference of 1 between a sensor set's number of starts and stops, wanted to aggregate the sum of each to show when the sensors reported activity. I chose streamstats as it mentioned aggregated data and running the statistics on each new event, which I felt was what I wanted, though I'm certainly open to a different suggestion if there is a better implementation.

On another note, do you have any insight as to why the eval on the SM_C.key=X does not return a match? Thank you for all your help.

[micahkemp]

I think you just need to add single quotes around your SM_C.key in your eval. This run anywhere search shows the concept working:

| makeresults
| append [| makeresults | eval sensor="sensor1", _time=1000, SM_C.key="RoomMonitor.Occupied"]
| append [| makeresults | eval sensor="sensor2", _time=995, SM_C.key="RoomMonitor.Occupied"]
| append [| makeresults | eval sensor="sensor1", _time=993, SM_C.key="RoomMonitor.UnOccupied"]
| append [| makeresults | eval sensor="sensor2", _time=993, SM_C.key="RoomMonitor.Occupied"]
| streamstats count(eval('SM_C.key'="RoomMonitor.Occupied")) AS occupied_count, count(eval('SM_C.key'="RoomMonitor.UnOccupied")) AS unoccupied_count BY sensor
| eval current_occupied_diff=occupied_count-unoccupied_count

But keep in mind, also, that streamstats considers events above it (those already returned by the search), which are typically in reverse chronological order. This needs to be considered when you run your streamstats, you may need to reverse things first (but that may be a bad idea if there is a lot of data.

[pjbuchan596]

Thank you, that got the count for working for each of the different event types. Now left is the visualization.

[micahkemp]

Great!

If you consider the answer valid and satisfactory, please accept it so this question no longer appears open.

[pjbuchan596]

When I tried your answer as is I get the following error:

Error in 'streamstats' command: The eval expression for dynamic field 'eval(SM_C.key="RoomMonitor.Occupied") AS occupied_count, count(SM_C.key="RoomMonitor.UnOccupied")' is invalid. Error='The operator at ') AS occupied_count, count(SM_C.key="RoomMonitor.UnOccupied"' is invalid.'

Tried making the slight following adjustment, just for kicks, and received "The argument 'as' is invalid:

| streamstats count(eval(SM_C.key="RoomMonitor.Occupied")) AS occupied_count, count(eval(SM_C.key="RoomMonitor.UnOccupied")) AS unoccupied_count as A_in_use count(eval(SM_C.key="PresenceMonitor.highConfidenceUsage.start") AS start_count, count(SM_C.key="PresenceMonitor.highConfidenceUsage")) AS high_confidence_count
| eval A_in_use=occupied_count-unoccupied_count, B_in_use=start_count-high_confidence_count

Thank you for the help!

[micahkemp]

Oops, edited to correct 🙂