topic Re: Nested Json dependent on a condition in Getting Data In

Nested Json dependent on a condition

bseifert14 — Mon, 26 Mar 2018 16:28:56 GMT

This is my first Splunk question bear with me on my explanation..

I have 70 events that all have multiple nested jsons in each event. The framework of two events looks something like this:

event1:{
"tests": [
          {
          "expectation": "true"
          "reality":" true"
          "test_statistics": {
                    "components": "foo, bar"
                                        }
           }
          {
          "expectation": "true"
          "reality": "false"
          "test_statistics": {
                    "components": "foo, bar, baz"
                                        }
            }

event2:{
"tests": [
          {
          "expectation": "true"
          "reality": "true"
          "test_statistics": {
                    "components": "foo, bar, baz"
                                        }
           }
          {
          "expectation": "true"
          "reality": "true"
          "test_statistics": {
                    "components": "foo, bar"
                                        }
             }

Ultimately, I want a query that will count up each component based on the fact that expectation=reality. Therefore, my condition should be that true==true (or in other words.. expectation==reality).

For my end goal I'd like to get the true tests grouped by components and the total tests grouped by components. So that, I could then generate a table that matches this format (bold is the column headers)-->

component | if(True==True) | Total Count
foo | 3 | 4
bar | 3 | 4
baz | 1 | 4

Re: Nested Json dependent on a condition

javiergn — Tue, 27 Mar 2018 08:20:21 GMT

Hi @bseifert14, is that how your JSON looks like?
It's not really standard JSON and you won't be able to use the spath command with that.
You need double quotes to delimit your field names and their values when non-numeric, commas to separate the key value pairs, etc.

If you do have that kind of JSON, would you mind posting it here using the code sample button above (the one with 1's and 0's) so that no special characters are escaped?

Then I can try to help as otherwise trying to convert your events above into standard JSON is going to take longer probably than solving your problem.

Thanks,
J

Re: Nested Json dependent on a condition

bseifert14 — Tue, 27 Mar 2018 13:07:30 GMT

Thanks for the response Javiergn,
As you directed I attempted to format as close as possible to the correct format. Also, it should be noted that my query looks something like this to begin with:

index=events
            | eventstats max(_time) as maxtime
            | where _time = maxtime
            | spath path=tests{} output=tests
            | mvexpand tests
            | spath input=tests
            | makemv delim=", " test_statistics.components

Re: Nested Json dependent on a condition

javiergn — Wed, 28 Mar 2018 11:01:49 GMT

OK I think I get it now.

See if the following sample I tested on my lab works for you:

| makeresults
| eval events = "
{\"tests\": [
  {
      \"expectation\": \"true\",
      \"reality\":\"true\",
      \"test_statistics\": {
        \"components\": \"foo, bar\"
      }
  },
  {
      \"expectation\": \"true\",
      \"reality\": \"false\",
      \"test_statistics\": {
        \"components\": \"foo, bar, baz\"
      }
  }
]}||
{\"tests\": [
  {
      \"expectation\": \"true\",
      \"reality\":\"true\",
      \"test_statistics\": {
        \"components\": \"foo, bar, baz\"
      }
  },
  {
      \"expectation\": \"true\",
      \"reality\": \"true\",
      \"test_statistics\": {
        \"components\": \"foo, bar\"
      }
  }
]}"
| eval events = split(events, "||")
| mvexpand events
| spath input=events path=tests{} output=tests
| mvexpand tests
| spath input=tests
| rename "test_statistics.components" as component
| makemv delim="," component
| mvexpand component
| stats count(eval(match(expectation, reality))) as RealityIsExpectationCount, count as TotalCount by component

This would be the output which I understand it is exactly what you were expecting:

Re: Nested Json dependent on a condition

javiergn — Tue, 29 May 2018 10:06:45 GMT

@bseifert14 please do not forget to accept the answer if you are happy with it