Solved: Re: Need help joining multisearch results - Page 2

joeybroesky · ‎03-11-2020

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 · ‎04-01-2020

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.

View solution in original post

joeybroesky · ‎03-25-2020

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

richgalloway · ‎03-17-2020

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.

---
If this reply helps you, Karma would be appreciated.

Need help joining multisearch results

eval

Join the Splunk Community Slack to learn, troubleshoot, and make connections with fellow Splunk practitioners in real time!

Join Splunk User Groups to connect and learn in-person by region or remotely by topic or industry.

Deep Dive: Accelerate threat investigation with Splunk’s AI Assistant in Security

Announcing Modern Navigation: A New Era of Splunk User Experience

Detection Engineering Office Hours: Real-World Troubleshooting & Q&A

Join the Conversation