How-To: Get the first and last event of a groupBy() query

To see the time of the first and last occurrence of a particular event when using groupBy(), you can write a query that looks like this: 

groupBy(UserName, function=[max(@timestamp, as=lastSeen), min(@timestamp, as=firstSeen)])
| firstSeen:=formatTime(field=firstSeen, format="%Y/%m/%d %H:%M:%S") 
| lastSeen:=formatTime(field=lastSeen, format="%Y/%m/%d %H:%M:%S")

Keep in mind, @timestamp is epoch. This means you can search for the smallest timestamp which is the oldest, or the largest which is the most recent.

The example achieves three things:

  • Groups the results by UserName

  • Finds the smallest (oldest) timestamp, and the largest (newest) timestamp

  • Reformats the epoch times into a readable format.