Need help with json spath search

[madhav_dholakia]

Hello, 

I have seen a few of the spath topics around, but wasn't able to understand enough to make it work for my data. 

I would like to create a line chart using pointlist values - it contains timestamp in epoch and CPU%

Search I tried but not working as expected to extract this data:

index="splunk_test" source="test.json"   
| spath output=pointlist path=series{}.pointlist{}{}
| mvexpand pointlist
| table pointlist

Please see below sample json.

{"status": "ok", "res_type": "time_series", "resp_version": 1, "query": "system.cpu.idle{*}", "from_date": 1698796800000, "to_date": 1701388799000, "series": [{"unit": [{"family": "percentage", "id": 17, "name": "percent", "short_name": "%", "plural": "percent", "scale_factor": 1.0}, null], "query_index": 0, "aggr": null, "metric": "system.cpu.idle", "tag_set": [], "expression": "system.cpu.idle{*}", "scope": "*", "interval": 14400, "length": 180, "start": 1698796800000, "end": 1701388799000, "pointlist": [[1698796800000.0, 67.48220718526889], [1698811200000.0, 67.15981521730248], [1698825600000.0, 67.07217666403122], [1698840000000.0, 64.72434584884627], [1698854400000.0, 64.0411289094932], [1698868800000.0, 64.17585938553243], [1698883200000.0, 64.044969119166], [1698897600000.0, 63.448143595246194], [1698912000000.0, 63.80226399404451], [1698926400000.0, 63.93216493520908], [1698940800000.0, 63.983679174088145], [1701331200000.0, 63.3783379315815], [1701345600000.0, 63.45321248782884], [1701360000000.0, 63.452383398041064], [1701374400000.0, 63.46314971048991]], "display_name": "system.cpu.idle", "attributes": {}}], "values": [], "times": [], "message": "", "group_by": []}

can you please help how I can achieve this?

Thank you.

Regards,

Madhav

[ITWhisperer · Accepted solution]

It appears that two dimensional arrays are not easily handled (unless someone else knows differently), so you could try something like this:

| spath output=pointlist path=series{}.pointlist{}{}
| mvexpand pointlist
| table pointlist
| streamstats count as row
| streamstats count(eval(row % 2==1)) as row
| stats list(pointlist) as pointlist by row
| sort 0 row
| eval pointX = mvindex(pointlist,0)
| eval pointY = mvindex(pointlist,1)

[madhav_dholakia]

Hi @ITWhisperer - thanks a lot, this worked like a charm.