topic Re: How to see only earliest and latest values in a field. in Splunk Search

How to see only earliest and latest values in a field.

franciscof — Tue, 23 Jun 2020 22:02:59 GMT

I'm having an issue because I need to show in a report only the first ticket received by an agent and the latest one, so all the other tickets in the middle I have to leave them behind. Here is the evidence:

Of all the tickets assigned to user1 or user2, how can I capture only the oldest and newest one?

Thanks in advance

Re: How to see only earliest and latest values in a field.

gcusello — Fri, 06 Mar 2020 14:41:31 GMT

Hi @franciscof,
did you tried using earliest and latest in stats?

index=your_index ticket_arrival=*
| eval ticket_arrival=strptime(ticket_arrival,"%d/%m/%y - %H:%M:%S"")
| stats earliest(ticket_arrival) AS earliest latest(ticket_arrival) AS latest BY user
| eval earliest=strftime(earliest,"%d/%m/%y - %H:%M:%S""), latest=strftime(latest,"%d/%m/%y - %H:%M:%S"")

Please check the format of the ticket_arrival field, because I'm not sure about the spaces before hours.
Ciao.
Giuseppe

Re: How to see only earliest and latest values in a field.

to4kawa — Fri, 06 Mar 2020 14:43:56 GMT

| makeresults
| eval _raw="user,ticket_arrival
user1,02/03/20 - 12:43:47
user1,02/03/20 - 12:44:40
user1,03/03/20 - 16:05:08
user1,04/03/20 - 15:39:37
user1,05/03/20 - 15:22:25
user2,04/03/20 - 18:03:13
user2,04/03/20 - 09:39:24
user2,04/03/20 - 10:32:35
user2,04/03/20 - 15:05:25
user2,05/03/20 - 10:46:35
user2,05/03/20 - 10:47:10"
| multikv forceheader=1
| table user,ticket_arrival
| rename COMMENT as "this is sample your stats output, from here, the logic"
| eval _time=strptime(ticket_arrival,"%d/%m/%y - %T")
| eventstats min(_time) as e_time max(_time) as l_time by user
| where _time=e_time OR _time=l_time
| table user,ticket_arrival

Hi, If you provide your query, there is more easy query.

Re: How to see only earliest and latest values in a field.

woodcock — Fri, 06 Mar 2020 15:29:37 GMT

The basic answer is to use earliest() and latest() but you can also use first() and last() just be aware that the former pair leverages the _time field (which may/not be present and may/not be correct) whereas the latter leverages the order of the data which you can control by sorting it. Both honor the BY section of stats.

Re: How to see only earliest and latest values in a field.

franciscof — Mon, 09 Mar 2020 14:05:41 GMT

Hi @gcusello , I did tried it and I think it didn't work and I can't figure out why. Below (in an answer) I'll show you what I got

Re: How to see only earliest and latest values in a field.

franciscof — Mon, 09 Mar 2020 14:06:16 GMT

any clues why this is happening?

Re: How to see only earliest and latest values in a field.

manjunathmeti — Mon, 09 Mar 2020 14:31:33 GMT

Do this:

| eval ticket_arrival = strptime(replace(ticket_arrival, "\s", ""), "%d/%m/%y-%H:%M:%S") | stats min(ticket_arrival) as min_time, max(ticket_arrival) as max_time by user | eval min_time=strftime(min_time, "%d/%m/%y-%H:%M:%S"), max_time=strftime(max_time, "%d/%m/%y-%H:%M:%S")

Re: How to see only earliest and latest values in a field.

franciscof — Mon, 09 Mar 2020 14:40:40 GMT

Solved it! All it was needed was to replace stats earliest and latest with stats first and last!

Thank you for your help

Re: How to see only earliest and latest values in a field.

gcusello — Mon, 09 Mar 2020 14:53:42 GMT

you're welcome!
Ciao and next time.
Giuseppe