Splunk Search

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

mdavis43
Path Finder

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?

1 Solution

Ayn
Legend

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.

View solution in original post

Ayn
Legend

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
Path Finder

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

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

Get Updates on the Splunk Community!

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...