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

View solution in original post

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
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!