- Splunk Answers
- :
- Using Splunk
- :
- Splunk Search
- :
- Need help joining multisearch results
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark Topic
- Subscribe to Topic
- Mute Topic
- Printer Friendly Page
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
.conf24 | Registration Open!
ICYMI - Check out the latest releases of Splunk Edge Processor
Introducing the 2024 SplunkTrust!
