Based on (https://answers.splunk.com/answers/709936/get-value-from-nested-json.html#answer-709944) I came up with a query:
index=dcos sourcetype=dcos:node:metrics
| rename datapoints{}.name as name, datapoints{}.value as value
| eval x=mvzip(name,value)
| mvexpand x
| eval x=split(x,",")
| eval name=mvindex(x,0)
| eval value=mvindex(x,1)
| table _time, name, value, host
| sort - _time
| search name=system.uptime
What I expected was a list with the system.uptime every minute for all 3 hosts, however it is not consistent and sometimes I only get the value for 1 host for a specific minute (see screenshot).
I checked to see if the data was onboarded allright and has the value I run my filter on (| search name=system.uptime) and all data has the value and is onboarded correct every minute.
When I run
index=dcos sourcetype=dcos:node:metrics
| spath datapoints{} output=datapoint
| mvexpand datapoint
| eval _raw=datapoint
| kv
| search name="system.uptime"
| table name value _time host
| sort - _time
I get the expected results ( I cant show it this website only allows 2 images) :S:
The problem is the size of your dataset and the INEXCUSABLY poor performance of the mvexpand
command. If you examine your search log
in your Job inspector
you will see that it tells you that your search is autofinalizing because it ran out of RAM and returning partial results. It is also pretty inexcuseable that Splunk does not make this more clear to you without you having to dig for it. In any case, the solution is to avoid mvexpand
:
|makeresults | eval time = "2018-12-20T15:02:52 2018-12-20T15:01:59 2018-12-20T15:01:00 2018-12-20T14:59:59 2018-12-20T14:58:52 2018-12-20T14:57:52 2018-12-20T14:56:51 2018-12-20T14:55:59 2018-12-20T14:54:49 2018-12-20T14:53:52 2018-12-20T14:52:53 2018-12-20T14:52:00"
| makemv time
| mvexpand time
| rename time AS _time
| eval _time = strptime(_time, "%Y-%m-%dT%H:%M:%S")
| eval host = "ics031120105 ics031120106 ics031120107"
| makemv host
| mvexpand host
| eval name = "a b c d e f g h i j system.uptime"
| eval value = "1 2 3 4 5 6 7 8 9 10 11"
| makemv name
| makemv value
| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"
Like this (for the specific approach):
| nomv name
| eval tester = mvindex(split(name, "system.uptime"), 0)
| eval position = (len(tester) - len(replace(tester, "\s", "")))
| eval uptime = mvindex(value, position)
| table _time, uptime, host
| sort - 0 _time
Or like this for the general approach:
| eval _raw = mvzip(name,value,"=")
| fields - name value
| kv
| table _time, uptime, host
| sort - 0 _time
Did you try this @sboogaar?
are there quotes around "system.uptime" on line 10?
@sboogaar
I have replied on below your question. Can you please check it?
https://answers.splunk.com/answers/709936/get-value-from-nested-json.html#answer-709944