How to search and extract values from a JSON file?

[jmc82]

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

[steveoo]

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]

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

[martin_mueller · Accepted solution]

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]

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