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.
@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
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
Thanks J 🙂 Need to study more on props.conf usage.
@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
This works for me (as well the rex thingy mentioned by @jawaharas ). Thank you @kamlesh_vaghela!
'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