Re: LDAP Query for Expired accounts

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

/kj
"Richard Mueller" <rlmueller-NOSPAM@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:%238yzWi5GGHA.2320@xxxxxxxxxxxxxxxxxxxxxxx
> 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 ... 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: Days Since Last Login IN ADUC Query
    ... Thanks again for your response Richard! ... Microsoft MVP Scripting and ADSI ... value is updated at logon only if the old value is more than 14 days old. ... I have created a query at the Root of the domain (and in varios OU's ...
    (microsoft.public.windows.server.active_directory)
  • Re: LDAP Query for Expired accounts
    ... it can be done in a VBScript program. ... Also, in testing the script below, I found that my ldap query is incomplete. ... Distinguished Names of all expired user accounts follows. ...
    (microsoft.public.windows.server.active_directory)