Splunk Search
Highlighted

Need help joining multisearch results

Path Finder

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

Labels (1)
0 Karma
Highlighted

Re: Need help joining multisearch results

SplunkTrust
SplunkTrust

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
---
If this reply helps you, an upvote would be appreciated.
0 Karma
Highlighted

Re: Need help joining multisearch results

Path Finder

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

0 Karma
Highlighted

Re: Need help joining multisearch results

Path Finder

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

0 Karma
Highlighted

Re: Need help joining multisearch results

SplunkTrust
SplunkTrust

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.

---
If this reply helps you, an upvote would be appreciated.
0 Karma
Highlighted

Re: Need help joining multisearch results

Path Finder

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.

0 Karma
Highlighted

Re: Need help joining multisearch results

SplunkTrust
SplunkTrust

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
---
If this reply helps you, an upvote would be appreciated.
0 Karma
Highlighted

Re: Need help joining multisearch results

Path Finder

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.

0 Karma
Highlighted

Re: Need help joining multisearch results

SplunkTrust
SplunkTrust

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

---
If this reply helps you, an upvote would be appreciated.
0 Karma
Highlighted

Re: Need help joining multisearch results

Path Finder

... | 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.

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.