topic Re: expand json array to multiple events, then search constraints on the results in Splunk Search

expand json array to multiple events, then search constraints on the results

zachsisinst — Thu, 23 Apr 2020 04:35:16 GMT

Hello, I've gone through a hundred of these types of posts and nothing is working for me. Here is the nested json array that I would like to split into a table of individual events, based on the computer.hardware.storage.device.partition{} and computer.general.name. Once I have these split into individual events, I would like to only put the 'boot' device event in the table.

{
    "computer": {
        "general": {
            "name": "woohoo-l3"
        },
        "hardware": {
            "storage": {
                "device": {
                    "partition": [
                        {
                            "name": "Macintosh HD (Boot Partition)",
                            "type": "boot",
                            "filevault_status": "Encrypted",
                            "filevault_percent": "100",
                        },
                        {
                            "name": "Recovery",
                            "type": "other",
                            "filevault_status": "Not Encrypted",
                            "filevault_percent": "0",
                        }
                    ]
                }
            }
        }
    }
}

I have come up with the following search but it does not do what I want. I've been messing with this all day and I'm stuck. Any help would be greatly appreciated!

index=sec-inventory sourcetype="jamf-computers" "c02z912nlvdl" 
| spath
| rename computer.hardware.storage.device{}.partition.filevault_status as filevault_status 
| rename computer.hardware.storage.device.partition{}.type as partitiontype
| rename computer.general.name as computername 
| eval zipped=mvzip(filevault_status, partitiontype)
| mvexpand zipped
| eval zipped=split(zipped, ",")
| eval filevault_status=mvindex(zipped, 0)
| eval type=mvindex(zipped, 1)
| fillnull value="null" 
| table computername, partitiontype, filevault_status
| search partitiontype="boot"

The table should look like

Re: expand json array to multiple events, then search constraints on the results

vnravikumar — Thu, 23 Apr 2020 05:13:36 GMT

Please try the following and check whether your JSON data is a valid one.
Finally, add your filter.

| makeresults 
| eval temp=" {
     \"computer\": {
         \"general\": {
             \"name\": \"woohoo-l3\"
                                     },
         \"hardware\": {
             \"storage\": {
                 \"device\": {
                     \"partition\": [
                         {
                             \"name\": \"Macintosh HD (Boot Partition)\",
                             \"type\": \"boot\",
                             \"filevault_status\": \"Encrypted\",
                             \"filevault_percent\": \"100\"
                                                     },
                         {
                             \"name\": \"Recovery\",
                             \"type\": \"other\",
                             \"filevault_status\": \"Not Encrypted\",
                             \"filevault_percent\": \"0\"
                                                     }
                     ]
                 }
             }
         }
     }
 }" 
| spath input=temp 
| rename computer.hardware.storage.device.partition{}.filevault_status as filevault_status 
| rename computer.hardware.storage.device.partition{}.type as partitiontype 
| rename computer.general.name as computername 
| fields computername partitiontype filevault_status 
| eval temp=mvzip(partitiontype,filevault_status) 
| table computername temp 
| mvexpand temp 
| eval value=split(temp,",") 
| eval partitiontype=mvindex(value,0),filevault_status=mvindex(value,1) 
| table computername partitiontype filevault_status

Re: expand json array to multiple events, then search constraints on the results

to4kawa — Thu, 23 Apr 2020 09:09:03 GMT

 index=sec-inventory sourcetype="jamf-computers" "c02z912nlvdl" 
| spath computer.hardware.storage.device.partition{} output=partition
| spath computer.general.name output=computername
| stats values(computername) as computername by partition
| spath input=partition
| fields - partition

this is enough.

Re: expand json array to multiple events, then search constraints on the results

zachsisinst — Tue, 28 Apr 2020 16:47:34 GMT

I can't seem to get this to work still.

index=sec-inventory sourcetype="jamf-computers" 
| spath 
| rename computer.hardware.storage.device.partition{}.filevault_status as filevault_status 
| rename computer.hardware.storage.device.partition{}.type as partitiontype 
| rename computer.general.name as computername 
| fields computername partitiontype filevault_status 
| eval temp=mvzip(partitiontype,filevault_status) 
| table computername temp 
| mvexpand temp 
| eval value=split(temp,",") 
| eval partitiontype=mvindex(value,0),filevault_status=mvindex(value,1) 
| table computername partitiontype filevault_status

Re: expand json array to multiple events, then search constraints on the results

zachsisinst — Tue, 28 Apr 2020 16:49:17 GMT

this returns no results in the stats table.