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
Super Champion

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
Super Champion

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

0 Karma

javiergn
Super Champion

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
Got questions? Get answers!

Join the Splunk Community Slack to learn, troubleshoot, and make connections with fellow Splunk practitioners in real time!

Meet up IRL or virtually!

Join Splunk User Groups to connect and learn in-person by region or remotely by topic or industry.

Get Updates on the Splunk Community!

Accelerate Splunk App Development with AI

  Learn how to automate the repetitive parts of your build cycle.   Watch On-Demand   Replay the Accelerate ...

Splunk Agent Observability: Measuring AI Value with Splunk Tokenomics on AWS

The AI landscape is moving faster than ever, and our latest 2026 State of Observability report provides the ...

Enterprise Security Watch Party: What’s New in 8.8 - Recap

Splunk Community HQ hosted a Splunk Enterprise Security Watch Party: What’s New In ES 8.8 last Thursday. I had ...