LDAP Query for Expired accounts

Adrian Rodriguez

unread,

Jan 16, 2006, 9:42:19 AM1/16/06

to

I need an LDAP query to list expired accounts under saved queries in AD
Users and Computers.

Any ideas?

Laura E. Hunter [MVP]

unread,

Jan 16, 2006, 12:00:49 PM1/16/06

to

As an alternate suggestion, you can use the FindExpAcc freeware tool from
http://www.joeware.net/win/free/tools/findexpacc.htm.

HTH

--
Laura E. Hunter: MVP Windows Server - Networking
All replies to newsgroup, please
Post provided as-is, no warranties expressed or implied

"Adrian Rodriguez" <arodr...@phillynews.com> wrote in message
news:OdZj%23JrGG...@TK2MSFTNGP15.phx.gbl...

Richard Mueller

unread,

Jan 16, 2006, 1:26:02 PM1/16/06

to

Hi,

Such a query is problematic. Here is an LDAP query that returns all user
objects that expired before 1/16/2006:

(&(objectCategory=person)(objectClass=user)(accountExpires<=127818648000000000))

The first two clauses restrict the query to user objects. Perhaps this could
be skipped and you could use:

(accountExpires<=127818648000000000)

The accountExpires attribute is Integer8, a 64-bit number representing the
number of 100-nanosecond intervals since 12:00 am 1/1/1601. The trick is to
find the number representing a given date. I see no way to save the query,
except with a specific hard coded date.

A VBScript program to convert any date/time value to the corresponding
Integer8 value:

http://www.rlmueller.net/Programs/DateToInteger8.txt

Adrian Rodriguez

unread,

Jan 17, 2006, 12:37:37 PM1/17/06

to

Ok this is a step in the right direction. I gues there is no room in ldap
queries for some equations?
Like (&(objectCategory=person)(objectClass=user)(accountExpires<=(equation
to figure thislong number to equal today)))

"Richard Mueller" <rlmuelle...@ameritech.NOSPAM.net> wrote in message
news:uk3MPpsG...@TK2MSFTNGP09.phx.gbl...

Richard Mueller

unread,

Jan 17, 2006, 2:02:39 PM1/17/06

to

Hi,

I know of no way to incorporate a function in an ldap query in ADUC, or any
other GUI. However, it can be done in a VBScript program.

Also, in testing the script below, I found that my ldap query is incomplete.
It retrieves users where accountExpires=0, which is the same as never. The
query should be (watch line wrapping):

(&(objectCategory=person)(objectClass=user)(accountExpires<=127818648000000000)(!accountExpires=0))

The "!" is the Not symbol. A VBScript program that displays the
Distinguished Names of all expired user accounts follows. It converts the
current date to the appropriate 64-bit value, then queries for all user
objects with accountExpires less than this value, but not equal to zero:

============================
Option Explicit

Dim dtmAdjusted, lngSeconds, str64Bit
Dim objShell, lngBiasKey, lngBias, k
Dim objRootDSE, strDNSDomain, objConnection, objRecordset
Dim strBase, strFilter, strAttributes, strQuery, strDN

' Obtain local Time Zone bias from machine registry.
Set objShell = CreateObject("Wscript.Shell")
lngBiasKey = objShell.RegRead("HKLM\System\CurrentControlSet\Control\" _
& "TimeZoneInformation\ActiveTimeBias")
If UCase(TypeName(lngBiasKey)) = "LONG" Then
lngBias = lngBiasKey
ElseIf UCase(TypeName(lngBiasKey)) = "VARIANT()" Then
lngBias = 0
For k = 0 To UBound(lngBiasKey)
lngBias = lngBias + (lngBiasKey(k) * 256^k)
Next
End If

' Convert current date/time value to UTC.
dtmAdjusted = DateAdd("n", lngBias, Now)

' Find number of seconds since 1/1/1601.
lngSeconds = DateDiff("s", #1/1/1601#, dtmAdjusted)

' Convert the number of seconds to a string
' and convert to 100-nanosecond intervals.
str64Bit = CStr(lngSeconds) & "0000000"

' Determine DNS domain name.
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")

' Use ADO to search Active Directory.
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objRecordset = CreateObject("ADODB.Recordset")
objRecordset.ActiveConnection = objConnection

' Search entire domain.
strBase = "<LDAP://" & strDNSDomain & ">"

' Filter on expired user accounts.
strFilter = "(&(objectCategory=person)(objectClass=user)" _
& "(accountExpires<=" & str64Bit & ")(!accountExpires=0))"

' Retrieve Distinguished Names.
strAttributes = "distinguishedName"

' Use ADO to query AD.
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
objRecordset.Source = strQuery
objRecordset.Open

' Enumerate expired user accounts.
Do Until objRecordSet.EOF
strDN = objRecordSet.Fields("distinguishedName")
Wscript.Echo strDN
objRecordSet.MoveNext
Loop

' Clean up.
objRecordset.Close
objConnection.Close
Set objRootDSE = Nothing
Set objConnection = Nothing
Set objRecordSet = Nothing
============================

If the VBScript above is in a file called ExpiredAccts.vbs, you can run it
at a command prompt and redirect the output to a text file with the command:

cscript //nologo ExpiredAccts.vbs > Expired.txt

--
Richard
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net

"Adrian Rodriguez" <arodr...@phillynews.com> wrote in message
news:%23qUO2y4...@TK2MSFTNGP15.phx.gbl...

/kj

unread,

Jan 17, 2006, 3:23:00 PM1/17/06

to

Thanks Richard! The date calculation and usage example will be immensely
helpful.

/kj

"Richard Mueller" <rlmuelle...@ameritech.NOSPAM.net> wrote in message

news:%238yzWi5...@TK2MSFTNGP11.phx.gbl...

Adrian Rodriguez

unread,

Jan 24, 2006, 11:15:58 AM1/24/06

to

the !accountexpires did the trick THANKS - You Da Man!

"Richard Mueller" <rlmuelle...@ameritech.NOSPAM.net> wrote in message

news:%238yzWi5...@TK2MSFTNGP11.phx.gbl...

to...@taylor-townsend.com

unread,

Feb 15, 2006, 1:31:19 PM2/15/06

to

So if I want to narrow down to a specific range, like for just the
month of March, how would that work?

Richard Mueller

unread,

Feb 15, 2006, 1:54:58 PM2/15/06

to

Hi,

You can use the operators >= and <= (< and > are not supported) to filter on
a range of dates. You have to use a program like the one I linked earlier in
this thread to convert a given date to the equivalent 64-bit value. In my
timezone, finding accounts that expire in March of 2006 could be found with
this filter:

(&(accountExpires>=127856664000000000)(accountExpires<=127883448000000000))

The program I used to get the 64-bit values is linked here:

http://www.rlmueller.net/Programs/DateToInteger8.txt

The values you get depend slightly on the time zone info in your local
registry. The values end in at least 7 zeros because I round to the nearest
second.

--
Richard
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net

<to...@taylor-townsend.com> wrote in message
news:1140028279....@g47g2000cwa.googlegroups.com...