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?
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
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
This is exactly what I needed, thank you!
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"