How to retrieve results for event only if its field does not match that of another event

[Traer001]

Hello,

I am trying to get only the events from my logs that have started a task (in this case, going to a room) and have not ended their task. So for instance I may have two different set of events for a user (each example is below).

In this first example, I would want to retrieve the fourth event because it is the latest "Go To Room" (or start task) that starts but does not have another event letting us know that it had left room 28 (there isn't an end task with the same field value of 28).

2021-05-21 16:34:22 UserId:123 Exit Room: 26
2021-05-21 16:34:12 UserId:123 Exit Room: 24
2021-05-21 16:34:08 UserId:123 Exit Room: 25
2021-05-21 16:33:59 UserId:123 Go To Room: 28
2021-05-21 16:33:52 UserId:123 Exit Room: 23

In this second example, I would NOT want to retrieve anything because the latest "Go To Room" task is finished with an "Exit Room" task with a matching field value of 28.

2021-05-21 16:34:22 UserId:123 Exit Room: 26
2021-05-21 16:34:12 UserId:123 Exit Room: 28
2021-05-21 16:34:08 UserId:123 Exit Room: 25
2021-05-21 16:33:59 UserId:123 Go To Room: 28
2021-05-21 16:33:52 UserId:123 Exit Room: 23

How should I go about this to retrieve the events I want?

[yuanliu]

If I understand the question correctly, you have a group of actors, some locations, and two cancelling acts.  You want to capture the events in which the act is not cancelled out by a subsequent act.

The concept is eventstats.  To apply it, I want to differentiate between the actual application and the algorithm somewhat.  In the application you described, if users are real persons (who cannot be in two rooms at the same time), all you need to do is to count whether actions against a room is even or odd.  But if I just wanted to answer the question as you described: whether a user entered a room and hasn't exited.

Assuming each event contains three fields, room, action, and userID, where action enumerates into "Exit" or "Go to".  This is a proof of concept:

| eventstats count latest(_time) as lastact by room action userID
| eval exitcount = if(action == "Exit", count, 0), gotocount = if(action != "Exit", count, 0)
| where gotocount > exitcount AND _time == lastact
| fields - count, exitcount, gotocount, lastact

 To test this, I use the following to generate an event sequence.

| makeresults count=30
| streamstats count
| eval _time = now() + count * 30, userID = 120 + random() % 3, action = if(count % 2 == 0, "Exit", "Go to"), room = 23 + (random() % 4)
| fields - count

 Sample vents can look like

_time	action	room	userID
2021-05-22 01:31:07	Go to	23	122
2021-05-22 01:31:37	Exit	23	122
2021-05-22 01:32:07	Go to	23	120
2021-05-22 01:32:37	Exit	24	122
2021-05-22 01:33:07	Go to	23	120
2021-05-22 01:33:37	Exit	23	120
2021-05-22 01:34:07	Go to	23	122
2021-05-22 01:34:37	Exit	25	122
2021-05-22 01:35:07	Go to	26	121

(Obviously because I'm assigning actions randomly, data will look like a bunch of drunk ghosts.)

If I put the event generator and the PoC code together,

| makeresults count=30
| streamstats count
| eval _time = now() + count * 30, userID = 120 + random() % 3, action = if(count % 2 == 0, "Exit", "Go to"), room = 23 + (random() % 4)
| fields - count
| eventstats count latest(_time) as lastact by room action userID
| eval exitcount = if(action == "Exit", count, 0), gotocount = if(action != "Exit", count, 0)
| where gotocount > exitcount AND _time == lastact
| fields - count, exitcount, gotocount, lastact

it will capture the unbalanced last act:

_time	action	userID	room
2021-05-22 00:52:50	Go to	25	122
2021-05-22 00:54:50	Go to	23	120
2021-05-22 00:56:50	Go to	25	121
2021-05-22 00:59:50	Go to	24	120
2021-05-22 01:00:50	Go to	26	121
2021-05-22 01:02:50	Go to	25	120
2021-05-22 01:03:50	Go to	23	121
2021-05-22 01:04:50	Go to	26	120
2021-05-22 01:05:50	Go to	24	121

(Because the generator is random, these results are not based on the sample given above.) Cancelling acts are probably not what you are really after.  But I think you can use eventstats to do the math you needed.