topic How to find _time when select distinct? in Splunk Enterprise

How to find _time when select distinct?

Julia1231 — Tue, 09 Aug 2022 14:07:57 GMT

Hello Everyone,

I have a table like this:

_time	value1	value2
30/12/2021 06:30	12.1	25.2
30/12/2021 06:00	12.1	25.2
30/12/2021 05:30	11.2	26.4
30/12/2021 05:00	11.2	26.4
30/12/2021 04:30	12.1	24.5
30/12/2021 04:00	10.6	29.5
30/12/2021 03:30	10.6	29.5
30/12/2021 03:00	10.6	35.2

I want to select distinct of value 1 and get the corresponding _time and value2.

When I do: |stats values(*) as * by value1, it returns only value1 and value2, no include _time

But I do want to see the _time.

Do you have any solution please?

Thanks,

Julia

Re: Find _time when select distinct

ITWhisperer — Mon, 08 Aug 2022 07:19:16 GMT

Fields beginning with _ are not matched to * - values only contains unique values and are sorted lexicographically - try something like this

|stats list(*) as * list(_time) as _time by value1

Re: Find _time when select distinct

Julia1231 — Mon, 08 Aug 2022 08:36:06 GMT

Thanks @ITWhisperer .

Now I find the _time.

However, when value1 matches with several value2, _time is displayed in the form of timestamps. When 1 value1 matches with 1 value2, _time is displayed as a date time normally, fyi.

Do you have any idea?

Julia.

Re: Find _time when select distinct

ITWhisperer — Mon, 08 Aug 2022 08:42:06 GMT

_time is normally an epoch time i.e. number of seconds since 1/1/1970. When Splunk displays it, it will convert it to a formatted string. However, when you collect a number (more than 1) of _time values in a multivalue field with the values() or list() aggregate functions, it no longer does this for you. To get around this, you could convert _time to a string before hand

|eval time=strftime(_time,"%F %T") |stats list(*) as * by value1

Re: Find _time when select distinct

Julia1231 — Mon, 08 Aug 2022 09:07:26 GMT

it works, thanks for your help and your clear explaination!