Splunk Search

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
1 Solution

Path Finder

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

0 Karma

Path Finder

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

0 Karma

SplunkTrust
SplunkTrust

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, an upvote would be appreciated.
0 Karma