Splunk Search

How to filter small table results by large table results?

queriousGeorge
Engager

I have two searches that will return orderNumbers

1.
index=main "Failed insert" | table orderNumber
//returns small list

2.
index=main "Successful insert" | table orderNumber
//returns huge list

I want a list of "Failed insert" orderNumbers that have NOT had a "Successful insert" previously. How can I use the results of the second search to filter the results of the first search? 

Labels (3)
0 Karma

bowesmana
SplunkTrust
SplunkTrust

@queriousGeorge 

You can do this is a single search for both data sets and then calculating

index=main "Failed insert" OR "Successful insert"
| eval isFailed=if(match(_raw, "Failed insert"), 1, 0)
| stats min(isFailed) as isFailed by orderNumber
| where isFailed=1

so is 'isFailed' is set to 1 if the event is a failed one and then by finding the minimum you can see that any order numbers that have a value 0 have succeeded at least once, where the minimum value of isFailed of 1 indicates a failure with no success.

queriousGeorge
Engager

This ended up working for me:


`index=main "failed insert" | stats count by orderNumber | eval previousSuccess=0 | table orderNumber previousSuccess | join type=left orderNumber [search index=main “successful insert” | eval previousSuccess=1 | table orderNumber previousSuccess] | table orderNumber previousSuccess | where previousSuccess=0`

I needed a count of not only the fails, but the fails with a previous success.

0 Karma

bowesmana
SplunkTrust
SplunkTrust

You should try to avoid join - and in almost all cases, you can with stats.

If you want only failures which have also succeeded, then replace my previous last 2 lines with

| stats values(isFailed) as isFailed by orderNumber
| where mvcount(isFailed)=2

 So, this will trigger when you have both a 0 and 1

join has limitations, it's slower and can cause unpredictable results depending on your data volume.

Note also that neither of these will deal with any time ordering of the events. You're looking for 'previous' success, but this will not show you whether the success comes before the failure.

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to November Tech Talks, Office Hours, and Webinars!

🍂 Fall into November with a fresh lineup of Community Office Hours, Tech Talks, and Webinars we’ve ...

Transform your security operations with Splunk Enterprise Security

Hi Splunk Community, Splunk Platform has set a great foundation for your security operations. With the ...

Splunk Admins and App Developers | Earn a $35 gift card!

Splunk, in collaboration with ESG (Enterprise Strategy Group) by TechTarget, is excited to announce a ...