Find accounts with lastlogon older than X days

[heidihart]

Hi,

I have built out an AD inputlookup that includes lastlogon dates. When I attempt to find only those users with last logon older then 90 days I am unable to return any results.

| inputlookup AD.csv
| search lastLogon=* accountStatus!="ACCOUNTDISABLE"
| where lastLogon>=relative_time(now(),"-90d@d")
| table employee lastLogon

I have parsed the dates with strftime and strptime within the lookup itself and can see the dates are being displayed correctly but no luck on refining the results to just those of interest. I have tried to define the relative time, reparsing the dates within the search itself.

I have tried rearrange the date format and made sure to include the four year digits and still no luck.

Not sure what I am missing.

Any help would be appreciated

[manjunathmeti]

Function relative_time gives output in epoch format. So you need to convert lastLogon to epoch before comparing.

Sample query:

| makeresults | eval somedate="2020-03-04 23:10:18", someepoch=strptime(somedate, "%Y-%m-%d %H:%M:%S") | where someepoch >= relative_time(now(),"-90d@d")

Modify your query, edit date time format in strptime function based on lastLogon values format:

| inputlookup AD.csv where lastLogon=* accountStatus!="ACCOUNTDISABLE"
| eval lastLogon=strptime(lastLogon, "%Y-%m-%d %H:%M:%S")
| where lastLogon>=relative_time(now(),"-90d@d")
| table employee lastLogon

[heidihart]

Perfect thanks that make sense now.

[manjunathmeti]

Please accept and upvote answer 🙂