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

[pjb2160]

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

[pjb2160 · Accepted solution]

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:

• makemv and mvexpand to handle multi-value fields;
• lower(...) to ensure comparisons aren't limited by case sensitivity; and
• the final where statement which is what effectively turns my query into an left outer join rather than a full outer join (http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/)

Thanks again,
Paul

[lguinn2]

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