Re: LDAP Query for Expired accounts

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" <arodriguez@xxxxxxxxxxxxxx> wrote in message
news:%23qUO2y4GGHA.1032@xxxxxxxxxxxxxxxxxxxxxxx
> 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" <rlmueller-NOSPAM@xxxxxxxxxxxxxxxxxxxx> wrote in message
> news:uk3MPpsGGHA.2680@xxxxxxxxxxxxxxxxxxxxxxx
>> 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
>>
>> More on ADO searches:
>>
>> http://www.rlmueller.net/ADOSearchTips.htm
>>
>> More on Integer8 attributes:
>>
>> http://www.rlmueller.net/Integer8Attributes.htm
>>
>> --
>> Richard
>> Microsoft MVP Scripting and ADSI
>> Hilltop Lab - http://www.rlmueller.net
>>
>> "Laura E. Hunter [MVP]" <nospamplease> wrote in message
>> news:%238j4A6rGGHA.2652@xxxxxxxxxxxxxxxxxxxxxxx
>>> 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" <arodriguez@xxxxxxxxxxxxxx> wrote in message
>>> news:OdZj%23JrGGHA.3700@xxxxxxxxxxxxxxxxxxxxxxx
>>>>I need an LDAP query to list expired accounts under saved queries in AD
>>>>Users and Computers.
>>>>
>>>> Any ideas?
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>


.

Relevant Pages

  • Re: LDAP Query for Expired accounts
    ... Also, in testing the script below, I found that my ldap query is ... Distinguished Names of all expired user accounts follows. ... Dim objShell, lngBiasKey, lngBias, k ... Set objConnection = CreateObject ...
    (microsoft.public.windows.server.active_directory)
  • Re: LDAP Query for Expired accounts
    ... > Also, in testing the script below, I found that my ldap query is ... > Distinguished Names of all expired user accounts follows. ... > Dim objShell, lngBiasKey, lngBias, k ...
    (microsoft.public.windows.server.active_directory)
  • Re: LDAP Query for Expired accounts
    ... Also, in testing the script below, I found that my ldap query is ... Distinguished Names of all expired user accounts follows. ... Dim objShell, lngBiasKey, lngBias, k ... Set objConnection = CreateObject ...
    (microsoft.public.windows.server.active_directory)
  • Re: LDAP Query for Expired accounts
    ... Thanks Richard! ... > Also, in testing the script below, I found that my ldap query is ... > Distinguished Names of all expired user accounts follows. ... > Dim objShell, lngBiasKey, lngBias, k ...
    (microsoft.public.windows.server.active_directory)
  • Re: LDAP query information
    ... execution of the vbscript? ... Microsoft MVP Scripting and ADSI ... Dim strBase, strFilter, strAttributes, strQuery, adoRecordset ... ' Construct LDAP syntax query. ...
    (microsoft.public.windows.server.scripting)
Loading