topic Re: How to count a number of keys in json object for each event? in Splunk Search

How to count a number of keys in json object for each event?

a_r1em — Thu, 22 Aug 2019 10:32:15 GMT

Hi, I am trying to create a table witch show number of fields in json object:

Event example:

{  
   "project": "my_project_name",
   "vulnerabilities":{  
      "paragonie/random_compat":{  
         "version":"v1.4.3",
         "advisories":[  
            {  
               "title":"Uses insecure CSPRNG (openssl_random_pseudo_bytes())",
               "link":"https://github.com/paragonie/random_compat/issues/96",
               "cve":""
            }
         ]
      },
      "symfony/http-foundation":{  
         "version":"v2.8.49",
         "advisories":[  
            {  
               "title":"CVE-2019-10913: Reject invalid HTTP method overrides",
               "link":"https://symfony.com/cve-2019-10913",
               "cve":"CVE-2019-10913"
            }
         ]
      },
      "symfony/polyfill-php55":{  
         "version":"v1.4.0",
         "advisories":[  
            {  
               "title":"Possible DOS attack with long user-submitted passwords (correct fix for CVE-2013-5958)",
               "link":"https://github.com/symfony/polyfill/pull/155",
               "cve":"CVE-2013-5958"
            }
         ]
      },
      "symfony/symfony":{  
         "version":"v2.3.41",
         "advisories":[  
            {  
               "title":"CVE-2018-11385: Session Fixation Issue for Guard Authentication",
               "link":"https://symfony.com/cve-2018-11385",
               "cve":"CVE-2018-11385"
            },
            {  
               "title":"CVE-2018-11386: Denial of service when using PDOSessionHandler",
               "link":"https://symfony.com/cve-2018-11386",
               "cve":"CVE-2018-11386"
            },
            {  
               "title":"CVE-2018-11406: CSRF Token Fixation",
               "link":"https://symfony.com/cve-2018-11406",
               "cve":"CVE-2018-11406"
            },
            {  
               "title":"CVE-2018-14773: Remove support for legacy and risky HTTP headers",
               "link":"https://symfony.com/blog/cve-2018-14773-remove-support-for-legacy-and-risky-http-headers",
               "cve":"CVE-2018-14773"
            }
         ]
      },
      "twig/twig":{  
         "version":"v1.23.1",
         "advisories":[  
            {  
               "title":"Sandbox Information Disclosure",
               "link":"https://symfony.com/blog/twig-sandbox-information-disclosure",
               "cve":""
            }
         ]
      },
      "zendframework/zend-diactoros":{  
         "version":"1.2.0",
         "advisories":[  
            {  
               "title":"URL Rewrite vulnerability",
               "link":"https://framework.zend.com/security/advisory/ZF2018-01",
               "cve":""
            }
         ]
      }
   }
}

Expected result:

______________________________________________
|   timestamp    |  vulnerable_dependencies  |
|  timestamp_1   |               6           |

For example, there are 5 different projects scanned every day. Scan results are sent to splunk. I want to get a table, which shows the date of last scan and number of vulnerabilities in last scan for each project.

I guess it can be something like this:

source="http:vulns_scan" | stats last(_time), "query which give me vulnerabilities number" by project

I tried to use this example: https://answers.splunk.com/answers/735442/how-do-i-count-fields-inside-a-json-array.html, but probably it works for arrays only. Could someone advise the search query, please?

Re: How to count a number of keys in json object for each event?

Sukisen1981 — Thu, 22 Aug 2019 10:50:35 GMT

didnt understand your expected output and also what you get when you apply an spath..can you clarify a bit more?

Re: How to count a number of keys in json object for each event?

Sukisen1981 — Thu, 22 Aug 2019 10:59:24 GMT

where are you picking the timestamp from? if you pick _time and you are ingesting this api say once every hour, you will get ALL vulnerabilities at the same timestamp...there would be a reported/created field in this api against each vulnerability, you further want to group them by day or something like that? please clarify

Re: How to count a number of keys in json object for each event?

a_r1em — Thu, 22 Aug 2019 11:56:36 GMT

I guess it can be something like this:

source="http:vulns_scan" | stats last(_time), "query which give me vulnerabilities number" by project

Re: How to count a number of keys in json object for each event?

Sukisen1981 — Thu, 22 Aug 2019 12:20:49 GMT

you are basically counting the cve , if a cve id exists you consider it to be a vulnerability.
try this

    | rex field=_raw "\"+cve\"+\:+\"(?<cve>.*?)\"" max_match=0 
    | mvexpand cve
    | where len(cve)!=0
    | stats count(cve) by _time,source

As far as splitting by project goes you will have to determine what that field is..it has to be something unique to the 5 projects you mention - maybe source, sourcetype or something else

Re: How to count a number of keys in json object for each event?

a_r1em — Thu, 22 Aug 2019 13:49:28 GMT

Thanks for your help, it is useful. I changed rex expression and have some results.

The result table included time and cve number for all scans for each project. However, I need only the last result for each project.

My data has uniqe field "project", which I can use to group results instead source.

Your method to count cve looks interesting, however to count keys in json['vulnerabilities'] object more universal if it is possible at all.

Re: How to count a number of keys in json object for each event?

Sukisen1981 — Wed, 30 Sep 2020 01:51:51 GMT

hi @a_r1em
It is possible of course but then you would not get a count of 6 as in your example. My suggestion is based on getting 6 vulnerabilities as in your expected output.
We can write 5-6 regex(extract the url,version , title and so on..) or 1 single regex and fetch everything between say
:{

"paragonie/random_compat":{

"version":"v1.4.3",
"advisories":[

{

"title":"Uses insecure CSPRNG (openssl_random_pseudo_bytes())",
"link":"https://github.com/paragonie/random_compat/issues/96",
"cve":""
}
]
this will give m 9 occurrences. it really depends on your use case

Re: How to count a number of keys in json object for each event?

Sukisen1981 — Fri, 23 Aug 2019 18:51:37 GMT

hi @a_r1em
Any updates?