using eval and time

[PaulaCom]

Afternoon All

i'd like some help please with some SPL logic that i just cant crack 😞 

I have data on some user in our Active Directory system and i am trying to:

create a new column with actions

identify those who have no logged in for more than 61 days and is so the action should return "reset password"

here's the part that i am having an issue with below. the first two lines are working as expected returning last_logon_total  day, month, year

i have a new field i created called 'action' that i want to return a value in of those users who have not logged in for more than 61 days.. but i cant get the spl right.

| eval epoch_lastLogonTimestamp_date = strptime(lastLogonTimestamp, "%Y-%m-%dT%H:%M:%S")
| eval last_logon_total = strftime(epoch_lastLogonTimestamp_date, "%d/%m/%Y")
| eval action = if(last_logon_total = relative_time(), "-61d@d", "reset password")

any ideas ?

Thanks

Paula  

[PaulaCom]

thanks this has worked perfectly. 

[Richfez · Accepted solution]

There were a few errors, but this should work.  Note I broke out the comparison_date calculation from the eval where you decide if they need to reset or not, to a) make it more clear and b) so you can see the dates/strings it's comparing with.

| makeresults format="CSV" data="date
2024-05-09T08:05:00
2024-02-09T08:05:00"
| eval epoch_lastLogonTimestamp_date = strptime(date, "%Y-%m-%dT%H:%M:%S")
| eval last_logon_total = strftime(epoch_lastLogonTimestamp_date, "%d/%m/%Y")
| eval comparison_date = relative_time(now(),"-61d@d")
| eval action = if(epoch_lastLogonTimestamp_date <= comparison_date, "reset password", "no change needed")

I think the biggest issue was that the epoch date is the only one you need.  Do your math on it, work with it.  If you need to see it in a more human readable version, you can convert it back at the end.  In this case, 'last_logon_total' is simply unused after you build it.

Happy splunking, and if this helped karma would be appreciated!

-Rich