Need help joining multisearch results

[joeybroesky]

Need help with bringing together results in a multisearch. Need to match department data from AD to an email address from O365 data on 1 row for reporting.

| multisearch
[search index="activedirectory" objectCategory="CN=Person*" AND sAMAccountType=805306368 AND userAccountControl!=514 AND userPrincipalName | eval ad_email=userPrincipalName | eval ad_department=department]
[search index="o365data" dataset_name=account_management AssignedLicense | eval 360_email=ad_email]
| table 360_email, ad_department

[joeybroesky · Accepted solution]

This was resolved with the following search:

(index="o365data" dataset_name=account_management AssignedLicense) OR (index="activedirectory" objectCategory="CN=Person*" AND sAMAccountType=805306368 AND userAccountControl!=514 AND userPrincipalName earliest=0)
| dedup _time
| rex "(?P<NewLicenses>((?<=NewValue)(.*?)(?=OldValue)))" max_match=0
| rex "(?P<OldLicenses>((?<=OldValue)(.*?)(?=Name....AssignedPlan)))" max_match=0
| rex field="NewLicenses" "\[SkuName=(?P<New>[^,]*)" max_match=0
| rex field="OldLicenses" "\[SkuName=(?P<Old>[^,]*)" max_match=0
| eval emailadd=coalesce(ObjectId, userPrincipalName)
| eval 360dept=ad_department | rename 360dept as dept
| eval addept=department | rename addept as dept
| stats values(dept) as "Department", values(Old) as "Old", values(New) as "New", values(UserId) as "UserId" latest(CreationTime) as "Date/Time" by emailadd
| where UserId != "" AND Old != New
| rename Old as "Old License", New as "New License", emailadd as "Account Changed", UserId as "Administrator"
| sort - "Date/Time"

This will extract the exchange account license changes from the "o365data" index and correlate the exchange account email address to an AD account email address to get the department data for the account.

I hope this help anyone else trying to join mulisearch results.

[woodcock]

Like this:

(index="activedirectory" AND objectCategory="CN=Person*" AND sAMAccountType="805306368" AND userAccountControl!="514" AND "userPrincipalName")
OR (index="o365data" AND dataset_name="account_management" AND "AssignedLicense")
| eval 360_email = coalesce(360_email, ad_email)
| stats values(department) AS ad_department BY 360_email

[to4kawa]

UPDATE:

(index="activedirectory" objectCategory="CN=Person*" AND sAMAccountType=805306368 AND userAccountControl!=514 AND userPrincipalName=*) OR ( index="o365data" dataset_name=account_management AssignedLicense=*) 
| eval 360_email=coalesce(ObjectId, userPrincipalName )
| stats values(department) as ad_department by 360_email

coalesce works to attach separate fields.
At this time, ObjectID from AD and userPrincipalName from o365data makes to 360_emal.
How about this?

[joeybroesky]

Thanks to4kawa but this only returns the email and department from the first search. We were using that as a test search to learn how to bring fields from the 2 searches together based on the email address. We are looking to pull the department field from AD for a specific email address on O365. Below is the 2 original searches that we are trying to incorporate.

## Get Exchange License Data ##
index="o365data" dataset_name=account_management AssignedLicense
| spath "ModifiedProperties{}" | search *
| dedup _time
| rex "(?P((?<=NewValue)(.*?)(?=OldValue)))" max_match=0
| rex "(?P((?<=OldValue)(.*?)(?=Name....AssignedPlan)))" max_match=0
| rex field="NewLicenses" "\[SkuName=(?P[^,]*)" max_match=0
| rex field="OldLicenses" "\[SkuName=(?P[^,]*)" max_match=0
| table _time, ObjectId, Old, New, UserId
| rename New as "New License Applied", Old as "Old License Applied", UserId as "Administrator Making Change", ObjectId as "Account Changed"

## Get AD Department ##
index="activedirectory" (userPrincipalName=*)
| table userPrincipalName, department

[to4kawa]

your rex can't work

Please check your post.

this only returns the email and department from the first search

your original query has | eval 360_email=ad_email.
This means make ad_email field's value into 360_email field.

My query | eval 360_email=coalesce(ad_email,userPrincipalName ) is
AD's userPrincipalName and o365data's ad_email both are same 360_email
Really? Doesn't index="o365data" have ad_email field?

[joeybroesky]

The rex does work but do you mean the rex won't work in the multisearch?

The index="o365data" hasObjectId as the field for email and index="activedirectory" has userPrincipalName as the field for email. I was trying to point one to the other using 360_email=ad_email.

Sorry still very new to Splunk and trying to learn the SPL.

[to4kawa]

In your comment:

 | rex "(?P((?<=NewValue)(.*?)(?=OldValue)))" max_match=0 | rex "(?P((?<=OldValue)(.*?)(?=Name....AssignedPlan)))" max_match=0 | rex field="NewLicenses" "\[SkuName=(?P[^,]*)" max_match=0 | rex field="OldLicenses" "\[SkuName=(?P[^,]*)" max_match=0 | 

Some strings are missing. my answer is updated.

[to4kawa]

userPrincipalName and ad_email is field?
your query isn't readable why string isn't with double quote.

[joeybroesky]

Yup the userPrincipalName is the field name identified as ad_email for referencing in the second sub search.

[richgalloway]

Try stats.

| multisearch [search index="activedirectory" objectCategory="CN=Person*" AND sAMAccountType=805306368 AND userAccountControl!=514 AND userPrincipalName | eval ad_email=userPrincipalName | eval ad_department=department] [search index="o365data" dataset_name=account_management AssignedLicense | eval 360_email=ad_email] 
| stats values(*) as * by ad_email 
| table 360_email, ad_department

[joeybroesky]

I tried it with stats but unfortunately only the ad_department fields are showing up in the table and not the 360_email data.

[joeybroesky]

I also tried the following which only shows the email address from the second sub search and does not list the department from the first sub search.

| multisearch
[search index="activedirectory" objectCategory="CN=Person*" AND sAMAccountType=805306368 AND userAccountControl!=514 AND userPrincipalName | eval ad_email=userPrincipalName | eval ad_department=department]
[search index="o365data" dataset_name=account_management AssignedLicense | eval 360_email=ObjectId | eval 360_department=ad_department]
| stats values(ad_department) as "Department" by 360_email

[richgalloway]

In the original question, eval 360_email=ad_email was used. which means only ad_email needed to be used in stats. The solution is to use rename or eval in the subsearches to ensure both of them return the same field name for email. Use the common field name in stats.

[joeybroesky]

Thanks for your input Rich. My apologies as I'm new to Splunk but would you be able to provide an example please? I'm struggling with getting it to work.

[richgalloway]

Here's an example.

| multisearch [search index="activedirectory" objectCategory="CN=Person*" AND sAMAccountType=805306368 AND userAccountControl!=514 AND userPrincipalName | eval ad_email=userPrincipalName | eval ad_department=department, email=ad_email] [search index="o365data" dataset_name=account_management AssignedLicense | eval 360_email=ObjectId | eval 360_department=ad_department, email=360_email] | stats values(ad_department) as "Department" by email

Here's another example that uses the coalesce command.

| multisearch [search index="activedirectory" objectCategory="CN=Person*" AND sAMAccountType=805306368 AND userAccountControl!=514 AND userPrincipalName | eval ad_email=userPrincipalName | eval ad_department=department] [search index="o365data" dataset_name=account_management AssignedLicense | eval 360_email=ObjectId | eval 360_department=ad_department] 
| eval email = coalesce(360_email, ad_email)
| stats values(ad_department) as "Department" by email

[joeybroesky]

Thanks for your help Rich! I think we almost have what we need. Using your examples, I reconstructed our search as follows but it does not show the department.

| multisearch
[search index="activedirectory" objectCategory="CN=Person*" AND sAMAccountType=805306368 AND userAccountControl!=514 AND userPrincipalName | eval ad_email=userPrincipalName | eval ad_department=department | rename ad_email as email]
[search index="o365data" dataset_name=account_management AssignedLicense | eval 360_email=ObjectId | eval 360_department=ad_department | rename 360_email as email]
| spath "ModifiedProperties{}" | search *
| dedup _time
| rex "(?P((?<=NewValue)(.*?)(?=OldValue)))" max_match=0
| rex "(?P((?<=OldValue)(.*?)(?=Name....AssignedPlan)))" max_match=0
| rex field="NewLicenses" "\[SkuName=(?P[^,]*)" max_match=0
| rex field="OldLicenses" "\[SkuName=(?P[^,]*)" max_match=0
| stats values(ad_department) as "Department" by email, Old, New, UserId

If I remove , Old, New, UserId as shown below it matches them up. Am I missing something?

| multisearch
[search index="activedirectory" objectCategory="CN=Person*" AND sAMAccountType=805306368 AND userAccountControl!=514 AND userPrincipalName | eval ad_email=userPrincipalName | eval ad_department=department | rename ad_email as email]
[search index="o365data" dataset_name=account_management AssignedLicense | eval 360_email=ObjectId | eval 360_department=ad_department | rename 360_email as email]
| spath "ModifiedProperties{}" | search *
| dedup _time
| rex "(?P((?<=NewValue)(.*?)(?=OldValue)))" max_match=0
| rex "(?P((?<=OldValue)(.*?)(?=Name....AssignedPlan)))" max_match=0
| rex field="NewLicenses" "\[SkuName=(?P[^,]*)" max_match=0
| rex field="OldLicenses" "\[SkuName=(?P[^,]*)" max_match=0
| stats values(ad_department) as "Department" by email

We need to see _time, ObjectId, Old, New, UserId in the output.

[richgalloway]

Try ... | stats values(ad_department) as "Department", values(Old) as Old, values(New) as New, values(UserId) as UserId by email.

[joeybroesky]

... | stats values(ad_department) as "Department", values(Old) as Old, values(New) as New, values(UserId) as UserId by email outputs the email and ad_department values but the other fields are blank. It is also listing all email addresses found in the activedirectory index with their respective department. Our goal is to match all the email addresses found in the o365data index and match them to an email/department in the activedirectory index and output a department for the addresses found in o365data.

stats values(ad_department) as "Department" by email, Old, New, UserId shows everything we need minus the department data. I've tried re-arranging the stats command but cant seem to figure out how to bring it all together.

[joeybroesky]

They do have values which we verified they do show. Those fields are being defined in the rex field commands. For some reason the copy/paste doesn't show up in the comment properly.

[joeybroesky]

Still having an issue bringing these results together. It will bring together the email/department fields for the "activedirectory" search events and list them separately from the "o365data" search events which also show the New/Old/UserId/email fields separately for the "o365data" search. It wont match the department from activedirectory search to the o365data search based on email.


| multisearch
[search index="o365data" dataset_name=account_management AssignedLicense | eval 360_email=ObjectId | eval 360_department=ad_department | rename 360_email as email]
[search index="activedirectory" objectCategory="CN=Person*" AND sAMAccountType=805306368 AND userAccountControl!=514 AND userPrincipalName | eval ad_email=userPrincipalName | eval ad_department=department | rename ad_email as email]
| spath "ModifiedProperties{}" | search *
| dedup _time
| rex "(?P((?<=NewValue)(.*?)(?=OldValue)))" max_match=0
| rex "(?P((?<=OldValue)(.*?)(?=Name....AssignedPlan)))" max_match=0
| rex field="NewLicenses" "\[SkuName=(?P[^,]*)" max_match=0
| rex field="OldLicenses" "\[SkuName=(?P[^,]*)" max_match=0
| stats values(ad_department) as "ad_department", values(Old) as "Old", values(New) as "New", values(UserId) as "UserId" by email