Solved

Script for Contact Information in AD

Posted on 2011-03-09
14
682 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
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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 500 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

This article demonstrates probably the easiest way to configure domain-wide tier isolation within Active Directory. If you do not know tier isolation read https://technet.microsoft.com/en-us/windows-server-docs/security/securing-privileged-access/s…
Group policies can be applied selectively to specific devices with the help of groups. Utilising this, it is possible to phase-in group policies, over a period of time, by randomly adding non-members user or computers at a set interval, to a group f…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

630 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