Solved

AD Group info into Excel.

Posted on 2013-01-09
20
805 Views
Last Modified: 2013-01-14
I'm looking for a script that does the following.

1.  I provide a txt file with a list of AD groups
2.  VBScript reads each line of file and gets user information out of each AD group listed in the text file.
3.  Inputs all available information from AD Group into excel.

I found this which would probably work but it requires that the AD groups are listed in the vbscript itself.  I want the script to read from a txt file.

Option Explicit
Sub OutputGroupMembership()
    Dim varGroups, varItem
    Dim wksOut As Worksheet
    Dim rngOut As Range
    Dim varMembers
    Dim lngIndex As Long
    Dim strMemberlist As String
     
    ' this is the worksheet where the list will go
    ' adjust as required
    Set wksOut = ActiveSheet
    With wksOut
        ' add titles
        .Range("A1").Value = "Group name"
        .Range("B1").Value = "Member ID"
        ' first output cell
        Set rngOut = .Range("A2")
    End With
    ' this is the list of groups you want to get the members for
    varGroups = Array("Group 1", "Group 2")
     
    For Each varItem In varGroups
        ' output group name
        With rngOut
            .Value = varItem
            .Font.Bold = True
        End With
         
        ' move down a row
        Set rngOut = rngOut.Offset(1)
        ' get member list
        ' returned as a comma separated list
        strMemberlist = GetGroupUsers(varItem)
        ' split list into an array
        varMembers = Split(strMemberlist, ",")
        ' loop and output member IDs
        For lngIndex = LBound(varMembers) To UBound(varMembers)
            rngOut.Offset(, 1).Value = varMembers(lngIndex)
            Set rngOut = rngOut.Offset(1)
        Next lngIndex
    Next varItem
         
End Sub
Function GetGroupUsers(ByVal strGroupName As String) As String
 
    Dim objGroup, objDomain, objMember
    Dim strMemberlist As String, strDomain As String
    On Error Resume Next
    Set objDomain = GetObject("LDAP://rootDse")
    strDomain = objDomain.Get("dnsHostName")
     
    Set objGroup = GetObject("WinNT://" & strDomain & "/" & strGroupName & ",group")
     
    For Each objMember In objGroup.Members
        strMemberlist = strMemberlist & "," & objMember.Name
    Next objMember
    ' strip off the leading comma
    GetGroupUsers = Mid$(strMemberlist, 2)
 
End Function

Open in new window


Appreciate any assistance.

Thanks
0
Comment
Question by:Paraleptropy
  • 11
  • 8
20 Comments
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 38761439
is the text file one group per line ?

that would be easiest
0
 

Author Comment

by:Paraleptropy
ID: 38761482
Yes, 1 group per line.
0
 
LVL 76

Expert Comment

by:arnold
ID: 38761988
http://www.activexperts.com/activmonitor/windowsmanagement/adminscripts/usersgroups/groups/

Do you also need to drill down in the event there is a sub-group?

I.s. group2 is a subgroup of group1

Depending what information you are looking to gather, it might be simpler/better to enumerate all users and the groups of which they are members

Username, group1,group2,etc.
http://m.techrepublic.com/blog/networking/simplify-admin-tasks-by-exporting-active-directory-data-with-csvde/427
0
 

Author Comment

by:Paraleptropy
ID: 38762647
I don't believe there are sub-groups.  

Groups will be listed in the text file:

group1
group2
group3
group4
etc...
0
 
LVL 76

Expert Comment

by:arnold
ID: 38762838
You are not addressing what information you are interested in gathering.
There are many script examples for reading data from a file which you will/could use as the outside loop.
With the inside loop as the member enumeration.

http://stackoverflow.com/questions/1065517/reading-a-text-file-line-by-line-in-a-batch-or-vbs-script
0
 

Author Comment

by:Paraleptropy
ID: 38762890
I'm really just trying to find the usernames of the group.

The example you provided is perfect but I'm not sure where in the script I would place it in order for it to cycle.  I can do this in WInbatch with no problem.  Just looking for a vbscript solution.
0
 
LVL 76

Expert Comment

by:arnold
ID: 38762954
You have the loop that reads in a group name at a time.

The example you posted is a set of functions.
Do you have the info that actually calls each function and then outputs.

You would need
Script with loo

Script enumerated members using the loop variable to specify the group.


Closingloop
0
 

Author Comment

by:Paraleptropy
ID: 38764083
All I would like to do is provide a .txt file with group names.  Have a script read the group names and query the group for a list of users of that group.  Dump the information into excel.
0
 
LVL 76

Expert Comment

by:arnold
ID: 38764146
Using the file read and inserting the member listing from a group
http://www.rlmueller.net/List%20Members%20of%20a%20Group.htm
You would need to replace the wscript.argument(0) as the identifier with the identifier you build including the data you read in from the file.

Alternatively, you could use dsquery group to get the list of groups in AD and then include a complete AD reference.
http://technet.microsoft.com/en-us/library/cc754525(v=ws.10).aspx
This way you will not need to construct the path.
0
 

Author Comment

by:Paraleptropy
ID: 38764551
Have something that more closely matches what I'm looking for.  Just can't seem to get around as to replace the following line with looping through the .txt file.

Option Explicit
Dim strFile, strGroupDN, objGroup, objExcel, intRow, objMember, objSheet
Dim objRootDSE, strDNSDomain, objTrans, strNetBIOSDomain, strGroup

' Constants for NameTranslate
Const ADS_NAME_INITTYPE_GC = 3
Const ADS_NAME_TYPE_NT4 = 3
Const ADS_NAME_TYPE_1779 = 1

'Specify (or prompt for) the spreadsheet file.
strFile = "c:\temp\Group.xls"

' Determine DNS domain name from RootDSE object.
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")

' Use the NameTranslate object to find the NetBIOS domain name
' from the DNS domain name.
Set objTrans = CreateObject("NameTranslate")
objTrans.Init ADS_NAME_INITTYPE_GC, ""
objTrans.Set ADS_NAME_TYPE_1779, strDNSDomain
strNetBIOSDomain = objTrans.Get(ADS_NAME_TYPE_NT4)
' Remove trailing backslash.
strNetBIOSDomain = Left(strNetBIOSDomain, _
    Len(strNetBIOSDomain) - 1)

strGroup = InputBox("Enter group Name")

' Use NameTranslate to convert NT name to DN.
On Error Resume Next
objTrans.Set ADS_NAME_TYPE_NT4, strNetBIOSDomain & "\" & strGroup
If (Err.Number <> 0) Then
    On Error GoTo 0
    ' NT name not found.
    Wscript.Echo "Group " & strGroup & " not found"
    Wscript.Quit
End If
strGroupDN = objTrans.Get(ADS_NAME_TYPE_1779)
' Bind to group object.
Set objGroup = GetObject("LDAP://" & strGroupDN)

' Create new workbook.
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Add

' Bind to worksheet.
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

' Add header row.
objSheet.Cells(1, 1).Value = "sAMAccountName"
objSheet.Cells(1, 2).Value = "Last Name"
objSheet.Cells(1, 3).Value = "First Name"

intRow = 2
For Each objMember In objGroup.Members
    objSheet.Cells(intRow, 1).Value = objMember.sAMAccountName
    objSheet.Cells(intRow, 2).Value = objMember.sn
    objSheet.Cells(intRow, 3).Value = objMember.givenName
    intRow = intRow + 1
Next

' Save the spreadsheet and close the workbook.
objExcel.ActiveWorkbook.SaveAs strFile
objExcel.ActiveWorkbook.Close

' Quit Excel.
objExcel.Application.Quit

Open in new window


strGroup = InputBox("Enter group Name")  >>>  This is the line I need to replace with the content in the txt file and loop until eof.
0
 
LVL 76

Expert Comment

by:arnold
ID: 38764752
The opening of the loop should be on line 26
StrGroup=<the readin data>

Line 68 you close the loop.

Replacingcurrent line 27 you paste in from the link previously provided on vbscript to read data from file.
Set fso = CreateObject("Scripting.FileSystemObject")
Set listFile = fso.OpenTextFile("list.txt")
do while not listFile.AtEndOfStream 
    StrGroup=  listFile.ReadLine()

Open in new window


On line 61 of your existing code add loop to close the read in data from file loop.
0
 

Author Comment

by:Paraleptropy
ID: 38764877
Ok, this was very helpful.  I had an idea where to put the first bit of code.  The last bit (line 61) was really what I was not aware of.

Now I did get this to work kind of.  So my excel spreadsheet is being created and it loops through each line.  The problem is that everytime it loops, it overwrites from the beginning!  Here is my (someone elses) new code:
Option Explicit
Dim strFile, strGroupDN, objGroup, objExcel, intRow, objMember, objSheet
Dim objRootDSE, strDNSDomain, objTrans, strNetBIOSDomain, strGroup
dim fso, filelist, listfile

' Constants for NameTranslate
Const ADS_NAME_INITTYPE_GC = 3
Const ADS_NAME_TYPE_NT4 = 3
Const ADS_NAME_TYPE_1779 = 1

'Specify (or prompt for) the spreadsheet file.
strFile = "c:\temp\Group.xls"

' Determine DNS domain name from RootDSE object.
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")

' Use the NameTranslate object to find the NetBIOS domain name
' from the DNS domain name.
Set objTrans = CreateObject("NameTranslate")
objTrans.Init ADS_NAME_INITTYPE_GC, ""
objTrans.Set ADS_NAME_TYPE_1779, strDNSDomain
strNetBIOSDomain = objTrans.Get(ADS_NAME_TYPE_NT4)
' Remove trailing backslash.
strNetBIOSDomain = Left(strNetBIOSDomain, _
    Len(strNetBIOSDomain) - 1)



Set fso = CreateObject("Scripting.FileSystemObject")
Set listFile = fso.OpenTextFile("c:\temp\group.txt")

do while not listFile.AtEndOfStream 
    StrGroup=  listFile.ReadLine()
    
' Use NameTranslate to convert NT name to DN.
On Error Resume Next
objTrans.Set ADS_NAME_TYPE_NT4, strNetBIOSDomain & "\" & strGroup
If (Err.Number <> 0) Then
    On Error GoTo 0
    ' NT name not found.
    Wscript.Echo "Group " & strGroup & " not found"
    Wscript.Quit
End If
strGroupDN = objTrans.Get(ADS_NAME_TYPE_1779)
' Bind to group object.
Set objGroup = GetObject("LDAP://" & strGroupDN)

' Create new workbook.
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Add

' Bind to worksheet.
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

' Add header row.
objSheet.Cells(1, 1).Value = "sAMAccountName"
objSheet.Cells(1, 2).Value = "Last Name"
objSheet.Cells(1, 3).Value = "First Name"

intRow = 2
For Each objMember In objGroup.Members
    objSheet.Cells(intRow, 1).Value = objMember.sAMAccountName
    objSheet.Cells(intRow, 2).Value = objMember.sn
    objSheet.Cells(intRow, 3).Value = objMember.givenName
    intRow = intRow + 1
Next

loop
ofile.close

' Save the spreadsheet and close the workbook.
objExcel.ActiveWorkbook.SaveAs strFile
objExcel.ActiveWorkbook.Close

' Quit Excel.
objExcel.Application.Quit

Open in new window


I'm thinking that after each group, I need to write the group name and append the memberlist after the groupname.
0
 
LVL 76

Expert Comment

by:arnold
ID: 38765676
You need to moe lines 49-62 which opens the foe before the start of the loop.
This will open the file add the heather rowand set the row count to 2
0
 

Author Comment

by:Paraleptropy
ID: 38766894
huh? Move the lines?  Move them to where?
0
 
LVL 76

Expert Comment

by:arnold
ID: 38767511
Before line 30 where you define/start the loop.
0
 

Author Comment

by:Paraleptropy
ID: 38767914
Seems an awful lot of time is wasted for something you obviously have the answer to.  Do I need to up the points to get the answer?  

I appreciate your help but it seems like more of a run around then anything.
0
 
LVL 76

Accepted Solution

by:
arnold earned 200 total points
ID: 38768041
You got most of it, here is the change

Added comment to indicate start of loop.
Moved the excel object creation and the initial selection of the workbook, addition of the three column headings before the start of the loop.
Option Explicit
Dim strFile, strGroupDN, objGroup, objExcel, intRow, objMember, objSheet
Dim objRootDSE, strDNSDomain, objTrans, strNetBIOSDomain, strGroup
dim fso, filelist, listfile

' Constants for NameTranslate
Const ADS_NAME_INITTYPE_GC = 3
Const ADS_NAME_TYPE_NT4 = 3
Const ADS_NAME_TYPE_1779 = 1

'Specify (or prompt for) the spreadsheet file.
strFile = "c:\temp\Group.xls"

' Determine DNS domain name from RootDSE object.
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")

' Use the NameTranslate object to find the NetBIOS domain name
' from the DNS domain name.
Set objTrans = CreateObject("NameTranslate")
objTrans.Init ADS_NAME_INITTYPE_GC, ""
objTrans.Set ADS_NAME_TYPE_1779, strDNSDomain
strNetBIOSDomain = objTrans.Get(ADS_NAME_TYPE_NT4)
' Remove trailing backslash.
strNetBIOSDomain = Left(strNetBIOSDomain, _
    Len(strNetBIOSDomain) - 1)

' Create new workbook.
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Add

' Bind to worksheet.
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

' Add header row.
objSheet.Cells(1, 1).Value = "sAMAccountName"
objSheet.Cells(1, 2).Value = "Last Name"
objSheet.Cells(1, 3).Value = "First Name"

intRow = 2



'define and start loop
Set fso = CreateObject("Scripting.FileSystemObject")
Set listFile = fso.OpenTextFile("c:\temp\group.txt")

do while not listFile.AtEndOfStream 
    StrGroup=  listFile.ReadLine()
    
' Use NameTranslate to convert NT name to DN.
On Error Resume Next
objTrans.Set ADS_NAME_TYPE_NT4, strNetBIOSDomain & "\" & strGroup
If (Err.Number <> 0) Then
    On Error GoTo 0
    ' NT name not found.
    Wscript.Echo "Group " & strGroup & " not found"
    Wscript.Quit
End If
strGroupDN = objTrans.Get(ADS_NAME_TYPE_1779)
' Bind to group object.
Set objGroup = GetObject("LDAP://" & strGroupDN)


For Each objMember In objGroup.Members
    objSheet.Cells(intRow, 1).Value = objMember.sAMAccountName
    objSheet.Cells(intRow, 2).Value = objMember.sn
    objSheet.Cells(intRow, 3).Value = objMember.givenName
    intRow = intRow + 1
Next

loop
ofile.close

' Save the spreadsheet and close the workbook.
objExcel.ActiveWorkbook.SaveAs strFile
objExcel.ActiveWorkbook.Close

' Quit Excel.
objExcel.Application.Quit

Open in new window

0
 

Author Comment

by:Paraleptropy
ID: 38774707
Ok, now that I've done the hard part.  (kidding)...  

This is just about everything I need.  I've tried to modify this to do one more thing but I'm having an issue.  Because the output doesn't contain the group names, I've added a few extra columns.  1 for the description and the other for the Group name, this way I can see that User A belongs to group1 and userB belongs to group2, etc...

I'm not sure what variable to use though
For Each objMember In objGroup.Members
    objSheet.Cells(intRow, 1).Value = objMember.sAMAccountName
    objSheet.Cells(intRow, 2).Value = objMember.sn
    objSheet.Cells(intRow, 3).Value = objMember.givenName
    objSheet.Cells(intRow, 4).Value = objMember.description
    objSheet.Cells(intRow, 5).Value = objGroup
    intRow = intRow + 1
Next

Open in new window


I did some reading around the net to figure out what some other "objMember" attributes I can use and have tried some successfully but I couldn't find which one for the actual name of the group so I tried some of the variables that are already included with the script.

I don't get an error.  I've tried changing "objGroup" to strGroup and strGroupDN.  Since there is no output in that field, I must have the variable wrong.  I thought for sure it would be one of the ones I mentioned.
0
 

Author Comment

by:Paraleptropy
ID: 38774851
Nevermind.. I got it!  "strGroup".
0
 

Author Closing Comment

by:Paraleptropy
ID: 38774856
Thanks for your help.  Really appreciated.  Sometimes when I have the solution to the problem, It makes it easier to figure out how to get there.
0

Join & Write a Comment

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Disabling the Directory Sync Service Account in Office 365 will stop directory synchronization from working.
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now