where username NOT equal to list of usernames in Lookup table

[r999]

i have some data indexed which is a snapshot of users who have access to a system.

i have uploaded a 1 column csv with a list of usernames who SHOULD have access to a system..

How do i use the lookup table to lo search:

source="user_snapshot" username != inputlookup "valid_users"

i.e i want the search to simply return list of the users who should not have access

[Ayn · Accepted solution]

This question seems similar enough to this: http://splunk-base.splunk.com/answers/32225/lookup-table-show-event-if-lookup-table-value-is-not-in-...

And possibly this: http://splunk-base.splunk.com/answers/31578/search-to-determine-what-is-missing-in-lookup-table?page...

Hopefully this should get you going!

[Ayn]

Use table.

... | table username

[r999]

thanks...

The only issues now is that this does not display as a table format where the first search did- just a list of events. how can i put this into a clean table of users which i can then add to a dashboard?

[Ayn]

Just switch the location of the search and the subsearch. You want to list all users in the snapshot and search for the ones that are in the snapshot but not in the lookup. I don't know what field names you're using in the lookup file, so I'm calling the relevant field here "username":

source="user_snapshot" NOT [| inputlookup valid_user.csv | fields username]

[r999]

hi thanks very much i actually got something working similar to the first link however that is returning the inverse of what i wanted and i cant workout how to change it...

results show "valid_users" who are not in the user snapshot

i would like to see list of users in the snapshot who are not Valid _users

how do i amend the follwing

| inputlookup valid_user.csv | search NOT [search source="user_snapshot*" | dedup username | fields username]

thanks in advance