Splunk Search

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

Traer001
Path Finder

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?

Labels (2)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

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

_timeactionroomuserID
2021-05-22 01:31:07Go to23122
2021-05-22 01:31:37Exit23122
2021-05-22 01:32:07Go to23120
2021-05-22 01:32:37Exit24122
2021-05-22 01:33:07Go to23120
2021-05-22 01:33:37Exit23120
2021-05-22 01:34:07Go to23122
2021-05-22 01:34:37Exit25122
2021-05-22 01:35:07Go to26121

(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:

_timeactionuserIDroom
2021-05-22 00:52:50Go to25122
2021-05-22 00:54:50Go to23120
2021-05-22 00:56:50Go to25121
2021-05-22 00:59:50Go to24120
2021-05-22 01:00:50Go to26121
2021-05-22 01:02:50Go to25120
2021-05-22 01:03:50Go to23121
2021-05-22 01:04:50Go to26120
2021-05-22 01:05:50Go to24121

(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.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...