Use lookup to find out if a user is NOT in an Active Directory group

[mdavis43]

We're trying to construct a search that tells us if any group changes have been made to a user by someone in a group other than the FIM user or one other group. More simply put, only the FIM user or other group is supposed to make changes to a users privileged groups. If someone makes a group change to a user, we want to be alerted on it, if it was not made by the FIM user or that other group.

We're returning the users that have made changes to someone with this search from Windows Security Operations Center...

index=ad_prod OR index=win_prod sourcetype="*wineventlog:security" ( CategoryString="Account Management" OR TaskCategory="Security Group Management" ) (Message="Security Enabled*" OR Message="A member was added to a*") ( EventCode=632 OR EventCode=4728) | eval caller = if(isnull(Account_Name), Caller_User_Name, mvindex(Account_Name,0)) | eval member = if(isnull(Account_Name), Member_Name, mvindex(Account_Name,1)) | eval group = if(isnull(Target_Account_Name), Group_Name, Target_Account_Name) | search caller="*" group="*" member="*" NOT "User=FIM_AD_MA" | table _time caller member group | rename _time AS Time member AS Username group AS Group caller AS "Action by" | convert timeformat="%H:%M:%S %d.%m.%Y." ctime(Time)

So from here I need to compare the list of users left, to a lookup table and if a user is not in that list, then alert. I've got a csv file populating from a cronjob that lists the authorized users.

How do I accomplish this using a lookup table? Or is a lookup table the best way to handle this?

[Ayn · Accepted solution]

You can filter with a lookup table using a subsearch. Something like this:

... | search ... AND NOT [|inputlookup users.csv | fields User]

Subsearches work very much like backticks in UNIX, in that they run first of all and then return their results to the outer search. Let's say you have a lookup table like this:

User
User1
User2
User3

Using the search above and a users.csv with this content, the subsearch will expand to this (give or take some parantheses):

... | search ... AND NOT ((User="User1") OR (User="User2") OR (User="User3"))

...which I believe should do what you want.

[mdavis43]

Thanks, that did it! I added it just before the formatting

"NOT [|inputlookup groups.csv | fields User]"