topic Re: how do I make a table out of common nested json keys with uncommon json key parents? in Splunk Search

how do I make a table out of common nested json keys with uncommon json key parents?

zhatsispgx — Fri, 02 Feb 2018 00:52:58 GMT

I have the following JSON event that I'm indexing in splunk:

{
    "plugins": {
        "Redirection": {
            "pluginstatus": "active",
            "wpvulndb": {
                "popular": true,
                "vulnerabilities": [{
                    "vulnerablestatus": false,
                    "title": "Redirection 2.3.3 - view/admin/item.php URL H&ling Reflected XSS",
                    "created_at": "2014-08-01T10:59:06.000Z",
                    "updated_at": "2015-05-15T13:48:24.000Z",
                    "vuln_type": "XSS",
                    "references": {},
                    "published_date": null,
                    "fixed_in": "2.3.4",
                    "id": 7001
                }, {
                    "vulnerablestatus": false,
                    "title": "Redirection - view/admin/log_item.php Non-existent Posts Referer HTTP Header XSS",
                    "created_at": "2014-08-01T10:59:06.000Z",
                    "updated_at": "2015-05-15T13:48:24.000Z",
                    "vuln_type": "XSS",
                    "references": {
                        "cve": ["2011-4562"],
                        "secunia": ["46310"]
                    },
                    "published_date": null,
                    "fixed_in": "2.2.10",
                    "id": 7002
                }, {
                    "vulnerablestatus": false,
                    "title": "Redirection - wp-admin/tools.php id Parameter XSS",
                    "created_at": "2014-08-01T10:59:06.000Z",
                    "updated_at": "2015-05-15T13:48:24.000Z",
                    "vuln_type": "XSS",
                    "references": {
                        "secunia": ["45782"]
                    },
                    "published_date": null,
                    "fixed_in": "2.2.9",
                    "id": 7003
                }],
                "last_updated": "2018-01-30T07:26:00.000Z",
                "latest_version": "3.1.1"
            },
            "version": "2.6.6",
            "pluginpath": "redirection/redirection.php"
        },
        "Yoast SEO Premium": {
            "pluginstatus": "active",
            "wpvulndb": {
                "popular": false,
                "vulnerabilities": [{
                    "vulnerablestatus": false,
                    "title": "WordPress SEO by Yoast <= 2.0.1 - Cross-Site Scripting (XSS)",
                    "created_at": "2015-04-20T17:38:38.000Z",
                    "updated_at": "2015-10-01T08:11:16.000Z",
                    "vuln_type": "XSS",
                    "published_date": "2015-04-20T00:00:00.000Z",
                    "fixed_in": "2.1",
                    "id": 7913
                }],
                "last_updated": null,
                "latest_version": null
            },
            "version": "3.0.7",
            "pluginpath": "wordpress-seo-premium/wp-seo-premium.php"
        }
    }
}

if you notice, each 'plugin' has its own 'vulnerablestatus' . How would I create a table that would display each plugin name (in this case, "Redirection" and "Yoast SEO Premium") and its "vulnerablestatus" if its "vulnerablestatus"=true ? Ideally I dont want to have to reference each plugin when building the table. i.e. |table plugins.*.vulnerabilities{}.vulnerablestatus, plugins.*.vulnerabilities{}.title

I would like each row to look like this:

pluginname | plugin vulnerability | vulnerabilitystatus
......................................................................................................
redirection   | "... HTTP Header XSS" | false 
redirection   | "... Parameter XSS"      | false 
Yoast SEO ... |" ... XSS"               | false

Re: how do I make a table out of common nested json keys with uncommon json key parents?

niketn — Fri, 02 Feb 2018 05:13:15 GMT

@zhatsispgx, Try the following run anywhere search based on sample data provided. Commands till ... | spath generate dummy data/fields as per the question.

| makeresults
| eval _raw= "{
     \"plugins\": {
         \"Redirection\": {
             \"pluginstatus\": \"active\",
             \"wpvulndb\": {
                 \"popular\": true,
                 \"vulnerabilities\": [{
                     \"vulnerablestatus\": false,
                     \"title\": \"Redirection 2.3.3 - view/admin/item.php URL H&ling Reflected XSS\",
                     \"created_at\": \"2014-08-01T10:59:06.000Z\",
                     \"updated_at\": \"2015-05-15T13:48:24.000Z\",
                     \"vuln_type\": \"XSS\",
                     \"references\": {},
                     \"published_date\": null,
                     \"fixed_in\": \"2.3.4\",
                     \"id\": 7001
                 }, {
                     \"vulnerablestatus\": false,
                     \"title\": \"Redirection - view/admin/log_item.php Non-existent Posts Referer HTTP Header XSS\",
                     \"created_at\": \"2014-08-01T10:59:06.000Z\",
                     \"updated_at\": \"2015-05-15T13:48:24.000Z\",
                     \"vuln_type\": \"XSS\",
                     \"references\": {
                         \"cve\": [\"2011-4562\"],
                         \"secunia\": [\"46310\"]
                     },
                     \"published_date\": null,
                     \"fixed_in\": \"2.2.10\",
                     \"id\": 7002
                 }, {
                     \"vulnerablestatus\": false,
                     \"title\": \"Redirection - wp-admin/tools.php id Parameter XSS\",
                     \"created_at\": \"2014-08-01T10:59:06.000Z\",
                     \"updated_at\": \"2015-05-15T13:48:24.000Z\",
                     \"vuln_type\": \"XSS\",
                     \"references\": {
                         \"secunia\": [\"45782\"]
                     },
                     \"published_date\": null,
                     \"fixed_in\": \"2.2.9\",
                     \"id\": 7003
                 }],
                 \"last_updated\": \"2018-01-30T07:26:00.000Z\",
                 \"latest_version\": \"3.1.1\"
             },
             \"version\": \"2.6.6\",
             \"pluginpath\": \"redirection/redirection.php\"
         },
         \"Yoast SEO Premium\": {
             \"pluginstatus\": \"active\",
             \"wpvulndb\": {
                 \"popular\": false,
                 \"vulnerabilities\": [{
                     \"vulnerablestatus\": false,
                     \"title\": \"WordPress SEO by Yoast <= 2.0.1 - Cross-Site Scripting (XSS)\",
                     \"created_at\": \"2015-04-20T17:38:38.000Z\",
                     \"updated_at\": \"2015-10-01T08:11:16.000Z\",
                     \"vuln_type\": \"XSS\",
                     \"published_date\": \"2015-04-20T00:00:00.000Z\",
                     \"fixed_in\": \"2.1\",
                     \"id\": 7913
                 }],
                 \"last_updated\": null,
                 \"latest_version\": null
             },
             \"version\": \"3.0.7\",
             \"pluginpath\": \"wordpress-seo-premium/wp-seo-premium.php\"
         }
     }
 }"
 | spath
 | fields *.vulnerablestatus *.title
 | foreach *.title [eval "<<MATCHSTR>>.zippedData"=mvzip('<<FIELD>>','<<MATCHSTR>>.vulnerablestatus',";")]
 | table *.zippedData
 | transpose
 | mvexpand "row 1"
 | eval "row 1"=split('row 1',";")
 | eval vulnerability=mvindex('row 1',0),vulnerability_status=mvindex('row 1',1)
 | eval plugin_name=replace(vulnerability,"^(\w+)(.*)","\1"), plugin_vulnarability=replace(vulnerability,"^([^-]-\s)(.*)","\2")
 | table plugin_name plugin_vulnarability vulnerability_status

Re: how do I make a table out of common nested json keys with uncommon json key parents?

zhatsispgx — Fri, 02 Feb 2018 18:34:11 GMT

this looks like it does what I need, however I have one key that looks like it has a | in it which is making the rest puke. The sample above isn't the full payload i'd be searching against.. just 2 plugin samples.

I'm getting the error Failed to parse templatized search for field 'plugins.Shareaholic | share buttons analytics related content.wpvulndb.vulnerabilities{}.title'

Re: how do I make a table out of common nested json keys with uncommon json key parents?

niketn — Sun, 04 Feb 2018 04:08:36 GMT

@zhatsispgx, Can you paste that key which has a pipe character? |

Also can you try the following?

<YourBaseSearch>
|  eval _raw=replace(_raw,"|","")

Re: how do I make a table out of common nested json keys with uncommon json key parents?

SEOCommmunity — Thu, 09 Sep 2021 09:51:40 GMT

Thanks for the post. I have resolved the yoast plugin problem on this site ( https://seotraining.co ).