Dashboards & Visualizations
Highlighted

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

New Member

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
Highlighted

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

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
Highlighted

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

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
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.