Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Script for Contact Information in AD

Posted on 2011-03-09
14
1 solution
Medium Priority
?
684 Views
Last Modified: 2012-05-11
Hi Experts.

I have one OU with about 40 child OUs.  In each of these live mail-enabled contacts.  I need  a script that will recursively go through the OUs and generate the following in CSV format:

Name of Contact
Email Address
Title
Office
Group Membership (some contact may be members of up to 5 groups)

Can anyone help me here?  The Group Membership part is the tricky one for me.

Thanks in advance!



0
Comment
Question by:wdurrett
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Join The Community
  • 7
  • 7
14 Comments
 
LVL 65

Expert Comment

by:RobSampson
ID: 35088756
Hi, try this.  Change strOU to suit.

Regards,

Rob.
strOU = "OU=TestUsers,OU=TestOU,"
strOutput = "Contacts.csv"

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objOutput = objFSO.CreateTextFile(strOutput, True)
objOutput.WriteLine """Name"",""Email"",""Title"",""Office"",""Groups"""
Const ADS_SCOPE_SUBTREE = 2
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
Set objRootDSE = GetObject("LDAP://RootDSE")
strDomain = objRootDSE.Get("defaultNamingContext")
If Trim(strOU) <> "" Then
	If Right(strOU, 1) <> "," Then strOU = strOU & ","
End If
objCommand.CommandText = "SELECT adsPath FROM 'LDAP://" & strOU & strDomain & "' WHERE objectCategory='user' AND objectClass='contact'"
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst
While Not objRecordSet.EOF
	Set objUser = GetObject(objRecordSet.Fields("adsPath").Value)
    objOutput.Write """" & objUser.Name & """,""" & objUser.Mail & """,""" & objUser.Title & """,""" & objUser.physicalDeliveryOfficeName & """"
    If TypeName(objUser.MemberOf) = "Empty" Then
    	objOutput.WriteLine ","""
    ElseIf TypeName(objUser.MemberOf) = "String" Then
    	objOutput.WriteLine ",""" & objUser.MemberOf & """"
    Else
    	For Each strGroup In objUser.MemberOf
    		objOutput.WriteLine ",""" & objUser.MemberOf & """"
    	Next
    End If
    objRecordSet.MoveNext
Wend
objOutput.Close
MsgBox "Done. Please see " & strOutput

Open in new window

0
 
LVL 10

Author Comment

by:wdurrett
ID: 35088976
Hi Rob.  Nice to see you agian.  :)

I recived the following error:
Missing '(' after 'if' in if statement.
chapter_info.ps1:17 char:4
+ If  <<<< Trim(strOU) <> "" Then
    + CategoryInfo          : ParserError: (OpenParenToken:TokenId) [], ParseException
    + FullyQualifiedErrorId : MissingEndParenthesisInIfStatement
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 35089016
Hi, sorry, this is not PowerShell.  It's VBS.  Save it as a VBS and run it.

Regarsd,

Rob.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Download e-book
 
LVL 10

Author Comment

by:wdurrett
ID: 35089067
Sorry, my bad.   However, I got this new error:

c:>chapter_info.vbs(21, 1) Provider: Table does not exist.

0
 
LVL 65

Expert Comment

by:RobSampson
ID: 35089174
Double check your OU path.  It needs to be listed in reverse order, not including the DC parts.  So, if you need to enumerate:
domain.com/Sites/MainOffice/Users

Then use
strOU = "OU=Users,OU=MainOffice,OU=Sites,"

Regards,

Rob.
0
 
LVL 10

Author Comment

by:wdurrett
ID: 35089211
My bad agian.  I was missing an "s" at the end of my OU.

But now I come back with yet another error:

c:\chapter_info.vbs(32, 7) Microsoft VBScript runtime error: Type mismatch
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 35089223
Oh! This time it's my bad.....change this:
          For Each strGroup In objUser.MemberOf
                objOutput.WriteLine ",""" & objUser.MemberOf & """"
          Next

to this
          For Each strGroup In objUser.MemberOf
                objOutput.WriteLine ",""" & strGroup & """"
          Next

Regards,

Rob.
0
 
LVL 10

Author Comment

by:wdurrett
ID: 35089417
OK, that time I did get some output.  However, there are some issues.

1) Iam seeing CN= in front of all the names.  I can deal with this if it has to be this way, but my hope is to pass this along to a non-technical end user for her to run.  That will freak her out.

2) The group names show as "CN=Seattle Core Committee,OU=Seattle,OU=United States,OU=Chapters,OU=Internet Contacts,DC=mydoman,DC=org"  Once again, I woudl like it just say "Seattle Core Committee."

Thanks in advance for your help.

0
 
LVL 65

Accepted Solution

by:
RobSampson earned 2000 total points
ID: 35089454
That's easy done.  Try this.

Regards,

Rob.
strOU = "OU=TestUsers,OU=TestOU,"
strOutput = "Contacts.csv"

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objOutput = objFSO.CreateTextFile(strOutput, True)
objOutput.WriteLine """Name"",""Email"",""Title"",""Office"",""Groups"""
Const ADS_SCOPE_SUBTREE = 2
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
Set objRootDSE = GetObject("LDAP://RootDSE")
strDomain = objRootDSE.Get("defaultNamingContext")
If Trim(strOU) <> "" Then
	If Right(strOU, 1) <> "," Then strOU = strOU & ","
End If
objCommand.CommandText = "SELECT adsPath FROM 'LDAP://" & strOU & strDomain & "' WHERE objectCategory='user' AND objectClass='contact'"
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst
While Not objRecordSet.EOF
	Set objUser = GetObject(objRecordSet.Fields("adsPath").Value)
    objOutput.Write """" & Mid(objUser.Name, 4) & """,""" & objUser.Mail & """,""" & objUser.Title & """,""" & objUser.physicalDeliveryOfficeName & """"
    If TypeName(objUser.MemberOf) = "Empty" Then
    	objOutput.WriteLine ","""
    ElseIf TypeName(objUser.MemberOf) = "String" Then
    	objOutput.WriteLine ",""" & Mid(objUser.MemberOf, 4) & """"
    Else
    	For Each strGroup In objUser.MemberOf
    		objOutput.WriteLine ",""" & Mid(strGroup, 4) & """"
    	Next
    End If
    objRecordSet.MoveNext
Wend
objOutput.Close
MsgBox "Done. Please see " & strOutput

Open in new window

0
 
LVL 10

Author Comment

by:wdurrett
ID: 35089513
One last request:

If a contact is not a member of any group, can it return "None"?  I am seeing some errors in the output followng a contact that has no group membership.

Thanks dude.  you are awesome.
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 35089568
Sure, just change
      If TypeName(objUser.MemberOf) = "Empty" Then
            objOutput.WriteLine ","""

to
      If TypeName(objUser.MemberOf) = "Empty" Then
            objOutput.WriteLine ",None"""

Regards,

Rob.
0
 
LVL 10

Author Comment

by:wdurrett
ID: 35089630
Yes, I just did that before you posted it.

The issue is that for any contact not in a group, the next contact is on the same line.  It is like I need to add a line return in the script for those few contacts.
0
 
LVL 10

Author Comment

by:wdurrett
ID: 35089641
Got it.  I was missing a quote.

Thanks again.
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 35089660
No problem. Thanks for the grade.

Regards,

Rob.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Learn More
Question has a verified solution.

If you are experiencing a similar issue, please ask a related question
Securing Active Directory Administrators Groups
Article by: Shaun
A hard and fast method for reducing Active Directory Administrators members.
How to manage local account passwords from Active Directory without LAPS
Article by: Shaun
This process allows computer passwords to be managed and secured without using LAPS. This is an improvement on an existing process, enhanced to store password encrypted, instead of clear-text files within SQL
[Webinar] Is Your Active Directory as Secure as You Think?
Video by: Experts Exchange
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
[Webinar] Are Unknown Privileged Accounts Putting You At Risk?
Video by: Experts Exchange
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
Advertise Here

705 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

Advertise Here