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
Super Champion

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
Communicator

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!

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

Getting Started with AIOps: Event Correlation Basics and Alert Storm Detection in ...

Getting Started with AIOps:Event Correlation Basics and Alert Storm Detection in Splunk IT Service ...

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...