Splunk Search

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

shikhanshua
Engager

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?

Labels (2)
Tags (2)
0 Karma
1 Solution

VatsalJagani
Champion

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{}', ", ")

 

VatsalJagani_0-1648878536737.png

Kindly accept the answer if it resolves your problem!!

View solution in original post

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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.

  1. 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"
  2. If time in index may be scrambled, sort first, i.e., "| sort _time | tail 1"
  3. 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{}
nameresult
2022-04-02 06:07:01
math
science
literature
fourth
this
that
abcPASS
2022-04-02 06:04:01
arts
science
literature
second
thing
matter
defPASS
2022-04-02 06:01:01
math
science
PE
third
here
there
abcFAIL

Using simple head, you get the first row

_time
courses{}
grade
interests{}
nameresult
2022-04-02 06:12:09
math
science
literature
fourth
this
that
abcPASS

Using dedup name, the output is

_time
courses{}
grade
interests{}
nameresult
2022-04-02 06:13:30
math
science
literature
fourth
this
that
abcPASS
2022-04-02 06:10:30
arts
science
literature
second
thing
matter
defPASS
Tags (3)
0 Karma

shikhanshua
Engager

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. 

0 Karma

VatsalJagani
Champion

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{}', ", ")

 

VatsalJagani_0-1648878536737.png

Kindly accept the answer if it resolves your problem!!

0 Karma
Get Updates on the Splunk Community!

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...

Reminder! Splunk Love Promo: $25 Visa Gift Card for Your Honest SOAR Review With ...

We recently launched our first Splunk Love Special, and it's gone phenomenally well, so we're doing it again, ...