1

We have Splunk logs like:

ts=20:10:01 id=1 state=first foo=bar
ts=20:10:05 id=1 state=second foo=bar
ts=20:10:06 id=1 state=third foo=bar

ts=20:10:03 id=2 state=first foo=bar

ts=20:11:01 id=3 state=first foo=bar
ts=20:11:03 id=3 state=second foo=bar
ts=20:11:05 id=3 state=third foo=bar

I would like to find all id that does not have the other 2 states. In this example all id=2 that logged first state but not the other 2. I was reading on JOIN and found that it can only look at events that occur with both events but we can't exclude those events.

index=my-idx foo=bar 
| join id type=outer
  [search index=my-idx foo=bar NOT (state=second OR state=third) | table id]
| table id

The Query I am thinking of should return a list of ids that don't have the state=second or state=third which in the above example should return id=2

Sumitk
  • 1,367
  • 5
  • 18
  • 29
  • It is not clear exactly what you trying to achieve. Just to list the `id` where `foo=bar` and `state` value is not `second` or `third`? Are you looking for a distinct list of the `id` values? – Mads Hansen Jan 26 '21 at 23:48
  • @MadsHansen I am looking to get a list of all id like `id=2` which doesn't have the state=second or third logged. – Sumitk Jan 27 '21 at 00:05
  • Oh, okay - now I see. You would want to exclude id 1 and 3 because even though they have the event with state=first, they also have the other events with state=second and third. – Mads Hansen Jan 27 '21 at 00:06
  • @MadsHansen yes and thanks for asking that. Updated my question as well as it wasn't clear there. – Sumitk Jan 27 '21 at 00:09

2 Answers2

1

Here's a run-anywhere example query that should do it. Comments in the query explain what it does. It assumes the first state of any id is always "first".

| makeresults 
| eval data="ts=20:10:01 id=1 state=first foo=bar;
ts=20:10:05 id=1 state=second foo=bar;
ts=20:10:06 id=1 state=third foo=bar;
ts=20:10:03 id=2 state=first foo=bar;
ts=20:11:01 id=3 state=first foo=bar;
ts=20:11:03 id=3 state=second foo=bar;
ts=20:11:05 id=3 state=third foo=bar"
| eval data=split(data,";")
| mvexpand data
| eval _raw=data
| extract kvdelim=" ", pairdelim="="
| fields ts,id,state,foo
```Above just sets up test data```
```Count how many different states each id has```
| streamstats dc(state) as count by id
```Find the highest count for each id```
| eventstats max(count) as max by id
```Select only those with a single state```
| where max=1
| table ts id state foo
RichG
  • 4,202
  • 1
  • 12
  • 23
1
stats values(state) as all_states dc(state) as state_count by id
| search all_states="first" AND state_count=1
| table id
Mads Hansen
  • 53,910
  • 11
  • 106
  • 137