Active Directory Group Member Export to Excel 1

[kachbo1]

I am looking for a script that will export the members of specific AD Groups including Domain Admin, Enterprise Admins etc, and populate the members in an Excel Spreadsheet.

Can anyone help me please. The script must also be able to export other groupnames that are withing those groups.

Many thanks in advance.

Kachbo

 

[PHV]

And what have you tried so far ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[markdmac]

This script will dump info to a text file. Refer to my FAQ faq329-4871 for info on how to alter to write to Excel.

'==========================================================================
'
' NAME: GetGroupMembers.vbs
'
' AUTHOR: Mark D. MacLachlan , The Spider's Parlor
' URL: [URL unfurl="true"]http://www.thespidersparlor.com[/URL]
' DATE  : 3/23/2005
'
' COMMENT: <comment>
'
'==========================================================================


Dim sResultText,Grps,MemberList
Dim oRootDSE, oConnection, oCommand, oRecordSet
Set oRootDSE = GetObject("LDAP://rootDSE")
Set oConnection = CreateObject("ADODB.Connection")
oConnection.Open "Provider=ADsDSOObject;"
Set objCommand = CreateObject("ADODB.Command")
objCommand.ActiveConnection = oConnection

ldstring = "<LDAP://" & oRootDSE.get("defaultNamingContext") & ">;" 

objCommand.CommandText=ldstring & "(objectClass=group);name,SamAccountName"

Set oRecordSet = objCommand.Execute()
Do While Not oRecordSet.EOF
	sResultText = sResultText & oRecordSet.Fields("samAccountName") & vbCrLf
	'WScript.Echo oRecordSet.Fields("samAccountName") & vbCrLf
	MemberList=RetrieveUsers(dom,oRecordSet.Fields("samAccountName"))
	'WScript.Echo Memberlist
	sResultText = sResultText & memberlist & vbCrLf & "************************************" & vbCrLf
	
	oRecordSet.MoveNext
Loop
'Wscript.Echo sResultText


Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.CreateTextFile (dom & "DomainGroupUsers.txt", ForWriting)
ts.write sResultText
MsgBox "Done"


'*****************************************************************************************
'*****************************************************************************************
Function RetrieveUsers(domainName,grpName)

dim dom
dim grp
dim GrpObj
dim mbrlist
dim mbr

'-------------------------------------------------------------------------------
' *** Enumerate Group Members ***
'-------------------------------------------------------------------------------

grp = grpName
Set objDomain = getObject("LDAP://rootDse")
domainName = objDomain.Get("dnsHostName")
' Build the ADSI query and retrieve the group object
Set GrpObj = GetObject("WinNT://" & domainName & "/" & grp & ",group")

' Loop through the group membership and build a string containing the names
for each mbr in GrpObj.Members
   On error resume next
   mbremail = SearchEmail(mbr.name)  
   If Err Then
	   mbrlist = mbrlist & vbTab & mbr.name & vbCrLf
   Else
   'if you don't want the email addresses, then copy the line 2 up to below
	   mbrlist = mbrlist & vbTab & mbr.name & vbTab & vbTab & mbremail+ vbCrLf
   End If
Next

'The next line returns mbrlist back up to the main body
RetrieveUsers=mbrlist

End Function

Public Function SearchEmail(ByVal vSAN)
    ' Function:     SearchDistinguishedName
    ' Description:  Searches the DistinguishedName for a given SamAccountName
    ' Parameters:   ByVal vSAN - The SamAccountName to search
    ' Returns:      The DistinguishedName Name
    Dim oRootDSE, oConnection, oCommand, oRecordSet

    Set oRootDSE = GetObject("LDAP://rootDSE")
    Set oConnection = CreateObject("ADODB.Connection")
    oConnection.Open "Provider=ADsDSOObject;"
    Set oCommand = CreateObject("ADODB.Command")
    oCommand.ActiveConnection = oConnection
    oCommand.CommandText = "<LDAP://" & oRootDSE.get("defaultNamingContext") & _
        ">;(&(objectCategory=User)(samAccountName=" & vSAN & "));mail;subtree"
    Set oRecordSet = oCommand.Execute
    On Error Resume Next
    SearchEmail = oRecordSet.Fields("mail")
    On Error GoTo 0
    oConnection.Close
    Set oRecordSet = Nothing
    Set oCommand = Nothing
    Set oConnection = Nothing
    Set oRootDSE = Nothing
End Function

I hope you find this post helpful.

Regards,

Mark

 

[TheLad]

Another great script Mark, thanks as usual

I winder if you can answer a few questions if possible?

1) Is there a way of including the location of the group in AD? For example the group APP-EXCEL is located in Applications.Groups. Is there a way of tagging this information on the end of the group name?

2) Is there a way of splitting up the user information so that it records one username at a time? I am trying to program an export into Excel and it doesn't like all of the usernames being in the RetrieveUsers(dom,oRecordSet.Fields("samAccountName")) bit of code

Thanks in advance

------------------------------------------------------------------------------------
"Life is like a box of chocolates, you never know what you're gonna get!"
------------------------------------------------------------------------------------

 

[markdmac]

So you want to limit this to a single Group?

If oRecordSet.Fields("samAccountName") = "APP-EXCEL" Then 
MemberList=RetrieveUsers(dom,oRecordSet.Fields("samAccountName"))
End If

mbrlist becomes an array. You can use that array to write to excel.

For Each oMember in mbrlist
'code to write oMember to Excel
Next

I hope you find this post helpful.

Regards,

Mark

 

[TheLad]

Cheers mate, most appreciated as usual The Star is from me btw

------------------------------------------------------------------------------------
"Life is like a box of chocolates, you never know what you're gonna get!"
------------------------------------------------------------------------------------

 

[markdmac]

Thanks Lad. Glad to be of service.

I hope you find this post helpful.

Regards,

Mark