Comparing 2 results and showing difference

willadams — Fri, 19 Feb 2021 07:20:35 GMT

My scenario is that I am trying to alert in the event where a user has been provided to an application but that same user wasn't added to an Active Directory group . So I have the following 2 indexes that provide me the information

Application Access is in "index=myapp"

Active Directory is in the "index=ad"

My search for a new user being given access to the application is something such as

index=myapp Operation=Creation user_object="user*@mydomain.com"

My search for a user being added to an Active Directory group is

index=ad EventCode=4728 Group_Name="myapp_users"

I have tried the following searches that provide me with data but I can't figure out the next step to show where my objective is met (i.e. where the user didn't get added to the group but was given access to the app).

First Search

index=myapp Operation=Creation user_object="user*@mydomain.com" | dedup RID | eval dest_user=split(user_object,"@") | eval extracteduser=mvindex(dest_user,0) | join type=inner extracteduser [search index=ad EventCode=4728 | rex field=user "^(?extracteduser>[^\,]+)" | eval extracteduser=split(extracteduser,"=") | eval extracteduser=mvindex(extracteduser,1) | fields Group_Name, extracteduser]

In my example I will get 3 returned results. USERA which was added to the application AND was added as a member to the AD group; USERB which was added to the application AND was added as a member to the AD group; and USERC which was added to the application but NOT added as a member to the AD group (myapp_users). The problem becomes that in the results that are returned I see

For the event returned for USERA and USERC, I see

Operation=Create user_object=USERA extracteduser=USERA Operation=Create user_object=USERB extracteduser=USERA

however for the USERC event, I see

Operation=Create user_object=USERC extracteduser=USERA

so I am getting the wrong extracteduser for the USERC event (no doubt because of the join). I have then abandoned the join and moved to a multi-search

Second Search

| multisearch [search index=myapp Operation=Creation user_object="user*@mydomain.com" | rename user_object as app_newuser] [search index=ad EventCode=4728" Group_Name="myapp_users" | rename user AS adperm_user] | rex field=adperm_user "^(?<extracteduser>[^\,]+)" | rex field=extracteduser "(?<CNAttrib>CN=(?<ad_user>.+))" | eval app_newuser=split(app_newuser,"@") | eval app_newuser=mvindex(app_newuser,0) | eval app_newuser=lower(app_newuser) | eval ad_user=lower(ad_user)

This gives me 2 fields that I now need to compare

ad_user shows USERA and USERB <-- these users were added to the AD group AND the app
app_newuser shows USERA, USERB, and USERC <-- these 3 users were added to the app

The result that I want in the end is to only show USERC as that was not a member of the AD group. I have tried using something like the following but come up blank.

| where NOT app_newuser = ad_user | search NOT app_newuser = ad_user

I have probably made this more complicated than it needs to be, but am stuck now.

Re: Comparing 2 results and showing difference

ITWhisperer — Fri, 19 Feb 2021 08:06:15 GMT

Try something like this

(index=myapp AND Operation=Creation AND user_object="user*@mydomain.com") OR (index=ad AND EventCode=4728" AND Group_Name="myapp_users") | rename user_object as app_newuser | rename user AS adperm_user | rex field=adperm_user "^(?<extracteduser>[^\,]+)" | rex field=extracteduser "(?<CNAttrib>CN=(?<user>.+))" | eval app_newuser=split(app_newuser,"@") | eval user=mvindex(app_newuser,0) | eval user=lower(user) | stats values(*) as * by user | fillnull value="NA" Group_Name | where Group_Name="NA"

Essentially, extract the user from whichever field it appears in, then "join" with stats, finally determine which users don't have an entry in AD

You may need to play around with field names depending on your actually data e.g. you might want to remove fields you are not interested in before the stats values(*)

Re: Comparing 2 results and showing difference

willadams — Fri, 19 Feb 2021 08:42:56 GMT

Thanks. I neglected to use "index OR index" as an an option. I will definitely remove fields as the real data has a lot more data in it.

topic Re: Comparing 2 results and showing difference in Splunk Search

Comparing 2 results and showing difference

Re: Comparing 2 results and showing difference

Re: Comparing 2 results and showing difference