Splunk Search

How to parse JSON with multiple array ?

New Member

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.

0 Karma

Champion

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.

0 Karma

Champion

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.

0 Karma

Communicator

Hi

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    
         ]    
       ]    
 }
0 Karma

New Member

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

0 Karma