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.
Solved
Script for Contact Information in AD
Posted on 2011-03-09
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!
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!
[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
Active 3 days ago
Hi, try this. Change strOU to suit.
Regards,
Rob.
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
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 : MissingEndParenthesisInIfS
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 : MissingEndParenthesisInIfS
Active 3 days ago
Hi, sorry, this is not PowerShell. It's VBS. Save it as a VBS and run it.
Regarsd,
Rob.
Regarsd,
Rob.
Sorry, my bad. However, I got this new error:
c:>chapter_info.vbs(21, 1) Provider: Table does not exist.
c:>chapter_info.vbs(21, 1) Provider: Table does not exist.
Active 3 days ago
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/MainOffic
Then use
strOU = "OU=Users,OU=MainOffice,OU
Regards,
Rob.
domain.com/Sites/MainOffic
Then use
strOU = "OU=Users,OU=MainOffice,OU
Regards,
Rob.
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
But now I come back with yet another error:
c:\chapter_info.vbs(32, 7) Microsoft VBScript runtime error: Type mismatch
Active 3 days ago
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.
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.
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=Un
Thanks in advance for your help.
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=Un
Thanks in advance for your help.
Active 3 days ago
That's easy done. Try this.
Regards,
Rob.
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
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.
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.
Active 3 days ago
Sure, just change
If TypeName(objUser.MemberOf)
objOutput.WriteLine ","""
to
If TypeName(objUser.MemberOf)
objOutput.WriteLine ",None"""
Regards,
Rob.
If TypeName(objUser.MemberOf)
objOutput.WriteLine ","""
to
If TypeName(objUser.MemberOf)
objOutput.WriteLine ",None"""
Regards,
Rob.
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.
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.
Featured Post
Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Previously, on our Nano Server Deployment series, we've created a new nano server image and deployed it on a physical server in part 2. Now we will go through configuration.
- Veeam
- Powershell
- Hyper-V
- Windows Server 2016
- Azure
- Windows 10, Windows, *Nano Server
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s
ManageEngine
webinar, where attendees received a comprehensive look at the ma…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Suggested Courses
Course of the Month8 days, 18 hours left to enroll
Earn Certification
MCSA Configuring Windows 7 Exam 70-680
$49.00$44.10
734 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.