Splunk Search

How can I find values present in one table but not in other without subsearch?

bhavyajain
Engager

I have to prepare reporting dashboards in Splunk for which I used this query until now:

 

field1=GTIN_RECEIVED field2=NREC field3=*1234* field4=SNS  
NOT 
[search field1=MESSAGE_INVALID OR field1=GTIN_INVALID field2=NREC OR field2=PRODUCER field3=*1234* field4=SNS | dedup field5 
| fields field5 ] 

| dedup field5 
| table field5 
| rename field5 as gtin

 

The data size is huge now and the query takes too long to run which is becoming very difficult for me to generate dashboard.

 

Can someone pls help and simplify this query so that it takes minimal time.

Labels (7)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

I believe this should work

 

field3=*1234* field4=SNS ((field1=GTIN_RECEIVED field2=NREC) OR ((field1=MESSAGE_INVALID OR field1=GTIN_INVALID) (field2=NREC OR field2=PRODUCER)))
| stats values(field1) as field1 by field5
| where isnull(mvfind(field1, "MESSAGE_INVALID")) AND isnull(mvfind(field1, "GTIN_INVALID"))
| fields field5
| rename field5 as gtin

 

You are searching for both the events you want (GTIN_RECEIVED) as well as those you don't (MESSAGE_INVALID or GTIN_INVALID). 

Use stats to collect all field1 values for all field5 values. If your field1 value has either of the two values you do not want, then the where clause will exclude those.

I hope you are also including index constraints there also, as with that search you will be searching all the indexes you have visibility of.

Make sure you have ALL the constraints you can in the search to minimise the data set you are processing, e.g. include sourcetype constraint to if appropriate

View solution in original post

bowesmana
SplunkTrust
SplunkTrust

I believe this should work

 

field3=*1234* field4=SNS ((field1=GTIN_RECEIVED field2=NREC) OR ((field1=MESSAGE_INVALID OR field1=GTIN_INVALID) (field2=NREC OR field2=PRODUCER)))
| stats values(field1) as field1 by field5
| where isnull(mvfind(field1, "MESSAGE_INVALID")) AND isnull(mvfind(field1, "GTIN_INVALID"))
| fields field5
| rename field5 as gtin

 

You are searching for both the events you want (GTIN_RECEIVED) as well as those you don't (MESSAGE_INVALID or GTIN_INVALID). 

Use stats to collect all field1 values for all field5 values. If your field1 value has either of the two values you do not want, then the where clause will exclude those.

I hope you are also including index constraints there also, as with that search you will be searching all the indexes you have visibility of.

Make sure you have ALL the constraints you can in the search to minimise the data set you are processing, e.g. include sourcetype constraint to if appropriate

bhavyajain
Engager

Thanks. This worked!

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...