Query, expired accounts, Active Directory Users and Computers

Hiroyuki Tamura
Hiroyuki Tamura used Ask the Experts™
on
Do you know how to set query to find expired accounts on Active Directory Users and Computers?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
MaheshArchitect
Distinguished Expert 2018

Commented:
use ad info tool from CJADEV, it has everything

http://www.cjwdev.com/Software/ADReportingTool/Download.html
Klavs RDeveloper, owner of AlbusBit
Commented:
You need to create new custom search query in ADUC (Saved Queries -> New -> Query -> Define Query... -> Find: Custom Search -> Advanced).
Use this LDAP Query to get users whose account has expired today or earlier.
(objectCategory=person)(objectClass=user)(accountExpires<=131574168000000000)

Open in new window


To customize accountExpires filter, you need to convert the date to integer8 type. You can do it in PowerShell:
PS (Get-Date "11/12/2017").ToFileTime()
131574168000000000

PS [datetime]::FromFileTime("131574168000000000")
Monday, December 11, 2017 at 00:00:00

Open in new window

Kevin StanushApplication Developer
Commented:
You can use a tool like Hyena to get this information.  Hyena's query library contains a number of pre-defined queries or you can create your own to do this:

  Hyena's Query Library
Its got a 30-day fully functional trial, as well as free support.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Shaun VermaakSenior Consultant
Awarded 2017
Distinguished Expert 2018
Commented:
Alternatively, you can configure and schedule this tool and never worry about it again
https://www.experts-exchange.com/articles/30820/Active-Directory-Cleanup-Tool-ADCleanup.html
E ATech Lead
Commented:
Dsquery can be used to get inactive users

dsquery user domainroot -name -inactive 30

Also, there are a bunch of great powershell scripts out there. Powershell is great for managing Active Directory.

However, few more Active Directory clean up solutions out there you can check like; this free ADCleanup, lepide and manageengine to find expired accounts.

Hope this helps!
E ATech Lead
Commented:
Few more informative article I would like to share:

Active Directory Saved Query Expired Accounts:
https://www.experts-exchange.com/questions/28092283/Active-Directory-Saved-Query-Expired-Accounts.html

Powershell – Expiring and Expired AD Account Notification
https://jeffbotsford.wordpress.com/2011/12/27/powershell-expiring-and-expired-ad-account-notification/
Application Developer
Commented:
Technically, you can't do this in ADUC.  ADUC lacks an option to customize the display fields that it offers beyond a hard-coded set of columns.  While you can modify it to allow any column to be displayed, its not easy, and probably not a good idea.  Once modified, you can then just sort on the expiration date and look at the cutoff you are wanting.  But like I said, the mod isn't easy and is sort of a 'voids the warranty' kind of thing.

Having a list of 'expired' accounts without knowing what the account expiration date is/was is not very useful, and the LDAP query language does not have a concept of 'today', so the date is always changing.  The EE link provided by Ajit Singh discusses this in detail.

The only (easy) way to do this is to use a 3rd party tool like has been mentioned or Powershell. ADUC is not designed to be very flexible or useful outside of what Microsoft designed it for.  I also checked and ADAC isn't much better and could not find a way to do it in ADAC either.  This is why there are a lot of 3rd party AD management tools (and Powershell).
Hiroyuki TamuraField Engineer

Author

Commented:
Thank you!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial