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

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

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

Hi expert,

can someone provide a quick script for exporting group members by taking group name as input from user and outputing all the members details like FUll name, display name, First name, Last name,..

Please help me.. i require it quite urgent...
0
anuroopkoka2005
Asked:
anuroopkoka2005
  • 7
  • 7
1 Solution
 
Chris DentPowerShell DeveloperCommented:

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
0
 
Mike KlineCommented:
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
0
 
anuroopkoka2005Author Commented:
Can you please get me in the form of vbscript
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Chris DentPowerShell DeveloperCommented:

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

Open in new window

0
 
anuroopkoka2005Author Commented:
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...

0
 
Chris DentPowerShell DeveloperCommented:

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

Chris
0
 
anuroopkoka2005Author Commented:
domain local and global group...
0
 
Chris DentPowerShell DeveloperCommented:

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"

Open in new window

0
 
anuroopkoka2005Author Commented:
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...
0
 
Chris DentPowerShell DeveloperCommented:

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"

Open in new window

0
 
anuroopkoka2005Author Commented:
Hi Chris,

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

Can you please look into this..
0
 
Chris DentPowerShell DeveloperCommented:

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

Chris
0
 
Chris DentPowerShell DeveloperCommented:

Well I'll just assume it's one of the more common problems, it should be fixed with this version.

The PowerShell version was so much neater you know ;)

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
      If IsArray(strValue) Then
        strValue = Join(strValue)
      End If
    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"

Open in new window

0
 
anuroopkoka2005Author Commented:
Hi Chris,

Sorry for the delay..

It worked..

Great.. Thanks for the help..

:)


0
 
anuroopkoka2005Author Commented:
Thanks a lot for your help
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

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