[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1467
  • Last Modified:

VBscript to list all mail enabled active directory groups with their members

I am looking for a vbscript that will list all active directory groups that are mail enabled with the members of each group.  You would think this would be easy to find, but I've been googling for a couple days and havent found exactly what I need.

Thanks
0
dmcconnell68
Asked:
dmcconnell68
  • 5
  • 4
1 Solution
 
RobSampsonCommented:
Hi there, as a start, see if this provides you with all of the mail enabled group names. It just checks whether an email address has been set or not.

Regards,

Rob.
Const ADS_SCOPE_SUBTREE = 2
 
Dim objRoot, strDNSDomain, objConn, objCommand, objRS
Set objRoot = GetObject("LDAP://RootDSE")
strDNSDomain = "LDAP://" & objRoot.Get("defaultNamingContext")
Set objConn = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConn.Provider = "ADsDSOObject"
objConn.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConn
 
cmd.CommandText = "SELECT ADsPath,cn FROM '" & strDNSDomain & "' WHERE mail='*' AND objectClass='group'"
cmd.Properties("Page Size") = 1000
cmd.Properties("Timeout") = 300
cmd.Properties("Searchscope") = ADS_SCOPE_SUBTREE
 
Set objRS = cmd.Execute
strResults = "Mail enabled groups:"
While Not objRS.EOF
	strResults = strResults & VbCrLf & objRS.Fields("cn").Value
	objRS.MoveNext
Loop
 
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objOutput = objFSO.CreateTextFile("MailEnabledGroups.txt", True)
objOutput.Write strResults
objOutput.Close
Set objOutput = Nothing
Set objFSO = Nothing
Set objRS = Nothing
Set cmd = Nothing
Set cn = Nothing
Set objDomain = Nothing
Set objRoot = Nothing
MsgBox "Done. Please see MailEnabledGroups.txt"

Open in new window

0
 
dmcconnell68Author Commented:
Hi, thanks for the script, but I'm getting the following errors:

(22, 1) Microsoft VBScript compilation error: 'loop' without 'do'
I changed line 22 from Loop to Wend, then I'm getting the following:

(12, 1) Microsoft VBScript runtime error: Object required: 'cmd'

Thanks for any help.

David
0
 
RobSampsonCommented:
Oh sorry....that's what I get for not testing.....try this.

Regards,

Rob.
Const ADS_SCOPE_SUBTREE = 2
 
Dim objRoot, strDNSDomain, objConn, objCommand, objRS
Set objRoot = GetObject("LDAP://RootDSE")
strDNSDomain = "LDAP://" & objRoot.Get("defaultNamingContext")
Set objConn = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConn.Provider = "ADsDSOObject"
objConn.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConn
 
objCommand.CommandText = "SELECT ADsPath,cn FROM '" & strDNSDomain & "' WHERE mail='*' AND objectClass='group'"
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Timeout") = 300
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
 
Set objRS = objCommand.Execute
strResults = "Mail enabled groups:"
While Not objRS.EOF
	strResults = strResults & VbCrLf & objRS.Fields("cn").Value
	objRS.MoveNext
Wend
 
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objOutput = objFSO.CreateTextFile("MailEnabledGroups.txt", True)
objOutput.Write strResults
objOutput.Close
Set objOutput = Nothing
Set objFSO = Nothing
Set objRS = Nothing
Set objCommand = Nothing
Set objConn = Nothing
Set objDomain = Nothing
Set objRoot = Nothing
MsgBox "Done. Please see MailEnabledGroups.txt"

Open in new window

0
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

 
dmcconnell68Author Commented:
Thank you, that works perfectly.  Now, how can I add the display name for each member of each group.  I would like the output to appear something like this:

Human Resources          John Smith, Pete Wright, Sally Jones
Payroll                             Denise Smith, Celia Smith
...
...

Thanks,
David

0
 
RobSampsonCommented:
Do you want the output in CSV or Text format?

Rob.
0
 
dmcconnell68Author Commented:
CSV would be great.  Thank you.
0
 
RobSampsonCommented:
Hi, see if this output is OK for you.

Regards,

Rob.
Const ADS_SCOPE_SUBTREE = 2
 
Dim objRoot, strDNSDomain, objConn, objCommand, objRS
Set objRoot = GetObject("LDAP://RootDSE")
strDNSDomain = "LDAP://" & objRoot.Get("defaultNamingContext")
Set objConn = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConn.Provider = "ADsDSOObject"
objConn.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConn
 
objCommand.CommandText = "SELECT ADsPath,cn FROM '" & strDNSDomain & "' WHERE mail='*' AND objectClass='group'"
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Timeout") = 300
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
 
Set objRS = objCommand.Execute
strResults = """Group Name"",""Members"""
While Not objRS.EOF
	Set objGroup = GetObject(objRS.Fields("adsPath").Value)
	strMembers = ""
	If TypeName(objGroup.Member) = "Empty" Then
		strMembers = ""
	ElseIf TypeName(objGroup.Member) = "String" Then
		strMembers = Mid(Split(objGroup.Member, ",")(0), 4)
	Else
		For Each strMember In objGroup.Member
			If strMembers = "" Then
				strMembers = Mid(Split(strMember, ",")(0), 4)
			Else
				strMembers = strMembers & ";" & Mid(Split(strMember, ",")(0), 4)
			End If
		Next
	End If
	strResults = strResults & VbCrLf & """" & objGroup.cn & """,""" & strMembers & """"
	objRS.MoveNext
Wend
 
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objOutput = objFSO.CreateTextFile("MailEnabledGroups.csv", True)
objOutput.Write strResults
objOutput.Close
Set objOutput = Nothing
Set objFSO = Nothing
Set objRS = Nothing
Set objCommand = Nothing
Set objConn = Nothing
Set objDomain = Nothing
Set objRoot = Nothing
MsgBox "Done. Please see MailEnabledGroups.csv"

Open in new window

0
 
dmcconnell68Author Commented:
Thank you Rob, this is exactly what I needed.
0
 
RobSampsonCommented:
No problem.  Thanks for the grade.

Regards,

Rob.
0

Featured Post

Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now