topic Re: stats value(_time) delimiter in Splunk Search

stats value(_time) delimiter

bx_ben — Tue, 29 Sep 2020 16:29:24 GMT

When I use stats values(_time) as _time group by the list of values in my table is delimitated by comma's.
ex:

10/25/2017 16:48:34,10/25/2017 17:17:11,10/25/2017 17:17:15,10/25/2017 17:17:17,10/25/2017 17:19:02,10/25/2017 19:10:03,10/25/2017 19:20:15,10/25/2017 19:32:48,10/25/2017 20:02:20,10/25/2017 22:01:18,10/25/2017 23:02:41,10/26/2017 00:02:11,10/26/2017 00:02:31,10/26/2017 03:25:27

When I use stats values(ip_address) as ip_addresses group by the list of values in my table is eliminated by new lines.
ex:

168.227.146.19
176.49.175.120
177.129.242.138
188.105.223.1
190.202.206.139
190.214.152.247
192.160.102.170
199.249.223.60
201.254.172.148
45.55.57.18
47.154.229.1
51.15.64.212
54.201.174.255

How do I get consistency? Preferably forcing the list of times to be delimited by the new line.

Re: stats value(_time) delimiter

DalJeanis — Thu, 26 Oct 2017 18:13:42 GMT

When those values come out of the initial stats command, they are not delimited at all. They are in a multivalue field, which will normally display as if it was newlines.

The field _time is special. It is normally in epoch format, but presents itself in a data format. When you do this...

| stats values(_time)

... the results in the multivalue field will be in epoch time values.

If you rename it back to _time like so...

| stats values(_time) as _time

...then when the interface tries to present the value of _time, it will realize that it is a multivalue field and present it in the comma-delimited form instead. but the values will still be in epoch form.

 1509011713,1509011758,1509011764,1509011792...

The fact that you are showing date/times in human-readable form implies that you did something else before the stats command.

| eval _time = strftime(_time,"%F %H:%M:%S")
| stats values(_time) as _time

2017-10-26 09:57:52,2017-10-26 09:58:20,2017-10-26 09:58:21,2017-10-26 09:59:52...

If you rename the result of the values() to anything else but_time, then it will remain in the plain epoch, plain multivalue form. I believe this is what you want.

| eval _time = strftime(_time,"%F %H:%M:%S")
| stats values(_time) as Time

2017-10-26 10:00:02 
2017-10-26 10:00:03 
2017-10-26 10:00:06 
 ...

Re: stats value(_time) delimiter

bx_ben — Tue, 29 Sep 2020 16:29:35 GMT

The results above are with | stats values(_time) as _time but still do not list the same way as stats values(ip_addresses) as ip_addresses. They show in this ,,, regardless if pre-modify the _time variable.

My question is, why is only _time showing with , delimiter, all other values show up with new lines list.

Re: stats value(_time) delimiter

DalJeanis — Tue, 29 Sep 2020 16:25:53 GMT

@bx_ben - there are lots of special things about internal fields like _time -- I'll give you some examples in a minute. When you do values(_time) that is going to break most of them, so you might as well rename it to something else that won't confuse you and the system. You don't NEED that multivalue field to be called _time, so rename it during the stats command and it will act as expected.

Okay, here's a list off the top of my head...

1) _time is the expected order of events, thus latest() on a different field will compare the _time fields on the two events in order to determine which one to present.

2) timechart implicitly uses _time, and only _time, for its x axis.

3) _time is in epoch time but will automatically format itself on the output interface. It is not precisely the same as another field that has been converted to ctime().

Re: stats value(_time) delimiter

wryanthomas — Mon, 02 Sep 2019 22:03:47 GMT

I just had to do this ... and discovered that this worked for me:

After the |stats values(_time) as _time, then...

| makemv delim="," _time