Dashboards & Visualizations

How to create a dynamic loop through a JSON array that looks for a trigger in any element

splunkr00kie
Engager

Data example:

 "storeID":"000",
    "activeStatus":"Active",
    "location":{ 
        "addressLine1":"4300 Store Rd"
        "city":"Atlanta",
        "zip":"99999"
        "state":"GA",
        "mainNumber":"9999999999"
    },
    "capabilities":[ 
        { 
            "name":"Sales"
            "startDateUtc":"2019-11-10T07:00:00"
        },
        { 
            "name":"Accounting"
            "startDateUtc":"2019-10-23T07:00:00"
        },
        { 
            "name":"Shipping"
            "startDateUtc":"2019-10-23T07:00:00"
        },
        { 
            "name":"Executive"
            "startDateUtc":"2019-10-23T07:00:00"
        },
        { 
            "name":"Delivery",
            "startDateUtc":"2019-10-23T07:00:00"
        }
    ]
}

Fairly new to Splunk and I am looking for a way to loop through "capabilities" looking for today's date. If there is a match from any element it returns the "name" associated with it.

0 Karma

vmacedo
Explorer

Here is a solution without using mvzip:

| makeresults
| eval _raw="{ \"storeID\":\"000\",
  \"activeStatus\":\"Active\",
  \"location\":{
  \"addressLine1\":\"4300 Store Rd\",
  \"city\":\"Atlanta\",
  \"zip\":\"99999\",
  \"state\":\"GA\",
  \"mainNumber\":\"9999999999\" },
  \"capabilities\":[
  {\"name\":\"Sales\", \"startDateUtc\":\"2019-11-10T07:00:00\"},
  {\"name\":\"Accounting\", \"startDateUtc\":\"2019-12-05T07:00:00\"},
  {\"name\":\"Shipping\", \"startDateUtc\":\"2019-10-23T07:00:00\"},
  {\"name\":\"Executive\", \"startDateUtc\":\"2019-10-23T07:00:00\"},
  {\"name\":\"Delivery\", \"startDateUtc\":\"2019-10-23T07:00:00\"}
]}"
| spath output=capabilities path=capabilities{}
| mvexpand capabilities
| spath input=capabilities
| eval today=strftime(now(), "%Y-%m-%d")
| where match(startDateUtc, today)
| table name, startDateUtc
0 Karma

aberkow
Builder

Hey! JSON arrays are rather notorious to work with - Splunk treats them like multi-value fields even though they look like they're separate (or at least from your event I understand that to be the case). Based on my understanding of your use case, you need to tie the individual events together, separate them out, filter to where startDateUtc is today, and then table the name. Try running this search and letting me know if its helpful, I would suggest going line by line and also adding various | table {fieldName}, where fieldName is the field you care about, to understand what I'm doing:

your_base_search
| eval name_zip_startDateUtc=mvzip(name,startDateUtc)
| mvexpand name_zip_startDateUtc
| eval name_zip_startDateUtc=split(name_zip_startDateUtc,",")
| eval name=mvindex(name_zip_startDateUtc,0)
| eval startDateUtc=mvindex(name_zip_startDateUtc,1)
| eval today=strftime(now(), "%Y-%m-%d")
| rex field=startDateUtc "(?.*)T.*"
| where Date=today
| table name

Hope this helps!

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to July Tech Talks, Office Hours, and Webinars!

What are Community Office Hours?Community Office Hours is an interactive 60-minute Zoom series where ...

Updated Data Type Articles, Anniversary Celebrations, and More on Splunk Lantern

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

A Prelude to .conf25: Your Guide to Splunk University

Heading to Boston this September for .conf25? Get a jumpstart by arriving a few days early for Splunk ...