topic Re: JSON spath mvexpand in Getting Data In

JSON spath mvexpand

surekhasplunk — Sat, 08 Aug 2020 04:04:12 GMT

Hi,

{ [-]
   advisories: [ [+]
   ]
   number_of_device: 1
   os_name: ios
   os_version: 1234
   status: checked
}

Above is my parent json

And under advisories i have below json.

advisories: [ [-]
     { [-]
       a_id: abcd1234
       cv: [ [-]
         random_number
       ]
       score: 6.5
       www: [ [-]
         www-12
       ]
       first_published: 2020-06-03T16:00:00
       last_updated: 2020-06-08T20:41:10
       ab_score: 2/4
       summary:something

So here I want to count how many times the ab_score =2/4 and then get the corresponding score=6.5 for each os_version.

But when i am using spath and mvexpand i am getting 2/4 for all ab_score and all a_id.

not understanding whats happening. Ideally in the raw data 2/4 is there in only 4 places with 4 ab_score attached to it. But i am receiving more than that and repeated .

Please help. @kamlesh_vaghela

Re: JSON spath mvexpand

thambisetty — Sat, 08 Aug 2020 05:43:59 GMT

Can you share raw text of an event?

regex may not match if I write regex based on the event you posted.

Re: JSON spath mvexpand

surekhasplunk — Sat, 08 Aug 2020 07:58:47 GMT

Hi @thambisetty

Please find 1 entry:

{"status": "checked", "os_version": "12.4(3g)", "number_of_device": "1", "advisories": [{"advisory_id": "abcd-1234", "last_updated": "2020-06-08T20:41:10", "cvss_base_score": "6.5", "sg_risk_scoring": "2/4", "first_published": "2020-06-03T16:00:00", "summary": "some comments", "cwe": ["CWE-20"], "cves": ["CVE-1234-5678"]}, {"advisory_id": "cdef-1234", "last_updated": "2020-04-28T17:46:50", "cvss_base_score": "8.8", "sg_risk_scoring": "3/4", "first_published": "2020-01-08T16:00:00", "summary": "some comments", "cwe": ["CWE-352"], "cves": ["CVE-2345-6789"]}, {"advisory_id": "bcde-1234", "last_updated": "2007-01-10T16:00:00", "cvss_base_score": "3.3", "sg_risk_scoring": "1/4", "first_published": "2007-01-10T16:00:00", "summary": "some comments", "cwe": ["CWE-399"], "cves": ["CVE-3456-7897"]}], "os_name": "ios"}

Re: JSON spath mvexpand

to4kawa — Sat, 08 Aug 2020 09:02:16 GMT

index=_internal | head 1 | fields _raw _time | eval _raw="{\"status\": \"checked\", \"os_version\": \"12.4(3g)\", \"number_of_device\": \"1\", \"advisories\": [{\"advisory_id\": \"abcd-1234\", \"last_updated\": \"2020-06-08T20:41:10\", \"cvss_base_score\": \"6.5\", \"sg_risk_scoring\": \"2/4\", \"first_published\": \"2020-06-03T16:00:00\", \"summary\": \"some comments\", \"cwe\": [\"CWE-20\"], \"cves\": [\"CVE-1234-5678\"]}, {\"advisory_id\": \"cdef-1234\", \"last_updated\": \"2020-04-28T17:46:50\", \"cvss_base_score\": \"8.8\", \"sg_risk_scoring\": \"3/4\", \"first_published\": \"2020-01-08T16:00:00\", \"summary\": \"some comments\", \"cwe\": [\"CWE-352\"], \"cves\": [\"CVE-2345-6789\"]}, {\"advisory_id\": \"bcde-1234\", \"last_updated\": \"2007-01-10T16:00:00\", \"cvss_base_score\": \"3.3\", \"sg_risk_scoring\": \"1/4\", \"first_published\": \"2007-01-10T16:00:00\", \"summary\": \"some comments\", \"cwe\": [\"CWE-399\"], \"cves\": [\"CVE-3456-7897\"]}], \"os_name\": \"ios\"}" | spath advisories{} output=advisories | mvexpand advisories | spath | spath input=advisories | fields - advisories* | table * | fields - _*

There is not ab_score, what are you going to ask?

Re: JSON spath mvexpand

surekhasplunk — Sun, 09 Aug 2020 02:16:07 GMT

Hi @to4kawa

Thanks a lot ... it works there is no ab_score as it was a mocked data.

The only problem is the os_version and os_name value appears twice

Re: JSON spath mvexpand

to4kawa — Sun, 09 Aug 2020 02:44:57 GMT

your log have one os_version and os_name.
my query does not duplicate the field values.
your props.conf setting is wrong. please contact your splunk admin.

workaround:

...
| eval os_version=mvdedup(os_version) , os_name=mvdedup(os_name)

Re: JSON spath mvexpand

wryanthomas — Sat, 31 Jul 2021 15:58:13 GMT

Here's a variation on this answer I came up with that might help others. The variation is it uses regex to match each object in _raw in order to produce the multi-value field "rows" on which to perform the mvexpand.

| rex max_match=0 field=_raw "(?<rows>\{[^\}]+\})"
| table rows
| mvexpand rows
| spath input=rows
| fields - rows