Splunk Search

Without using a subsearch, how do you find events that are not in a lookup table?

brajaram
Communicator

I have a lookup table filled with thousands of user IDs. I have a log filled with tens of thousands of user IDs. I am trying to find the user IDs in the lookup table that are not in the log.

|inputlookup lookuptable.csv
|search NOT [index=... rest of search query here|stats count by userID|fields - count]

I use the stats count by userID clause to essentially table and dedup the events to get only the userID, which I then feed back into the lookup table to find which events are not in the list.

The problem with this search is that the subsearch returns tens of thousands of results, so I end up hitting the maxout. I would need to increase the maxout to 100,000 to see all the results, which isn't possible to do. Is there a way I can get the same analysis without using a subsearch?

0 Karma
1 Solution

kamlesh_vaghela
SplunkTrust
SplunkTrust

@brajaram

Can you please try this?

index=... rest of search query here 
| stats count by userID 
| fields - count 
| eval flg1=1 
| append 
    [| inputlookup lookuptable.csv | fields userID 
    | eval flg2=1 ] 
| stats values(flg1) as flg1,values(flg2) as flg2 by userID 
| where isnull(flg1) AND isnotnull(flg2)

Thanks

View solution in original post

kamlesh_vaghela
SplunkTrust
SplunkTrust

@brajaram

Can you please try this?

index=... rest of search query here 
| stats count by userID 
| fields - count 
| eval flg1=1 
| append 
    [| inputlookup lookuptable.csv | fields userID 
    | eval flg2=1 ] 
| stats values(flg1) as flg1,values(flg2) as flg2 by userID 
| where isnull(flg1) AND isnotnull(flg2)

Thanks

brajaram
Communicator

Works perfectly! Didn't know I could use append like that, thanks for the help.

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

Yeah, With the different data sets, Splunk commands combinations works mind blowing. like combination of append and stats commands are awesome.

Glad to help you..

!!! Happy Splunking !!!

0 Karma

brajaram
Communicator

One other question, is there a way I would be able to do this is if the lookup table is larger than the maxout?

Or more specifically, if I have two separate searches, like the following:

index=.... params=...
|stats count by userID
|fields - count

and

index=.... params=somethingelse
|stats count by userID
|fields - count

Both searches return more than 100,000 events, so if I want to track down where users were in search 1 but not in search 2, I still can't use the solution posted due to the maxout.

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

Yes, many command has it's own limit. You can find it in limits.conf.

http://docs.splunk.com/Documentation/Splunk/6.1/admin/Limitsconf

But in this case you have to execute search again and again to filter users upto when results count is not in such proper manner. Each results you have to store in another lookup, which will use in main search to filter those users which is already considered.

0 Karma
Get Updates on the Splunk Community!

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...

Ready, Set, SOAR: How Utility Apps Can Up Level Your Playbooks!

 WATCH NOW Powering your capabilities has never been so easy with ready-made Splunk® SOAR Utility Apps. Parse ...