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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...