Splunk Enterprise

How to find _time when select distinct?

Julia1231
Communicator

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

Julia1231_0-1659942633966.png

 

But I do want to see the _time.

Do you have any solution please?

Thanks,

Julia

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

ITWhisperer
SplunkTrust
SplunkTrust

_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

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

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

 

Julia1231
Communicator

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.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

_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

Julia1231
Communicator

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...