Getting Data In

Splunk search show results from JSON

panulpet
Loves-to-Learn

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

0 Karma
1 Solution

sumanssah
Communicator

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

View solution in original post

0 Karma

sumanssah
Communicator

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
0 Karma

panulpet
Loves-to-Learn

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 🙂

0 Karma

panulpet
Loves-to-Learn

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?

0 Karma

panulpet
Loves-to-Learn

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 🙂

0 Karma

panulpet
Loves-to-Learn
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 
0 Karma

panulpet
Loves-to-Learn

Or maybe like this - populating new search after that:

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

? Am I right?

0 Karma

to4kawa
Ultra Champion
| 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.

0 Karma

panulpet
Loves-to-Learn

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

0 Karma

to4kawa
Ultra Champion
.... 
| search  field="yyy"
0 Karma

panulpet
Loves-to-Learn

Thanks for this!

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...