Solved

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

Posted on 2011-02-23
14
495 Views
Last Modified: 2012-05-11
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
Comment
Question by:elcait
  • 5
  • 5
  • 3
  • +1
14 Comments
 
LVL 65

Expert Comment

by:RobSampson
ID: 34965985
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
 
LVL 10

Expert Comment

by:GDG_DBA
ID: 34967085
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
 
LVL 12

Expert Comment

by:prashanthd
ID: 34967663
As the first and last names may not be unique, multiple records may match in AD.
0
 
LVL 10

Expert Comment

by:GDG_DBA
ID: 34970171
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
 

Author Comment

by:elcait
ID: 34974167
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
 

Author Comment

by:elcait
ID: 34974205
Sorry, I meant second and third not first and second.
0
 
LVL 10

Expert Comment

by:GDG_DBA
ID: 34974942
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 65

Accepted Solution

by:
RobSampson earned 500 total points
ID: 34975072
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
 

Author Comment

by:elcait
ID: 34975483
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
 
LVL 65

Expert Comment

by:RobSampson
ID: 34975495
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
 

Author Comment

by:elcait
ID: 34975732
It showed me the correct names as it looped but the output file still has a blank column.
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 34975754
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
 

Author Comment

by:elcait
ID: 34997338
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
 
LVL 65

Expert Comment

by:RobSampson
ID: 35001106
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Script to copy or move mouse-selected collection of files plus targets referenced by shortcuts (.lnk) The purpose of this article is to help illuminate the real challenges and options available (where they may exist) for utilizing simple scriptin…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now