How Do I Exclude Data Based On An Event?

[Wagzillion]

Hello Everyone, 

I am hoping someone can help me out as I have exhausted everything I can think of and cannot seem to get anything to work. Essentially what I am looking to do is pull results to get a total based off an ID. The issue I am running into is that the ID will have between 1-4 events associated to it. These events are related to the status. 

I am only wanting to get the results for any ID that are Open and Escalated but the issue I am running into is that it is pulling all of the events even of those that have since had the status changed to closed or another status. 

I am wanting to excluded all of the events for IDs that have had their status changed to anything other than Open or Escalated. The other trouble that I am running into is that this "status" event is occuring in the metadata of the whole transaction. I have the majority of my query built out but where I am struggling is removing the initial Open and Escalated events for the alerts that the status was changed. The field the status changes in is under "logs" and then "logs{}.action".

[yuanliu]

Please share event with raw text, not search app's format.

Regardless, you should not need any regex to deal with this data because Splunk already extracted everything.  Secondly, you do not need to consider logs{}.action because your requirement only concerns status "Open" and "Escalated".  What actions have been taken is irrelevant to filter.

In other words, given status and id like the following:

_time	id	status
2025-01-10 23:24:57	xxx10	Escalated
2025-01-10 23:17:57	xxx10	Other
2025-01-10 23:10:57	xxx10	Open
2025-01-10 23:03:57	xxx10	Other
2025-01-10 22:56:57	xxx10	Open
2025-01-10 23:30:57	xxx11	Closed
2025-01-10 23:23:57	xxx11	Closed
2025-01-10 23:16:57	xxx11	Open
2025-01-10 23:09:57	xxx11	Escalated
2025-01-10 23:02:57	xxx11	Other
2025-01-10 22:55:57	xxx11	Open
2025-01-10 23:29:57	xxx12	Assigned
2025-01-10 23:22:57	xxx12	Open
2025-01-10 23:15:57	xxx12	Closed
2025-01-10 23:08:57	xxx12	Closed
2025-01-10 23:01:57	xxx12	Open
2025-01-10 22:54:57	xxx12	Escalated
2025-01-10 23:28:57	xxx13	Open
2025-01-10 23:21:57	xxx13	Open
2025-01-10 23:14:57	xxx13	Assigned
2025-01-10 23:07:57	xxx13	Open
2025-01-10 23:00:57	xxx13	Closed
2025-01-10 22:53:57	xxx13	Closed
2025-01-10 23:27:57	xxx14	Assigned
2025-01-10 23:20:57	xxx14	Escalated
2025-01-10 23:13:57	xxx14	Open
2025-01-10 23:06:57	xxx14	Open
2025-01-10 22:59:57	xxx14	Assigned
2025-01-10 22:52:57	xxx14	Open
2025-01-10 23:26:57	xxx15	Open
2025-01-10 23:19:57	xxx15	Open
2025-01-10 23:12:57	xxx15	Assigned
2025-01-10 23:05:57	xxx15	Escalated
2025-01-10 22:58:57	xxx15	Open
2025-01-10 22:51:57	xxx15	Open
2025-01-10 23:25:57	xxx16	Open
2025-01-10 23:18:57	xxx16	Other
2025-01-10 23:11:57	xxx16	Open
2025-01-10 23:04:57	xxx16	Open
2025-01-10 22:57:57	xxx16	Assigned

You only want to count events for id's xxx10 (last status Escalated), xxx13 (Open), xxx15 (Open), and xxx16 (Open).  Using eventstats is perhaps the easiest.

| eventstats latest(status) as final_status by id
| search final_status IN (Open, Escalated)
| stats count by id final_status

Here, final_status is thrown in just to confirm that final_status only contains Open or Escalated.  The above mock data will result in

id	final_status	count
xxx10	Escalated	5
xxx13	Open	6
xxx15	Open	6
xxx16	Open	5

Here is the emulation that generates the mock data.  Play with it and compare with real data.

| makeresults count=40
| streamstats count as _count
| eval _time = _time - _count * 60
| eval id = "xxx" . (10 + _count % 7)
| eval status = mvindex(mvappend("Open", "Assigned", "Other", "Escalated", "Closed"), -(_count * (_count % 3)) % 5)
``` data emulation above ```

Hope this helps.

[isoutamo]

Can you show some sample events and SPL what you have tried?

[Wagzillion]

Yes ofcourse Here is my example data. On the left is the alert when it first comes in and on the right is after it has been reviewed and closed.

Alert Received

Alert Closed

alert_type: search query    asset: { [+]    }    asset_term: null    content_created_at: 2017-01-10T11:00:00+00:00    escalated: false    id: XXXXXX112    last_modified: 2025-01-09T16:33:37Z    logs: [ [-]      { [-]        action: open        detail:        id:         subject:        timestamp: 2025-01-09T16:33:37+00:00      }      { [-]        action: modify tags        detail:        id:         subject:        timestamp: 2025-01-09T16:33:37+00:00      }    ]    metadata: { [+]    }    network: domains    severity: 4    status: Open    timestamp: 2025-01-09T16:33:37+00:00    timestamp_modify_tags: 2025-01-09T16:33:37+00:00

alert_type: search query    asset: { [+]    }    asset_term: null    content_created_at: 2017-01-10T11:00:00+00:00    escalated: false    id: XXXXXX112    last_modified: 2025-01-09T17:10:52Z    logs: [ [-]      { [-]        action: close        detail:        id:         subject:        timestamp: 2025-01-09T17:10:52+00:00      }      { [-]        action: modify notes        detail:        id:         subject:        timestamp: 2025-01-09T17:10:48+00:00      }      { [-]        action: assign        detail:        id:         timestamp: 2025-01-09T17:09:25+00:00      }      { [-]        action: open        actor:        detail:        id:         subject:        timestamp: 2025-01-09T16:33:37+00:00      }      { [+]      }    ]    metadata: { [+]    }    network: domain    severity: 4    status: Closed    timestamp: 2025-01-09T16:33:37+00:00    timestamp_modify_notes: 2025-01-09T17:10:48+00:00    timestamp_modify_tags: 2025-01-09T16:33:37+00:00

I tried initially to just dedup but that was before I knew it had mulitple events it was pulling in. Since then I have tried the following: 

1. I tried doing an mvindex on the status events but it was still pullin in all of the events.

2. I then tried doing the lastest(status) but realized that was only going to pull in what the actualy lastest status of the ID was and would still include all of the events. 

3. I also tried doing a sub search per some guidance from a colleague that ended up looking lik the following: 

| dedup id

| where  [ search index=source | stats latest(status) as latest_status by id | where latest_status="closed" | return $id ]

4. Lastly, I tried going at the metadata which looked like the following: 

| dedup id | fields id | format | rex mode=sed field=search "s/ / OR /g" | eval search="NOT (id IN (" + search + "))" | fields search | format "" "" "" "" "" "" "search"

and turned into this

[ | search index="source" NOT (status="Open" OR status="Escalated") | stats count by id | fields id | format "" "" "" "" "," "OR" "id!=" | rex mode=sed field=search "s/^(.*)$/NOT (id IN (\1))/"

[PickleRick]

Dedup is rarely the way to go.

OK. So you have some json events (for future reference - It's better to copy-paste _raw_ event, not the rendered form from the Splunk UI into a code block or a preformatted-styled paragraph.

I assume all your events for a single alert share the id field, right? I'm not sure however what is the relation between those two evens since one seems to contain subset of the data contained in the other one. It looks a bit wasteful if you're not just logging changes in your alert's state but repeat the growing "history" with each subsequent event.

What are you trying to get from those event then?

[Wagzillion]

I don't need those really. I only need the ones that have not been updated so the status is still Open or Esclated as I am trying to get a number for volume on what is still outstanding. So yes you are correct, these events are associted with the same ID but there are different IDs. What I want is to exclude all of the IDs where the status has been updated and closed (I don't want them to show the open or escalated event). 

[PickleRick]

Ok. So these actually were different ids? Because of your anonymization they looked as if they were to be the same id.

So you have only one event per each id? And you want to exclude those that have action=closed or some other values?

As far as I can see your jsons should parse so that you get a multivalued field some.path{}.log.action, am I right?

If so, you can use normal field=value conditions. Just remember that with multivalued fields key!="myvalue" matches an event where there is at least one value in the field key not equal to myvalue whereas NOT key="myvalue" requires that none of the values in the field key match myvalue (or the field is empty).