topic Re: How to parse JSON with multiple array ? in Splunk Search

How to parse JSON with multiple array ?

jegron — Wed, 31 Jul 2019 12:47:31 GMT

Hi,

Here is a sample :

{   
     columnNames:   [
          usersession.city  
          Browser   
          name  
          count(duration)   
          median(duration)  
    ]   
     extrapolationLevel:     1  
     values:    [
        [   
         City1
         Browser1
         URL1   
         1  
         4795   
        ]   
        [
         City2
         Browser2
         URL2   
         1  
         9761   
        ]   
    ]   
}

How to parse it into a table or chart ? I tried some request with spath command without success.

Thanks in advance for your help.

Re: How to parse JSON with multiple array ?

jaime_ramirez — Wed, 31 Jul 2019 16:33:13 GMT

I tried to parse the sample without success. Are you sure the sample complies the rules for JSON formatting like the following?

{    
        "columnNames":  [
           "usersession.city",    
           "Browser",    
           "name",    
           "count(duration)",    
           "median(duration)"  
       ],    
        "extrapolationLevel":     1,   
        "values":    [
         [    
            "City1",
            "Browser1",
            "URL1",    
            1,    
            4795    
         ],    
         [
            "City2",
            "Browser2",
            "URL2",    
            1,    
            9761    
         ]    
       ]    
 }

Re: How to parse JSON with multiple array ?

Sukisen1981 — Wed, 31 Jul 2019 17:04:16 GMT

try this :

| makeresults | eval payload="{    
        columnNames:    [
           usersession.city    
           Browser    
           name    
           count(duration)    
           median(duration)    
       ]    
        extrapolationLevel:     1    
        values:    [
         [    
            City1
            Browser1
            URL1    
            1    
            4795    
         ]    
         [
            City2
            Browser2
            URL2    
            1    
            9761    
         ]    
       ]    
 }
" 
|  rex field=payload "(?ms)values\:+\s+\[+\s+(?<value>.*?)\]+\s+}" max_match=0 
| eval k=split(value,"]")| fields k 
|  fields - _time
| mvexpand k
| makemv delim=" " k
| eval usersession.city =mvindex(k,2)
| eval Browser=mvindex(k,3)
| eval name=mvindex(k,4)
| eval count(duration)=mvindex(k,6)
| eval median(duration)=mvindex(k,8)

NOTE : You might need to tinker with the values of the last 5 mvindex. Since i kinda copied from your text, count duration should ideally be mvindex(5) but I received a blank and hence switched to the next higher number. Try this code as is first and see the output.

Re: How to parse JSON with multiple array ?

jegron — Thu, 01 Aug 2019 07:44:32 GMT

Yes, I copy pasted the Splunk visualization but the raw have the quotation mark like in your post.

Re: How to parse JSON with multiple array ?

Sukisen1981 — Mon, 19 Aug 2019 15:12:22 GMT

hi @jegron

Please let us know if your issue has been resolved and accept the answer if it significantly helped your resolution. Do not forget to add additional resolution details for the benefit of other form members.