topic Re: Parse json into rows in Splunk Search

How to parse json into rows?

maayan — Mon, 01 May 2023 11:26:25 GMT

Hi,

I have issue similar to: https://community.splunk.com/t5/Getting-Data-In/how-to-split-the-json-array-into-multiple-new-events/m-p/122265
But my case is a bit different.

json structure:
{
"MetaData": {
"Host Name": "....",
"Wi-Fi Driver Version": "..."
},
"Payloads": [
{
"Header": {
"Type": "Event",
"Name": "...",
"TimeStamp": ...
},
"Payload": {
"MAC Address": "00:00:00:00:00:00",
"Network Adapter Type": ...
}
},
{
]
}

i need to:
1. extract table contain the following columns : MetaData.host name,MetaData.Wi-Fi Driver Version,Header.Type, Header.Name,Payload.MAC Address,Payload.Network Adapter Type

2. i expected to see 2 rows in this case

3. the fields name under MetaData,Header and Payload can changed, so it's should be generic.

I have started to write something like that- but it's not generic (type,name,..) and it doesn't extract the meta data:
| spath input=json output=payloadsObjects path=Payloads{}
| mvexpand payloadsObjects
| spath input=payloadsObjects output=Type path=Header.Type
| spath input=payloadsObjects output=Name path=Header.Name
| table *

json as example to use:
| makeresults
| eval _raw="{ \"MetaData\": { \"Host Name\": \"maya-MOBL\", \"Driver Version\": \"99.0.100.4\" }, \"Payloads\": [ { \"Header\": { \"Type\": \"Event\", \"Name\": \"IP Disconnection\", \"TimeStamp\": 133265876804261336 }, \"Payload\": { \"MAC Address\": \"00:00:00:00:00:00\", \"Adapter Type\": 140 } }, { \"Header\": { \"Type\": \"Event\", \"Name\": \"Connection success\", \"TimeStamp\": 133265877087374706 }, \"Payload\": { \"MAC Address\": \"00:00:00:00:00:00\", \"Network Adapter Type\": 131, \"Address\": \"0000:0:0000:000:000:df:0000:0000\", \"Prefix Length\": 64, \"Is Local Address\": false, \"Gateway IP Address\": \"::\", \"DNS Server\": [], \"DHCP Server\": null, \"DHCP Lease Duration\": 000000000, \"DHCP Retrieval Time\": 0 } } ] }"

May I get your help please?

*note- nice to have also solution that doesn't use makeresult because it made me problem to find Payloads{} field when i used real json file in my report and not makeresult.
*note 2- need to take time performance into consideration

Re: Parse json into rows

maayan — Sun, 30 Apr 2023 13:18:22 GMT

*I would explain that: Payloads is an array of Events.
Each Event is divided to Header and Payload. Fields under Payload and Header can be changed (therefore it should be generic).
As i said- Meta data fields also need to be extracted. they are also generic.

Re: Parse json into rows

yuanliu — Sun, 30 Apr 2023 21:54:02 GMT

It's great that you not only illustrate data, but offer makeresults emulation. This helps a lot. Let me first point to the solution:

| spath path=Payloads{}.Payload | mvexpand Payloads{}.Payload ``` | rename Payload{}.Payload Payload ```

(I added a rename in comment in case you want to use a field name without citing the full path.)

I want to circle back to the data illustration. The sample makeresults unfortunately did not produce valid JSON. This is what I suspected is a valid emulation of your real data:

| makeresults | eval _raw="{ \"MetaData\": { \"Host Name\": \"maya-MOBL\", \"Driver Version\": \"99.0.100.4\" }, \"Payloads\": [ { \"Header\": { \"Type\": \"Event\", \"Name\": \"IP Disconnection\", \"TimeStamp\": 133265876804261336 }, \"Payload\": { \"MAC Address\": \"00:00:00:00:00:00\", \"Adapter Type\": 140 } }, { \"Header\": { \"Type\": \"Event\", \"Name\": \"Connection success\", \"TimeStamp\": 133265877087374706 }, \"Payload\": { \"MAC Address\": \"00:00:00:00:00:00\", \"Network Adapter Type\": 131} }], \"Address\": \"0000:0:0000:000:000:df:0000:0000\", \"Prefix Length\": 64, \"Is Local Address\": false, \"Gateway IP Address\": \"::\", \"DNS Server\": [], \"DHCP Server\": null, \"DHCP Lease Duration\": 0, \"DHCP Retrieval Time\": 0 }" ```| eval valid = if(json_valid(_raw), "true", "false")```

The emulation gives this:

{ "MetaData": { "Host Name": "maya-MOBL", "Driver Version": "99.0.100.4" }, "Payloads": [ { "Header": { "Type": "Event", "Name": "IP Disconnection", "TimeStamp": 133265876804261336 }, "Payload": { "MAC Address": "00:00:00:00:00:00", "Adapter Type": 140 } }, { "Header": { "Type": "Event", "Name": "Connection success", "TimeStamp": 133265877087374706 }, "Payload": { "MAC Address": "00:00:00:00:00:00", "Network Adapter Type": 131} }], "Address": "0000:0:0000:000:000:df:0000:0000", "Prefix Length": 64, "Is Local Address": false, "Gateway IP Address": "::", "DNS Server": [], "DHCP Server": null, "DHCP Lease Duration": 0, "DHCP Retrieval Time": 0 }

Hope this helps

Re: Parse json into rows

mph — Mon, 01 May 2023 09:17:28 GMT

Hi,

Thanks for your help. Regarding the invalid json- it's ChatGPT fault, not mine sorry 🙂

Here is an updated json:
{ "MetaData": { "Host Name": "maya-MOBL", "Driver Version": "0.0.0.0" }, "Payloads": [ { "Header": { "Type": "Event", "Name": "IP Disconnection", "TimeStamp": 133265876804261336 }, "Payload": { "MAC Address": "00:00:00:00:00:00", "Network Adapter Type": 131 } }, { "Header": { "Type": "Event", "Name": "conn success", "TimeStamp": 133265877087374706 }, "Payload": { "MAC Address": "00:00:00:00:00:10", "Network Adapter Type": 131, "IPv6 Address": "0000:0:0000:000:000:00:0000:0000", "Prefix Length": 64, "Is Local Address": false, "Gateway IP Address": "::", "DNS Server IP Address": [ ], "DHCP Server": null, "DHCP Lease Duration": 4294967295, "DHCP Retrieval Time": 0 } } ] }

makeresult format:
_raw="{ \"MetaData\": { \"Host Name\": \"maya-MOBL\", \"Driver Version\": \"0.0.0.0\" }, \"Payloads\": [ { \"Header\": { \"Type\": \"Event\", \"Name\": \"IP Disconnection\", \"TimeStamp\": 133265876804261336 }, \"Payload\": { \"MAC Address\": \"00:00:00:00:00:00\", \"Network Adapter Type\": 131 } }, { \"Header\": { \"Type\": \"Event\", \"Name\": \"conn success\", \"TimeStamp\": 133265877087374706 }, \"Payload\": { \"MAC Address\": \"00:00:00:00:00:10\", \"Network Adapter Type\": 131, \"IPv6 Address\": \"0000:0:0000:000:000:00:0000:0000\", \"Prefix Length\": 64, \"Is Local Address\": false, \"Gateway IP Address\": \"::\", \"DNS Server IP Address\": [ ], \"DHCP Server\": null, \"DHCP Lease Duration\": 4294967295, \"DHCP Retrieval Time\": 0 } } ] }"

I expect to see the result like this(contained all the columns of Payload, MetaData and Header),I haven't succeed yet:

Thanks a lot,

Maayan