how to restrict a search to the last event per user

[fabsi]

I would like to know the number of users per application version over a period of time.

The events always include the application version and the user id but a user could have used different application versions over a period of time and I am interested on the latest, which happen to be the latest event for a given user, so how can I restrict a query to search only through the last events per user?, below it is my attempt which is not working..

search |stats values(email) as email, values(deviceSerial) as deviceSer, values(appVersion) as appVersion, latest(_time) AS latest_time by deviceSerial |fields email deviceSer appVersion latest_time| dedup email latest_time|  stats  dc(email) as users by appVersion | sort -users

[rjthibod · Accepted solution]

Try this

search 
| stats max(_time) as latest_time by email appVersion
| stats dc(email) as users by appVersion 
| sort -users

[fabsi]

I tried, but still users are counted twice if there are events done with multiple versions per user . So I managed to filter those cases using eventstats.. (there is usually a one to one relationship between deviceSerial and email fields)

search
| eventstats latest(_time) AS latest_time by deviceSerial
|dedup deviceSerial latest_time
| stats dc(email) as users by appVersion
| sort -users

[fabsi]

Hi, I am answering myself.. I decided to use eventstats. If somebody has another idea, please tell, specially if you think that the performance can be better..

search string| eventstats latest(_time) AS latest_time by deviceSerial |dedup deviceSerial latest_time|  stats  dc(email) as users by appVersion | sort -users

Seems to work..

[richgalloway]

I believe you don't need both eventstats and dedup. I usually just use dedup when I need the latest event containing a particular field or set of fields.

[fabsi]

will that be dedup deviceSerial then? and by definition will take the latest event?. thank you richgalloway and rjthibod