My splunk instance queries a database once an hour for data about products, and gets a JSON string back that is structured like this:

{"counts":
    {"green":413,
     "red":257,
     "total":670,
     "product_list":
        { "urn:product:1":{ 
            "name":"M & Ms" ,
            "total":332 ,
            "green":293 ,
            "red":39 } ,
          "urn:product:2":{ 
            "name":"Christmas Ornaments" ,
            "total":2 ,
            "green":0 ,
            "red":2 } ,
         "urn:product:3":{ 
            "name":"Traffic Lights" ,
            "total":1 ,
            "green":0 ,
            "red":1 } ,
         "urn:product:4":{ 
            "name":"Stop Signs" ,
            "total":2 ,
            "green":0 ,
            "red":2 },
            ...
        }
    }
}

I have a query that alerts when the counts.green drops by 10% over 24 hours:

index=database_catalog source=RedGreenData | head 1 
| spath path=counts.green output=green_now
| table green_now
| join host
    [| search index=database_catalog source=RedGreenData latest=-1d | head 1 | spath path=counts.green output=green_yesterday
    | table green_yesterday]
| where green_yesterday > 0
| eval delta=(green_yesterday - green_now)/green_yesterday * 100 
| where delta > 10 

While I'm an experienced developer in C, C , Java, SQL, JavaScript, and several others, I'm fairly new to Splunk's Search Processing Language, and references and tutorials seem pretty light, at least the ones I've found.

My next story is to at least expose all the individual products, and identify which ones have a 10% drop over 24 hours.

I thought a reasonable learning exercise would be to extract the names of all the products, and eventually turn that into a table with name, product code (e.g. urn:product:4), green count today, green count 24 hours ago, and then filter that on a 10% drop for all products where yesterday's count is positive. And I'm stuck. The references to {} are all for a JSON array [], not a JSON object with keys and values.

I'd love to get a table out that looks something like this:

How do I do that?

CodePudding user response：

I think produces the output you want:

| spath
| table counts.product_list.*
| transpose
| rex field=column "counts.product_list.(?<ID>[^.]*).(?<fieldname>.*)"
| fields - column
| xyseries ID fieldname "row 1"
| table ID name green red total

1. use transpose to get the field names as data
2. use rex to extract the ID and the field name
3. use xyseries to pivot the data into the output

Here is a run-anywhere example using your source data:

| makeresults
| eval _raw="
{\"counts\":
    {\"green\":413,
     \"red\":257,
     \"total\":670,
     \"product_list\":
        { \"urn:product:1\":{ 
            \"name\":\"M & Ms\" ,
            \"total\":332 ,
            \"green\":293 ,
            \"red\":39 } ,
          \"urn:product:2\":{ 
            \"name\":\"Christmas Ornaments\" ,
            \"total\":2 ,
            \"green\":0 ,
            \"red\":2 } ,
         \"urn:product:3\":{ 
            \"name\":\"Traffic Lights\" ,
            \"total\":1 ,
            \"green\":0 ,
            \"red\":1 } ,
         \"urn:product:4\":{ 
            \"name\":\"Stop Signs\" ,
            \"total\":2 ,
            \"green\":0 ,
            \"red\":2 },
        }
    }
}"
| spath
| table counts.product_list.*
| transpose
| rex field=column "counts.product_list.(?<ID>[^.]*).(?<fieldname>.*)"
| fields - column
| xyseries ID fieldname "row 1"
| table ID name green red total