How do I stats both the `latest()` and `values()` of a multi-value field?

[camillak]

I am trying to get both latest() and values() of a multivalue field. I am sending the field to Splunk as a comma-separated list of IDs. So, for a single event, I would get 4,5,24.

In props.conf, I create a multi-value field so I can use a CSV lookup from the field without having to split it in each search" EVAL-mv_id=split(id,",") . Then, at search time, I can do the lookup: lookup cat_id.csv mv_id output catname, which gives me a multi-value with 3 values for catname.

However, when I do | stats latest(mv_id) AS mv_id latest(catname) AS catname BY group, only one of the values are returned, so I only get 4 and a single catname, although 4, 5, and 24 are all from the same event and were sent at the same time. This is a problem for me because I want both the multi-value latest(catname) and values(catname).

I realize I could do the following:

| stats latest(id) AS latest_id values(id) AS values_id BY group
| eval  mv_id=split(latest_id,","), values_id=split(values_id,",")
| lookup cat_id.csv mv_id OUTPUT catname
| rename catname AS latest_catname, values_id AS mv_id
| lookup cat_id.csv mv_id OUTPUT catname
| rename catname as values_catname
| table latest_catname values_catname group

That just seems unnecessarily complicated to me, especially since the latest ID truly is a multi-value. Is there a better way to do this? Is there something I'm missing in the props.conf EVAL or elsewhere?

[woodcock]

You will have to collapse the values back into a single-value by using nomv or mvjoin and use that collapsed field instead.

[DalJeanis]

Here's an example of one method...

Try...

Your search 
your lookup 
| eval catnamejoined=mvjoin(catname,"!!!!"), 
| stats latest(catnamejoined) as latest_catname values(catname) as all_catname by group
| makemv delim="!!!!" latest_catname

Use the same three-step strategy with mv_id: flatten, use the flat one for latest, then unflatten.

We use "!!!!" since it hardly ever appears in normal data.