Multivalue Field Extraction

[joeybroesky]

Need help with parsing out some events from our exchange data where we want to track license changes on exchange accounts. I’m able to pinpoint the events but having a hard time pulling specific data out of a multivalue fields.

Current Search String:
index="o365data" dataset_name=account_management | spath "ExtendedProperties{}.Name" | search "ExtendedProperties{}.Value"="[\"AssignedLicense\",\"AssignedPlan\",\"TargetId.UserType\"]" | spath "ModifiedProperties{}.Name" | search "ModifiedProperties{}.Name"=AssignedLicense | table _time, UserId, Operation, ObjectId, ModifiedProperties{}.NewValue, ModifiedProperties{}.OldValue | sort _time desc

Example Output:
**
***_time:* 2020-02-20 08:40:50
UserId: xxx@domain.ca
Operation: Update user.
ObjectId: xxxx@domain.ca
ModifiedProperties{}.NewValue: [ "[SkuName=O365_BUSINESS_ESSENTIALS, AccountId=xxx SkuId=3b555118-da6a-4418-894f-7df1e2096870, DisabledPlans=[WHITEBOARD_PLAN1,MYANALYTICS_P2,KAIZALA_O365_P2,STREAM_O365_SMB,OFFICEMOBILE_SUBSCRIPTION,BPOS_S_TODO_1,FORMS_PLAN_E1,FLOW_O365_P1,POWERAPPS_O365_P1,PROJECTWORKMANAGEMENT,SWAY,SHAREPOINTWAC,YAMMER_ENTERPRISE,MCOSTANDARD,SHAREPOINTSTANDARD]]" ]
[ { "SubscribedPlanId": "9738cc87-6e16-4861-9486-32ebdda261d1", "ServiceInstance": "Exchange/namprd17-001-01", "CapabilityStatus": 0, "AssignedTimestamp": "2020-02-20T14:40:50.0728559Z", "InitialState": null, "Capability": null, "ServicePlanId": "9aaf7827-d63c-4b61-89c3-182f06f82e5c" }, { "SubscribedPlanId": "502e8918-06b9-453d-a44b-9fc4920b0f20", "ServiceInstance": "TeamspaceAPI/NA001", "CapabilityStatus": 0, "AssignedTimestamp": "2020-02-20T14:40:50.0728559Z", "InitialState": null, "Capability": null, "ServicePlanId": "57ff2da0-773e-42df-b2af-ffb7a2317929" } ]
AssignedLicense, AssignedPlan
Member

We just want the “SkuName” (“0365_BUSINESS_ESSENTIALS” from example), “ServiceInstance” (“Exchange/namprd17-001-01” / “TeamspaceAPI/NA001” from example), and “DisabledPlans” (“WHITEBOARD_PLAN1” / “MYANALYTICS_P2” / etc.. from example).

Would someone be able to provide some assistance with this?

[to4kawa · Accepted solution]

index="o365data" dataset_name=account_management  SkuName ServiceInstance DisabledPlans AssignedLicense
| rex "SkuName=(?<SkuName>[^,]*)"
| rex "ServiceInstance\":\s*(?<ServiceInstance>[^,]*)"
| rex "DisabledPlans=(?<DisabledPlans>\[.*?\])"

[joeybroesky]

We ended up using the following:

index="o365data" dataset_name=account_management AssignedLicense
| spath "ModifiedProperties{}" | search *
| search UserId="teamsystems.ca"
| 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"

Thanks for your help to4kawa! 🙂