Help with AD / VBScript

[blastman]

Hey all,

I've got a big long list of user names 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!!)

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 of names 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 them 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

 

[GravyMonster]

I'm not sure what you mean by 'found the object cn...' but the below code will get the usernames into the format you need. Bear in mind it's very very rough, and will only work with the format 'John Smith' -> 'johns'. If you have 'John Edward Smith' it would be 'johne', or 'john-edward smith' would be 'john-edwards'.

Do Until objExcel.Cells(i, 1).Value = ""
    strName = objExcel.Cells(i,1)
    
	strFirstname = left(strName, instr(strName, " ")-1)
	strSurnameInitial = mid(strName, instr(strName, " ")+1, 1)
	strUsername = lcase(strFirstname & strSurnameInitial)
	    
    objCommand.CommandText = _
        "SELECT * FROM 'LDAP://dc=mydomain,dc=com' WHERE objectCategory='user' " & _
            "AND sAMAccountname='" & strUsername & "'"
    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

*edit*
tidied up slightly

 

[blastman]

Man that was quick, thanks.

Thats great, I see that you've cut up the Adam Smith to make adams and search LDAP for that.

I still have an issue as some users have the same first name and first letter form thier last name, (example - adam smith = adams & adam Soothly = adamso)

Is there a way to search for the diaply name in LADP???

that would work loads better as the name slisted in the excel sheet are in perfect.

Cheers again for all your help so far!!

 

[Confused]

Is your AD correct when using the firstname and lastname fields, and not logon name? (And, it's quite short-sighted by a previous admin to do the logon names in that fashion - even in a small organisation with 25 users I've still made them use firstname.surname as username!)

 

[blastman]

yeah, I agree. but thats what I've got to work with. and having over 400 users, I'm not about to start changing the standard.

Anyway, I managed to edit the code you provide. It now checks the first name and first letter of the surname and then if anything isn't found, it checked the first name and 2 letters for the surname. That whittled it down to only a few, which I could check for manually.

Thanks for your help!!