Leveraging spath with arbitrary JSON fields

[David]

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?

[martin_mueller]

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.