How to extract values from three levels deep list

[helmekkaoui]

Hello splunkers,

currently the appevent that I'm working on contain lists within lists :
trx: [ [-]
{ [-]
htlIDs: [ [-]
{ [-]
errCode: 0

htlid: XX123456
}

]

src: x
type: y
}

{ [-]
htlids: [ [-]
{ [-]
errCode: 1257

htlid: YY123456
}

]

source: x
type: y
}

}

]
I'm trying to extract all the first two letters of the values of htlid individually with their corresponding errors so i m having my query like this :

| eval chain=substr(trx{}.htlids{}.htlid, 1, 2) 
| table trx{}.htlids{}.htlid,  chain,  trx{}.htlids{}.errCode

The problem with this is that it works fine when we have only one occurrence of htlids when there are more the eval doesn't work the values of trx{}.htlids{}.htlid get displayed on the same raw and chain is empty.

Can somebody please help me to understand how to go through the values individually?

Many thanks.

[kamlesh_vaghela · Accepted solution]

@helmekkaoui

Can you please try this?

YOUR_SEARCH | rename trx{}.htlids{}.* as * 
| eval temp=mvzip(errCode,htlid) 
| mvexpand temp 
| eval errCode=mvindex(split(temp,","),0), htlid=mvindex(split(temp,","),1) | eval chain=substr(htlid, 1, 2) 
| table htlid errCode chain

My Sample Search:

| makeresults 
| eval _raw="{\"trx\":[{\"type\":\"y\",\"src\":\"x\",\"htlids\":[{\"htlid\":\"XX123456\",\"errCode\":\"1257\"}]},{\"type\":\"y\",\"src\":\"x\",\"htlids\":[{\"htlid\":\"YY123456\",\"errCode\":\"1257\"}]}],\"ClientId\":245860224012578433,\"SeqNb\":3102,\"Type\":\"RsMonitor\",\"Epoch\":1568798767432}" 
| extract 
| rename trx{}.htlids{}.* as * 
| eval temp=mvzip(errCode,htlid) 
| mvexpand temp 
| eval errCode=mvindex(split(temp,","),0), htlid=mvindex(split(temp,","),1) | eval chain=substr(htlid, 1, 2) 
| table htlid errCode chain

Thanks

[kamlesh_vaghela]

@helmekkaoui

Can you please share valid JSON event and your expected output? Use Code Block for the same.

[helmekkaoui]

> { [-] 
     AS:     994_HOS    
     Application:    Monitoring
     ClientId:   245860224012578430 
     Epoch:  1568798767432  
     SeqNb:  3102   
     Source:    
     Type:   RsMonitor  
     trx:   [   [-] 
        {   [-] 
         htlids:    [   [-] 
            {   [-] 
             errCode:    1257   
             htlid:  XX123456
            }   
        ]   
         src:    x
         type:   y
        }   
        {   [-] 
         htlids:    [   [-] 
            {   [-] 
             errCode:    1257   
             htlid:  YY123456
            }   
        ]   
         src:    x
         type:   y
        }   
  ] 
}   

the expected output is to have a table with with 3 columns : htlid, chain and errCode
for this example to have :

                 htlid                  chain              errCode

line1        XX123456         XX                   1257 
line2        YY123456         YY                   1257 

for now what I have is : (with the search I put)

                 htlid                  chain              errCode

line1        XX123456                                  1257 
**line1**YY123456                                  1257 

so the chain is not retrieved

[kamlesh_vaghela]

Thanks @helmekkaoui

Is it possible to share raw( _raw) event?

eg

{"quiz": {"sport": {"q1": {"question": "Which one is correct team name in NBA?","options": ["New York Bulls","Los Angeles Kings","Golden State Warriros","Huston Rocket"],"answer": "Huston Rocket"}},"maths": {"q1": {"question": "5 + 7 = ?","options": ["10","11","12","13"],"answer": "12"},"q2": {"question": "12 - 8 = ?","options": ["1","2","3","4"],"answer": "4"}}}}

[helmekkaoui]

Hello sorry for the misunderstanding, here is it :

{"trx":[{"type":"y","src":"x","htlids":[{"htlid":"XX123456","errCode":"1257"}]},{"type":"y","src":"x","htlids":[{"htlid":"YY123456","errCode":"1257"}]}],"ClientId":245860224012578433,"SeqNb":3102,"Type":"RsMonitor","Epoch":1568798767432}

[kamlesh_vaghela]

Thanks, @helmekkaoui for a sample event. Please check my answer.