Active Directory Saved Query Expired Accounts

NSEMonsanto
NSEMonsanto used Ask the Experts™
on
I am trying to set a query in the Active Directory Saved Queries, to display the Expired Users accounts.
So far the custom query I found was

(&(&(objectCategory=person)(objectClass=user)(!AccountExpires=0)(!AccountExpires=9223372036854775807)))

But it provides all accounts with an expiration date set.

With a different sintax I have tried also the following:

(&(objectCategory=person)(objectClass=user)" _ & "(!accountExpires=9223372036854775807)(!accountExpires=0))

and it gives no result, even knowing that there are expired accounts.
Any better query?
I am not looking for Shell scripts.

Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Meir RivkinFull stack Software Engineer

Commented:
the LDAP filter is:
(&(objectCategory=person)(objectClass=user) (!accountExpires=9223372036854775807) (!accountExpires=0))

Open in new window

from http://www.rlmueller.net/AccountExpires.htm
i tried run it using DSQUERY on command line and it worked great.

Author

Commented:
Thanks, however it is like the first query I posted, and shows all users with expiring accounts set (too comprehensive), not expired ones.
Meir RivkinFull stack Software Engineer

Commented:
try:
(&(objectCategory=person)(objectClass=user)(accountExpires<=128635956000000000)(!accountExpires=0))

Open in new window

Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
Same same: no results.
even changing the operators <=> it doesn't make a difference: no users.
Your query makes more sense so far, it looks for accounts that have expired up to date.
However it looks like there should be a sharper definition I cannot get my head around.
Top Expert 2013

Commented:
So not willing to use adfind or powershell...do you only want a saved query?

Author

Commented:
Thanks for the effort.
You got it right, I'd like to use the saved query.

C.
PowerShell Developer
Top Expert 2010
Commented:
I'm afraid you can't make a saved query for this.

The problem is, the accountExpires value you're testing must be generated based on today's date. There is no pre-defined value for "today". accountExpires is the number of 100 nano second intervals since 01/01/1601 (http://msdn.microsoft.com/en-us/library/windows/desktop/ms675098%28v=vs.85%29.aspx).

This filter works just fine for accounts that have expired before today (that is, little before I posted):
(&(!accountExpires=0)(accountExpires<=1301180319619150000))

Open in new window

But if you want that to work for tomorrow? You'll need a new filter, with a new value for the accountExpires test.

It's not particularly "hard" to generate the filter, but it's hardly "saved". This little PowerShell snippet generates the filter:
$SecondsSince = (Get-Date).ToUniversalTime() - (Get-Date "00:00:00 01/01/1601") |
  Select-Object -ExpandProperty TotalSeconds
$100NanoSecondIntervals = ($SecondsSince * [Math]::Pow(10, 8)).ToString("0")

Write-Host "(&(!accountExpires=0)(accountExpires<=$100NanoSecondIntervals))"

Open in new window

That means that if you want to do this on a regular basis you must look for something other than a saved query. You need something to build this filter for you at any given point in time.

Chris
Theoretically the following answers your question, but it seems not to always work
(objectcategory=user)(userAccountControl:1.2.840.113556.1.4.803:=8388608 )

Try this query to find users who must change password on next login:
(objectCategory=user)(pwdLastSet=0)

Users whose password never expires
(objectcategory=user)(userAccountControl:1.2.840.113556.1.4.803:=65536)

http://www.selfadsi.org/ads-attributes/user-userAccountControl.htm
Chris DentPowerShell Developer
Top Expert 2010

Commented:
8388608 is password expired, not account expired (http://support.microsoft.com/kb/305144). As such, it won't work for accounts that have expired anyway.

Chris

Author

Commented:
Thanks Chris.

Your explanation is sound, and makes a whole lot of sense.
I will then just find the way to translate time into 100ths of seconds.

Cheers,

C.

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