Hey all,

I've got a big long list of user name in a excel sheet. I'm trying to write a script to determine weather the name exists in AD.

This is what I have so far (Pouched from Hey scripting guy!!)

Code: [Select]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.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\scripts\ADusers.xls")
objExcel.Visible = True

i = 1

Do Until objExcel.Cells(i, 1).Value = ""
strName = objExcel.Cells(i,1)
objCommand.CommandText = _
"SELECT * FROM 'LDAP://dc=mydomain,dc=com' WHERE objectCategory='user' " & _
"AND sAMAccountname='" & strName & "'"
Set objRecordSet = objCommand.Execute

If objRecordset.RecordCount = 1 Then
objExcel.Cells(i,2) = "Found"
Else
objExcel.Cells(i,2) = "Not found"
End If

i = i + 1
objRecordset.Close
Loop

objConnection.Close

The problem I have is that the list was generated from a list of extension numbers and there owners and are in First name Last name format. Our NT logon is First name First letter of last name. (Example - Adam Smith = adams)

The PART "sAMAccountname" part is using the names provided in the excel sheet and comparing against AD's logon usernames. This means that they all fail and aren't found.

I've found the object "cn" that compares against the display name instead (or at least I think it does) I've tried running the script with "cn" instead of "sAMAccountName" but it only finds about a 1/4 when I know that it should be closer to 3/4.

Any ideas why???

Have I used the correct method or is there a better way of doing this than what I've done??

Thanking you in advance
You may be trying to compare apples with oranges. Try listing the active directory users and comparing their format with the Excel name format (last name first?, first name first?, comma included?, etc)

You may have to manipulate the names in one of the data sources before you even attempt a compare.

Don't know MUCH about AD, but if a single user can have multiple entries, I WOULD check the recordset count for zero and not zero.

Good luck. Hey,

Thanks for the reply.

I managed to fudge a fix. I basically took the first name and the first letter of the surname and tested it against AD. If any name failed I then took the first name and 2 letters form the surname.

Since that is our standard it gave PRETTY good results. This is something i have to ever again, so it was close enough.

Cheers for the advice.