Solved

How to print out all users with their name and email address in an Organization Unit?

Posted on 2007-11-16
13
1,383 Views
Last Modified: 2012-06-21
I have Exchange 2003 server in Windows 2003 AD environment.
I need to print out all users with their name and email address in an Organization Unit. With the help from this community, I was able to print out all distribution lists with members using the following code. Now can someone modify it to print out users with name and email address?
Example output may look like this:
Allan Blair  all.blair@myCorp.com
Cathy Lee  cathy.lee@myCorp.com

// working code to print out distribution lists with members in Excel file

'Script starts here
on Error Resume Next
const ADS_SCOPE_SUBTREE = 2
Dim objGroup, objExcel, iRow, strUser
strGroup = "*"

' Use ADO to search the domain for all users.  
 
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

'OU Path
StrOU = "CN=Users," 'Change this to suit your need
'StrOU = "CN=Recipients,CN=Users," ' print out lists in Recipients folders only


' Determine the DNS domain from the RootDSE object
Set objRootDSE = GetObject("LDAP://RootDSE")  
strLDAP = objRootDSE.Get("DefaultNamingContext")  
wscript.echo strLDAP

objCommand.CommandText = "SELECT Name, adSPath FROM 'LDAP://" & strOU & strLDAP & "'

WHERE objectCategory='group' AND Name='" & strGroup & "'"

Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst

Set objExcel = CreateObject("Excel.Application")
With objExcel
  .SheetsInNewWorkbook = 1
  .Workbooks.Add
  .Visible = True
 .Worksheets.Item(1).objUser.CN = objRecordSet.Fields("Name").Value
  irow=1

Do Until objRecordSet.EOF
  Set objGroup = GetObject(objRecordSet.Fields("adsPath").Value)

    .Cells(iRow,1) = objGroup.CN
  For Each strUser In objGroup.Members
    .Cells(iRow,2) = strUser.CN
    irow=irow + 1
  Next
  Set objGroup = Nothing
  objRecordSet.MoveNext
Loop

.Columns(1).entirecolumn.autofit
.Columns(2).entirecolumn.autofit
End With

Set objExcel = Nothing
Set objGroup = Nothing

'Quit
Wscript.echo "Completed"
wscript.quit
'Script ends here

Thank you very much for your help.
0
Comment
Question by:richtree
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 12

Accepted Solution

by:
chandru_sol earned 300 total points
ID: 20300318
Try this...........

Const strX400Search = "X400:"
Const Primary_EmailAddress = "SMTP:"
Const Secondary_EmailAddress = "smtp:"

strOutputFile = "C:\proxyaddresses.csv"

Set objRootDSE = GetObject("LDAP://rootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")

'Set the ADO connection query strings
QueryPath ="" & strDNSDomain
strFilter = "(&(proxyAddresses=*))"
strAttributes = "adspath"

'Start the ADO connection
Set objCommand = CreateObject("ADODB.Command")
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
objCommand.ActiveConnection = objConnection

'Create the Query
strQuery = "<LDAP://" & QueryPath & ">;" & strFilter & ";" _
& strAttributes & ";subtree"

objCommand.CommandText = strQuery
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Timeout") = 30
objCommand.Properties("Cache Results") = False

Set objRecordSet = objCommand.Execute

objCount = 0
'objCount = objRecordSet.RecordCount '<--not possible when 'caching results' is false
addrCount = 0

If not objRecordSet.eof then
objRecordSet.MoveFirst

'Get the emailaddresses of all Items with a proxyaddress in the domain
While Not objRecordset.EOF
objCount = objCount + 1
strItemPath = objRecordSet.Fields("AdsPath").Value
Set objItem = GetObject(strItemPath)
strObjClass = objItem.class
ProxyAddresses = objItem.proxyAddresses

If IsArray(ProxyAddresses) Then
For Each ProxyAddress in ProxyAddresses
If Not CBool(InStr(UCase(ProxyAddress), strX400Search)) Then
emailaddres = "present"
addrCount = addrCount + 1
If CBool(InStr(ProxyAddress, Primary_EmailAddress)) Then _
emailaddres = Mid(proxyAddress,6) & ",Primary"
If CBool(InStr(ProxyAddress, Secondary_EmailAddress)) Then _
emailaddres = Mid(proxyAddress,6) & ",Secondary"
strResult = strResult & VbCrlf & strObjClass & "," & _
chr(34) & objItem.distinguishedName & chr(34) _
& "," & emailaddres
End If
Next
Else
ProxyAddress = ProxyAddresses
If Not CBool(InStr(UCase(ProxyAddress), strX400Search)) Then
emailaddres = "present"
addrCount = addrCount + 1
If CBool(InStr(ProxyAddress, Primary_EmailAddress)) Then _
emailaddres = Mid(proxyAddress,6) & ",Primary"
If CBool(InStr(ProxyAddress, Secondary_EmailAddress)) Then _
emailaddres = Mid(proxyAddress,6) & ",Secondary"
strResult = strResult & VbCrlf & strObjClass & "," & _
chr(34) & objItem.distinguishedName & chr(34) _
& "," & emailaddres & ",#"
End If
End If

objRecordSet.MoveNext
Wend
End If
objRecordSet.Close

'Output to a text file
Set objFileSystem = CreateObject("Scripting.FileSystemObject")
Set objOutputFile = objFileSystem.CreateTextFile(strOutputFile)
objOutputFile.Write "objectClass,DistinguishedName,Emailaddress,pri/sec address,'"
objOutputFile.Write strResult
objOutputFile.close

wscript.echo "Counted: ", objCount, "records and", addrCount, "emailaddresses"

Wscript.quit

regards
Chandru
0
 

Author Comment

by:richtree
ID: 20300979
Hi Chandru,
Your code runs without error! The output is like the following:
column1/column2/column3/column4
user / CN=Tom Blair,CN=Users,DC=myCorp,DC=Com / present / <blank>
user / CN=Tom Blair,CN=Users,DC=myCorp,DC=Com / tom.blair@myCorp.com / Primary
user / CN=accounting,CN=Users,DC=myCorp,DC=Com / present / <blank>
user / CN=accounting,CN=Users,DC=myCorp,DC=Com / accounting@myCorp.com / Primary
............
Can you modify the code so it print out the user with human name (Tom Blair) with email address only?
For example, user / Tom Blair / tom.blair@myCorp.com

Thank you very much for your help again.
0
 
LVL 26

Assisted Solution

by:farhankazi
farhankazi earned 200 total points
ID: 20301194
Hi Richtree,
Do you really need a script for this? I think this can be done with a single command line statement.

Like:
DSQuery * "OU Path Here" -Filter "(samAccountType=805306368)" -Attr name mail -Limit 0 >FileNameHere.txt

Example:
DSQuery * "OU=ACCOUNTS,DC=TRAINING,DC=COM" -Filter "(samAccountType=805306368)" -Attr Name Mail -Limit 0 >UserEmails.txt

Check UserEmails.txt file for output.
If you omit >UserEmails.txt then output will be shown on screen.

Hope this helps!
Farhan
0
 

Author Comment

by:richtree
ID: 20301719
Hi Farhan,
Your command is like a magic. I prints out all users with or without email addresses. For some reason, I have to use CN instead of OU to make it work, so the working command is:
dsquery * "CN=users,DC=myCorp,DC=Com" -filter "samAccountType=805306368)" -attr name mail limit 0 > user-email.txt
Q#2. How to filter out users without email addresses? Forget it if it is complext to filter. I am glad to see the results generated by your simple code.
Q#3. How do you get the magic number 805306368? I am just curious to know how to use it to specify other account type.
Thanks a lot.
0
 
LVL 12

Assisted Solution

by:chandru_sol
chandru_sol earned 300 total points
ID: 20303144
Hi Rich,

Try this...........


Run the script as cscript emailaddress.vbs > emailaddress.csv

regards
Chandru
Option Explicit
 

Dim objRootDSE, strDNSDomain, objCommand, objConnection

Dim strBase, strFilter, strAttributes, strQuery, objRecordSet

Dim strNTName, strDN, colmail, strmail, stremail, strOU
 

StrOU = "OU=India,"
 

' Determine DNS domain name.

Set objRootDSE = GetObject("LDAP://RootDSE")

strDNSDomain = objRootDSE.Get("defaultNamingContext")
 

' Use ADO to search Active Directory.

Set objCommand = CreateObject("ADODB.Command")

Set objConnection = CreateObject("ADODB.Connection")

objConnection.Provider = "ADsDSOObject"

objConnection.Open "Active Directory Provider"

objCommand.ActiveConnection = objConnection
 

' Search entire domain.

strBase = "<LDAP://" & strOU & strDNSDomain & ">"
 

' Filter on contacts

strFilter = "(&(&(& (mailnickname=*)(| (&(objectCategory=person)(objectClass=user))))))"
 

' Comma delimited list of attribute values to retrieve.

strAttributes = "cn,distinguishedName,adspath,proxyaddresses"
 

' Construct the ADO query, using LDAP syntax.

strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
 

' Run the query.

objCommand.CommandText = strQuery

objCommand.Properties("Page Size") = 100

objCommand.Properties("Timeout") = 30

objCommand.Properties("Cache Results") = False

Set objRecordSet = objCommand.Execute
 

' Enumerate the recordset and output the values retrieved in

' comma delimited format.

Do Until objRecordSet.EOF

strNTName = objRecordSet.Fields("cn").Value

strDN = objRecordSet.Fields("distinguishedName").Value

colmail = objRecordSet.Fields("proxyAddresses")
 

If IsEmpty(colMail) Then

' The attribute is empty (has no values).

wscript.echo "No Email address"

Else

' The attribute has one or more values - enumerate.

For Each strMail In colMail

If Instr(strMail, "SMTP:") then

strEmail = Right(strMail, Len(strMail)-5)

End If

Next 

End If

Wscript.Echo strNTName & "," & strEmail

objRecordSet.MoveNext

Loop

objRecordSet.Close
 

' Clean up.

objConnection.Close

Set objRootDSE = Nothing

Set objCommand = Nothing

Set objConnection = Nothing

Set objRecordSet = Nothing

'===============

Open in new window

0
 
LVL 26

Assisted Solution

by:farhankazi
farhankazi earned 200 total points
ID: 20304046
>> Q#2. How to filter out users without email addresses?

(1) Following statement will list users with email address

DSQuery * "OU=ACCOUNTS,DC=TRAINING,DC=COM" -Filter "(&(samAccountType=805306368)(mail=*))" -Attr Name Mail -Limit 0

(2) Following statement will list users without email address

DSQuery * "OU=ACCOUNTS,DC=TRAINING,DC=COM" -Filter "(&(samAccountType=805306368)(!mail=*))" -Attr Name -Limit 0

>> Q#3. How do you get the magic number 805306368?

Following is the list of samAccountTypes

268435456 = GROUP OBJECT
268435457 = NON SECURITY GROUP OBJECT
536870912 = ALIAS OBJECT
536870913 = NON SECURITY ALIAS OBJECT
805306368 = NORMAL USER ACCOUNT
805306369 = MACHINE ACCOUNT
805306370 = TRUST ACCOUNT
1073741824 = APP BASIC GROUP
1073741825 = APP QUERY GROUP
2147483647 = ACCOUNT TYPE MAX

Hope this helps!
Farhan
0
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 

Author Comment

by:richtree
ID: 20312376
Farhan,
Your command works perfectly.
Thanks a lot!
0
 

Author Comment

by:richtree
ID: 20312414
chandru,
Your new code also works! I just had a little problem pipe the output to a file. Your output is in a popup box which cannot be redirected to a file. How to output the results to the screen? Or how to combine your previous code to output it to a Excel file? If possible, I just like to your code working as an alternate to Farhan's command solution. I am very happy with what you two have provided.
Thanks.
0
 
LVL 12

Expert Comment

by:chandru_sol
ID: 20312580
Try this.......

cscript emailaddress.vbs > emailaddress.csv

0
 

Author Comment

by:richtree
ID: 20312614
Hi chandru,
Sorry I misunderstood your your command. It works. Great!
0
 
LVL 12

Expert Comment

by:chandru_sol
ID: 20312656
Cheers!
0
 

Author Closing Comment

by:richtree
ID: 31409600
Thank you so much for your solutions. I wish I could assign more points to your solutions. The result is more than I expected when I posted the question here. Execellent!
0
 

Expert Comment

by:dpcorp
ID: 23641516
for EHL email printout
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
edit user account 1 29
Active sync admin policy, how to avoid "data wipe-out" while accepting the policy 2 27
MDM vs GPO 16 31
Exchange 2010 Certs 2 15
Follow this checklist to learn more about the 15 things you should never include in an email signature from personal quotes, animated gifs and out-of-date marketing content.
MS Outlook is a world-class email client application that is mainly used for e-communication globally.  In this article, we will discuss the basic idea about MS Outlook, its advanced features, and types of MS Outlook File formats.
In this video we show how to create an Address List in Exchange 2013. We show this process by using the Exchange Admin Center. Log into Exchange Admin Center.: First we need to log into the Exchange Admin Center. Navigate to the Organization >> Ad…
The video tutorial explains the basics of the Exchange server Database Availability groups. The components of this video include: 1. Automatic Failover 2. Failover Clustering 3. Active Manager

910 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

22 Experts available now in Live!

Get 1:1 Help Now