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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...