How to extract Json array of objects data into table format in Splunk ?

harishalipaka — Wed, 11 Mar 2020 08:00:40 GMT

Example data :
We need to extract below json data into table format in Splunk ?link text

"assets": [
{
"id": 1,

        "last_seen_time": "2020-02-26T16:23:06Z",
        "network_ports": [
            {
                "id": 100,
                "port_number": 111,
                "extra_info": "",
                "hostname": null,
                "name": "unknown",
                "ostype": "",
                "product": null,
                "protocol": "tcp",
                "state": "open",
                "version": null
            },
            {
                "id": 343,
                "port_number": 444,
                "extra_info": "",
                "hostname": null,
                "name": "unknown",
                "ostype": "",
                "product": null,
                "protocol": "tcp",
                "state": "open",
                "version": null
            },

        ],
        "tags": [
            "Loc: Ajay"
        ],
        "owner": null,
        "urls": {
            "vulnerabilities": "google.com/examples/1012/tests"
        },
        "ip_address": "1.1.0.91",
        "database": null,
        "hostname": "swetha",
        "asset_groups": [
            {
                "id": 191300,
                "name": "All examples"
            }
        ]
    },
    {
        "id": 1012,

        "last_seen_time": "2020-02-26T16:23:06Z",
        "network_ports": [
            {
                "id": 331,
                "port_number": 135,
                "extra_info": "",
                "hostname": null,
                "name": "unknown",
                "ostype": "",
                "product": null,
                "protocol": "tcp",
                "state": "open",
                "version": null
            },
            {
                "id": 343,
                "port_number": 444,
                "extra_info": "",
                "hostname": null,
                "name": "unknown",
                "ostype": "",
                "product": null,
                "protocol": "tcp",
                "state": "open",
                "version": null
            },

        ],
        "tags": [
            "Loc: NorthCEE"
        ],
        "owner": null,
        "urls": {
            "vulnerabilities": "google.com/examples/2/tests"
        },
        "ip_address": "1.1.0.92",
        "database": null,
        "hostname": "sweety",
        "asset_groups": [
            {
                "id": 191300,
                "name": "All exs"
            }
        ]
    },

]

Re: How to extract Json array of objects data into table format in Splunk ?

to4kawa — Wed, 11 Mar 2020 12:01:55 GMT

| makeresults 
| eval _raw="\"assets\":[{\"id\":1,\"last_seen_time\":\"2020-02-26T16:23:06Z\",\"network_ports\":[{\"id\":100,\"port_number\":111,\"extra_info\":\"\",\"hostname\":null,\"name\":\"unknown\",\"ostype\":\"\",\"product\":null,\"protocol\":\"tcp\",\"state\":\"open\",\"version\":null},{\"id\":343,\"port_number\":444,\"extra_info\":\"\",\"hostname\":null,\"name\":\"unknown\",\"ostype\":\"\",\"product\":null,\"protocol\":\"tcp\",\"state\":\"open\",\"version\":null}],\"tags\":[\"Loc: Ajay\"],\"owner\":null,\"urls\":{\"vulnerabilities\":\"google.com/examples/1012/tests\"},\"ip_address\":\"1.1.0.91\",\"database\":null,\"hostname\":\"swetha\",\"asset_groups\":[{\"id\":191300,\"name\":\"All examples\"}]},{\"id\":1012,\"last_seen_time\":\"2020-02-26T16:23:06Z\",\"network_ports\":[{\"id\":331,\"port_number\":135,\"extra_info\":\"\",\"hostname\":null,\"name\":\"unknown\",\"ostype\":\"\",\"product\":null,\"protocol\":\"tcp\",\"state\":\"open\",\"version\":null},{\"id\":343,\"port_number\":444,\"extra_info\":\"\",\"hostname\":null,\"name\":\"unknown\",\"ostype\":\"\",\"product\":null,\"protocol\":\"tcp\",\"state\":\"open\",\"version\":null}],\"tags\":[\"Loc: NorthCEE\"],\"owner\":null,\"urls\":{\"vulnerabilities\":\"google.com/examples/2/tests\"},\"ip_address\":\"1.1.0.92\",\"database\":null,\"hostname\":\"sweety\",\"asset_groups\":[{\"id\":191300,\"name\":\"All exs\"}]}]"
| rex mode=sed "s/(.*)/{\1}/"
| spath

This query works. If you want only searching, try this rex and spath
I guess your JSON is not valid.

recommed props.conf

[your sourcetype]
SEDCMD-add_header = s/(.*)/{\1}/
KV_MODE=json

topic How to extract Json array of objects data into table format in Splunk ? in Splunk Search

How to extract Json array of objects data into table format in Splunk ?

Re: How to extract Json array of objects data into table format in Splunk ?