Splunk Search

How to extract multivalue fields from json based on specific multivalue json field to a table?

krishnar
Explorer

So I have json in this format:

{
   "data":{
      "details":[
         {
            "id":"1111",
            "admin":{
               "isLocked":false
            }
         },
         {
            "id":"2222",
            "admin":{
               "due":"3796.10",
               "date":"24 Sep 18"
            }
         },
         {
            "id":"3333",
            "admin":{
               "isLocked":false
            }
         },
         {
            "id":"4444",
            "admin":{
               "isLocked":false
            }
         },
         {
            "id":"5555",
            "admin":{
               "due":"1000"
            }
         }
      ]
   }
}

I'm looking to get all ids with isLocked values.

1. id              isLocked 
2. 1111          false 
3. 3333          false
4. 4444          false

I've tried and tried in vain:

| spath input=jsonData output=locked path=data.details{}.admin{}.isLocked 
| spath input=jsonData output=smthg path=data.details{}.id  
| rename locked as loc 
| rename smthg as newId 
| eval idLock=mvzip(newId, loc, "--")
| mxexpand idLock
| eval id = mvindex(idLock, 0)
| eval isLocked = mvindex(idLock, 1)
| table id isLocked 

This gives me output:

     id                          isLocked 
 1. 1111--false 

Only one row is returned (sometimes its 2222 or 3333) and no value for isLocked column.
I know I have to add more evals somewhere, not able to figure out where (and possibly foreach)
I think loc and newId are returned as arrays as when I do mvcount on them, I get 3 and 5 respectively.
How do I correlate them though?

Any suggestions are highly appreciated.

1 Solution

kamlesh_vaghela
SplunkTrust
SplunkTrust

@krishnar

Can you please try below search?

YOUR_SEARCH | spath path=data.details{} output=details | mvexpand details | eval _raw=details | kv | search admin.isLocked=* | rename admin.isLocked as isLocked | table id isLocked

My Sample Search:

| makeresults 
| eval _raw="{\"data\":{\"details\":[{\"id\":\"1111\",\"admin\":{\"isLocked\":false}},{\"id\":\"2222\",\"admin\":{\"due\":\"3796.10\",\"date\":\"24 Sep 18\"}},{\"id\":\"3333\",\"admin\":{\"isLocked\":false}},{\"id\":\"4444\",\"admin\":{\"isLocked\":false}},{\"id\":\"5555\",\"admin\":{\"due\":\"1000\"}}]}}
    " 
| spath path=data.details{} output=details | mvexpand details | eval _raw=details | kv | search admin.isLocked=* | rename admin.isLocked as isLocked | table id isLocked

Thanks

View solution in original post

jawaharas
Motivator

Probably we can do with a custom sourcetype.

--props.conf

[_json_mod1]
BREAK_ONLY_BEFORE = "id
KV_MODE = none
NO_BINARY_CHECK = true
category = Structured
disabled = false
pulldown_type = true

--Query

 sourcetype="_json_mod1" 
| rex field=_raw "id\":\"(?<id>.+)\""
| rex field=_raw "isLocked\":(?<isLocked>.+)"
| fillnull value="n/a" isLocked
| table id, isLocked

--Output

id  isLocked
1111    false
2222    n/a
3333    false
4444    false
5555    n/a

krishnar
Explorer

Thanks J 🙂 Need to study more on props.conf usage.

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@krishnar

Can you please try below search?

YOUR_SEARCH | spath path=data.details{} output=details | mvexpand details | eval _raw=details | kv | search admin.isLocked=* | rename admin.isLocked as isLocked | table id isLocked

My Sample Search:

| makeresults 
| eval _raw="{\"data\":{\"details\":[{\"id\":\"1111\",\"admin\":{\"isLocked\":false}},{\"id\":\"2222\",\"admin\":{\"due\":\"3796.10\",\"date\":\"24 Sep 18\"}},{\"id\":\"3333\",\"admin\":{\"isLocked\":false}},{\"id\":\"4444\",\"admin\":{\"isLocked\":false}},{\"id\":\"5555\",\"admin\":{\"due\":\"1000\"}}]}}
    " 
| spath path=data.details{} output=details | mvexpand details | eval _raw=details | kv | search admin.isLocked=* | rename admin.isLocked as isLocked | table id isLocked

Thanks

krishnar
Explorer

This works for me (as well the rex thingy mentioned by @jawaharas ). Thank you @kamlesh_vaghela!

0 Karma

jawaharas
Motivator

'kv' command is a good choice. Below search works for me.

|BASE QUERY
| spath path=data.details{} output=details 
| mvexpand details 
| eval _raw=details 
| kv 
| search "isLocked":false 
| rex field=_raw "id\":\"(?<id>.+)\"" 
| rex field=_raw "isLocked\":(?<isLocked>.+)" 
| fillnull value="n/a" isLocked 
| table id, isLocked
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...