JSON spath mvexpand

[surekhasplunk]

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

[thambisetty]

Can you share raw text of an event?

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

[surekhasplunk]

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"}

[to4kawa · Accepted solution]

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?

[wryanthomas]

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

[surekhasplunk]

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 

[to4kawa]

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)