Active Directory UDF - Help & Support (III)

[martin-8sy]

Hello there,

first of all, i want to say thank you for this awesome UDF. 

But since i upgraded to Windows 10 Build 1803 the UDF isnt working for me. AD 1.4.9.0, AutoIT v3.3.14.5

Can anyone  confirm that?

Not even the exampe _AD_Open.au3 works. "_AD_Open encountered a problem. @error = 4, @extended = -2147024843

[martin-8sy]

Thanks for your attention. I’m looking forward to your reply.

br

Martin

[water]

Unfortunately I'm still on Windows 7, so can't test.
But in the wiki you find some Debugging Information: https://www.autoitscript.com/wiki/Active_Directory_UDF_-_General#Debugging

[water]

The value of @Extended translates to: 0x80070035 The network path was not found

On Windows 7 I get this message when the executable is saved to a location that is not regarded as a secure location.
Secure locations are defined by group policies. I suggest to copy the exe to another directory and try again or ask your sysadmin.

[martin-8sy]

Thanks for the fast reply. I didnt compiled my script. But indeed the AutoIt3.exe, the ad udf and the au3 file is stored on a Turnkey based Network drive. I try it out with a local copy tomorrow. But its strange that everything works perfectly on 1709 and all perivous builds until the automatic windows update installed the 1803 build.

I will give an update tomorrow.

[AdamUL]

This may have something to do about it.  

 

Adam

[martin-8sy]

I was so curious that i logged in via VPN to have a test drive. You are totally right. When the  AutoIt3.exe is stored on a turnkey file share, the network  function was blocked. Stored on a local disk or even a Network Share provided  by Windows server 2012 R2 , everything works! 

I tried to force turnkey to not use smbv1 by adding a line to conf: server min protocol = SMB2

But this didnt work. I also tried to add the server to trusted sites in IE, but neither work. So to keep it simple i copy the programm files to a Windows Server Share. By the way, only the exe and other AutoIT runtime files need to be moved. The AD UDFs and my au3 File can stay on the turnkey share for now.

Thanks for the help!

[Valnurat]

I have this and it is working as it should, but I would like to have extra filter on it. 

$aObjects = _AD_GetObjectsInOU("OU=firm,DC=ad,DC=firm,DC=org", "(&(objectcategory=person)(objectclass=user)(physicalDeliveryOfficeName=" & $aOUs[$i] & "*))", 2, "sAMAccountName,distinguishedName,displayname", "displayname")

I would like to have that "distinguishedName", but I want to filter on only "OU=Users" or "OU=Consultants"

The distinguishedName looks like this:

CN=name,OU=Users,OU=XX,OU=ZZ,OU=firm,DC=ad,DC=firm,DC=org
CN=name,OU=Consultants,OU=XX,OU=ZZ,OU=firm,DC=ad,DC=firm,DC=org

Not sure how to deal with that.

[water]

Easiest way would be to query both OUs (specify them as parameter 1) and then combine the returned arrays.

[Valnurat]

I'm sorry, you lost me. You want me to run it two time?

[water]

Exactly. The following solution returnes a 0-based array (without the record count in row 0):

Global $aResult, $aResult1, $aResult2, $aTemp[0]
Global $sOU1 = "CN=name,OU=Users,OU=XX,OU=ZZ,OU=firm,DC=ad,DC=firm,DC=org"
Global $sOU2 = "CN=name,OU=Consultants,OU=XX,OU=ZZ,OU=firm,DC=ad,DC=firm,DC=org"
$aResult1 = _AD_GetObjectsInOU($sOU1, "(&(objectcategory=person)(objectclass=user)(physicalDeliveryOfficeName=" & $aOUs[$i] & "*))", 2, "sAMAccountName,distinguishedName,displayname", "displayname")
$aResult2 = _AD_GetObjectsInOU($sOU2, "(&(objectcategory=person)(objectclass=user)(physicalDeliveryOfficeName=" & $aOUs[$i] & "*))", 2, "sAMAccountName,distinguishedName,displayname", "displayname")
If IsArray($aResult1) And IsArray($aResult2) Then
    _ArrayConcatenate($aResult1, $aResult2, 1) ; Combine Result1 and Result2. Start with record 1 and drop the count of returned elements
ElseIf IsArray($aResult1) Then
    _ArrayConcatenate($aTemp, $aResult1, 1) ; Only Result1 returned an array. Start with record 1 and drop the count of returned elements
Else
    _ArrayConcatenate($aTemp, $aResult2, 1) ; Only Result2 returned an array. Start with record 1 and drop the count of returned elements
EndIf

[Valnurat]

Okay, now you said the easiest way.

What is the hardest way?

[water]

Trying to do it in one single query - but at the moment I do not know how to do it

[Valnurat]

no problem.

[Subz]

Hey water

First BIG Kudos for all of your UDFs and your ongoing support.

Have been using AD UDF over the past few years, especially for internal Microsoft license audits, I run a report which gets the last logon date, anything above 90 days is not counted and anything that is 0 is also not counted, we have a custom attribute to say if its a real persons account not a service or support type account.  However the report takes over 20 hours to complete namely because of _AD_GetLastLoginDate function, which looped through the results of _AD_GetObjectsinOU to obtain the lastLogon date and time.

So after some playing around found that I could cut the time down to less than 10 minutes by running a modified version of _AD_GetObjectsInOU, by running the LDAP queries against each domain controller, which does give me 100000+ entries in an array, majority are duplicates from each DC, I'm currently looking at ways to remove all the duplicates, just deciding how to do it, either within the custom function or separate, but this should still only take a few minutes to do.

Anyway the question is do you know of a better method to do this, maybe another function which I may have missed or maybe someone has requested something similar previously, i couldn't find anything in my search, so thought I'd ask.

[water]

Glad you like the AD UDF (which originally has been written by Jonathan Clelland)

Using _AD_GetLastLoginDate for >100,000 really takes a long time.

You could reduce the run time by:

• _AD_GetLastLoginDate: Specifying the site for which the domain controllers will be queried (parameter $sSite) or by creating the list of Docain Controllers to query in advance and then pass it to _AD_GetLastLoginDate as parameter $aDCList
• _AD_GetLastLoginDate: This function returns the REAL last login date. You could just query lastLogontimeStamp attribute by calling _AD_GetObjectProperties for each entry in the array. This attribute is not as accurate as the data returned by _AD_GetLastLoginDate as described here: https://blogs.technet.microsoft.com/askds/2009/04/15/the-lastlogontimestamp-attribute-what-it-was-designed-for-and-how-it-works/
• _AD_GetObjectsInOU: Depends on the LDAP query you use. Make sure all attributes you query are indexed.
  if you can post your query I can have a look at it.
   

[Subz]

Hey Water thanks for the feedback, sorry may not have explained it correctly:
The Organisation has:

1 Domain
15 Physical Sites
15 Domain Controllers
7000 User Accounts in total spread across 15 sites, staff tend to roam between sites.

Script which took 20+ hours  to complete, my understanding is that _AD_GetLastLoginDate accepts only one object at a time and compares that object against all 15 DCs, which would mean 7000 Objects * 15 DC Connections or a grand total of 105000 connections.  Is that correct or have I got my wires crossed.

$aAD_Objects = _AD_GetObjectsInOU("", _                                                                     ;~ OU
"(&(&(objectcategory=person)(objectclass=user)(samaccountname=*)))", _                                      ;~ Filter
2, _                                                                                                        ;~ Subtree
"samaccountname,givenName,sn,mail,lastLogon,accountexpires,st,department,whencreated,manager,description")  ;~ Data to Retrieve. (lastLogon is a place holder)

For $i = 1 To $aADObjects[0][0]
    $aAD_Objects[$i][4] = _AD_GetLastLoginDate($aAD_Objects[$i][0])                                         ;~ Lastlogon from all Domain Controllers
Next

The following script which is a modified _AD_GetObjectsInOU only takes 10 minutes to complete, since we're only having to perform only 15 connections, 1 for each DC. we than compare the previous DC captured lastLogon results against the current results and so afterwards we're able to get the real a list of attributes + real lastLogon.

Hope that makes sense.

Thanks again.

expandcollapse popup

#include <AD.au3>
#include <Debug.au3>

Local $aAD_Objects[1][12], $aDCServers
_AD_Open()
    $aDCServers = _AD_ListDomainControllers()
    For $i = 1 To $aDCServers[0][0]
        _AD_GetObjectsInOUByDC($aAD_Objects,                                                                        _ ;~ 2D Array Variable
        "",                                                                                                         _ ;~ OU
        "(&(&(objectcategory=person)(objectclass=user)(samaccountname=*)))",                                        _ ;~ Filter
        2,                                                                                                          _ ;~ Subtree
        "sAMAccountName,givenName,sn,mail,lastLogon,accountexpires,st,department,whencreated,manager,description",  _ ;~ Data to Retrieve. (lastLogon is a place holder)
        "sAMAccountName",                                                                                           _ ;~ SortBy
        False,                                                                                                      _ ;~ bCount
        True,                                                                                                       _ ;~ Return Null
        $aDCServers[$i][2])                                                                                           ;~ Domain Controller
    Next
_DebugArrayDisplay($aAD_Objects)

Func _ConvertLastLogon($_oAD_LastLogon)
    Local $sAD_DTStruct, $sLocalTime
    If $_oAD_LastLogon.LowPart = -1 Then Return 0
    If $_oAD_LastLogon.LowPart > 0 And $_oAD_LastLogon.HighPart > 0 Then
        $sAD_DTStruct = DllStructCreate("dword low;dword high")
        DllStructSetData($sAD_DTStruct, "Low", $_oAD_LastLogon.LowPart)
        DllStructSetData($sAD_DTStruct, "High", $_oAD_LastLogon.HighPart)
        $sSystemTime = _Date_Time_FileTimeToSystemTime(DllStructGetPtr($sAD_DTStruct))
        $sLocalTime = _Date_Time_SystemTimeToTzSpecificLocalTime(DllStructGetPtr($sSystemTime))
        Return _Date_Time_SystemTimeToDateTimeStr($sLocalTime, 1)
    EndIf
EndFunc


; #FUNCTION# ====================================================================================================================
; Name...........: _AD_GetObjectsInOUByDC
; Description ...: Create/Update filtered array of objects and attributes for a given OU or just the number of records if $bCount is True.
; Syntax.........: _AD_GetObjectsInOUByDC(ByRef $_aAD_Objects, $sOU[, $sFilter = "(name=*)"[, $iSearchScope = 2[, $sDataToRetrieve =  "sAMAccountName"[, $sSortBy = "sAMAccountName"[, $bCount = False[, $vReturnNull = True[, $_sAD_HostServer] = $sAD_HostServer]]]]])
; Parameters ....: $_aObjects - Variable to hold returned data
;                  $sOU - The OU to retrieve from (FQDN) (default = "", equals "search the whole AD tree")
;                  $sFilter - Optional: An additional LDAP filter if required (default = "(name=*)")
;                  $iSearchScope - Optional: 0 = base, 1 = one-level, 2 = sub-tree (default)
;                  $sDataToRetrieve - Optional: A comma-seperated list of attributes to retrieve (default = "sAMAccountName").
;                  |More than one attribute will create a 2-dimensional array
;                  $sSortBy - Optional: name of the attribute the resulting array will be sorted upon (default = "sAMAccountName").
;                  |To completely suppress sorting (even the default sort) set this parameter to "". This improves performance when doing large queries
;                  $bCount - Optional: If set to True only returns the number of records returned by the query (default = False)
;                  $vReturnNull - Optional: If set to any other value but True Null values (occur when the property has never been set) are returned as this value (default = True)
;                  $_sAD_HostServer - Optional: Name of Domain Controller (default = $sAD_HostServer defined in AD.au3 UDF)
; Return values .: Success - Number of records retrieved or a one or two dimensional one-based array of objects and attributes in the given OU. First entry is for the given OU itself
;                  Failure - "", sets @error to:
;                  |1 - Specified OU does not exist
;                  |2 - No records returned from Active Directory. $sDataToRetrieve is invalid (attribute may not exist). @extended is set to the error returned by LDAP
;                  |3 - No records returned from Active Directory. $sFilter didn't return a record
; Author ........: Jonathan Clelland
; Modified.......: water
; Remarks .......: Multi-value attributes are returned as string with the pipe character (|) as separator.
;+
;                  The default filter returns an array including one record for the OU itself. To exclude the OU use a different filter that doesn't include the OU
;                  e.g. "(&(objectcategory=person)(objectclass=user)(name=*))"
;+
;                  To make sure that all properties you specify in $sDataToRetrieve exist in the AD you can use _AD_ObjectExistsInSchema.
;+
;                  The following examples illustrate the use of the escaping mechanism in the LDAP filter:
;                    (o=Parens R Us \28for all your parenthetical needs\29)
;                    (cn=*\2A*)
;                    (filename=C:\5cMyFile)
;                    (bin=\00\00\00\04)
;                    (sn=Lu\c4\8di\c4\87)
;                  The first example shows the use of the escaping mechanism to represent parenthesis characters.
;                  The second shows how to represent a "*" in a value, preventing it from being interpreted as a substring indicator.
;                  The third illustrates the escaping of the backslash character.
;                  The fourth example shows a filter searching for the four-byte value 0x00000004, illustrating the use of the escaping mechanism to
;                  represent arbitrary data, including NUL characters.
;                  The final example illustrates the use of the escaping mechanism to represent various non-ASCII UTF-8 characters.
; Related .......: _AD_GetAllOUs
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _AD_GetObjectsInOUByDC(ByRef $_aAD_Objects, $sOU = "", $sFilter = "(name=*)", $iSearchScope = 2, $sDataToRetrieve = "sAMAccountName", $sSortBy = "sAMAccountName", $bCount = False, $vReturnNull = True, $_sAD_HostServer = $sAD_HostServer)
    If $sOU = Default Then $sOU = ""
    If $sFilter = Default Then $sFilter = "(name=*)"
    If $iSearchScope = Default Then $iSearchScope = 2
    If $sDataToRetrieve = "" Then $sDataToRetrieve = "sAMAccountName"
    If $sDataToRetrieve = Default Then $sDataToRetrieve = "sAMAccountName"
    $sDataToRetrieve = StringStripWS($sDataToRetrieve, 8)
    If StringInStr($sDataToRetrieve, "lastLogon") Then
        If Not StringInStr($sDataToRetrieve, "sAMAccountName") Then $sDataToRetrieve &= ",sAMAccountName"
    EndIf
    If $sSortBy = Default Then $sSortBy = "sAMAccountName"
    If $bCount = Default Then $bCount = False
    If $vReturnNull = Default Then $vReturnNull = True
    If $sOU = "" Then
        $sOU = $sAD_DNSDomain
    Else
        If _AD_ObjectExists($sOU, "distinguishedName") = 0 Then Return SetError(1, 0, "")
    EndIf
    Local $sReturnNull = ""
    If Not IsBool($vReturnNull) Then
        $sReturnNull = $vReturnNull
        $vReturnNull = False
    EndIf
    Local $iCount2, $aDataToRetrieve, $aTemp
    $__oAD_Command.Properties("Searchscope") = $iSearchScope
    $__oAD_Command.CommandText = "<LDAP://" & $_sAD_HostServer & "/" & $sOU & ">;" & $sFilter & ";" & $sDataToRetrieve
    $__oAD_Command.Properties("Sort On") = $sSortBy
    Local $oRecordSet = $__oAD_Command.Execute
    If @error Or Not IsObj($oRecordSet) Then Return SetError(2, @error, "")
    Local $iCount1 = $oRecordSet.RecordCount
    If $iCount1 = 0 Then
        If $bCount Then Return SetError(3, 0, 0)
        Return SetError(3, 0, "")
    EndIf
    If $bCount Then Return $iCount1
    If StringInStr($sDataToRetrieve, ",") Then
        $aDataToRetrieve = StringSplit($sDataToRetrieve, ",")
        ;~ Get samaccount attribute index
        Local $_isAMAccount = _ArraySearch($aDataToRetrieve, "sAMAccountName")
            $_isAMAccount = $_isAMAccount = -1 ? -1 : $_isAMAccount - 1
        ;~ Get lastLogon attribute index
        Local $_ilastLogon = _ArraySearch($aDataToRetrieve, "lastLogon")
            $_ilastLogon = $_ilastLogon = -1 ? -1 : $_ilastLogon - 1
        $_aAD_Objects[0][1] = $aDataToRetrieve[0]
        Local $_iSearch, $_aTemp[1][$aDataToRetrieve[0]]
        $oRecordSet.MoveFirst
        Do
            For $iCount1 = 1 To $aDataToRetrieve[0]
                If IsArray($oRecordSet.Fields($aDataToRetrieve[$iCount1]).Value) Then
                    $aTemp = $oRecordSet.Fields($aDataToRetrieve[$iCount1]).Value
                    $_aTemp[0][$iCount1 - 1] = _ArrayToString($aTemp)
                Else
                    $_aTemp[0][$iCount1 - 1] = $oRecordSet.Fields($aDataToRetrieve[$iCount1]).Value
                    If Not $vReturnNull And IsKeyword($_aTemp[0][$iCount1 - 1]) = $KEYWORD_NULL Then $_aTemp[0][$iCount1 - 1] = $sReturnNull
                EndIf
            Next
            $_iSearch = _ArraySearch($_aAD_Objects, $_aTemp[0][$_isAMAccount], 0, 0, 0, 0, 1, $_isAMAccount)
            If $_iSearch > -1 Then
                If _DateIsValid($_aAD_Objects[$_iSearch][$_ilastLogon]) Then
                    If _DateIsValid(_ConvertLastLogon($_aTemp[0][$_ilastLogon])) Then
                        $_aAD_Objects[$_iSearch][$_ilastLogon] = _DateDiff("s", $_aAD_Objects[$_iSearch][$_ilastLogon], _ConvertLastLogon($_aTemp[0][$_ilastLogon])) < 0 ? $_aAD_Objects[$_iSearch][$_ilastLogon] : _ConvertLastLogon($_aTemp[0][$_ilastLogon])
                    EndIf
                EndIf
            Else
                $_aTemp[0][$_ilastLogon] = _ConvertLastLogon($_aTemp[0][$_ilastLogon])
                _ArrayAdd($_aAD_Objects, $_aTemp)
            EndIf
            $oRecordSet.MoveNext
            $iCount2 += 1
        Until $oRecordSet.EOF
    Else
        Local $_aAD_Objects[$iCount1 + 1]
        $_aAD_Objects[0] = UBound($_aAD_Objects) - 1
        $iCount2 = 1
        $oRecordSet.MoveFirst
        Do
            If IsArray($oRecordSet.Fields($sDataToRetrieve).Value) Then
                $aTemp = $oRecordSet.Fields($sDataToRetrieve).Value
                $_aTemp[$iCount2] = _ArrayToString($aTemp)
            Else
                $_aTemp[$iCount2] = $oRecordSet.Fields($sDataToRetrieve).Value
            EndIf
            _ArrayAdd($_aAD_Objects, $_aTemp)
            $oRecordSet.MoveNext
            $iCount2 += 1
        Until $oRecordSet.EOF
    EndIf
    $__oAD_Command.Properties("Sort On") = "" ; Reset sort property
    $_aAD_Objects[0][0] = UBound($_aAD_Objects) - 1
EndFunc   ;==>_AD_GetObjectsInOUByDC

 

[water]

7 minutes ago, Subz said:

Script which took 20+ hours  to complete, my understanding is that _AD_GetLastLoginDate accepts only one object at a time and compares that object against all 15 DCs, which would mean 7000 Objects * 15 DC Connections or a grand total of 105000 connections.  Is that correct or have I got my wires crossed.

That's correct.

Function _AD_GetObjectsInOU is the swiss army knive of the AD UDF. So there might be situations where performance is bad.
Maybe I should add a parameter for a function to be called for every record in the recordset?

[Subz]

Quote

Maybe I should add a parameter for a function to be called for every record in the recordset?

I'm not sure I understand?  Do you mean a function that will return all instances of "lastLogon" or "User.LastLogin"?  That would be handy, the only thing is that would need to also include the sAMAccountName or some identifier The ability to define the "$sAD_HostServer" in _AD_GetObjectsinOU() would also be a good addition imho.

[water]

No, it was a more general idea. Not related to your problem.

As the number of connections and the number of DCs to query slows down the script I would try the following to achieve the goal with the unmodified UDF (untested):

_AD_Open to connect to AD
Retrieve the list of DCs by calling _AD_ListDomainControllers ($aDCs)
_AD_Close the connection
Loop through the list of domain controllers in $aDCs
    Call _AD_GetObjectsInOU to retrieve the users of this DC
    Create a one based 2D array with one row/7 columns and copy the row from ($aDCs) to this array ($aDC)
    Loop through all returned users
        call _AD_GetLastLoginDate and set parameter 3 to $aDC
    EndLoop
EndLoop

This approach queries DC by DC for a list of users and then only queries the same DC for the last logindate.
Might be slower than your solution but uses the default AD UDF

Edited July 1, 2018 by water