How to build a Splunk query that extracts data from a JSON array?

Question

We have a series of splunk log entries and want to plot the "Data" field on a dashboard.

{
"Message": "Test Message",
"Timestamp":2022-09-30T14:06:01.8294071-05:00,
"Data": [{"Key":"key1","Value":"3"},{"Key":"key2","Value":"4"}]
}
{
"Message": "Test Message",
"Timestamp":2022-09-30T15:06:01.8294071-05:00,
"Data": [{"Key":"key1","Value":"4"},{"Key":"key2","Value":"3"}]
}
{
"Message": "Test Message",
"Timestamp":2022-09-30T17:06:01.8294071-05:00,
"Data": [{"Key":"key1","Value":"5"},{"Key":"key2","Value":"2"}]
}

We want to plot key1 and key2 on the same Splunk plot. The documentation shows a way that should work: https://docs.splunk.com/Documentation/Splunk/latest/Search/Chartmultipledataseries

Taking inspiration for how to handle array data from https://community.splunk.com/t5/Splunk-Search/How-to-search-or-extract-specific-key-value-pair-from-array/m-p/590937#M205723 We've tried:

index=myindex 
| bin _time 
| rename Data{} as data_*
| eval key1 = mvindex(data_Value,mvfind(data_Key,"key1"))
| eval key2 = mvindex(data_Value,mvfind(data_Key,"key2"))
| stats sum(key1) as key1sum, sum(key2) as key2sum by _time, source 
| eval s1="key1s key2s" 
| makemv s1 
| mvexpand s1 
| eval yval=case(s1=="key1s",key1sum,s1=="key2s",key2sum) 
| eval series=source+":"+s1 
| xyseries _time,series,yval

When I run the query the events are found, but no statistic are generated. I suspect the issue lies in reading the data from the log entry. Can someone help me out?

Answer 1

Here's a run-anywhere query that produces results. I had no luck with the rename command so used 'Data{}.', instead.

Debugging a problem like this is matter of running the query one command at a time until you find the one that produces no results. Then you fix that command and continue the process until you have a finished query.

| makeresults
| eval data="{
\"Message\": \"Test Message\",
\"Timestamp\":\"2022-09-30T14:06:01.8294071-05:00\",
\"Data\": [{\"Key\":\"key1\",\"Value\":\"3\"},{\"Key\":\"key2\",\"Value\":\"4\"}]
};
{
\"Message\": \"Test Message\",
\"Timestamp\":\"2022-09-30T15:06:01.8294071-05:00\",
\"Data\": [{\"Key\":\"key1\",\"Value\":\"4\"},{\"Key\":\"key2\",\"Value\":\"3\"}]
};
{
\"Message\": \"Test Message\",
\"Timestamp\":\"2022-09-30T17:06:01.8294071-05:00\",
\"Data\": [{\"Key\":\"key1\",\"Value\":\"5\"},{\"Key\":\"key2\",\"Value\":\"2\"}]
}"
| eval data=split(data,";") 
| mvexpand data
| eval _raw=data, source="foo"
| fields - data
| spath 
```Everything above just sets up test data```
| eval key1 = mvindex('Data{}.Value',mvfind('Data{}.Key',"key1"))
| eval key2 = mvindex('Data{}.Value',mvfind('Data{}.Key',"key2"))
| stats sum(key1) as key1sum, sum(key2) as key2sum by _time, source 
| eval s1="key1s key2s" 
| makemv s1 
| mvexpand s1 
| eval yval=case(s1=="key1s",key1sum,s1=="key2s",key2sum) 
| eval series=source+":"+s1 
| xyseries _time,series,yval