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!

Financial Services Industry Use Cases, ITSI Best Practices, and More New Articles ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Splunk Federated Analytics for Amazon Security Lake

Thursday, November 21, 2024  |  11AM PT / 2PM ET Register Now Join our session to see the technical ...

Splunk With AppDynamics - Meet the New IT (And Engineering) Couple

Wednesday, November 20, 2024  |  10AM PT / 1PM ET Register Now Join us in this session to learn all about ...