Splunk Search

How to search and extract values from a JSON file?

jmc82
Explorer

I have a JSON file with the following format:

"Checks": {
        "Price": {
            "Category": "Critical",
            "Classification": "Mandatory",
            "Value": {
                "Unit": "Percent",
                "Value": 15.0
            }
        },
        "Time": {
            "Category": "NotSpecified",
            "Classification": "General",
            "Value": {
                "Min": {
                    "Unit": "Seconds",
                    "Value": -10
                },
                "Max": {
                    "Unit": "Seconds",
                    "Value": 10
                }
            }
        },

So essentially I have an object called 'Checks', which contains a bunch of different rules (Price, Time etc in the above example). Each rule has a Category, a Classification and a Value, which is ultimately what I'm interested in displaying in a panel.

I would like some advice on the best way of searching this JSON data so that I can do the following:

  1. Browse all the rules by their Category and/or their classification
  2. Extract the details from the 'Value' object

For example, if I wanted to show only the 'Critical' checks from above I would like to display:

Category Classification RuleName Value    Unit
Critical Mandatory      Price    15.0    Percent

I have read the SPATH documentation but I don't really see how I can extract the Category, Classification and Value without explicitly stating the rule name. For example, I suspect I need something like this:

spath Checks.*.Category

1 Solution

martin_mueller
SplunkTrust
SplunkTrust

The annoying thing appears to be that you have a list of checks, but aren't storing the checks as a list but rather as a bunch of named properties. That makes working with the list much much harder.

That being said, you would do something like this:

| stats count | eval _raw = "{\"Checks\": {
  \"Price\": {\"Category\": \"Critical\", \"Classification\": \"Mandatory\", \"Value\": {\"Unit\": \"Percent\", \"Value\": 15.0}},
  \"Time\": {\"Category\": \"NotSpecified\", \"Classification\": \"General\", \"Value\": {\"Min\": {\"Unit\": \"Seconds\", \"Value\": -10},
  \"Max\": {\"Unit\": \"Seconds\", \"Value\": 10}}}}}"
| spath

... and now you're in trouble. Spath bravely extracted all your fields, so now you have three fields for your categories... in order to filter you have to filter by each field. Bugger.

Now, if your JSON looked like this:

| stats count | eval _raw = "{
    \"Checks\": [
        {
            \"Name\": \"Price\",
            \"Category\": \"Critical\",
            \"Classification\": \"Mandatory\",
            \"Value\": {
                \"Unit\": \"Percent\",
                \"Value\": 15
            }
        },
        {
            \"Name\": \"Time\",
            \"Category\": \"NotSpecified\",
            \"Classification\": \"General\",
            \"Value\": {
                \"Min\": {
                    \"Unit\": \"Seconds\",
                    \"Value\": -10
                },
                \"Max\": {
                    \"Unit\": \"Seconds\",
                    \"Value\": 10
                }
            }
        }
    ]
}"
| spath Checks{} | mvexpand Checks{} | spath input=Checks{} | search Category="critical"

Then you can easily work with the data. Extract the list using spath Checks{}, split the list into single events using mvexpand, and extract each list item using the second spath. Then filter as you normally would. As a performance gain, filter for the word "critical" in the initial search to throw out events that can't match the last search.

View solution in original post

steveoo
Engager

If the data is not sensitive, an alternative way to do this is to use an online tool json-csv.com to convert the JSON to CSV then open the CSV file up in a spreadsheet and filter by whatever column values you wish.

So in the example you gave you could easily set a standard filter for column "Category" and value "critical" in Excel.

martin_mueller
SplunkTrust
SplunkTrust

I doubt that'd be a viable Splunk integration approach.

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

The annoying thing appears to be that you have a list of checks, but aren't storing the checks as a list but rather as a bunch of named properties. That makes working with the list much much harder.

That being said, you would do something like this:

| stats count | eval _raw = "{\"Checks\": {
  \"Price\": {\"Category\": \"Critical\", \"Classification\": \"Mandatory\", \"Value\": {\"Unit\": \"Percent\", \"Value\": 15.0}},
  \"Time\": {\"Category\": \"NotSpecified\", \"Classification\": \"General\", \"Value\": {\"Min\": {\"Unit\": \"Seconds\", \"Value\": -10},
  \"Max\": {\"Unit\": \"Seconds\", \"Value\": 10}}}}}"
| spath

... and now you're in trouble. Spath bravely extracted all your fields, so now you have three fields for your categories... in order to filter you have to filter by each field. Bugger.

Now, if your JSON looked like this:

| stats count | eval _raw = "{
    \"Checks\": [
        {
            \"Name\": \"Price\",
            \"Category\": \"Critical\",
            \"Classification\": \"Mandatory\",
            \"Value\": {
                \"Unit\": \"Percent\",
                \"Value\": 15
            }
        },
        {
            \"Name\": \"Time\",
            \"Category\": \"NotSpecified\",
            \"Classification\": \"General\",
            \"Value\": {
                \"Min\": {
                    \"Unit\": \"Seconds\",
                    \"Value\": -10
                },
                \"Max\": {
                    \"Unit\": \"Seconds\",
                    \"Value\": 10
                }
            }
        }
    ]
}"
| spath Checks{} | mvexpand Checks{} | spath input=Checks{} | search Category="critical"

Then you can easily work with the data. Extract the list using spath Checks{}, split the list into single events using mvexpand, and extract each list item using the second spath. Then filter as you normally would. As a performance gain, filter for the word "critical" in the initial search to throw out events that can't match the last search.

jmc82
Explorer

Thanks. I've changed the format of the JSON file as per your advice and its much more straightward to search the data now

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