Working with mutivalues in stats command

[cindygibbs_08]

Hello Everyone I hope you are all having a great day.

I have been trying to understand how to properly work with multi values in Splunk, but it has been a true struggle for me,  after i execute this command:

| stats values(CODE) as CODE	values(DATE_IN) as DATE_IN	values(DATE_REQUESTS) as DATE_REQUESTS by HOST

I get something like this:

HOST	CODE	DATE_IN	DATE_REQUESTS
A	UYUJ XYHH	6/05/2021 6/06/2021	6/07/2021
B	OLP NMJ TYU WER BIYU	7/06/2021 8/06/2021 9/06/2021 10/06/2021 11/06/2021	8/09/2021 9/09/2021

But what I really want is to create a table like this one: 

Where I can create a single row for each ID , CODE and DATE_IN and to later on subtract each DATE_REQUEST to the single value in DATE_IN in a field named DATE_DIFF and finally create a field called SELECT in which I will be picking the smallest positive number from the  potentially multivalue field DATE_DIFF

I appreciate everyone's good will to help me out on this one I'm a rookie when it comes to Splunk and all of my attempts at using the mvexpand fucntion have not returned my desired outcome, thank you guys for your help.

Kindly,

Cindy

[ITWhisperer · Accepted solution]

Hi @cindygibbs_08 

values will gives an ordered array of values from the events which when you have more than one values field the order in the fields may not align with the original events and there may be different numbers of values in the fields. For example, CODE for HOST B would not be in the order you show, nor would DATE_IN if they are string values.

However, if you use list instead, the lists maintain the order and number of values from the original events.

mvexpand only works on one field at a time and cannot easily be used to re-establish the relationship between the fields to match the original events.

mvzip (which is possibly a command you should look at) can be used to join multivalue fields together into one multivalue field, which could then be mvexpanded and then split into separate fields.