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

dmbr — Mon, 18 Oct 2021 06:04:09 GMT

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:

How can I most efficiently subtract the time of the most recent role activation event from the current time?
How can I then only show the most recent results?
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

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

ITWhisperer — Mon, 18 Oct 2021 07:51:05 GMT

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

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

dmbr — Mon, 18 Oct 2021 10:00:26 GMT

Sincere Thank you ITWhisperer for the reply, much appreciated.

As expected, the solution was much stupid simple. 😂

topic Display days passed between current time and most recent activation - User Role Auditing in Splunk Search

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

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

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