Getting Data In

json array searching

Maurice
Explorer

Hi, 

I am trying to return results if an item in the array has both values set to specific values.

ie bu = "blob" and disp="enforce" on the one array item

However,  my search seems to happen across items.

 

|makeresults
|eval _raw ="{
\"sp_v\":[
{\"bu\":\"blob\",\"disp\":\"enforce\"},
{\"bu\":\"inline\",\"disp\":\"report\"}
]
}"
| spath
| search sp_v{}.bu=blob AND sp_v{}.disp=report

This is returning result as the first item has 'blob' and the second has 'report'.

I would not expect any results in this search

Would appreciate any help,

Kind Regards,

Maurice

Labels (1)
0 Karma
1 Solution

kamlesh_vaghela
SplunkTrust
SplunkTrust

@Maurice 

If you have multiple fields then I suggest this solution.

|makeresults
|eval _raw ="{
\"sp_v\":[
{\"bu\":\"blob\",\"disp\":\"enforce\", \"an\":\"test\"},
{\"bu\":\"inline\",\"disp\":\"report\", \"an\":\"another\"}
]
}"
| spath path=sp_v{} output=data | stats count by data | rename data as _raw | extract 
| where bu="blob" AND disp="enforce" AND an="test"

 

Thanks
KV
▄︻̷̿┻̿═━一

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.

View solution in original post

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@Maurice 

Can you please try this?

YOUR_SEARCH
| spath
| rename sp_v{}.* as *
| eval t = mvzip(bu,disp) | mvexpand t| eval bu=mvindex(split(t,","),0),disp=mvindex(split(t,","),1)
| where bu="blob" AND disp="report"

 

My Sample Search :

|makeresults
|eval _raw ="{
\"sp_v\":[
{\"bu\":\"blob\",\"disp\":\"enforce\"},
{\"bu\":\"inline\",\"disp\":\"report\"}
]
}"
| spath
| rename sp_v{}.* as *
| eval t = mvzip(bu,disp) | mvexpand t| eval bu=mvindex(split(t,","),0),disp=mvindex(split(t,","),1)
| where bu="blob" AND disp="report"

 

Thanks
KV
▄︻̷̿┻̿═━一

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.

0 Karma

Maurice
Explorer

Thanks KV,

 That works great.

 My only issue is that in my dashboard I am building up this query using inputs (for most properties on the array).

So there could be up to 7 or 8 properties to search on.

I notice from the docs that mvzip only works with 2 properties by default. so I tried with 3 which worked(see below):

|makeresults
|eval _raw ="{
\"sp_v\":[
{\"bu\":\"blob\",\"disp\":\"enforce\", \"an\":\"test\"},
{\"bu\":\"inline\",\"disp\":\"report\", \"an\":\"another\"}
]
}"
| spath
| rename sp_v{}.* as *
| eval t = mvzip(mvzip(bu,disp), an)
| mvexpand t
| eval bu=mvindex(split(t,","),0),disp=mvindex(split(t,","),1), an=mvindex(split(t,","),2)
| where bu="blob" AND disp="enforce" AND an="test"

 

I'd imagine the code would become hard to read as I have to nest mvzip inside itself and also change the index

Do you know of a more readable way it accomplish this with  more properties?

Kind regards,

Maurice

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@Maurice 

If you have multiple fields then I suggest this solution.

|makeresults
|eval _raw ="{
\"sp_v\":[
{\"bu\":\"blob\",\"disp\":\"enforce\", \"an\":\"test\"},
{\"bu\":\"inline\",\"disp\":\"report\", \"an\":\"another\"}
]
}"
| spath path=sp_v{} output=data | stats count by data | rename data as _raw | extract 
| where bu="blob" AND disp="enforce" AND an="test"

 

Thanks
KV
▄︻̷̿┻̿═━一

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.

0 Karma

Maurice
Explorer

Thanks KV,

That looks like much more maintainable code. 😀

 One last think, I wanted to create a timechart also off the data but it fails once i use a real index instead of make results.

I am presuming it has something to do with _time not being in the result set:

index=myIndex source=mySource  spath path=sp_v{} output=data | stats count by data | rename data as _raw | extract | timechart span=1d count(bu)  useother=f usenull=f

 

Any ideas?

Kind Regards,

Maurice

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@Maurice 

For timechart try this.

index=myIndex source=mySource 
| spath path=sp_v{} output=data 
| stats count by _time data 
| rename data as _raw | extract 
| timechart span=1d count(bu)  useother=f usenull=f

 

🙂 

Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...