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!

Splunk Cloud | Empowering Splunk Administrators with Admin Config Service (ACS)

Greetings, Splunk Cloud Admins and Splunk enthusiasts! The Admin Configuration Service (ACS) team is excited ...

Tech Talk | One Log to Rule Them All

One log to rule them all: how you can centralize your troubleshooting with Splunk logs We know how important ...

Splunk Security Content for Threat Detection & Response, Q1 Roundup

Join Principal Threat Researcher, Michael Haag, as he walks through: An introduction to the Splunk Threat ...