Getting Data In

Leveraging spath with arbitrary JSON fields

David
Splunk Employee
Splunk Employee

I have a datasource that looks like this:

{ 
  "results": {
    "serverone": {
       "time": 2,
       "results": 330
     },
     "servertwo": {
         "time": 35,
         "results": 0
     }
   }
}

With up to 70+ servers. I get the JSON formatting, but I would like to be able to do statistics like avg(time) by server. I've gotten this to work, but it's pretty ugly:

source="*example.search*" | rename "results.*.time" as server* | foreach server* [eval times = mvappend(times, "<<FIELD>>:" . '<<FIELD>>')] | mvexpand times | rex field=times "^(?<server>.*):(?<time>.*)" | stats avg(time) by server

Is there a better way that I should be doing this?

Tags (2)
0 Karma

martin_mueller
SplunkTrust
SplunkTrust

In my mind the core issue is the JSON standing on its head. Ideally the results property would be an array with server objects that have a name, a time, and a results property.

However, I'm guessing you can't change the JSON structure... here's a different way of coaxing the goods from the JSON as it is, without the need to foreach anything:

| stats count
| eval _raw = "{\"results\": {\"serverone\": {\"time\": 2, \"results\": 330},\"servertwo\": {\"time\": 35,\"results\": 0}}}"
| spath results
| makemv results tokenizer="(?:\{|,)(.*?\d\})"
| mvexpand results
| rex field=results "(?<server>[^\"]+).*?time\D+(?<time>\d+)"

The magic (ninjutsu? ;)) lies in tokenizing the content of the results object in such a way that you get a list of server-ish objects out of it. Those aren't proper JSON any more though, so the final step can't be done with spath.

Depending on the actual JSON input you have there may be some sed shenanigans that could shuffle the event around to make it look nicer. Something like this, only in transforms.conf:

| stats count
| eval _raw = "{\"results\": {\"serverone\": {\"time\": 2, \"results\": 330},\"servertwo\": {\"time\": 35,\"results\": 0}}}" 
| rex mode=sed "s/(results[^{]+)(.*)\}\}/\1[\2]}/" | rex mode=sed "s/((?:\[|\},))\{?([^:]+):[^\"]+/\1{\"name\": \2, /g"
| spath results{} output=element | mvexpand element | spath input=element

This builds a JSON structure looking like this:

{"results": [
  {"name": "serverone", "time": 2, "results": 330},
  {"name": "servertwo", "time": 35,"results": 0}
]} 

That's not perfect of course, sed'ing around on structured data is kinda looking for trouble. However, if your JSON events are stable enough and my late-night regex-foo is on par then doing this in transforms.conf would seriously de-clutter the searches. Again, ideally the JSON events would look like this from the get-go.

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 ...