topic Re: Only select matching JSON data in Splunk Search

Only select matching JSON data

joesecurity — Wed, 08 May 2019 20:10:59 GMT

I load JSON reports into Splunk and those reports have many arrays:

{  
   "analysis":{  
      "behavior":{  
         "processes":{  
            "process":[  
               {  
                  "fileactivities":{  
                     "fileCreated":{  
                        "call":[  
                           {  
                              "path":"C:\\Windows\\a"
                           },
                           {  
                              "path":"C:\\b"
                           }
                        ]
                     }
                  }
               }
            ]
         }
      }
   }
}

When I search:

source=test| search "behavior.system.processes.process{}.fileactivities.fileCreated.call{}.path"="C:\\Windows*"

I often like to show the matching data. I use a table to do so:

source=test| search "behavior.system.processes.process{}.fileactivities.fileCreated.call{}.path"="C:\\Windows*" | table "behavior.system.processes.process{}.fileactivities.fileCreated.call{}.path"

However, the issue is that this shows me all fileCreated of the matching event and not only the one starting with C:\Windows.

How do I filter that?

Re: Only select matching JSON data

kamlesh_vaghela — Thu, 09 May 2019 04:42:24 GMT

@joesecurity

Can you please share sample event?

Re: Only select matching JSON data

joesecurity — Thu, 09 May 2019 07:16:36 GMT

Added event data.

Re: Only select matching JSON data

tom_frotscher — Thu, 09 May 2019 07:43:32 GMT

Looks like your field is a multivalue field because the way through your JSON Object is the same for all fields called "path".

You can select a value from a multivalue field with the help of eval and mvindex:

source=test| search "behavior.system.processes.process{}.fileactivities.fileCreated.call{}.path"="C:\\Windows*" | eval path=mvindex('behavior.system.processes.process{}.fileactivities.fileCreated.call{}.path',0) | table path

Does this work for you?

Re: Only select matching JSON data

joesecurity — Thu, 09 May 2019 07:52:18 GMT

This does not really help as I want to search all paths in all events but obviously only show the paths which matched.

Re: Only select matching JSON data

tom_frotscher — Thu, 09 May 2019 08:16:25 GMT

Then you might use mvfilter to filter down your multivalue fields to what you need in the end? Like using a regex with mvfilter that filters out only paths that start with C:\\Windows*.

Re: Only select matching JSON data

tom_frotscher — Thu, 09 May 2019 08:21:54 GMT

I will give you an example. You can copy this and run it in your splunk:

| makeresults | eval field="C:\\Windows\\a,F:\\Foo" | makemv delim="," field | eval path=mvfilter(match(field,"C:\\\\Windows.*"))

Everything up to | makeresults | eval field="C:\\Windows\\a,F:\\Foo" | makemv delim="," field should look like your result and the | eval path=mvfilter(match(field,"C:\\\\Windows.*")) filters down the result to the C:\Windows* match.

Re: Only select matching JSON data

kamlesh_vaghela — Thu, 09 May 2019 08:27:48 GMT

@joesecurity

Can you please try below search?

source=test | rename "analysis.behavior.processes.process{}.fileactivities.fileCreated.call{}.path" as fileCreated_path 
| mvexpand fileCreated_path 
| search fileCreated_path="C:\\Windows\\*"

My Sample Search:

| makeresults 
| eval _raw="{\"analysis\":{\"behavior\":{\"processes\":{\"process\":[{\"fileactivities\":{\"fileCreated\":{\"call\":[{\"path\":\"C:\\\\Windows\\\\a\"},{\"path\":\"C:\\\\b\"}]}}}]}}}}" 
| kv 
| rename "analysis.behavior.processes.process{}.fileactivities.fileCreated.call{}.path" as fileCreated_path 
| mvexpand fileCreated_path 
| search fileCreated_path="C:\\Windows\\*"

Re: Only select matching JSON data

joesecurity — Thu, 09 May 2019 09:04:12 GMT

I tried this on my data but I don't get any results.

Re: Only select matching JSON data

joesecurity — Thu, 09 May 2019 09:16:58 GMT

Is there a way to debug the call to see why it does not work?

Re: Only select matching JSON data

kamlesh_vaghela — Thu, 09 May 2019 10:08:26 GMT

@joesecurity

Did you get any results from the below search? Can you please confirm?

 source=test | table "analysis.behavior.processes.process{}.fileactivities.fileCreated.call{}.path"

Re: Only select matching JSON data

joesecurity — Thu, 09 May 2019 10:29:47 GMT

No results found in the visualization tab.

Re: Only select matching JSON data

kamlesh_vaghela — Thu, 09 May 2019 10:31:26 GMT

in Statistics tab?

Re: Only select matching JSON data

joesecurity — Thu, 09 May 2019 10:42:25 GMT

I found it. There was a difference between the JSON format listed in the example and the actual data.

Re: Only select matching JSON data

joesecurity — Thu, 09 May 2019 10:52:15 GMT

One last question, let us assume "call" has more elements, also "status". How can I list the "path" and "status" for all calls which have path="C:\Windows*?

Re: Only select matching JSON data

kamlesh_vaghela — Thu, 09 May 2019 11:27:19 GMT

For that, I have a magic for you.

| makeresults 
| eval _raw=" {  
    \"analysis\":{  
       \"behavior\":{  
          \"processes\":{  
             \"process\":[  
                {  
                   \"fileactivities\":{  
                      \"fileCreated\":{  
                         \"call\":[  
                            {  
                               \"path\":\"C:\\\\Windows\\\\a\",
                               \"status\":\"status1\"
                                    },
                            {  
                               \"path\":\"C:\\\\b\",
                               \"status\":\"status2\",
                            }
                         ]
                      }
                   }
                }
             ]
          }
       }
    }
 }" 
| kv 
| rename "analysis.behavior.processes.process{}.fileactivities.fileCreated.call{}.path" as fileCreated_path, "analysis.behavior.processes.process{}.fileactivities.fileCreated.call{}.status" as fileCreated_status 
| eval temp=mvzip(fileCreated_path,fileCreated_status) 
| mvexpand temp 
| eval fileCreated_path=mvindex(split(temp,","),0),fileCreated_status=mvindex(split(temp,","),1) 
| search fileCreated_path="C:\\Windows\\*"
| table _time fileCreated_path fileCreated_status

Happy Splunking