Object returned in AD SQL Query?

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
cn,
SAMAccountName,
mail,
employeeType,
manager,
accountExpires,
department,
l,
o,
orgName,
company
FROM
'LDAP://OU=Contractor,OU=Accounts,DC=MYCOMPANY,DC=com'
Where
objectCategory='user' AND
objectClass='organizationalPerson' AND
accountExpires&gt;=%Yesterday% AND
accountExpires&lt;=%Today%</AMDATABASE>

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 11.0.6.11 Standard on Windows Server 2016.

Patrick,

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.

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.

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:

http://www.rlmueller.net/Integer8Attributes.htm

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.

test