Learn how to a build a cloud-first strategyRegister Now

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

AD Group info into Excel.

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
Paraleptropy
Asked:
Paraleptropy
  • 11
  • 8
1 Solution
 
Robberbaron (robr)Commented:
is the text file one group per line ?

that would be easiest
0
 
ParaleptropyAuthor Commented:
Yes, 1 group per line.
0
 
arnoldCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ParaleptropyAuthor Commented:
I don't believe there are sub-groups.  

Groups will be listed in the text file:

group1
group2
group3
group4
etc...
0
 
arnoldCommented:
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
 
ParaleptropyAuthor Commented:
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
 
arnoldCommented:
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
 
ParaleptropyAuthor Commented:
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
 
arnoldCommented:
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
 
ParaleptropyAuthor Commented:
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
 
arnoldCommented:
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
 
ParaleptropyAuthor Commented:
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
 
arnoldCommented:
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
 
ParaleptropyAuthor Commented:
huh? Move the lines?  Move them to where?
0
 
arnoldCommented:
Before line 30 where you define/start the loop.
0
 
ParaleptropyAuthor Commented:
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
 
arnoldCommented:
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
 
ParaleptropyAuthor Commented:
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
 
ParaleptropyAuthor Commented:
Nevermind.. I got it!  "strGroup".
0
 
ParaleptropyAuthor Commented:
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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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