Splunk search show results from JSON

[panulpet]

Hello,

I have following JSON data coming in:

{
"event_timestamp" : "2020-03-03 T 12:56:54 +0200",
"file_timestamp" : "",
"username" : "xxxx",
"session_id" : "F23AA957F1A494C12F2B21B5A7533FF3",
"request_id" : "74b9cf97-934c-41cb-b81e-1152f51e28b7",
"register_id" : [ ],
"system_id" : "ASDFG",
"environment" : "LINUX",
"service_id" : "12355",
"parameters" : [ {
"field" : "xxx",
"value" : "xx-123",
"search" : false,
"securityProhibition" : false
}, {
"field" : "yyy",
"value" : "yy-564",
"search" : false,
"securityProhibition" : false
}, {
"field" : "zzz",
"value" : "1234433222",
"search" : false,
"securityProhibition" : false
}, {
"field" : "vvv",
"value" : "www.google.com",
"search" : false,
"securityProhibition" : false
}, {
"field" : "qqq",
"value" : "qwert",
"search" : false,
"securityProhibition" : false
} ],
"info" : null,
"error" : [ {
"code" : "202",
"message" : "General Error"
} ],
"schema_version" : "1.0"
};

I have Dashboard where users can make searches based on given values. For example, users can search events selecting yyy (dropdown) and giving value "yy-564" and Splunk tries to search all events where that can be found. For example here I populate the search like this: index=myindex (parameters{}.field="yyy" AND parameters{}.value="yy-564").. That works but it also finds the events where that value "yy-564" is on another parameter field like in zzz.

Any Ideas on how should I make this to work the correct way. So that It would only match inside parameters field "yyy" and it's corresponding value "yy-564"?

Thanks

[sumanssah · Accepted solution]

referring to @to4kawa SPL, I performed minor changes to achieve the result

Try this

| makeresults 
| eval json="{\"event_timestamp\":\"2020-03-03 T 12:56:54 +0200\",\"file_timestamp\":\"\",\"username\":\"xxxx\",\"session_id\":\"F23AA957F1A494C12F2B21B5A7533FF3\",\"request_id\":\"74b9cf97-934c-41cb-b81e-1152f51e28b7\",\"register_id\":[],\"system_id\":\"ASDFG\",\"environment\":\"LINUX\",\"service_id\":\"12355\",\"parameters\":[{\"field\":\"xxx\",\"value\":\"xx-123\",\"search\":false,\"securityProhibition\":false},{\"field\":\"yyy\",\"value\":\"yy-564\",\"search\":false,\"securityProhibition\":false},{\"field\":\"zzz\",\"value\":\"1234433222\",\"search\":false,\"securityProhibition\":false},{\"field\":\"vvv\",\"value\":\"www.google.com\",\"search\":false,\"securityProhibition\":false},{\"field\":\"qqq\",\"value\":\"qwert\",\"search\":false,\"securityProhibition\":false}],\"info\":null,\"error\":[{\"code\":\"202\",\"message\":\"General Error\"}],\"schema_version\":\"1.0\"}\";" 
| rex "(?<json>\{.+)" 
| spath input=json 
| fields - json 
| rename parameters{}.* as * 
| eval fieldValue=mvzip(field,value) 
| mvexpand fieldValue 
| eval fieldValue=split(fieldValue,",") 
| eval field=mvindex(fieldValue,0) 
| eval value=mvindex(fieldValue,1) 
| fields - fieldValue search securityProhibition

[panulpet]

Thanks for this also. Can you also show how to search events where field yyy has value "yy-564".. I'm a bit newbie here 🙂

[panulpet]

Hi, I noticed later on that mvexpand command shows "dublicate" events on search results on the table when searching using wildcards (*). Is is possible to prevent that or can we make query without mvexpand?

[panulpet]

I'm near to get this working as I want 🙂

index=myindex | rex "(?<json>\{.+)" 
 | spath input=json 
 | fields - json 
 | rename parameters{}.* as * 
 | eval fieldValue=mvzip(field,value) 
 | mvexpand fieldValue 
 | eval fieldValue=split(fieldValue,",") 
 | eval field=mvindex(fieldValue,0) 
 | eval value=mvindex(fieldValue,1) 
 | fields - fieldValue search securityProhibition | search field="*" value="*" | table event_timestamp request_id service_id system_id parameters{}.field parameters{}.value _raw

The only thing here is that earlier "parameters{}.field and parameters{}.value" populated table with all values. Now that part is empty. How I "print" all field names and values to table from that certain event? Did you get the point 🙂

[panulpet]

Or I added that myValue=fieldValue which solved my case 🙂 Thanks I'' accept this answer!!

    index=myindex | rex "(?<json>\{.+)" 
      | spath input=json 
      | fields - json 
      | rename parameters{}.* as * 
      | eval fieldValue=mvzip(field,value) 
      **|eval myValue=fieldValue**
      | mvexpand fieldValue 
      | eval fieldValue=split(fieldValue,",") 
      | eval field=mvindex(fieldValue,0) 
      | eval value=mvindex(fieldValue,1) 
      | fields - fieldValue search securityProhibition 

[panulpet]

Or maybe like this - populating new search after that:

| search field="yyy" value="yy-564"

? Am I right?

[to4kawa]

| makeresults 
| eval _raw="{\"event_timestamp\":\"2020-03-03 T 12:56:54 +0200\",\"file_timestamp\":\"\",\"username\":\"xxxx\",\"session_id\":\"F23AA957F1A494C12F2B21B5A7533FF3\",\"request_id\":\"74b9cf97-934c-41cb-b81e-1152f51e28b7\",\"register_id\":[],\"system_id\":\"ASDFG\",\"environment\":\"LINUX\",\"service_id\":\"12355\",\"parameters\":[{\"field\":\"xxx\",\"value\":\"xx-123\",\"search\":false,\"securityProhibition\":false},{\"field\":\"yyy\",\"value\":\"yy-564\",\"search\":false,\"securityProhibition\":false},{\"field\":\"zzz\",\"value\":\"1234433222\",\"search\":false,\"securityProhibition\":false},{\"field\":\"vvv\",\"value\":\"www.google.com\",\"search\":false,\"securityProhibition\":false},{\"field\":\"qqq\",\"value\":\"qwert\",\"search\":false,\"securityProhibition\":false}],\"info\":null,\"error\":[{\"code\":\"202\",\"message\":\"General Error\"}],\"schema_version\":\"1.0\"}\";" 
| spath path=parameters{} output=parameters 
| spath 
| stats values(*) as * by parameters 
| spath input=parameters 
| fields - parameters* 
| rename error{}.* as *

if you make table like above, search is easy way.

[panulpet]

Thanks for this. Still wondering how to search from that table 🙂

[to4kawa]

.... 
| search  field="yyy"

[panulpet]

Thanks for this!