topic Re: Extract a field from an escaped string inside a nested JSON in Splunk Search

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

z0r0 — Mon, 21 Mar 2022 17:41:04 GMT

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

Re: Extract a field from an escaped string inside a nested JSON

ITWhisperer — Mon, 21 Mar 2022 08:45:42 GMT

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

Re: Extract a field from an escaped string inside a nested JSON

z0r0 — Mon, 21 Mar 2022 18:33:49 GMT

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

Re: Extract a field from an escaped string inside a nested JSON

ITWhisperer — Mon, 21 Mar 2022 18:41:51 GMT

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.

Re: Extract a field from an escaped string inside a nested JSON

z0r0 — Mon, 21 Mar 2022 19:03:02 GMT

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

Re: Extract a field from an escaped string inside a nested JSON

z0r0 — Mon, 21 Mar 2022 19:44:36 GMT

@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)?

Re: Extract a field from an escaped string inside a nested JSON

z0r0 — Tue, 22 Mar 2022 16:49:17 GMT

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