Posted Sat, 14 Jul 2018 02:44:03 GMT by

I am trying to set up a handler for expiring accounts in AD to send notifications and to flag accounts to decommission once expired.


I was originally using the AD functions built in for querying the users, but it is exceedingly slow to perform a list object path, then walk through each to perform an additional get object dataset account to populate variables, then weed through each of those to identify the ones I need. It took 9 seconds to process a single user this way.


As an alternative we are looking to utilize the SQL ADsDSOObject interface instead so it's a single query and it populates a dataset with all of the responses with the properties we care about. The problem is the accountExpires field.


When executing this code:

<AMDATABASE ACTIVITY="sql_query" CONNECTIONTYPE="connection_string" CONNECTION="Provider=ADsDSOObject;Encrypt Password=False;Integrated Security=SSPI;Mode=Read;Bind Flags=0;ADSI Flag=-2147483648" RESULTDATASET="UserList">SELECT
objectCategory='user' AND
objectClass='organizationalPerson' AND
accountExpires&gt;=%Yesterday% AND


I get the response back from it, however, UserList.accountExpires shows as a blank value in the Variables view. When I attempt to view it through a msgbox I receive the error "[Main](Step 24) Syntax error: wrong type". If I display the VarType on it, I get a 9, which is a vbObject!


Has anyone attempted to view accountExpires through a SQL call? Does anyone know the properties that I need to access to view the actual value?


This also occurs when executed in this manner:


<AMDATABASE ACTIVITY="sql_query" CONNECTIONTYPE="connection_string" CONNECTION="Provider=ADsDSOObject;Encrypt Password=False;Integrated Security=SSPI;Mode=Read;Bind Flags=0;ADSI Flag=-2147483648" RESULTDATASET="UserList" AM_ENABLED="NO">&lt;LDAP://OU=Contractor,OU=Accounts,DC=MYCOMPANY,DC=com&gt;;(&amp;(objectCategory=user)(objectClass=organizationalPerson)(accountExpires&gt;=%Yesterday%)(accountExpires&lt;=%Today%));cn,samaccountname,mail,employeetype,manager,accountexpires,department,l,o,orgName,company;onelevel</AMDATABASE>


This is on Automate Standard on Windows Server 2016.

Posted Tue, 17 Jul 2018 01:18:50 GMT by


I think the date value coming from LDAP query is not seen as valid database date object type. I suggest you use the basic function IsDate(Var) and Day(DateExpr) or DateValue(Date) and see if you can access it that way. I hope this helps to get to what you are looking for.

Posted Tue, 17 Jul 2018 03:32:24 GMT by

accountExpires is not a date. It is a 64 bit integer/string value in Microsoft FileTime format (number of 100 nanoseconds since 1/1/1601 UTC. 100 nanosecond ticks are 1/10,000,000 of a second).


For example, 131693616000000000 is the FileTime corresponding to 04/28/2018 00:00:00 EDT


Given that it is a flat value, the ADsDSOObject call should be generating a string value for it, not a vbObject value. But, since it is a vbObject value, what parameters are on it that I can try to access?


I'm working around this using a PowerShell get-aduser query instead, but I'd prefer to keep it native to AutoMate.

Posted Fri, 14 Sep 2018 21:50:21 GMT by

I posted a response to this before, but it never went up for some reason. I have continued digging and have ultimately found this web site:


The back end VBScript that is utilized for ADO processing splits Integer8 items into objects with a HighPart and LowPart properties.


So, UserList.accountExpires.HighPart and UserList.accountExpires.LowPart are used to provide the two 32 bits of data required to reconstruct it.

Posted Fri, 14 Sep 2018 22:28:58 GMT by


You must be signed in to post in this forum.