Scirpt to export group members inclusing their details into a excel file

Hey,

Can I suggest you grab PowerShell?

http://www.microsoft.com/windowsserver2003/technologies/management/powershell/default.mspx

And these CmdLets from Quest:

http://www.quest.com/activeroles-server/arms.aspx

Then all you have to do is run this within PowerShell:

Get-QADGroupMember "GroupName" | Export-CSV -Path "YourCSV.csv"

That should include the majority of the available attributes for the user, including each of those you mention.

Chris

adfind  by Joe Richards is another good way to do this,
http://www.joeware.net/freetools/tools/adfind/index.htm
adfind -default  -f name="groupname" member -list | adfind -s base givenname sn displayname -nodn -csv
Thanks
Mike

Can you please get me in the form of vbscript

It's not nearly as neat but this will do it.

You have to fill in the DN for the group, and tell it the properties you want it to load in the constants at the top.

Chris

Option Explicit
 
Const GROUP_DN = "CN=The Group,OU=Somewhere,DC=domain,DC=com"
Const PROPERTIES_TO_LOAD = "distinguishedName,name,displayName,givenName,sn"
Const FILE_NAME = "Out.csv"
 
Dim strFilter : strFilter = "(&(objectClass=user)(objectCategory=person)(memberOf=" & GROUP_DN & "))"
 
Dim objConnection : Set objConnection = CreateObject("ADODB.Connection")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
 
Dim objRootDSE : Set objRootDSE = GetObject("LDAP://RootDSE")
Dim objRecordSet : Set objRecordSet = objConnection.Execute( _
  "<LDAP://" & objRootDSE.Get("defaultNamingContext") & ">;" & _
  strFilter & ";" & PROPERTIES_TO_LOAD & ";subtree")
Set objRootDSE = Nothing
 
Dim objFSO : Set objFSO = CreateObject("Scripting.FileSystemObject")
Dim objFile : Set objFile = objFSO.OpenTextFile(FILE_NAME, 2, True, 0)
objFile.WriteLine PROPERTIES_TO_LOAD 
 
Dim arrLine()
While Not objRecordSet.EOF
 
  Dim i : i = 0 : Dim strProperty
  For Each strProperty in Split(PROPERTIES_TO_LOAD, ",")
    ReDim Preserve arrLine(i)
    
    Dim strValue
    If IsNull(objRecordSet.Fields(strProperty)) Then
      strValue = ""
    Else
      strValue = objRecordSet.Fields(strProperty).Value
    End If
 
    If InStr(strValue, ",") > 0 Then
      strValue = """" & strValue & """"
    End If
 
    arrLine(i) = strValue
    i = i + 1
  Next
 
  objFile.WriteLine Join(arrLine, ",")
 
  objRecordSet.MoveNext
WEnd

Select allOpen in new window

Hi Chris,

The script is working fin but it will be good if it can just ask us the group name and should be able generate report for both domain group and domain local...

because it will be difficult for the level 1 to edit the script and extract the report...

Domain Group and Domain Local? You mean domain groups and local groups?

Chris

domain local and global group...

I wasn't distinguishing between the two so it shouldn't much matter which group type it is :)

This revision prompts for a group name and searches for the group. Notifies if it cannot find the group, and notifies on completion.

Chris

Option Explicit
 
Const PROPERTIES_TO_LOAD = "distinguishedName,name,displayName,givenName,sn"
Const FILE_NAME = "Out.csv"
 
Function GetGroupDN(strGroup)
 
  Dim objConnection : Set objConnection = CreateObject("ADODB.Connection")
  objConnection.Provider = "ADsDSOObject"
  objConnection.Open "Active Directory Provider"
 
  Dim objRootDSE : Set objRootDSE = GetObject("LDAP://RootDSE")
  Dim objRecordSet : Set objRecordSet = objConnection.Execute("<LDAP://" & objRootDSE.Get("defaultNamingContext") & ">;" & _
    "(&(objectClass=group)(|(name=" & strGroup & ")(sAMAccountName=" & strGroup & ")));distinguishedName;subtree")
  Set objRootDSE = Nothing
 
  GetGroupDN = ""
  If objRecordSet.RecordCount = 1 Then
    While Not objRecordSet.EOF
      GetGroupDN = objRecordSet.Fields("distinguishedName").Value
      objRecordSet.MoveNext
    WEnd
  End If
End Function
 
Dim strGroup : strGroup = InputBox("Please enter the Group Name", "Group Name")
Dim strGroupDN : strGroupDN = "" : strGroupDN = GetGroupDN(strGroup)
If strGroupDN = "" Then
  MsgBox "Sorry I couldn't find " & strGroup, vbOK + vbExclamation, "Failed to find Group"
  WScript.Quit
End If
 
Dim strFilter : strFilter = "(&(objectClass=user)(objectCategory=person)(memberOf=" & strGroupDN & "))"
 
Dim objConnection : Set objConnection = CreateObject("ADODB.Connection")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
 
Dim objRootDSE : Set objRootDSE = GetObject("LDAP://RootDSE")
Dim objRecordSet : Set objRecordSet = objConnection.Execute( _
  "<LDAP://" & objRootDSE.Get("defaultNamingContext") & ">;" & _
  strFilter & ";" & PROPERTIES_TO_LOAD & ";subtree")
Set objRootDSE = Nothing
 
Dim objFSO : Set objFSO = CreateObject("Scripting.FileSystemObject")
Dim objFile : Set objFile = objFSO.OpenTextFile(FILE_NAME, 2, True, 0)
objFile.WriteLine PROPERTIES_TO_LOAD 
 
Dim arrLine()
While Not objRecordSet.EOF
 
  Dim i : i = 0 : Dim strProperty
  For Each strProperty in Split(PROPERTIES_TO_LOAD, ",")
    ReDim Preserve arrLine(i)
    
    Dim strValue
    If IsNull(objRecordSet.Fields(strProperty)) Then
      strValue = ""
    Else
      strValue = objRecordSet.Fields(strProperty).Value
    End If
 
    If InStr(strValue, ",") > 0 Then
      strValue = """" & strValue & """"
    End If
 
    arrLine(i) = strValue
    i = i + 1
  Next
 
  objFile.WriteLine Join(arrLine, ",")
  objRecordSet.MoveNext
WEnd
 
MsgBox "Report Generated. See " & FILE_NAME, vbOK + vbInformation, "Report Generated"

Select allOpen in new window

Hi Chris..


Thank u very much for the script..
sorry i just remembered that some users also have description ...
so can that field be added also..

Please...

Yep, all you have to do to add additional fields is pop them in the PROPERTIES_TO_LOAD constant at the top.

I've tagged it on here, but you could do the same for physicalDeliveryOfficeName (aka Office) and telephoneNumber, and any other simple attribute (simple text fields).

Chris

Option Explicit
 
Const PROPERTIES_TO_LOAD = "distinguishedName,name,displayName,givenName,sn,description"
Const FILE_NAME = "Out.csv"
 
Function GetGroupDN(strGroup)
 
  Dim objConnection : Set objConnection = CreateObject("ADODB.Connection")
  objConnection.Provider = "ADsDSOObject"
  objConnection.Open "Active Directory Provider"
 
  Dim objRootDSE : Set objRootDSE = GetObject("LDAP://RootDSE")
  Dim objRecordSet : Set objRecordSet = objConnection.Execute("<LDAP://" & objRootDSE.Get("defaultNamingContext") & ">;" & _
    "(&(objectClass=group)(|(name=" & strGroup & ")(sAMAccountName=" & strGroup & ")));distinguishedName;subtree")
  Set objRootDSE = Nothing
 
  GetGroupDN = ""
  If objRecordSet.RecordCount = 1 Then
    While Not objRecordSet.EOF
      GetGroupDN = objRecordSet.Fields("distinguishedName").Value
      objRecordSet.MoveNext
    WEnd
  End If
End Function
 
Dim strGroup : strGroup = InputBox("Please enter the Group Name", "Group Name")
Dim strGroupDN : strGroupDN = "" : strGroupDN = GetGroupDN(strGroup)
If strGroupDN = "" Then
  MsgBox "Sorry I couldn't find " & strGroup, vbOK + vbExclamation, "Failed to find Group"
  WScript.Quit
End If
 
Dim strFilter : strFilter = "(&(objectClass=user)(objectCategory=person)(memberOf=" & strGroupDN & "))"
 
Dim objConnection : Set objConnection = CreateObject("ADODB.Connection")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
 
Dim objRootDSE : Set objRootDSE = GetObject("LDAP://RootDSE")
Dim objRecordSet : Set objRecordSet = objConnection.Execute( _
  "<LDAP://" & objRootDSE.Get("defaultNamingContext") & ">;" & _
  strFilter & ";" & PROPERTIES_TO_LOAD & ";subtree")
Set objRootDSE = Nothing
 
Dim objFSO : Set objFSO = CreateObject("Scripting.FileSystemObject")
Dim objFile : Set objFile = objFSO.OpenTextFile(FILE_NAME, 2, True, 0)
objFile.WriteLine PROPERTIES_TO_LOAD 
 
Dim arrLine()
While Not objRecordSet.EOF
 
  Dim i : i = 0 : Dim strProperty
  For Each strProperty in Split(PROPERTIES_TO_LOAD, ",")
    ReDim Preserve arrLine(i)
    
    Dim strValue
    If IsNull(objRecordSet.Fields(strProperty)) Then
      strValue = ""
    Else
      strValue = objRecordSet.Fields(strProperty).Value
    End If
 
    If InStr(strValue, ",") > 0 Then
      strValue = """" & strValue & """"
    End If
 
    arrLine(i) = strValue
    i = i + 1
  Next
 
  objFile.WriteLine Join(arrLine, ",")
  objRecordSet.MoveNext
WEnd
 
MsgBox "Report Generated. See " & FILE_NAME, vbOK + vbInformation, "Report Generated"

Select allOpen in new window

Hi Chris,

I m getting error line 65 and char 5 Error Code Type Mismatch

Can you please look into this..

What's on your line 65? It doesn't match up with the line numbers above.

Chris

Hi Chris,

Sorry for the delay..

It worked..

Great.. Thanks for the help..

:)

Thanks a lot for your help