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!

Splunk Observability Cloud's AI Assistant in Action Series: Auditing Compliance and ...

This is the third post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Splunk Community Badges!

  Hey everyone! Ready to earn some serious bragging rights in the community? Along with our existing badges ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

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