[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 778
  • Last Modified:

Use SQL to query a recordset in vbscript

So I have a script that updates our active directory based on data from our employee records database. Currently I pull all records from a view of the employee database, and cycle through each record, then query AD baesd on the users employee ID.

So this means I query AD for everyone employee I cycle through. My script takes about two hours to complete for around 2500 users. I was wondering if it would be quicker to pull all of AD into a recordset, and rather then querying AD for every user, I could query the AD recordset which would be in memory.

I can't seem to find if this is possible with vbscript. I know I can search the recordset, but not using SQL. If anyone has any ideas for me that would be great.
0
colesy
Asked:
colesy
  • 3
  • 3
1 Solution
 
exx1976Commented:
You can write SQL queries against AD, like the example code below.

HTH,
exx
On Error Resume Next
Const ADS_SCOPE_SUBTREE = 2
Set oConn = CreateObject("ADODB.Connection")
Set oComm = CreateObject("ADODB.Command")
oConn.Provider = ("ADsDSOObject")
oConn.Open "Active Directory Provider"
oComm.ActiveConnection = objConnection
oComm.CommandText =  "SELECT sAMAccountName, homeDirectory FROM 'LDAP://dc=domainname,dc=com' WHERE objectCategory='user' AND sAMAccountName='testusername'"
oComm.Properties("SearchScope") = ADS_SCOPE_SUBTREE
Set objRecordSet = objCommand.Execute
If objRecordSet.RecordCount = 0 Then
    Wscript.Echo "The samAccount was not found"
Else
    Wscript.Echo "The user Home drive is: " & objRecordSet.Fields("homeDirectory").Value
End If

Open in new window

0
 
colesyAuthor Commented:
I know you can do that, which is what I am currently doing..  What I would like to do is take your objRecordSet results, and query those. So in my AD query I would do something like this.

oComm.CommandText  = select samaccountname, employeeID, surname, etc from AD where objectCategory='user'
Set objRecordSet = oComm.Execute

select * from objRecordset where employeeID = strEmployeeID

So that recordset would include all users within AD. Then I could query against that entire recordset rather then query AD every time.
0
 
exx1976Commented:
Ahh.  You had failed to mention that you were already querying AD using ADODB.  I thought you might have been doing it old-school.  LOL


Ok..  Well, you can't *query* a disconnected recordset, but you CAN use filters.  See example.  It's kind of quick and dirty, so you may need to fiddle with it, but you'll get the idea.


HTH,
exx
On Error Resume Next 
Const ADS_SCOPE_SUBTREE = 2 
Set oConn = CreateObject("ADODB.Connection") 
Set oComm = CreateObject("ADODB.Command") 
oConn.Provider = ("ADsDSOObject") 
oConn.Open "Active Directory Provider" 
oComm.ActiveConnection = objConnection 
oComm.CommandText =  "SELECT SAMAccountName, employeeID, sn FROM 'LDAP://dc=domainname,dc=com' WHERE objectCategory='user'" 
oComm.Properties("SearchScope") = ADS_SCOPE_SUBTREE 
Set oRS = oComm.Execute 
oRS.Filter = "SAMAccountName = 'JohnDoe'"
oRS.MoveFirst
If oRS.RecordCount > 0 Then
	Do While Not oRS.EOF
		WScript.Echo oRS.Fields("EmployeeID")
		oRS.MoveNext
	Loop
End If
oRS.Filter = ""

Open in new window

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
colesyAuthor Commented:
So could I set the filter inside my while statement as I cycle through employee_IDs from my employee records database?

Basically I would pull all records from the employee records database, then pull all records from AD.

Start my while to cycle through each record from the employee records, and inside that set my filter using the employee ID?
0
 
exx1976Commented:
That sounds like it should do what you want, yes.
0
 
colesyAuthor Commented:
I will test it out and let you know. Sounds like it might do what I want.
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now