topic Re: stats or tstats latest not working on array fields in Splunk Search

Why are stats or tstats latest not working on array fields?

shikhanshua — Mon, 04 Apr 2022 14:38:58 GMT

I have events like these (just some made-up data), that are pushed in JSON format to Splunk:

{"name":"abc", "grade":"third", "result": "PASS", "courses":["math","science","literature"], "interests":["this","that"]}

Events are being generated all the time, and I need to get the latest values of "result", "courses" and "interests" for a given "name" and "grade". Note that "courses" and "interests" are lists/arrays, while other fields are strings.

So I am doing somethings like:

index=whatever name=abc grade=third | stats latest(courses) as courses, latest(interests) as interests, latest(result) as result index=whatever name=abc grade=third | stats latest(courses{}) as courses, latest(interests{}) as interests, latest(result) as result index=whatever name=abc grade=third | eval courses=json_array_to_mv(courses), interests=json_array_to_mv(interests) | stats latest(courses) as courses, latest(interests) as interests, latest(result) as result

Also tried with "tstats" approach.

None of those work. I get the courses and interests as empty values. result comes in fine, because its a string.

How can I get the "latest" lists of courses and interests given other values?

Re: stats or tstats latest not working on array fields

VatsalJagani — Sat, 02 Apr 2022 05:50:23 GMT

This works for me:

| makeresults | eval _raw="{\"name\":\"abc\", \"grade\":\"third\", \"result\": \"PASS\", \"courses\":[\"math\",\"science\",\"literature\"], \"interests\":[\"this\",\"that\"]}" | kv | eval courses=mvjoin('courses{}', ", "), interests=mvjoin('interests{}', ", ") | stats latest(courses) as courses, latest(interests) as interests, latest(result) as result

| eval courses=mvjoin('courses{}', ", "), interests=mvjoin('interests{}', ", ")

Kindly accept the answer if it resolves your problem!!

Re: stats or tstats latest not working on array fields

yuanliu — Sat, 02 Apr 2022 06:19:20 GMT

As @VatsalJagani demonstrated, you can bind array into a single string for use with stats. But you don't need stats to get "latest" of every field if the sole goal is to get the latest. Here are some thoughts.

In most indexes, data are stored in reverse time order already. All you need to do is to retrieve the last row, i.e., "| head 1"
If time in index may be scrambled, sort first, i.e., "| sort _time | tail 1"
If you want to get the latest by some key, you can use dedup, e.g., "| dedup name" (again, if index may contain out of order events, sort first, e.g., "| sort - _time | dedup name".)

Here are some simulated data

_time	courses{}	grade	interests{}	name	result
2022-04-02 06:07:01	math science literature	fourth	this that	abc	PASS
2022-04-02 06:04:01	arts science literature	second	thing matter	def	PASS
2022-04-02 06:01:01	math science PE	third	here there	abc	FAIL

Using simple head, you get the first row

_time	courses{}	grade	interests{}	name	result
2022-04-02 06:12:09	math science literature	fourth	this that	abc	PASS

Using dedup name, the output is

_time	courses{}	grade	interests{}	name	result
2022-04-02 06:13:30	math science literature	fourth	this that	abc	PASS
2022-04-02 06:10:30	arts science literature	second	thing matter	def	PASS

Re: stats or tstats latest not working on array fields

shikhanshua — Tue, 05 Apr 2022 13:57:54 GMT

In my case, the fields are coming from different kinds of events and I am compiling them together. So can’t do head 1 etc as the latest of one field maybe in an older event, while latest of another may be in the latest event. Etc.

Thanks for the suggestion though. Definitely useful to do when all the fields are in all events that i am looking at.