topic Re: How do I extract fields and values from a multivalue field? in Splunk Search

How do I extract fields and values from a multivalue field?

sivaranjiniG — Thu, 20 Sep 2018 15:26:54 GMT

this is my JSON object i am getting as an event

{
  "id": 78124,
  "uuid": "AWBr0ilGbvobIxfakBsC",
  "key": "com.vitalityactive.va:master",
  "name": "Mobile - Android - Vitality Active",
  "scope": "PRJ",
  "qualifier": "TRK",
  "date": "2017-12-20T19:07:51+0000",
  "creationDate": "2017-12-18T22:50:23+0000",
  "lname": "Mobile - Android - Vitality Active",
  "version": "1.0.0-rc.3",
  "msr": [
    {
      "key": "ncloc",
      "val": 91895,
      "frmt_val": "91,895"
    },
    {
      "key": "complexity",
      "val": 15906,
      "frmt_val": "15,906"
    },
    {
      "key": "file_complexity",
      "val": 11.2,
      "frmt_val": "11.2"
    },
    {
      "key": "class_complexity",
      "val": 7.9,
      "frmt_val": "7.9"
    },
    {
      "key": "function_complexity",
      "val": 1.2,
      "frmt_val": "1.2"
    },
    {
      "key": "coverage",
      "val": 11.8,
      "frmt_val": "11.8%"
    },
    {
      "key": "violations",
      "val": 5886,
      "frmt_val": "5,886"
    },
    {
      "key": "blocker_violations",
      "val": 13,
      "frmt_val": "13"
    },
    {
      "key": "critical_violations",
      "val": 165,
      "frmt_val": "165"
    },
    {
      "key": "major_violations",
      "val": 3687,
      "frmt_val": "3,687"
    },
    {
      "key": "sqale_index",
      "val": 123465,
      "frmt_val": "257d"
    }
  ]
}

I want to extract all the keys and values inside "msr".

Re: How do I extract fields and values from a multivalue field?

kamlesh_vaghela — Thu, 20 Sep 2018 16:36:11 GMT

@sivaranjiniG

Can you please try following search?

YOUR_SEARCH  | rename msr{}.* as * 
| eval tempField=mvzip(key,val) 
| stats count by _time id tempField
| eval key = mvindex(split(tempField,","),0), val= mvindex(split(tempField,","),1)  
| table key val

My Sample Search

| makeresults 
| eval _raw="{
\"id\": 78124,
\"uuid\": \"AWBr0ilGbvobIxfakBsC\",
\"key\": \"com.vitalityactive.va:master\",
\"name\": \"Mobile - Android - Vitality Active\",
\"scope\": \"PRJ\",
\"qualifier\": \"TRK\",
\"date\": \"2017-12-20T19:07:51+0000\",
\"creationDate\": \"2017-12-18T22:50:23+0000\",
\"lname\": \"Mobile - Android - Vitality Active\",
\"version\": \"1.0.0-rc.3\",
\"msr\": [
{
\"key\": \"ncloc\",
\"val\": 91895,
\"frmt_val\": \"91,895\"
    },
{
\"key\": \"complexity\",
\"val\": 15906,
\"frmt_val\": \"15,906\"
    },
{
\"key\": \"file_complexity\",
\"val\": 11.2,
\"frmt_val\": \"11.2\"
    },
{
\"key\": \"class_complexity\",
\"val\": 7.9,
\"frmt_val\": \"7.9\"
    },
{
\"key\": \"function_complexity\",
\"val\": 1.2,
\"frmt_val\": \"1.2\"
    },
{
\"key\": \"coverage\",
\"val\": 11.8,
\"frmt_val\": \"11.8%\"
    },
{
\"key\": \"violations\",
\"val\": 5886,
\"frmt_val\": \"5,886\"
    },
{
\"key\": \"blocker_violations\",
\"val\": 13,
\"frmt_val\": \"13\"
    },
{
\"key\": \"critical_violations\",
\"val\": 165,
\"frmt_val\": \"165\"
    },
{
\"key\": \"major_violations\",
\"val\": 3687,
\"frmt_val\": \"3,687\"
    },
{
\"key\": \"sqale_index\",
\"val\": 123465,
\"frmt_val\": \"257d\"
    }
]
}" 
| kv 
| rename msr{}.* as * 
| eval tempField=mvzip(key,val) 
| stats count by _time id tempField
| eval key = mvindex(split(tempField,","),0), val= mvindex(split(tempField,","),1)  
| table key val

Thanks

Re: How do I extract fields and values from a multivalue field?

msivill_splunk — Thu, 20 Sep 2018 16:37:39 GMT

This will split the json into the key and val values in separate rows -

| makeresults 
| eval json_field = "{
\"id\": 78124,
\"uuid\": \"AWBr0ilGbvobIxfakBsC\",
\"key\": \"com.vitalityactive.va:master\",
\"name\": \"Mobile - Android - Vitality Active\",
\"scope\": \"PRJ\",
\"qualifier\": \"TRK\",
\"date\": \"2017-12-20T19:07:51+0000\",
\"creationDate\": \"2017-12-18T22:50:23+0000\",
\"lname\": \"Mobile - Android - Vitality Active\",
\"version\": \"1.0.0-rc.3\",
\"msr\": [
{
\"key\": \"ncloc\",
\"val\": 91895,
\"frmt_val\": \"91,895\"
    },
{
\"key\": \"complexity\",
\"val\": 15906,
\"frmt_val\": \"15,906\"
    },
{
\"key\": \"file_complexity\",
\"val\": 11.2,
\"frmt_val\": \"11.2\"
    },
{
\"key\": \"class_complexity\",
\"val\": 7.9,
\"frmt_val\": \"7.9\"
    },
{
\"key\": \"function_complexity\",
\"val\": 1.2,
\"frmt_val\": \"1.2\"
    },
{
\"key\": \"coverage\",
\"val\": 11.8,
\"frmt_val\": \"11.8%\"
    },
{
\"key\": \"violations\",
\"val\": 5886,
\"frmt_val\": \"5,886\"
    },
{
\"key\": \"blocker_violations\",
\"val\": 13,
\"frmt_val\": \"13\"
    },
{
\"key\": \"critical_violations\",
\"val\": 165,
\"frmt_val\": \"165\"
    },
{
\"key\": \"major_violations\",
\"val\": 3687,
\"frmt_val\": \"3,687\"
    },
{
\"key\": \"sqale_index\",
\"val\": 123465,
\"frmt_val\": \"257d\"
    }
]
}" 
| spath input=json_field output=keys path=msr{}.key 
| spath input=json_field output=vals path=msr{}.val 
| fields keys, vals 
| eval x=mvzip(keys,vals) 
| mvexpand x 
| eval x = split(x,",") 
| eval key=mvindex(x,0) 
| eval val=mvindex(x,1) 
| table key, val

You will need to tweak the above to use the raw event rather than the generated eval field.