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

[CSULeigh]

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.

[starcher · Accepted solution]

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

[CSULeigh]

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

[maciep]

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?

[CSULeigh]

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.

[maciep]

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.