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