Retrieving AccountExpirationDate property from AD

[Mrugson]

Hello,

I am trying to build a VB.NET application which is supposed to query AD and retrieve user account's expiration date and if account is expiring this month (or any given month for that matter) return DN of such account - target would be to write the data to spreadsheet for reporting purposes.

So far I've tried two ways of doing it:

1) This one retrieves information from all user accounts in AD and then determines if any of the expiration dates falls within specified time frame.

    Private Sub BackgroundWorker1_DoWork(ByVal sender As System.Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork
        'create connection to AD
        Dim dirEntry As DirectoryEntry = New DirectoryEntry
        dirEntry.Path = "LDAP://dc=TEAM,dc=vm,dc=com"

        dirEntry.Username = "Administrator"
        dirEntry.Password = "XXXXXXXXX"

        'search AD for all users and extract DN and AccountExpirationDate
        Dim dirSearcher As New DirectorySearcher(dirEntry)
        dirSearcher.PropertiesToLoad.Add("DistinguishedName")
        dirSearcher.PropertiesToLoad.Add("AccountExpirationDate")

        dirSearcher.Filter = "(&(objectCategory=person))"

        'check AccountExpirationDate for each user found
        'and check if account expires any day this month
        For i As Integer = 1 To 31
            Dim results As SearchResultCollection
            results = dirSearcher.FindAll()
            Dim result As SearchResult

            For Each result In results
                If result.Properties("AccountExpirationDate")(0) = i & "/" & Month(Today) & "/" & Year(Today) & "" Then
                    MsgBox(result.Properties("DistinguishedName")(0), MsgBoxStyle.Information, "Message")
                End If
            Next
        Next
    End Sub

With this one I get completely nothing - it goes past the query but then behaves as if there were no accounts expiring even with expiration date set to 1/1/1970 it doesn't return anything (I've set a test account with appropriate expiration date) - does not give any error though...

2) This one is using AccountExpirationDate as filter - should be a bit faster. Again goes past query but doesn't return anything, no errors...

    Private Sub BackgroundWorker1_DoWork(ByVal sender As System.Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork
        'create connection to AD
        Dim dirEntry As DirectoryEntry = New DirectoryEntry
        dirEntry.Path = "LDAP://dc=TEAM,dc=vm,dc=com"

        dirEntry.Username = "Administrator"
        dirEntry.Password = "XXXXXXX"

        'search AD for all users and extract DN and AccountExpirationDate
        'searcg only for user accounts with AccountExpirationDate
        'occuring any day this month
        For i As Integer = 1 To 31
            Dim dirSearcher As New DirectorySearcher(dirEntry)
            dirSearcher.PropertiesToLoad.Add("DistinguishedName")
            dirSearcher.PropertiesToLoad.Add("AccountExpirationDate")

            dirSearcher.Filter = "(&(AccountExpirationDate=" & i & "/" & Month(Today) & "/" & Year(Today) & ")(objectCategory=person))"

            Dim results As SearchResultCollection
            results = dirSearcher.FindAll()
            Dim result As SearchResult

            For Each result In results
                MsgBox(result.Properties("DistinguishedName")(0), MsgBoxStyle.Information, "Message")
            Next
        Next
    End Sub

I had some issues to get VBS AD doing exactly the same query working in past but once I got the date format correctly it works like a charm (tried the same here, also #date# and different formats but nothing worked). Now I need to put some GUI on top of it and I'd like to write the whole query in VB.NET rather than just getting the program to run VB scripts...

Any help much appriciated, thanks in advance!

Edited April 4, 2010 by Mrugson

[allen2]

You could do the job with a simple ldap query command:

dsquery * -filter "(&(objectCategory=Person)(objectClass=User)(!accountExpires=0)(!accountExpires=9223372036854775807)(accountExpires<=129170520000000000))" -attr distinguishedName -limit 10000

Where 129170520000000000 is the number of 100-nanosecond intervals since 12:00 AM January 1, 1601, in Coordinated Universal Time (UTC) for the 30/04/2010.

There is a good post on this subject there.

[Mrugson]

Ok, that works just great! Thanks a lot!

One more thing though - I would need it to list all user accounts expiring current month whenever it is run so I would need some way of counting 100 nanoseconds intervals since 1/1/1601 to get the correct number into a variable and put it into the query filter.

Do you know any easy way to do it?

Cheers!

[Mrugson]

I finally managed to get it working properly and found a bit easier way of translating date & time to the intervals. As it will only be used in GMT time zone didn't need to use the 'lngBias'.

Here's the code if anyone cares to look...

    Private Sub BackgroundWorker1_DoWork(ByVal sender As System.Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork
        Dim startDate As String = New DateTime(Year(Today), Month(Today), 1, 0, 0, 0)
        Dim endDate As String = New DateTime(Year(Today), Month(Today), 1, 0, 0, 0).AddMonths(1).AddDays(-1)

        Dim dtmDateValue As String
        Dim dtmAdjusted As String
        Dim lngSeconds As String
        Dim startDate64Bit As String
        Dim endDate64Bit As String

        dtmDateValue = CDate(startDate)
        dtmAdjusted = DateAdd("n", 0, dtmDateValue)
        lngSeconds = DateDiff("s", #1/1/1601#, dtmAdjusted)
        startDate64Bit = CStr(lngSeconds) & "0000000"

        dtmDateValue = CDate(endDate)
        dtmAdjusted = DateAdd("n", 0, dtmDateValue)
        lngSeconds = DateDiff("s", #1/1/1601#, dtmAdjusted)
        endDate64Bit = CStr(lngSeconds) & "0000000"

        Dim dirEntry As DirectoryEntry = New DirectoryEntry
        dirEntry.Path = "LDAP://dc=TEAM,dc=vm,dc=com"

        dirEntry.Username = "Administrator"
        dirEntry.Password = "XXXXXXXX"

        Dim dirSearcher As New DirectorySearcher(dirEntry)
        dirSearcher.PropertiesToLoad.Add("DistinguishedName")

        dirSearcher.Filter = "(&(objectCategory=Person)(objectClass=User)(accountExpires>=" & startDate64Bit & ")(accountExpires<=" & endDate64Bit & "))"

        Dim results As SearchResultCollection
        results = dirSearcher.FindAll()
        Dim result As SearchResult
        Dim strUser As String
        Dim oFSO As Object = CreateObject("Scripting.FileSystemObject")
        Dim oFile As Object = oFSO.OpenTextFile("OutputFile.txt", 8, True)

        For Each result In results
            strUser = result.Properties("DistinguishedName")(0)
            MsgBox(strUser, MsgBoxStyle.Information, "strUser")
            oFile.Write(strUser & vbCrLf)
        Next
    End Sub

allen2 - sorry if I appeard not to read the post you've provided a link to carefully... The method used to translate date & time was a bit to complicated for me to transform to VB.NET.

Thank you very much for all your help!

Edited April 7, 2010 by Mrugson