Display days passed between current time and most recent activation - User Role Auditing

[dmbr]

The answer to this probably stupid simple. Banging my head on this.

Help and patience please.

 

I am writing a query which audits user role activations.

A user can have many roles, i.e. Network Admin, Security Reader, User Admin, Storage Operator, etc.

Specifically, I want to view how many days has passed since a user activated a specific role.

 

Today - Last Activation = Days Since Last Activation

Questions:

1. How can I most efficiently subtract the time of the most recent role activation event from the current time?
2. How can I then only show the most recent results?
3. How to then audit all users who have not activated a role in the past 30 days?

Base query for a single user search over 30 days and sample results are below.

 index=audit user=bob@example.com 
| eval days = round((now() - _time)/86400) 
| table Role, user, _time, days 
| sort - days

Sample Data Below

Role	UserName	_time	days
Global Reader	bob@example.com	2021-09-19T08:35:06.998	29
Global Reader	bob@example.com	2021-09-19T08:35:05.514	29
Systems Administrator	bob@example.com	2021-09-23T05:55:51.177	25
Systems Administrator	bob@example.com	2021-09-23T05:55:49.036	25
Global Reader	bob@example.com	2021-09-24T00:48:20.254	24
Storage Operator	bob@example.com	2021-09-24T00:48:18.942	24
Systems Administrator	bob@example.com	2021-09-27T07:22:23.971	21
Systems Administrator	bob@example.com	2021-09-27T07:22:22.971	21
Global Reader	bob@example.com	2021-09-27T07:19:40.569	21
Global Reader	bob@example.com	2021-09-27T07:19:39.460	21

Desired results only show the most recent events 

Role	UserName	_time	days
Global Reader	bob@example.com	2021-09-24T00:48:20.254	24
Storage Operator	bob@example.com	2021-09-24T00:48:18.942	24
Systems Administrator	bob@example.com	2021-09-27T07:22:22.971	21
Global Reader	bob@example.com	2021-09-27T07:19:39.460	21

[ITWhisperer · Accepted solution]

index=audit user=bob@example.com 
| stats latest(_time) as _time by user, Role
| eval days = round((now() - _time)/86400) 
| table Role, user, _time, days 
| sort - days

[dmbr]

Sincere Thank you ITWhisperer for the reply, much appreciated.

As expected, the solution was much stupid simple. 😂