Splunk Search

How to compare inputlookup/lookup to search results and return the results from only the search?

CSULeigh
Explorer

I am searching for a user list that I have in a inputlookup/lookup CSV. I need to compare results from a search to the inputlookup/lookup list which is over 80k users and return only the new results that are not in the CSV. This is my search:

index=blahblah event_type=login
| convert timeformat="%m/%d/%Y" ctime(_time) AS date
| stats earliest(date) AS date by unique_id
| lookup userlist unique_id AS unique_id OUTPUTNEW unique_id AS match
| where isnull(match)

This does kind of work, but I believe I am hitting the max_output_limits for a subsearch.
Any help would be greatly appreciated.

0 Karma
1 Solution

starcher
SplunkTrust
SplunkTrust

As mentioned by @maciep there is no sub search there so no issue with limits.conf. Also to the previous comment lookups by default are case sensitive. If your data is lowercase but the column in the lookup is not then you will not get a match. Change the lookup definition to be insensitive.

index=blahblah event_type=login 
| stats min(_time) AS firstSeen by unique_id 
| lookup userlist unique_id OUTPUTNEW unique_id AS match 
| where isnull(match) 
| convert timeformat="%m/%d/%Y" ctime(firstSeen) AS date

View solution in original post

0 Karma

starcher
SplunkTrust
SplunkTrust

As mentioned by @maciep there is no sub search there so no issue with limits.conf. Also to the previous comment lookups by default are case sensitive. If your data is lowercase but the column in the lookup is not then you will not get a match. Change the lookup definition to be insensitive.

index=blahblah event_type=login 
| stats min(_time) AS firstSeen by unique_id 
| lookup userlist unique_id OUTPUTNEW unique_id AS match 
| where isnull(match) 
| convert timeformat="%m/%d/%Y" ctime(firstSeen) AS date
0 Karma

CSULeigh
Explorer

Thank you for the help. I forgot to uncheck the case sensitivity in the lookup settings

0 Karma

maciep
Champion

seems like that should work...and i don't see any subsearches in there. Are you expecting to see users in your results that you don't? Or do you see users you don't expect?

is the lookup case insensitive or is all of your unique_id's in the same case in the data and in the lookup?

0 Karma

CSULeigh
Explorer

when the search runs, I get results back that I should not see. I have read about the limits.conf maybe limiting the return of 10k rows.

All of the ID's are in lower case.

0 Karma

maciep
Champion

I don't think that limit comes into play with lookups, but I could be wrong. I would also check to make sure that you don't have any white space around the id field in your data and the one in the lookup.

0 Karma
Get Updates on the Splunk Community!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

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

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...