Query to get latest timestamp

Follow

Sagan Sidhu

• March 01, 2013 20:30

I have a search query:

_sourceHost="xxxxx" | parse "event=*," as eventId | parse "userId=*," as userId | count by userId

Results in:

userid _count

1 "aaa" 200

2 "null" 52

3 "bbb" 76

4 "ccc" 3

5 "ddd" 29

6 "eee" 204

What I want to know, is what do I add to the query to get the latest timestamp for each userId, where it would be the latest record for each userId.

Thanks.

 

1

• 

  Kevin Keech

  • March 01, 2013 20:30

  try this:

  _sourceHost="xxxxx"
  
  | parse "event=*," as eventId
  
  | parse "userId=*," as userId
  
  // _messagetime is an internal field representing the time the message occured.
  
  // max(_messagetime) pulls out the maximum ("latest") _messagetime for each group.
  
  // There's one group for each userId, because you are aggregating on it.
  
  | count, max(_messagetime) as last_login_time by userId
  
  // Need a quick cast to tell Sumo that this is actually a long (millis since epoch).
  
  | toLong(last_login_time)
  
  // And finally, the magic incantation to turn a timestamp into a string based on
  
  // a supplied format. The format string follows the Java SimpleDateFormat.
  
  | formatDate(fromMillis(last_login_time), "yyyy-MM-dd HH:mm:ss.SSS") as last_login_time

  1

  Comment actions Permalink

Please sign in to leave a comment.