Splunk Search

Comparing 2 results and showing difference

willadams
Contributor

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.

Labels (3)
0 Karma

ITWhisperer
Ultra Champion

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(*)

0 Karma

willadams
Contributor

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.  

0 Karma
Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!