Splunk Search

With two data sources Source1 and Source2 , how to identify any Source1 record that does not have a corresponding Source2 record?

pjb2160
Path Finder

Hello,

I have two data sources Active Directory (Source 1) and Change Approvals (Source 2). I need to identify any Active Directory record which does not have a corresponding Change Approval. Prior to correlating between the two data sources I need to filter my Active Directory results to only include those with specific Event IDs.

To start with I am attempting to find the records that match and I believe this is working (for the most part), here's my sample code for the first part:

index=active_directory sourcetype=ad_logs [| inputlookup ad_event_codes | fields event_id ]
 | eval common_group = group_name | eval common_admin = change_user_id | eval common_user = changed_user_name
 | eval common_action = event_id_action
 | join type=inner common_group common_user common_action[search index=approvals sourcetype=approval_logs
 | rename groups_affected as common_group | rename admin_officer as common_admin
 | rename users_affected as common_user | rename action as common_action | eval approvals_raw = _raw ]
 | table common_group common_admin common_user common_action _raw approvals_raw

As you can see I've used an inner join type and am using common fields between the two data sources (group name, administrator name and changed user name) as the join keys.

(Please note: At this point in time this is the best I have and am aware I am likely to yield numerous false positives in my exception reporting once I have this query working)

Now i need to determine which of the filtered Active Directory records do not match as part of the join. Can anyone offer some guidance here? Is it a different join type or is it as simple as saying "NOT join" or something?

I would welcome the opportunity to discuss this further.

many thanks,
Paul

Tags (1)
0 Karma
1 Solution

pjb2160
Path Finder

Thanks @lguinn for your suggestion, I spent some time working with this however it didn't quite work for me. That said, it was very useful to try and resolve the problem with your suggestion however in the end I reverted to my initial approach. I revised my initial query to use a left outer join.

As a matter of interest I've shown my final result below (with some additional explanation probably more so for the benefit of other users who may refer to this post in the future) :

index=active_directory sourcetype=ad_logs [| inputlookup ad_event_codes | fields event_id ]
| eval common_group = lower(group_name) | eval common_admin = lower(change_user_id) | eval common_user = lower(changed_user_id) | eval common_action = lower(event_id_action)
| join type=outer common_group common_admin common_user common_action[search index=approvals sourcetype=approval_logs
| makemv delim=";" groups_affected | makemv delim=";" users_affected | mvexpand groups_affected | mvexpand users_affected
| eval common_group=lower(groups_affected) | eval common_admin=lower(admin_officer) | eval common_user=lower(users_affected) | eval common_action=lower(action)
| eval approval_logs_raw = _raw]
| table common_group common_user common_action _raw approval_logs_raw
| where NOT len(approval_logs_raw)>0

In the new query I have added a couple of additional elements:

Thanks again,
Paul

View solution in original post

pjb2160
Path Finder

Thanks @lguinn for your suggestion, I spent some time working with this however it didn't quite work for me. That said, it was very useful to try and resolve the problem with your suggestion however in the end I reverted to my initial approach. I revised my initial query to use a left outer join.

As a matter of interest I've shown my final result below (with some additional explanation probably more so for the benefit of other users who may refer to this post in the future) :

index=active_directory sourcetype=ad_logs [| inputlookup ad_event_codes | fields event_id ]
| eval common_group = lower(group_name) | eval common_admin = lower(change_user_id) | eval common_user = lower(changed_user_id) | eval common_action = lower(event_id_action)
| join type=outer common_group common_admin common_user common_action[search index=approvals sourcetype=approval_logs
| makemv delim=";" groups_affected | makemv delim=";" users_affected | mvexpand groups_affected | mvexpand users_affected
| eval common_group=lower(groups_affected) | eval common_admin=lower(admin_officer) | eval common_user=lower(users_affected) | eval common_action=lower(action)
| eval approval_logs_raw = _raw]
| table common_group common_user common_action _raw approval_logs_raw
| where NOT len(approval_logs_raw)>0

In the new query I have added a couple of additional elements:

Thanks again,
Paul

lguinn2
Legend

Try this instead:

( index=active_directory sourcetype=ad_logs [| inputlookup ad_event_codes | fields event_id ] ) OR
( index=approvals sourcetype=approval_logs )
| sort _time
| eval group=coalesce(group_name,groups_affected)
| eval admin=coalesce(change_user_id,admin_officer)
| eval user=coalesce(changed_user_name,users_affected)
| eval ad=if(sourcetype=="ad_logs",1,0)
| eval approval=if(sourcetype=="approval_logs",1,0)
| stats list(_raw) as messages sum(ad) as NumAdEntries sum(approval) by NumApprovals by group user action
| where NumAdEntries!=NumApprovals

Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...