query samid using a list of first and last names.

nav2567
nav2567 used Ask the Experts™
on
I have a list of over one hundred name (First Name, Last Name).  Is it possible to write a script to generate a list of samid in AD?

I use dsquery to group before,  

Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SamID, SID? Or SAMAccountName?

Author

Commented:
win2k3 login account.
DSQuery User might work depending on your data
(example here: http://technet.microsoft.com/en-us/library/cc725702%28WS.10%29.aspx)

Also, CSVDE or LDFDE are both viable options and have a larger query base.
(examples here: http://technet.microsoft.com/en-us/library/cc732101%28WS.10%29.aspx)

The variable you want from each account is sAMAccountName.

Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Top Expert 2013
Commented:
So if you just want to pull out samaccountnames from all accounts  a tool like adfind is great for that


http://www.joeware.net/freetools/tools/adfind/index.htm

adfind -default -f "&(objectcategory=person)(objectclass=user)" sn givenname samaccountname -csv  -nodn > c:\users.csv

....but if you have a spreadsheet of first and last names and just want to pull those then that will take some scripting.  I can add some scripting zones for you if that is the case.

Thanks

Mike
This does make more sense in a scripting zone.

We've used this script for a few years to retreive distringuished names from a text file - you can modify the locations requesting the dn and exchange it for sAMAccountName. It's commented pretty well and should work for you. It's a VB script, watch out for line breaks.
Option Explicit

Dim strExcelPath, strFilePath, adoConnection, adoCommand, objRootDSE,
strDNSDomain
Dim strFilter, strQuery, adoRecordset, strDN, objExcel, objSheet, k,
objUser, objFSO
Dim objFile, strLine, intIndex

' Check for required arguments.
Const ForReading = 1

' Specify the text file of user names.
strFilePath = "c:\MyFolder\UserList.txt"

' Open the file for read access.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strFilePath, ForReading)

' Spreadsheet file to be created.
strExcelPath = "c:\myfolder\selectivelist.xls"

' Bind to Excel object.
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Add

' Bind to worksheet.
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
objSheet.Name = "Domain User"
objSheet.Cells(1, 1).Value = "User Distinguished Name"

' Use ADO to search the domain for all users.
Set adoConnection = CreateObject("ADODB.Connection")
Set adoCommand = CreateObject("ADODB.Command")
adoConnection.Provider = "ADsDSOOBject"
adoConnection.Open "Active Directory Provider"
Set adoCommand.ActiveConnection = adoConnection

' Determine the DNS domain from the RootDSE object.
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")

adoCommand.Properties("Page Size") = 100
adoCommand.Properties("Timeout") = 30
adoCommand.Properties("Cache Results") = False

k = 2
' Read the text file of names.
Do Until objFile.AtEndOfStream
strLine = Trim(objFile.ReadLine)
' Skip blank lines.
If (strLine <> "") Then
strFilter = "(&(objectCategory=person)(objectClass=user)(cn=" &
strLine & "))"
strQuery = "<LDAP://" & strDNSDomain & ">;" & strFilter _
& ";distinguishedName;subtree"
adoCommand.CommandText = strQuery

' Enumerate all users. Write each user's Distinguished Name to the
' spreadsheet.
Set adoRecordset = adoCommand.Execute
Do Until adoRecordset.EOF
strDN = adoRecordset.Fields("distinguishedName").Value
' Escape any forward slash characters, "/", with the backslash
' escape character. All other characters that should be escaped
are.
strDN = Replace(strDN, "/", "\/")
objSheet.Cells(k, 1).Value = strDN
k = k + 1
adoRecordset.MoveNext
Loop
adoRecordset.Close
End If
Loop

' Format the spreadsheet.
objSheet.Range("A1:A1").Font.Bold = True
objSheet.Select
objExcel.Columns(1).ColumnWidth = 80

' Save the spreadsheet.
objExcel.ActiveWorkbook.SaveAs strExcelPath
objExcel.ActiveWorkbook.Close

' Quit Excel.
objExcel.Application.Quit

' Clean up.
adoConnection.Close
objFile.Close
Set objFile = Nothing
Set objFSO = Nothing
Set objUser = Nothing
Set adoConnection = Nothing
Set adoCommand = Nothing
Set objRootDSE = Nothing
Set adoRecordset = Nothing
Set objSheet = Nothing
Set objExcel = Nothing

Wscript.Echo "Done"

Open in new window

Author

Commented:
Thanks both.  I can only write simple dos script to read a list of first and last names.  I was trying something like: dsquery user -name john* | dsget user -samid but this returns a lot of John's samids.
Like I said, I am only provided a list of first and last names and need to put together a list of samids, so that I can write another script to input the samids into a domain group.
Unfortunately, I do not know much about VB.  I think I do need other script zones with more examples.
Thanks again.  
Senior Active Directory Engineer
Top Expert 2012
Commented:
Create batch file and use this syntax

user fn and ln should be in text file (users.txt)

i.e

John Smith
Ann Kovalsky

batch file

@echo off

for /f "tokens=1,2" %%i in (users.txt) do dsquery user -name "%%i %%j" " dsget user -samid >c:\samid.txt

save batch in the same place where is zour text file and run it L=

Regards,
Krzysztof
Krzysztof PytkoSenior Active Directory Engineer
Top Expert 2012

Commented:
soory, my keybored changed layout, batch syntax once again :)

@echo off

for /f "tokens=1,2" %%i in (users.txt) do dsquery user -name "%%i %%j" | dsget user -samid >c:\samid.txt

Author

Commented:
isiek,
I tried your script and got the following error:
dsquery failed:value for "startnode" has incorrect format.
 
Krzysztof PytkoSenior Active Directory Engineer
Top Expert 2012

Commented:
looks like mistype in syntax :)

can you post it here, please?

Author

Commented:
users.txt contains:
Amy Smith
John Casey
.......
test.bat contains:

@echo off
for /f "tokens=1,2" %%i in (users.txt) do dsquery user -name "%%i %%j" " dsget user samid >c:\samid.txt
pause
Krzysztof PytkoSenior Active Directory Engineer
Top Expert 2012

Commented:
yeah, the problem is in syntax
I wrote one post lower taht my keyboard switched layout so syntax is wrong :)

use thise below


for /f "tokens=1,2" %%i in (users.txt) do dsquery user -name "%%i %%j" | dsget user -samid >c:\samid.txt

Krzysztof

Author

Commented:
This time I got: "Target object for this command is missing"
Sorry.

Author

Commented:

@echo off
for /f "tokens=1,2" %%i in (users.txt) do dsquery user -name "%%i %%j" | dsget user -samid >c:\samid.txt
pause
Krzysztof PytkoSenior Active Directory Engineer
Top Expert 2012

Commented:
ok, check manually

dsquery user -name "FirstName LastName"

and check what is will give you back. Maybe you have no FullName set up as FN and LN

Author

Commented:
I did check manually after I looked at your script.  Let me play around with it again.
I think we are getting close.  

Author

Commented:
Just tried:
dsquery user -name "John Casey" |dsget user -samid
It returns: Target object for this command is missing.
Tried:
dsquery user -name "John Casey" alone, it returns nothing.
 
Krzysztof PytkoSenior Active Directory Engineer
Top Expert 2012

Commented:
ok, looks like you have different way of FullName (not FN and LN)

for test, open ADUC and check what do you see in "Name" column :)

Author

Commented:
works.  Just one thing, the for loop only able to return the sam id of the last line of the txt file.  Any idea?  
 
@echo off
for /f "tokens=1,2" %%i in (users.txt) do dsquery user -name "%%i %%j" | dsget user -samid >c:\samid.txt
pause  
Krzysztof PytkoSenior Active Directory Engineer
Top Expert 2012

Commented:
oh, strange.

ok, my big mistake. I'm sorry

> sign means create new file
>> means append text file :)

so, the proper syntax

@echo off
for /f "tokens=1,2" %%i in (users.txt) do dsquery user -name "%%i %%j" | dsget user -samid >>c:\samid.txt
pause

Regards,
Krzysztof

Author

Commented:
Thanks.  I need to grant points to mklines71 and grantsewell as they put effort  and good suggestion as well.  

Thank you so much everyone.  
Krzysztof PytkoSenior Active Directory Engineer
Top Expert 2012

Commented:
You're welcome :)

Points... that's no problem :]

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial