topic Re: Can earliest/latest function be used over my own timestamp field? in Splunk Search

Can earliest/latest function be used over my own timestamp field?

codewarrior — Thu, 17 Jun 2021 03:57:34 GMT

Hi folks, my dataset looks like this:

timestamp	id	userMail	reason
t1	id1	a@example.com	test
t2	id1	a@example.com	test
t3	id1	a@example.com	test
t2	id2	b@example.com	testtest
t4	id2	b@example.com	ttt

I want to group by id and userMail, then find the last (latest) record in each group. From the help document on latest/earliest function, it uses metadata field _time to find the latest row, how can I let latest function to use timestamp field in my case?
I know I can use something like eval _time = timestamp to overwrite the _time field, but want to know if there are better ways to achieve.

My second question is how to write the query, can i do in this way:

| eval _time=timestamp/pow(10,3)
| chart latest(*) by id, userEmail

Thanks!

Re: Can earliest/latest function be used over my own timestamp field?

yuanliu — Thu, 17 Jun 2021 03:08:25 GMT

Yes, you can. If you want to use search command, rename your timestamp _time; else you will need to do calculations in where command.

Re: Can earliest/latest function be used over my own timestamp field?

isoutamo — Thu, 17 Jun 2021 06:40:37 GMT

If needed you could add new field (or convert timestamp) for epoch time (time in seconds). Then just look min/max from this values and then add values(timestamp) as timestamp your query or convert that min/max value back to displayable format.
r. Ismo

Re: Can earliest/latest function be used over my own timestamp field?

yuanliu — Thu, 17 Jun 2021 07:39:41 GMT

@codewarrior wrote:
My second question is how to write the query, can i do in this way:

| eval _time=timestamp/pow(10,3)
| chart latest(*) by id, userEmail

Also yes. As soon as you redefine _time, it behaves as the original _time. (In some use cases, you want to first rename the original in order to save its value. But for the stats that you are using it with, that is not necessary.)