How to parse JSON metrics array in Splunk

Question

I receive JSON from API in the following format:

[
    {
    "scId": "000DD2",
    "sensorId": 2,
    "metrics": [
        {
            "s": 5414,
            "dateTime": "2018-02-02T13:03:30+01:00"
        },
        {
            "s": 5526,
            "dateTime": "2018-02-02T13:04:56+01:00"
        },
        {
            "s": 5631,
            "dateTime": "2018-02-02T13:06:22+01:00"
        }
}, .... ]

Currently trying to display these metrics on the linear chart with dateTime for the X-axis and "s" for Y.

I use the following search query:

index="main" source="rest://test3" | spath input=metrics{}.s| mvexpand metrics{}.s
| mvexpand metrics{}.dateTime | rename metrics{}.s as s 
| rename metrics{}.dateTime as dateTime| table s,dateTime

And I receive the data in the following format which is not applicable for linear chart. The point is - how to correctly parse the JSON to apply date-time from dateTime field in JSON to _time in Splunk.

Query results

score 3 · Answer 1 · answered May 29 '18 at 10:23

@Max Zhylochkin,

Can you please try following search?

index="main" source="rest://test3" 
| spath input=metrics{}.s 
| mvexpand metrics{}.s 
| mvexpand metrics{}.dateTime 
| rename metrics{}.s as s 
| rename metrics{}.dateTime as dateTime 
| table s,dateTime
| eval _time = strptime(dateTime,"%Y-%m-%dT%H:%M:%S.%3N")

Thanks

How to parse JSON metrics array in Splunk

1 Answers1