'Parse nested Json to splunk query which has string

I have a multiple result for a macAddress which contains the device details.

This is the sample data

    "data": {
        "a1:b2:c3:d4:11:22": {
            "deviceIcons": {
                "type": "Phone",
                "icons": {
                    "3x": null,
                    "2x": "image.png"
                }
            },
            "advancedDeviceId": {
                "agentId": 113,
                "partnerAgentId": "131",
                "dhcpHostname": "Galaxy-J7",
                "mac": "a1:b2:c3:d4:11:22",
                "lastSeen": 12,
                "model": "Android Phoe",
                "id": 1
            }
        },
        "a0:b2:c3:d4:11:22": {
            "deviceIcons": {
                "type": "Phone",
                "icons": {
                    "3x": null,
                    "2x": "image.png"
                }
            },
            "advancedDeviceId": {
                "agentId": 113,
                "partnerAgentId": "131",
                "dhcpHostname": "Galaxy",
                "mac": "a0:b2:c3:d4:11:22",
                "lastSeen": 12,
                "model": "Android Phoe",
                "id": 1
            }
        }
    }
}

How can I query in splunk for all the kind of above sample results to get the advancedDeviceId.model and advancedDeviceId.id in tabular format?

Solution 1:^[1]

I think this will do what you want

| spath
| untable _time column value
| rex field=column "data.(?<address>[^.]+)\.advancedDeviceId\.(?<item>[^.]+)"
| table _time address item value
| eval {item}=value
| stats list(model) as model
        list(id) as id
        list(dhcpHostname) as dhcpHostname
        list(mac) as mac
        by address

Here is a "run anywhere" example that has two events each with two addresses:

| makeresults
| eval _raw="{\"data\":{\"a1:b2:c3:d4:11:21\":{\"deviceIcons\":{\"type\":\"Phone\",\"icons\":{\"3x\":null,\"2x\":\"image.png\"}},\"advancedDeviceId\":{\"agentId\":113,\"partnerAgentId\":\"131\",\"dhcpHostname\":\"Galaxy-J7\",\"mac\":\"a1:b2:c3:d4:11:21\",\"lastSeen\":12,\"model\":\"Android Phoe\",\"id\":1}},\"a0:b2:c3:d4:11:22\":{\"deviceIcons\":{\"type\":\"Phone\",\"icons\":{\"3x\":null,\"2x\":\"image.png\"}},\"advancedDeviceId\":{\"agentId\":113,\"partnerAgentId\":\"131\",\"dhcpHostname\":\"iPhone 6\",\"mac\":\"a0:b2:c3:d4:11:22\",\"lastSeen\":12,\"model\":\"Apple Phoe\",\"id\":2}}}}"
| append [
    | makeresults
    | eval _raw="{\"data\":{\"b1:b2:c3:d4:11:23\":{\"deviceIcons\":{\"type\":\"Phone\",\"icons\":{\"3x\":null,\"2x\":\"image.png\"}},\"advancedDeviceId\":{\"agentId\":113,\"partnerAgentId\":\"131\",\"dhcpHostname\":\"Nokia\",\"mac\":\"b1:b2:c3:d4:11:23\",\"lastSeen\":12,\"model\":\"Symbian Phoe\",\"id\":3}},\"b0:b2:c3:d4:11:24\":{\"deviceIcons\":{\"type\":\"Phone\",\"icons\":{\"3x\":null,\"2x\":\"image.png\"}},\"advancedDeviceId\":{\"agentId\":113,\"partnerAgentId\":\"131\",\"dhcpHostname\":\"Windows\",\"mac\":\"b0:b2:c3:d4:11:24\",\"lastSeen\":12,\"model\":\"Windows Phoe\",\"id\":4}}}}"
]
| spath
| untable _time column value
| rex field=column "data.(?<address>[^.]+)\.advancedDeviceId\.(?<item>[^.]+)"
| table _time address item value
| eval {item}=value
| stats list(model) as model
        list(id) as id
        list(dhcpHostname) as dhcpHostname
        list(mac) as mac
        by address

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Jerry Jeremiah

'Parse nested Json to splunk query which has string

Solution 1:[1]

Sources

Related Questions

Solution 1:^[1]