Splunk Search

How to exclude results based on previous results

dudiventura
New Member

Hi Splunkers,
I'm pretty new to Splunk and trying to exclude events based on previous results.
Here is an example of my main search results:

  1. 9/16/19 2:05AM id1=1111 id2=aaaa error = -1
  2. 9/16/19 2:00AM id1=2222 id2=xxxx error = -1
  3. 9/16/19 1:50AM id1=1111 id2=xxxx error = 0
  4. 9/16/19 1:48AM id1=1111 id2=xxxx error = 100
  5. 9/16/19 1:45AM id1=1111 id2=aaaa error = 0
  6. 9/16/19 1:30AM id1=1111 id2=xxxx error = 0
  7. 9/16/19 1:05AM id1=2222 id2=bbbb error = -1
  8. 9/16/19 1:00AM id1=2222 id2=bbbb error = 0

The events I wish to exclude are the ones that has error = -1 and in the last 30 min there is error = 0 for the same id1 and id2.
For the example above, event 1 should be excluded because of event 5 and event 7 should be excluded because of event 8.

And the result list I wish to have is:
1. 9/16/19 2:00AM id1=2222 id2=xxxx error = -1
2. 9/16/19 1:50AM id1=1111 id2=xxxx error = 0
3. 9/16/19 1:48AM id1=1111 id2=xxxx error = 100
4. 9/16/19 1:45AM id1=1111 id2=aaaa error = 0
5. 9/16/19 1:30AM id1=1111 id2=xxxx error = 0
6. 9/16/19 1:00AM id1=2222 id2=bbbb error = 0

Not sure what is the best approach here.

Thanks for the helpers
David

0 Karma

Richfez
SplunkTrust
SplunkTrust

When I plug your above data into an index on my system...

host="maxwell" index="answers_772425" sourcetype="csv"
| transaction maxspan=30m id1, id2 
| stats list(error) AS error_types, first(_time) as _time BY id1, id2
| eval is_in_error = if(error_types!=0, 1, 0)

This produces the fourresults:

> id1   id2 error_types _time          is_in_error
> 1111  aaaa    -1 0     2019-09-16 01:45:00    0
> 1111  xxxx    0 100   2019-09-16 01:30:00 0
> 2222  bbbb    -1 0     2019-09-16 01:00:00    0
> 2222  xxxx    -1     2019-09-16 02:00:00  1

I know it's not quite like your list, but ... your results list doesn't seem to fit right. If you don't want to include line because of line 4 (or 5, whatever it is) then why does your results include line 1?

All right, explanations - and note that you'll replace my line 1 with your base search, but that everythign after that you can run once each line to see each results, then just add the next line and so on:
MY line 1: just a base search.

2: transaction is a useful command in these cases because it can maxspan. It takes multiple events and "piles them into one event", in this case whenever they happen within 30 minutes of one another and they have the same id1 and id2, make them into a single event.

3: stats is then used to sort of "remove all the extra information". We get a list of all the errors (and call it "error_types" for each of the events being spit out by transaction above. "error_types" is a multi-valued field now - it can take on more than one value for each event. You'll see for example the first item in my list above has a "-1" and "0" as values both.

4: Lastly, we create a new field "is_in_error" that is a flag to tell us if the entire thing is in error. Which just means check that if all the error_types in any particular newly smashed together and sifted events are ALL non-zero (because it searches all the multi-valued values at once for each event), the the thing is in error. If any of them are zero, then it's not in error (because regardless of how many error codes there may be in the list, we only care that at least ONE of them is non-zero).

Of course you could replace 4 above with something like | search is_in_error = 1 to display just the error ones.

Or you could change line 4 to | eval error_condition = if(error_types!=0, "ERROR", "OK") too, then it would just fill that column with "OK" and "ERROR" based on what we found.

Anyway, hope this helps, and happy Splunking!
-Rich

0 Karma

Sukisen1981
Champion

not clear , you say - event 1 should be excluded because of event 4
and your condition is - error = -1 and in the last 30 min there is error = 0 for the same id1 and id2.
but event 1 has id2=aaaa and event 4 has id2=xxxx, also error=100
can you clarify a bit more?

0 Karma

dudiventura
New Member

You are right, I'm sorry.
Added event number 4 just before I've posted the question in order to exemplify that another error codes are possible and totally forgot to edit the condition.

So what actually supposed to be written there is that even 1 should be excluded because of event 5 and event 7 should be excluded because of event 8.

Thank you for pointed that - fixed it also in the question.

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