How to query Active Directory to Fetch more than 1000 rows using Sql Server–Part II

Continuing from where I left in my previous post.  The Stored Procedure mentioned in my previous post cannot be used to query pwdlastset from AD since it is stored in Integer8Date format which cannot be interpreted by sql server.

So when there is a requirement to query pwdlastset from AD we need to use a VBScript which I created.

Before running the VBScript we need to create a table in the sql server database where the data queried from AD will be inserted. Following is the tsql script to create table in database

CREATE TABLE [dbo].[ADtable](
[pwdlastset] [datetime] NULL,
[networkaddress] [varchar](50) NULL,
[OperatingSystemServicePack] [varchar](50) NULL,
[OperatingSystem] [varchar](50) NULL,
[DNSHostName] [varchar](50) NULL,
[ComputerName] [varchar](50) NULL
) ON [PRIMARY]

Once the table is created we need to run the VBScript which follows

VBScript to query AD and load the data in a table in sql server
==================================
Dim lngTZBias,objShell, lngBiasKey, k
‘ Obtain local Time Zone bias from machine registry.
‘ This bias changes with Daylight Savings Time.
Set objShell = CreateObject(“Wscript.Shell”)
lngBiasKey = objShell.RegRead(“HKLMSystemCurrentControlSetControl” _
& “TimeZoneInformationActiveTimeBias”)
If (UCase(TypeName(lngBiasKey)) = “LONG”) Then
lngTZBias = lngBiasKey
ElseIf (UCase(TypeName(lngBiasKey)) = “VARIANT()”) Then
lngTZBias = 0
For k = 0 To UBound(lngBiasKey)
lngTZBias = lngTZBias + (lngBiasKey(k) * 256^k)
Next
End If

Set objShell = Nothing

Const ADS_SCOPE_SUBTREE = 2

Set objConnection = CreateObject(“ADODB.Connection”)
Set objCommand =   CreateObject(“ADODB.Command”)
objConnection.Provider = “ADsDSOObject”
objConnection.Open “Active Directory Provider”

Set objCOmmand.ActiveConnection = objConnection
objCommand.CommandText = _
“Select pwdlastset,networkAddress,OperatingSystemServicePack,OperatingSystem,DNSHostName,CN from ‘LDAP://DC=CONTOSO,DC=com’ ” _
& “Where objectClass=’computer'”
objCommand.Properties(“Page Size”) = 1000
objCommand.Properties(“Searchscope”) = ADS_SCOPE_SUBTREE
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst

Dim sqlconn,sqlcomm
set sqlconn = CreateObject(“ADODB.Connection”)
sqlconn.Provider = “SQLOLEDB;data source=WIN2K3R2EESQL2005;initial catalog=master;integrated security=SSPI”
sqlconn.open
set sqlcomm =  CreateObject(“ADODB.Command”)
Set sqlcomm.ActiveConnection = sqlconn
sqlcomm.CommandText = “truncate table ADtable1”
sqlcomm.Execute

Do Until objRecordSet.EOF
‘Wscript.Echo “Computer Name: ” & objRecordSet.Fields(“CN”).Value
‘ Specify the Value property of the Field object.

Set objDate = objRecordset.Fields(“pwdLastSet”).Value
‘ Invoke methods of the IADsLargeInteger interface directly.
lngHigh = objDate.HighPart
‘ Or use the Integer8Date function documented in the link above.
dtmDate = Integer8Date(objDate, lngTZBias)
‘Wscript.Echo “Password Last Set: ” & dtmdate

sqlcomm.CommandText = “insert into ADtable1(pwdlastset,networkAddress,OperatingSystemServicePack,OperatingSystem,DNSHostName,ComputerName) values(‘”& dtmDate & “‘,'” &   objRecordSet.Fields(“networkAddress”).Value _
& “‘,'” & objRecordSet.Fields(“OperatingSystemServicePack”).Value & “‘,'” &  objRecordSet.Fields(“OperatingSystem”).Value & “‘,'” &  objRecordSet.Fields(“DNSHostName”).Value & “‘,'” & objRecordSet.Fields(“CN”).Value & “‘)”

sqlcomm.Execute
objRecordSet.MoveNext
Loop

objRecordSet.Close
set sqlconn = Nothing
set objConnection = Nothing
set objcommand = Nothing
set sqlcomm = Nothing

Function Integer8Date(ByVal objDate, ByVal lngBias)
‘ Function to convert Integer8 (64-bit) value to a date, adjusted for
‘ local time zone bias.

Dim lngAdjust, lngDate, lngHigh, lngLow
lngAdjust = lngBias
lngHigh = objDate.HighPart
lngLow = objdate.LowPart
‘ Account for error in IADsLargeInteger property methods.
If (lngLow < 0) Then
lngHigh = lngHigh + 1
End If
If (lngHigh = 0) And (lngLow = 0) Then
lngAdjust = 0
End If
lngDate = #1/1/1601# + (((lngHigh * (2 ^ 32)) _
+ lngLow) / 600000000 – lngAdjust) / 1440
‘ Trap error if lngDate is ridiculously huge.
On Error Resume Next
Integer8Date = CDate(lngDate)
If (Err.Number <> 0) Then
On Error GoTo 0
Integer8Date = #1/1/1601#
End If
On Error GoTo 0
End Function

Further we can run the above VBScript as a SQL Agent (ActiveX Script) Job. The Job can be scheduled to periodically load table in sql server and we can further the table in sql server to get the Data from AD.

In the above VBScript I have hard-coded the query and designed the table specific to this query. However you can customize the VBScript and table according to your needs

Select pwdlastset,networkAddress,OperatingSystemServicePack,OperatingSystem,DNSHostName,CN from ‘LDAP://DC=CONTOSO,DC=com’ ” _
& “Where objectClass=’computer'”

Finally thanks to script and Function from Rich Mueller which helped to construct the above VBScript

Reference
======
http://www.rlmueller.net/Programs/Integer8Date.txt

Parikshit Savjani
Premier Field Engineer, Microsoft Services