How to extract a field from an escaped string inside a nested JSON

[z0r0]

I'm looking for help in extracting "allowedSourceAddressPrefix" field/value from a JSON. This field is an escaped JSON string inside a nested JSON. Following is the JSON tree

- properties (extracted by splunk)

- /subscription/..../.../ (dynamic field)

- ports (escaped json)

- allowedSourceAddressPrefix (nested json)

The allowedSourceAddressPrefix takes values of single ipaddress (or) multiple ip addresses (or) *.

I have tried various rex patterns but failed in extracting the required field, Any help is appreciated. Following is the JSON that has the required field

properties: {
"User": "johndoe@contoso.com",
"/subscriptions/3483b2ca-02cf-4ff6-92af-99326c8fac7f/resourceGroups/apple-dev/providers/Microsoft.Compute/virtualMachines/gjappledev": "{\"id\":\"/subscriptions/3483b2ca-02cf-4ff6-92af-99326c8fac7f/resourceGroups/apple-dev/providers/Microsoft.Compute/virtualMachines/gjappledev\",\"ports\":[{\"number\":3389,\"allowedSourceAddressPrefix\":\"*\",\"endTimeUtc\":\"2022-03-21T1:50:39.1599446Z\"}]}",
"Justification": null
}

TIA

[ITWhisperer · Accepted solution]

| rex "allowedSourceAddressPrefix\\\\\":\\\\\"(?<allowedSourceAddressPrefix>.*?)\\\\\""

[z0r0]

@ITWhisperer , the mistake I was doing is using | rex field=properties, your command worked for the actual data too. Thanks @ITWhisperer 

[z0r0]

Thanks @ITWhisperer , but seems like I'm missing something to apply this when trying on actual data. The rex you've shared is working on makeresults(testing pattern). Can you pls correct me?

I'm trying this command

index=test_ms* "operationName.localizedValue"="Initiate JIT Network Access Policy" "eventName.localizedValue"="JIT network access request initiate started"
| rex field=properties "allowedSourceAddressPrefix\\\":\\\"(?<allowedSourceAddressPrefix>.*?)\\\""
| table allowedSourceAddressPrefix

And here's a sample data entry (actual raw data)

{
  "channels": "Operation",
  "eventName": {
    "value": "JIT network access request initiate started",
    "localizedValue": "JIT network access request initiate started"
  },
  "eventSource": {
    "value": "Security",
    "localizedValue": "Security"
  },
  "id": "/subscriptions/3483b2ca-02cf-4ff6-92af-99326c8fac7f/resourceGroups/apple-dev/providers/Microsoft.Compute/virtualMachines/gjappledev/events/04xxxxab-5ecc-46b0-abfa-6aacb1f550ac/ticks/63783455299xxxxxx3",
  "level": "Informational",
  "resourceGroupName": "apple-dev",
  "resourceProviderName": {
    "value": "Microsoft.Compute",
    "localizedValue": "Microsoft.Compute"
  },
  "resourceUri": "/subscriptions/3483b2ca-02cf-4ff6-92af-99326c8fac7f/resourceGroups/apple-dev/providers/Microsoft.Compute/virtualMachines/gjappledev",
  "operationName": {
    "value": "Microsoft.Security/locations/jitNetworkAccessPolicies/initiate/action",
    "localizedValue": "Initiate JIT Network Access Policy"
  },
  "properties": {
    "User": "johndoe@contoso.com",
    "/subscriptions/3483b2ca-02cf-4ff6-92af-99326c8fac7f/resourceGroups/apple-dev/providers/Microsoft.Compute/virtualMachines/gjappledev": "{\"id\":\"/subscriptions/3483b2ca-02cf-4ff6-92af-99326c8fac7f/resourceGroups/apple-dev/providers/Microsoft.Compute/virtualMachines/gjappledev\",\"ports\":[{\"number\":3389,\"allowedSourceAddressPrefix\":\"*\",\"endTimeUtc\":\"2022-03-21T1:50:39.1599446Z\"}]}",
    "Justification": null
  },
  "status": {
    "value": "Accepted",
    "localizedValue": "Accepted"
  },
  "subStatus": {
    "value": null
  },
  "eventTimestamp": "2022-03-21T1:50:39.1599446Z",
  "submissionTimestamp": "2022-03-21T1:50:39.1599446Z",
  "subscriptionId": "3483b2ca-02cf-4ff6-92af-99326c8fac7f"
}

Thanks Again

[ITWhisperer]

You don't have enough backslashes - you need 3 sets of 5 (like I showed in my example) - SPL requires an extra level of escaping for backslashes above what is required by regex101.com for example.

[z0r0]

@ITWhisperer , just checked on regex101.com, the rex with three slashes works for the raw data i shared

the rex string i gave in regex101.com is 
/allowedSourceAddressPrefix\\\":\\\"(?<allowedSourceAddressPrefix>.*?)\\\"/gm

for the raw data i shared and it was able to match and get the desired value.

[z0r0]

@ITWhisperer , Oh, you meant more than regex101, still unable to get results with 5 slashes. Am I missing something when translating the command from makeresults example to the actual data(any formatting stuff)?