topic Re: Need help joining multisearch results in Splunk Search

Need help joining multisearch results

joeybroesky — Mon, 08 Jun 2020 21:58:17 GMT

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

Re: Need help joining multisearch results

richgalloway — Wed, 11 Mar 2020 16:03:33 GMT

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

Re: Need help joining multisearch results

joeybroesky — Wed, 30 Sep 2020 04:37:04 GMT

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

Re: Need help joining multisearch results

to4kawa — Wed, 11 Mar 2020 21:53:56 GMT

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

Re: Need help joining multisearch results

joeybroesky — Wed, 11 Mar 2020 21:57:18 GMT

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

Re: Need help joining multisearch results

joeybroesky — Wed, 11 Mar 2020 21:59:15 GMT

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.

Re: Need help joining multisearch results

to4kawa — Wed, 11 Mar 2020 22:26:36 GMT

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?

Re: Need help joining multisearch results

richgalloway — Thu, 12 Mar 2020 12:41:17 GMT

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.

Re: Need help joining multisearch results

joeybroesky — Thu, 12 Mar 2020 16:10:08 GMT

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.

Re: Need help joining multisearch results

joeybroesky — Thu, 12 Mar 2020 19:11:25 GMT

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

Re: Need help joining multisearch results

to4kawa — Wed, 30 Sep 2020 04:38:05 GMT

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?

Re: Need help joining multisearch results

richgalloway — Fri, 13 Mar 2020 12:35:41 GMT

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

Re: Need help joining multisearch results

joeybroesky — Fri, 13 Mar 2020 13:40:12 GMT

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.

Re: Need help joining multisearch results

to4kawa — Fri, 13 Mar 2020 20:22:04 GMT

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.

Re: Need help joining multisearch results

woodcock — Fri, 13 Mar 2020 21:26:24 GMT

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

Re: Need help joining multisearch results

joeybroesky — Fri, 13 Mar 2020 21:27:15 GMT

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?

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

Re: Need help joining multisearch results

richgalloway — Mon, 16 Mar 2020 12:51:37 GMT

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

Re: Need help joining multisearch results

joeybroesky — Mon, 16 Mar 2020 14:36:05 GMT

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

Re: Need help joining multisearch results

richgalloway — Tue, 17 Mar 2020 14:18:58 GMT

Make sure the Old and New fields have values. I'm not seeing where those fields are set in the query so they must be defined in the data somewhere.

Re: Need help joining multisearch results

joeybroesky — Tue, 17 Mar 2020 19:18:07 GMT

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.