All Apps and Add-ons

Splunk Support for Active Directory: How to search a list of users whose accounts will expire within 30 days of today's date?

dkorlat
Explorer

I'm trying to generate a list of users whose accounts will expire within 30 days of today's date.
I first download the Active Directory users to a CSV lookup table using ldapsearch. Then I turn the account expires field into Epoch time and I run the following search commands. I can't accurately get the accounts that expire in 30 days. I tested the epoch time using a epoch time converter which tested fine. Here are the commands I've tested.

|inputlookup AGSEC_Users | eval AccountExpiryEpoch=strptime(accountExpires,"%Y-%m-%dT%H:%M:%SZ") | eval DaysLeft = (now() - AccountExpiryEpoch)/86400 | where DaysLeft<=30 | table displayName, sAMAccountName, accountExpires, AccountExpiryEpoch 

and I've tested this:

|inputlookup AGSEC_Users | eval AccountExpiryEpoch=strptime(accountExpires,"%Y-%m-%dT%H:%M:%SZ") | where AccountExpiryEpoch < relative_time(now(),"-30d@d") | table displayName, sAMAccountName, accountExpires, AccountExpiryEpoch

both return results but not accounts that will expire in within 30 days.

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Try this

|inputlookup AGSEC_Users | eval AccountExpiryEpoch=strptime(accountExpires,"%Y-%m-%dT%H:%M:%SZ") | where AccountExpiryEpoch < relative_time(now(),"+30d@d") AND AccountExpiryEpoch >=now() | table displayName, sAMAccountName, accountExpires, AccountExpiryEpoch

View solution in original post

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Try this

|inputlookup AGSEC_Users | eval AccountExpiryEpoch=strptime(accountExpires,"%Y-%m-%dT%H:%M:%SZ") | where AccountExpiryEpoch < relative_time(now(),"+30d@d") AND AccountExpiryEpoch >=now() | table displayName, sAMAccountName, accountExpires, AccountExpiryEpoch
0 Karma

dkorlat
Explorer

I have the users email address from Active Directory in the lookup table. Is there a way Splunk can turn the users email address into a variable then send a email to the individual email addresses it finds in the report? Currently we send the report to the Service Desk and the Service Desk calls the user advising them they need to start the process in getting their accounts extended. It will be nice if Splunk could also email the users.

0 Karma

dkorlat
Explorer

Thanks that worked perfectly.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Assuming the accounts have not yet expired, the phrase now() - AccountExpiryEpoch will produce a negative number. I believe you want AccountExpiryEpoch - now().

---
If this reply helps you, Karma would be appreciated.
0 Karma

jkat54
SplunkTrust
SplunkTrust

doubting this is the issue but I've added tonumber to one of your evals. Also added DaysLeft to your table so you can see those details and get more ideas there:

|inputlookup AGSEC_Users | eval AccountExpiryEpoch=strptime(accountExpires,"%Y-%m-%dT%H:%M:%SZ") | eval DaysLeft = tonumber((now() - AccountExpiryEpoch)/86400) | where DaysLeft<=30 | table displayName, sAMAccountName, accountExpires, AccountExpiryEpoch, DaysLeft
0 Karma
Get Updates on the Splunk Community!

Announcing the 1st Round Champion’s Tribute Winners of the Great Resilience Quest

We are happy to announce the 20 lucky questers who are selected to be the first round of Champion's Tribute ...

We’ve Got Education Validation!

Are you feeling it? All the career-boosting benefits of up-skilling with Splunk? It’s not just a feeling, it's ...

What’s New in Splunk Cloud Platform 9.1.2308?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2308! Analysts can ...