Query to get latest timestamp
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.
-
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
Please sign in to leave a comment.
Comments
1 comment