Getting Data In

Nested Json dependent on a condition

bseifert14
Engager

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

Tags (2)
0 Karma

javiergn
SplunkTrust
SplunkTrust

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:

alt text

0 Karma

javiergn
SplunkTrust
SplunkTrust

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

0 Karma

javiergn
SplunkTrust
SplunkTrust

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

0 Karma

bseifert14
Engager

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
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...