Solved

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

Posted on 2007-11-16
13
1,382 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
Free book by J.Peter Bruzzese, Microsoft MVP

Are you using Office 365? Trying to set up email signatures but you’re struggling with transport rules and connectors? Let renowned Microsoft MVP J.Peter Bruzzese show you how in this exclusive e-book on Office 365 email signatures. Better yet, it’s free!

 

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This article explains in simple steps how to renew expiring Exchange Server Internal Transport Certificate.
Learn to move / copy / export exchange contacts to iPhone without using any software. Also see the issues in configuration of exchange with iPhone to migrate contacts.
In this Micro Video tutorial you will learn the basics about Database Availability Groups and How to configure one using a live Exchange Server Environment. The video tutorial explains the basics of the Exchange server Database Availability grou…
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

706 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

19 Experts available now in Live!

Get 1:1 Help Now