How do I filter my search using inputlookup with a CSV file?

[jj85]

I have created a search that searches for any Windows logon events in my environment.

index=windows EventID=528 OR EventID=540 OR EventID=4624 OR EventID=4776
| dedup Computer,IpAddress,TargetUserName 
| rename Computer as DestinationHost, IpAddress as SourceIP, TargetUserName as SourceUsername 
| table _time, SourceIP, SourceUsername, DestinationHost

I also have an inputlookup named identities.csv which contains Active Directory information for users, including a field labeled "department".

How can I create a subsearch that takes the above search, checks identities.csv for the username and department, and excludes results where the user is in a specific department?

[javiergn · Accepted solution]

I will apply a lot of filtering first to speed up your query performance.
If you only want to present 4 or 5 fields, then use fields or table as early as possible to get rid of the non-required ones.

Take a look at this instead:

index=windows EventID=528 OR EventID=540 OR EventID=4624 OR EventID=4776
| fields _time, Computer, IpAddress, TargetUserName
| lookup IDENTITIES_LOOKUP TargetUserName AS UserNameFieldInYourCSV OUTPUT department AS Department
| rename Computer as DestinationHost, IpAddress as SourceIP, TargetUserName as SourceUsername
| search NOT Department = "Department I do not want to see"
| table _time, SourceIP, SourceUsername, DestinationHost, Department

[jj85]

This is exactly what I needed, thank you!

[sundareshr]

What you need is the lookup command. Like this.

.... | table _time, SourceIP, SourceUsername, DestinationHost | lookup lookupfile.csv <<name of the col with users>> AS SourceUsername OUTPUT department as Dept | where Dept != "accounting"