topic How to extract the json based key value pair for defined match? in Splunk Search

How to extract the json based key value pair for defined match?

nb662x — Wed, 05 Apr 2023 18:13:08 GMT

below is my json file. I want to notify whenever there is a change in last property , "displayName": Included Updated Properties when newvalue:false and oldvalue:true. please let me know the search query

json file

resultReason:
targetResources: [ [-]
{ [-]
administrativeUnits: [ [+]
]
displayName: Authorization Policy
id: abc
modifiedProperties: [ [-]
{ [-]
displayName: PermissionGrantPolicyIdsAssignedToDefaultUserRole
newValue: ["microsoft-user"]
oldValue: ["Manage"]
}
{ [-]
displayName: Included Updated Properties
newValue: "true"
oldValue: "false"
}
{ [+]
}
]

Re: How to extract the json based key value pair for defined match?

woodcock — Wed, 05 Apr 2023 22:08:41 GMT

That is not valid json, not is it really what your events are. How do you expect us to deal with it?

Re: How to extract the json based key value pair for defined match?

kamlesh_vaghela — Thu, 06 Apr 2023 05:34:23 GMT

@nb662x

Share your full sample event. we need _raw from your search.

index=YOUR_INDEX | head 1| table _raw

Copy and paste in a code block ( </>).

This is code block

Re: How to extract the json based key value pair for defined match?

nb662x — Thu, 06 Apr 2023 06:30:37 GMT

@kamlesh_vaghela Please find the json-

{
"time": "2023-04-04T07:58:02.7508973Z",
"resourceId": "/tenants/r456ach/providers/Microsoft.aadiam",
"operationName": "Update authorization policy",
"operationVersion": "1.0",
"category": "AuditLogs",
"tenantId": "r456ach",
"resultSignature": "None",
"durationMs": 0,
"callerIpAddress": "20.190.145.169",
"correlationId": "16800",
"Level": 4,
"properties": {
"id": "Directory_498",
"category": "AuthorizationPolicy",
"correlationId": "4985e174-",
"result": "success",
"resultReason": "",
"activityDisplayName": "Update authorization policy",
"activityDateTime": "2023-04-04T07:58:02.7508973+00:00",
"loggedByService": "Core Directory",
"operationType": "Update",
"userAgent": null,
"initiatedBy": {
"user": {
"id": "deb6abb8",
"displayName": null,
"userPrincipalName": "user@test.onmicrosoft.com",
"ipAddress": "20.10.10.10",
"roles": []
}
},
"targetResources": [
{
"id": "c8458b3c",
"displayName": "Authorization Policy",
"type": "Other",
"modifiedProperties": [
{
"displayName": "PermissionGrantPolicyIdsAssignedToDefaultUserRole",
"oldValue": "[\"ManagePermissionGrantsForSelf.microsoft-user-default-legacy\"]",
"newValue": "[\"microsoft-user-default-legacy\"]"
},
{
"displayName": "Included Updated Properties",
"oldValue": null,
"newValue": "\"DefaultUserRolePermissions.AllowedToCreateApp, PermissionGrantPolicyIdsAssignedToDefaultUserRole\""
},
{
"displayName": "DefaultUserRolePermissions.AllowedToCreateApp",
"oldValue": "true",
"newValue": "false"
}
],
"administrativeUnits": []
}
],
"additionalDetails": [
{
"key": "User-Agent",
"value": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/111.0.0.0 Safari/537.36"
}
]
}
}

I tried to create below query but not sure if it's the right way because then i need to hard code array position {2} in the query. Could you please suggest right way

sourcetype=azure:logs "properties.targetResources{}.modifiedProperties{}.displayName"="DefaultUserRolePermissions.AllowedToCreateApp" "properties.activityDisplayName"="Update authorization policy"

| spath path=properties.targetResources{} output=mp | spath path=properties.activityDisplayName output=activityName | spath output=UPN path=properties.initiatedBy.user.userPrincipalName

| spath output=NewTenantCreationRestriction path=properties.targetResources{0}.modifiedProperties{2}.newValue

| search NewTenantCreationRestriction=*false*

| spath output=OldTenantCreationRestriction path=properties.targetResources{0}.modifiedProperties{2}.oldValue

| search OldTenantCreationRestriction=*true*

| spath output=Date path=properties.activityDateTime | spath output=tenantId path=properties.tenantId

| table tenantId, Date, activityName, UPN , NewTenantCreationRestriction, OldTenantCreationRestriction

Re: How to extract the json based key value pair for defined match?

nb662x — Thu, 06 Apr 2023 06:31:13 GMT

Please find the json-

I tried to create below query but not sure if it's the right way because then i need to hard code array position {2} in the query. Could you please suggest right way

sourcetype=azure:logs "properties.targetResources{}.modifiedProperties{}.displayName"="DefaultUserRolePermissions.AllowedToCreateApp" "properties.activityDisplayName"="Update authorization policy"

| spath path=properties.targetResources{} output=mp | spath path=properties.activityDisplayName output=activityName | spath output=UPN path=properties.initiatedBy.user.userPrincipalName

| spath output=NewTenantCreationRestriction path=properties.targetResources{0}.modifiedProperties{2}.newValue

| search NewTenantCreationRestriction=*false*

| spath output=OldTenantCreationRestriction path=properties.targetResources{0}.modifiedProperties{2}.oldValue

| search OldTenantCreationRestriction=*true*

| spath output=Date path=properties.activityDateTime | spath output=tenantId path=properties.tenantId

| table tenantId, Date, activityName, UPN , NewTenantCreationRestriction, OldTenantCreationRestriction

Re: How to extract the json based key value pair for defined match?

woodcock — Thu, 06 Apr 2023 17:14:13 GMT

Like this:

| makeresults | eval _raw="{ \"time\": \"2023-04-04T07:58:02.7508973Z\", \"resourceId\": \"/tenants/r456ach/providers/Microsoft.aadiam\", \"operationName\": \"Update authorization policy\", \"operationVersion\": \"1.0\", \"category\": \"AuditLogs\", \"tenantId\": \"r456ach\", \"resultSignature\": \"None\", \"durationMs\": 0, \"callerIpAddress\": \"20.190.145.169\", \"correlationId\": \"16800\", \"Level\": 4, \"properties\": { \"id\": \"Directory_498\", \"category\": \"AuthorizationPolicy\", \"correlationId\": \"4985e174-\", \"result\": \"success\", \"resultReason\": \"\", \"activityDisplayName\": \"Update authorization policy\", \"activityDateTime\": \"2023-04-04T07:58:02.7508973+00:00\", \"loggedByService\": \"Core Directory\", \"operationType\": \"Update\", \"userAgent\": null, \"initiatedBy\": { \"user\": { \"id\": \"deb6abb8\", \"displayName\": null, \"userPrincipalName\": \"user@test.onmicrosoft.com\", \"ipAddress\": \"20.10.10.10\", \"roles\": [] } }, \"targetResources\": [ { \"id\": \"c8458b3c\", \"displayName\": \"Authorization Policy\", \"type\": \"Other\", \"modifiedProperties\": [ { \"displayName\": \"PermissionGrantPolicyIdsAssignedToDefaultUserRole\", \"oldValue\": \"[\\\"ManagePermissionGrantsForSelf.microsoft-user-default-legacy\\\"]\", \"newValue\": \"[\\\"microsoft-user-default-legacy\\\"]\" }, { \"displayName\": \"Included Updated Properties\", \"oldValue\": null, \"newValue\": \"\\\"DefaultUserRolePermissions.AllowedToCreateApp, PermissionGrantPolicyIdsAssignedToDefaultUserRole\\\"\" }, { \"displayName\": \"DefaultUserRolePermissions.AllowedToCreateApp\", \"oldValue\": \"true\", \"newValue\": \"false\" } ], \"administrativeUnits\": [] } ], \"additionalDetails\": [ { \"key\": \"User-Agent\", \"value\": \"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/111.0.0.0 Safari/537.36\" } ] } }" | kv | spath path=properties.activityDisplayName output=activityName | spath output=UPN path=properties.initiatedBy.user.userPrincipalName | spath output=Date path=properties.activityDateTime | table tenantId, Date, activityName, UPN , NewTenantCreationRestriction, OldTenantCreationRestriction _raw | rex max_match=0 "(?ms){[\r\n\s]*\"displayName\":.*?\"oldValue\":\s*\"?(?<oldValue>.*?)[\",]*[\r\n\s]+\"newValue\":\s*\"(?<newValue>.*?)[\",]*[\r\n]" | where mvindex(oldValue, -1)=="true" AND mvindex(newValue, -1)=="false"

OR like this:

| makeresults | eval _raw="{ \"time\": \"2023-04-04T07:58:02.7508973Z\", \"resourceId\": \"/tenants/r456ach/providers/Microsoft.aadiam\", \"operationName\": \"Update authorization policy\", \"operationVersion\": \"1.0\", \"category\": \"AuditLogs\", \"tenantId\": \"r456ach\", \"resultSignature\": \"None\", \"durationMs\": 0, \"callerIpAddress\": \"20.190.145.169\", \"correlationId\": \"16800\", \"Level\": 4, \"properties\": { \"id\": \"Directory_498\", \"category\": \"AuthorizationPolicy\", \"correlationId\": \"4985e174-\", \"result\": \"success\", \"resultReason\": \"\", \"activityDisplayName\": \"Update authorization policy\", \"activityDateTime\": \"2023-04-04T07:58:02.7508973+00:00\", \"loggedByService\": \"Core Directory\", \"operationType\": \"Update\", \"userAgent\": null, \"initiatedBy\": { \"user\": { \"id\": \"deb6abb8\", \"displayName\": null, \"userPrincipalName\": \"user@test.onmicrosoft.com\", \"ipAddress\": \"20.10.10.10\", \"roles\": [] } }, \"targetResources\": [ { \"id\": \"c8458b3c\", \"displayName\": \"Authorization Policy\", \"type\": \"Other\", \"modifiedProperties\": [ { \"displayName\": \"PermissionGrantPolicyIdsAssignedToDefaultUserRole\", \"oldValue\": \"[\\\"ManagePermissionGrantsForSelf.microsoft-user-default-legacy\\\"]\", \"newValue\": \"[\\\"microsoft-user-default-legacy\\\"]\" }, { \"displayName\": \"Included Updated Properties\", \"oldValue\": null, \"newValue\": \"\\\"DefaultUserRolePermissions.AllowedToCreateApp, PermissionGrantPolicyIdsAssignedToDefaultUserRole\\\"\" }, { \"displayName\": \"DefaultUserRolePermissions.AllowedToCreateApp\", \"oldValue\": \"true\", \"newValue\": \"false\" } ], \"administrativeUnits\": [] } ], \"additionalDetails\": [ { \"key\": \"User-Agent\", \"value\": \"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/111.0.0.0 Safari/537.36\" } ] } }" | kv | spath path=properties.activityDisplayName output=activityName | spath output=UPN path=properties.initiatedBy.user.userPrincipalName | spath output=Date path=properties.activityDateTime | table tenantId, Date, activityName, UPN , NewTenantCreationRestriction, OldTenantCreationRestriction _raw | rex "(?ms){[\r\n\s]*\"displayName\":[\r\n\s]+\"DefaultUserRolePermissions.AllowedToCreateApp\",[\r\n\s]+\"oldValue\":\s*\"?(?<oldValue>.*?)[\",]*[\r\n\s]+\"newValue\":\s*\"(?<newValue>.*?)[\",]*[\r\n]" | where oldValue=="true" AND newValue=="false"

Re: How to extract the json based key value pair for defined match?

lindonmorris — Tue, 06 Aug 2024 07:12:56 GMT

I know this is an old one, but I went searching in the hopes someone had already written, so sharing here.

This is specifically for EntraID (/Azure AD) Audit logs - Which are "valid" json, however there's some wierd stuff going on within the JSON itself - And a real mixture of data structures (probably depends on which developer wrote each piece i'm guessing).

Anyway, hope it helps someone - it deals with the 2x value data as well as the name/old/new type data.

index=entra_audit activityDisplayName="Consent to application" category="ApplicationManagement"
``` Entra ID Audit Logs - Extract Application Consent JSON into usable fields ```
| spath operationType
| rename targetResources{}.* AS targetResources.*
| table _time index sourcetype source correlationId additionalDetails* activityDisplayName category operationType result initiatedBy.user.ipAddress initiatedBy.user.userPrincipalName targetResources.*

``` additionalDetails is a 2 entry key/value pair in JSON ```
| rename additionalDetails{}.* AS additionalDetails_*
| eval additionalDetails=mvzip(additionalDetails_key,additionalDetails_value, "=") 
| mvexpand additionalDetails 
| eval Key=mvindex(split(additionalDetails,"="),0), Value=mvindex(split(additionalDetails,"="),1) 
| eval additionalDetails.{Key}=Value

``` targetResources.modifiedProperties{} is a 3 entry name/newvalue/oldvalue in JSON ```
| rename targetResources.modifiedProperties{}.* AS targetm_*
| eval targetm=mvzip(targetm_displayName,targetm_newValue, ";") 
| eval targetm=mvzip(targetm,targetm_oldValue, ";")
| mvexpand targetm 
| eval Key=mvindex(split(targetm,";"),0).".newValue", Value=trim(mvindex(split(targetm,";"),1),"\"")
| eval modifiedProperties.{Key}=Value 
| eval Key=mvindex(split(targetm,";"),0).".oldValue", Value=trim(mvindex(split(targetm,";"),2),"\"") 
| eval modifiedProperties.{Key}=Value

``` Now extract the permissions, which are in yet a different format - multiple fields with `double_bracket`old => `double_bracket`new, but json format is the same old/new format as other above fields - yet the old and new is stored in the "newValue" field only, and "oldValue" is blank```
``` Within the double brackets are multiple ID's which relate to individual scopes, however we're only interested in the scope and context type ```
``` Remove spaces ```
| rex mode=sed field=modifiedProperties.ConsentAction.Permissions.newValue "s/ //g"
``` Extract new/old values ```
| rex field=modifiedProperties.ConsentAction.Permissions.newValue "\[(?P<perms_new>\[.*\])\]=>\[(?P<perms_old>\[.*\])\]"
| makemv perms_new delim="],"
| mvexpand perms_new
| eval perms_new = trim(perms_new,"[]")
| rex field=perms_new "ConsentType:(?P<perms_type_new>.*?),.*Scope:(?P<perms_scope_new>.*?),"
| makemv perms_old delim="],"
| mvexpand perms_old
| eval perms_old = trim(perms_old,"[]")
| rex field=perms_old "ConsentType:(?P<perms_type_old>.*?),.*Scope:(?P<perms_scope_old>.*?),"
| rename perms_type_new AS permissions.consentType.newValue perms_scope_new AS permissions.scope.newValue
| rename perms_type_old AS permissions.consentType.oldValue perms_scope_old AS permissions.scope.oldValue

``` Clean up unecessary fields - Slighly faster to do this before the transaction```
| fields - Key Value *_key *_value targetm* additionalDetails modifiedProperties.ConsentAction.Permissions* perms*

``` Condense those events down to a single event ```
| transaction correlationId maxspan=1s
``` Format into a nicer table and field names ```
| rename initiatedBy.user.ipAddress AS src initiatedBy.user.userPrincipalName AS user
| rename modifiedProperties.ConsentContext.* AS context.*
| rename modifiedProperties.TargetId.* AS target.*
| table _time index sourcetype source category operationType result src user activityDisplayName correlationId additionalDetails.* targetResources.* target.* context.* permissions.*

```| collect index=my_summary ```

For performance I suggest you collect that into a summary index, csv or kvstore

Here's a sanitised copy of an event:


{"id": "Directory_zzzzz-zzzz-zzzz", "category": "ApplicationManagement", "correlationId": "zzzz-zzzz-zzzz-zzzz-zzzz", "result": "success", "resultReason": "", "activityDisplayName": "Consent to application", "activityDateTime": "2024-08-05T06:10:19.1808273Z", "loggedByService": "Core Directory", "operationType": "Assign", "initiatedBy": {"app": null, "user": {"id": "zzzz-zzzz-zzzz-zzzz-zzzz", "displayName": null, "userPrincipalName": "zzzz", "ipAddress": "0.0.0.0", "userType": null, "homeTenantId": null, "homeTenantName": null}}, "targetResources": [{"id": "zzzz-zzzz-zzzz-zzzz-zzzz", "displayName": "Microsoft Graph PowerShell", "type": "ServicePrincipal", "userPrincipalName": null, "groupType": null, "modifiedProperties": [{"displayName": "ConsentContext.IsAdminConsent", "oldValue": null, "newValue": "\"True\""}, {"displayName": "ConsentContext.IsAppOnly", "oldValue": null, "newValue": "\"False\""}, {"displayName": "ConsentContext.OnBehalfOfAll", "oldValue": null, "newValue": "\"False\""}, {"displayName": "ConsentContext.Tags", "oldValue": null, "newValue": "\"WindowsAzureActiveDirectoryIntegratedApp\""}, {"displayName": "ConsentAction.Permissions", "oldValue": null, "newValue": "\"[] => [[Id: zzzz-zzzz-zzzz-zzzz-zzzz_-OGPWDcaCSZ3yquwoJpK3, ClientId: zzzz-zzzz-zzzz-zzzz-zzzz, PrincipalId: zzzz-zzzz-zzzz-zzzz-zzzz, ResourceId: zzzz-zzzz-zzzz-zzzz-zzzz, ConsentType: Principal, Scope: Application.ReadWrite.All Organization.Read.All AuditLog.Read.All openid profile offline_access, CreatedDateTime: , LastModifiedDateTime ]]; \""}, {"displayName": "TargetId.ServicePrincipalNames", "oldValue": null, "newValue": "\"zzzz-zzzz-zzzz-zzzz-zzzz\""}]}], "additionalDetails": [{"key": "User-Agent", "value": "EvoSTS"}, {"key": "AppId", "value": "zzzz-zzzz-zzzz-zzzz-zzzz"}]}