• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 530
  • Last Modified:

Compare AD full name with Excel full name and then import samaccount name into rows that match

I have a csv file from an oracle sql query that has signonid,fisrtname,lastname. What i want to do is pull samAccountName from AD to populate a new column with the matching names.
This will be used to tie an AD samaccountname to their oracle account.

So it will look something like this:
SIGNONID,FIRSTNAME,LASTNAME,SAMACCT
MSMITH,Mark,Smith,Mark_Smith

I'm thinking the script would open the excel, query AD to match first and second fields. Then it would return the sam and append it before it loops to the next.

I can find scripts to read AD, to read csv, to write in csv but I can put them together to query AD, match name to the name in the csv and then populate the csv with the sam name at the end of each row.

I used search but I'm still looking for anything usefull.

Any assistance is appreciated. Thanks.







0
elcait
Asked:
elcait
  • 5
  • 5
  • 3
  • +1
1 Solution
 
RobSampsonCommented:
Hi, this should be possible.  You say you want to match query AD to match first and second fields.  That would be the SignonID and Firstname fields, correct?  Which two fields in AD are these to be matched with?

Regards,

Rob.
0
 
GDG_DBACommented:
How large is the dataset?

I do this using SQL Server to gather AD and Oracle records. Matching is very complicated, as no mutual key exists, and names can be quite difficult to use for matching.

You have my best wishes trying to do this in Excel.

-G
0
 
prashanthdCommented:
As the first and last names may not be unique, multiple records may match in AD.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 
GDG_DBACommented:
Yes, exactly.

I get a very good match rate, but I don't even try to match Names.  

If you have a small data set, you might be able to get the two in sync.  However, if the set is very large at all, you will find it very difficult.

e.g.

"VanDerberg" vs "Van Derberg" vs "Van-Derberg"
"O'neil" vs "Oneil"
"Krystal" vs "Crystal"
"Michael" vs "Mike"
... just to name a few.

-gg
0
 
elcaitAuthor Commented:
Thank you for your responses.

By the second and third fields I mean first and last name.

The data set is small about 40 rows and should not grow by much.

The names are different enough. Although we have two Mikes with an Mc prefix to their last name but that's as close as it gets.

I'm not stuck on using excel.
I'll get my hands dirty with it, so long as its not full fledged programming.

So its not possible to tell it, match first AND last name (ignoring case) before it executes an append?

I'm not sure what you mean by the example you give. I thought it got exact matches?
The names should be the same in both systems for the same person and if they ar not we will correct them and make it a proceedure going forward since its not that many.

e.g.


"VanDerberg" vs "Van Derberg" vs "Van-Derberg"
"O'neil" vs "Oneil"
"Krystal" vs "Crystal"
"Michael" vs "Mike"


Thanks again
0
 
elcaitAuthor Commented:
Sorry, I meant second and third not first and second.
0
 
GDG_DBACommented:
I believe it is possible.  You would need to use the ADSI libraries in VBscript.  I do something similar in SSIS using VBScript.

I don't have code samples for what you are wanting to do, but there are lots of good resources on MSDN for Active Directory and ADSI.

-G
0
 
RobSampsonCommented:
Hi there,

Try this code.  I will search AD for the first and last names, and write the new data to a new file, writing multiple matches if found.

Regards,

Rob.
strOracleCSV = "OracleData.csv"
strNewCSV = "NewData.csv"

Set objFSO = CreateObject("Scripting.FileSystemObject")
Const ForReading = 1

Set objOracleCSV = objFSO.OpenTextFile(strOracleCSV, ForReading, False)
Set objNewCSV = objFSO.CreateTextFile(strNewCSV, True)
strHeader = "SIGNONID,FIRSTNAME,LASTNAME,SAMACCT"
objNewCSV.WriteLine strHeader

Const ADS_SCOPE_SUBTREE = 2
Set objRootDSE = GetObject("LDAP://RootDSE")
strDomain = objRootDSE.Get("defaultNamingContext")
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

While Not objOracleCSV.AtEndOfStream
	strLine = objOracleCSV.ReadLine
	If InStr(UCase(strHeader), UCase(strLine)) = 0 Then
		If InStr(strLine, ",") > 0 Then
			objCommand.CommandText = "SELECT samAccountName FROM 'LDAP://" & strDomain & "' WHERE objectCategory='user' AND givenName='" & Split(strLine, ",")(1) & "' AND sn='" & Split(strLine, ",")(2) & "'"
			Set objRecordSet = objCommand.Execute
			If Not objRecordSet.BOF Then objRecordSet.MoveFirst
			strMatches = ""
			Do Until objRecordSet.EOF
			    If strMatches = "" Then
			    	strMatches = strLine & "," & objRecordSet.Fields("samAccountName").Value
			    Else
			    	strMatches = strMatches & VbCrLf & strLine & "," & objRecordSet.Fields("samAccountName").Value
			    End If
			    objRecordSet.MoveNext
			Loop
			If strMatches = "" Then
				objNewCSV.WriteLine strLine
			Else
				objNewCSV.WriteLine strMatches
			End If
		End If
	End If
Wend

objNewCSV.Close
objOracleCSV.Close

MsgBox "Done. Please see " & strNewCSV

Open in new window

0
 
elcaitAuthor Commented:
Thanks Rob.

I ran the script and it exported the NewData.csv with all the headers but the SAMACCT column is blank.
The script runs pretty fast so i get the feeling it's not capturing something.

When I just run this part of the code with a hard coded name I get ",Roger_Benton" in the message box.

Set objFSO = CreateObject("Scripting.FileSystemObject")
Const ForReading = 1
Const ADS_SCOPE_SUBTREE = 2
Set objRootDSE = GetObject("LDAP://RootDSE")
strDomain = objRootDSE.Get("defaultNamingContext")
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 samAccountName FROM 'LDAP://" & strDomain & "' WHERE objectCategory='user' AND GivenName='Roger' AND sn='Benton'"
Set objRecordSet = objCommand.Execute
If Not objRecordSet.BOF Then objRecordSet.MoveFirst
strMatches = ""
Do Until objRecordSet.EOF
If strMatches = "" Then
strMatches = strLine & "," & objRecordSet.Fields("samAccountName").Value
Else
strMatches = strMatches & VbCrLf & strLine & "," & objRecordSet.Fields("samAccountName").Value
End If
objRecordSet.MoveNext
Loop


MsgBox strMatches
0
 
RobSampsonCommented:
OK, maybe it's not reading the CSV properly. Above this line:
                  objCommand.CommandText = "SELECT samAccountName FROM 'LDAP://" & strDomain & "' WHERE objectCategory='user' AND givenName='" & Split(strLine, ",")(1) & "' AND sn='" & Split(strLine, ",")(2) & "'"

put this:
                  MsgBox "SELECT samAccountName FROM 'LDAP://" & strDomain & "' WHERE objectCategory='user' AND givenName='" & Split(strLine, ",")(1) & "' AND sn='" & Split(strLine, ",")(2) & "'"

so you can see the LDAP query its using.

Regards,

Rob.
0
 
elcaitAuthor Commented:
It showed me the correct names as it looped but the output file still has a blank column.
0
 
RobSampsonCommented:
It did work for me....how odd....how about under this line:
                  Do Until objRecordSet.EOF

you add this:
MsgBox Split(strLine, ",")(1) & " " & Split(strLine, ",")(2) & ": " & objRecordSet.Fields("samAccountName").Value

and see if that shows you some data....

Rob.
0
 
elcaitAuthor Commented:
I added the snippet and it did not show the values.

I opened the csv to see if there was anything funky with it and notice it had blank lines so removed them with any blank spaces and boom, headshot.

Thanks again for all your help Rob!
0
 
RobSampsonCommented:
Humph!  I should have used Trim on the values then.  This:
                  objCommand.CommandText = "SELECT samAccountName FROM 'LDAP://" & strDomain & "' WHERE objectCategory='user' AND givenName='" & Split(strLine, ",")(1) & "' AND sn='" & Split(strLine, ",")(2) & "'"

can be this:
                  objCommand.CommandText = "SELECT samAccountName FROM 'LDAP://" & strDomain & "' WHERE objectCategory='user' AND givenName='" & Trim(Split(strLine, ",")(1)) & "' AND sn='" & Trim(Split(strLine, ",")(2)) & "'"

Thanks for the grade.

Regards,

Rob.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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