Query 2 different recordsets for matching values

[tyant]

I want to query 2 different recordsets for matching values and I don't know how to. I need to run down through one set of records and search the second set of records for matching values

This is what I have so far

On Error Resume Next

Const ADS_SCOPE_SUBTREE = 2
Const ForReading = 1
Const ForAppending = 1
Dim Stuff, myFSO, WriteStuff

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

objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE

objCommand.CommandText = _
"SELECT AdsPath,samAccountName,Name,userPrincipalName,mailNickName,mail,givenName,sn FROM 'LDAP://Server1/dc=domain,dc=win' WHERE objectCategory='user'"





Set objConnection2 = CreateObject("ADODB.Connection")
Set objCommand2 = CreateObject("ADODB.Command")
objConnection2.Provider = "ADsDSOObject"
objConnection2.Open "Active Directory Provider"
Set objCommand2.ActiveConnection = objConnection

objCommand2.Properties("Page Size") = 1000
objCommand2.Properties("Searchscope") = ADS_SCOPE_SUBTREE

objCommand2.CommandText = _
"SELECT AdsPath,samAccountName,Name,userPrincipalName,mailNickName,mail,givenName,sn FROM 'LDAP://Server2/dc=Domain2,dc=win' WHERE objectCategory='user'"


Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst
Do Until objRecordSet.EOF

strUser = objRecordSet.Fields("Name").Value
strUser2 = objRecordSet2.Fields("Name").Value

If strUser = strUser2 Then

Set myFSO = CreateObject("Scripting.FileSystemObject")
Set WriteStuff = myFSO.OpenTextFile("Usermatch.csv", 8, True)

WriteStuff.WriteLine (strUser & "," & strUser2)
WriteStuff.Close
SET WriteStuff = NOTHING
SET myFSO = NOTHING
End If
objRecordSet.MoveNext


Loop


Any help would be cool

 

[tsuji]

You better use an attribute unique across the forest to find out which users exist on the two servers, if that is the purpose. sAMAccountName would be a better choice for the matching. Also remove unnecessary attributes to return to the resultset, it affects performance. So it looks roughly like this. Look out for detail yourself.
[tt]
[red]'[/red]On Error Resume Next

Const ADS_SCOPE_SUBTREE = 2
Const ForReading = 1
Const ForAppending = [red]8[/red]
'etc etc

[red]'[/red]objCommand2.CommandText = _
[red]'[/red] "SELECT AdsPath,samAccountName,Name,userPrincipalName,mailNickName,mail,givenName,sn FROM 'LDAP://Server2/dc=Domain2,dc=win' WHERE objectCategory='user'"

[blue]'moved up[/blue]
[blue]Set myFSO = CreateObject("Scripting.FileSystemObject")
Set WriteStuff = myFSO.OpenTextFile("Usermatch.csv", 8, True)[/blue]

Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst
Do Until objRecordSet.EOF

[red]'[/red]strUser = objRecordSet.Fields("Name").Value
[red]'[/red]strUser2 = objRecordSet2.Fields("Name").Value
[blue]strSamAccountName=objRecordSet.Fields("sAMAccountName").Value
objCommand2.CommandText = _
"SELECT AdsPath,samAccountName,Name,userPrincipalName,mailNickName,mail,givenName,sn " & _
"FROM 'LDAP://Server2/dc=Domain2,dc=win' WHERE objectCategory='user' and " & _
"sAMAccountName='" & strSamAccountName & "'"
Set objRecordSet2 = objCommand2.Execute
if not objRecordSet2.EOF then[/blue]
WriteStuff.WriteLine [blue]objRecordSet.Fields("Name").Value & "," & objRecordSet2.Fields("Name").Value[/blue]
[blue]end if
objRecordSet2.Close
set objRecordSet2=Nothing[/blue]
objRecordSet.MoveNext
Loop
[blue]'moved down[/blue]
WriteStuff.Close
SET WriteStuff = NOTHING
SET myFSO = NOTHING

objRecordSet.Close
Set objRecordSet=Nothing
[/tt]

 

[tyant]

Thanks for your help

One Problem. The Case is different on other domains

Need to compare all in UpperCase or LowerCase to get a proper result.

Tried using Upper and Ucase but no values are being returned

Thanks

 

[PHV]

Need to compare all in UpperCase
If UCase(strUser) = UCase(strUser2) Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tyant]

In the script, only one record set has a string defined. The other is pulling the information directly from the SQL statement which is selecting the data

 

[PHV]

You don't know how to use the UCase function ?
What is your ctual code and where are you stuck with the uppercase conversion ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tyant]

I know how to use it.

It doesn't work inside string I'm using to query. If the case of sAMAccountName was the same as the string strSamAccountName it would be fine but because its matching directly from an SQL LDAP lookup and sAMAccountName is not defined, it doesn't seem to work

"SELECT AdsPath,samAccountName,Name,userPrincipalName,mailNickName,mail,givenName,sn " & _
"FROM 'LDAP://Server2/dc=Domain2,dc=win' WHERE objectCategory='user' and " & _
"sAMAccountName='" & strSamAccountName & "'

 

[PHV]

Perhaps this ?
"SELECT AdsPath,samAccountName,Name,userPrincipalName,mailNickName,mail,givenName,sn " & _
"FROM 'LDAP://Server2/dc=Domain2,dc=win' WHERE objectCategory='user' and " & _
"sAMAccountName='" & UCase(strSamAccountName) & "'"
Or this ?
"SELECT AdsPath,samAccountName,Name,userPrincipalName,mailNickName,mail,givenName,sn " & _
"FROM 'LDAP://Server2/dc=Domain2,dc=win' WHERE objectCategory='user' and " & _
"sAMAccountName='" & LCase(strSamAccountName) & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tyant]

That's pretty good but the only results it will return is if the second domain has a SamAccountName with all upper or all lower case letters. If there is a mix of Upper and Lower case letters, no value is returned.

I need to convert both the sAMAccountName value & the string strSamAccountName to either upper or lower