Getting Data In

JSON spath mvexpand

surekhasplunk
Communicator

Hi,

{ [-]
   advisories: [ [+]
   ]

   number_of_device1
   os_nameios
   os_version1234
   statuschecked

}

Above is my parent json

And under advisories i have below json. 

advisories: [ [-]
     { [-]
       a_idabcd1234
       cv: [ [-]
         random_number
       ]

       score6.5
       www: [ [-]
         www-12
       ]

       first_published2020-06-03T16:00:00
       last_updated2020-06-08T20:41:10
       ab_score2/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

Labels (1)
Tags (2)
0 Karma
1 Solution

to4kawa
Ultra Champion
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?

View solution in original post

0 Karma

thambisetty
SplunkTrust
SplunkTrust

Can you share raw text of an event?

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

————————————
If this helps, give a like below.
0 Karma

surekhasplunk
Communicator

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

0 Karma

to4kawa
Ultra Champion
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?

0 Karma

wryanthomas
Contributor

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

0 Karma

surekhasplunk
Communicator

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 

surekhasplunk_0-1596939325603.png

 

0 Karma

to4kawa
Ultra Champion

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)

Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...