All, 

First post on the wiki. Have been writing VB for 2 years now and still learning. PowerShell seems a lot easier than VB because of inbuilt cmdlets. Posting my favourite VB script that gets group membership of users in Excel. This is required for those who are involved in SOX auditing etc. Please feel free to edit and modify as per your requirements. Kindly make sure that you are using VBSCRIPT compiler to make any changes. Am using VbsEdit at this time. 

Save it as GroupMem.vbs and then launch CMD prompt. Use Cscript groupmem.vbs to execute the script.

Modify the input and outpath in the script to get the desired output at right locations.

' List All the Members of a Group
' Input path should be in the form of Distinguished name of group.
' Input text file is C:\YourPath\InputTextFile.txt
' Feel free to modify any change to the scripts .
' the scripts writes output to a excel file and also to a notepad.
' ..............' ..............' ..............
'Author: N . Anand Rao
' Version: 1.0
 
On Error Resume Next
 
Set Fso = CreateObject("Scripting.FileSystemObject")
If FSO.FileExists("C:\YourPath\Groupmembership.xlsx") Then
fso.DeleteFile "C:\YourPath\Groupmembership.xlsx"
End If
 
 
Dim objFile1
strLog1="C:\YourPath\PassNevExp.txt"
Set objFSO=CreateObject("Scripting.FileSystemObject")
set objFile1=objFSO.CreateTextFile(strLog1,True)
 
 
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
'objExcel.Workbooks.Add
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)
intRow = 2
col = 2
 
objExcel.Cells(1, 1).Value = "Group Name"
objExcel.Cells(1, 2).Value = "First Name"
objExcel.Cells(1, 3).Value = "Last Name"
objExcel.Cells(1, 4).Value = "Group Members"
objExcel.Cells(1, 5).Value = "LastLogonTimeStamp"
objExcel.Cells(1, 6).Value = "GroupMemberDescription"
objExcel.Cells(1, 7).Value��= "GroupDescription"
objExcel.Cells(1, 1).Interior.ColorIndex = 27
objExcel.Cells(1, 2).Interior.ColorIndex = 27
objExcel.Cells(1, 3).Interior.ColorIndex = 27
objExcel.Cells(1, 4).Interior.ColorIndex = 27
objExcel.Cells(1, 5).Interior.ColorIndex = 27
objExcel.Cells(1, 6).Interior.ColorIndex = 27
objExcel.Cells(1, 7).Interior.ColorIndex = 27
objExcel.Cells(1, 1).font.bold = True
objExcel.Cells(1, 7).font.bold = True
objExcel.Cells(1, 2).font.bold = True
objExcel.Cells(1, 3).font.bold = True
objExcel.Cells(1, 4).font.bold = True
objExcel.Cells(1, 5).font.bold = True
objExcel.Cells(1, 6).font.bold = True
 
Set oFS = CreateObject("Scripting.FileSystemObject")
Set objShell = CreateObject("WScript.Shell")
InputFile = "C:\YourPath\InputTextFile.txt"
Set f = oFS.OpenTextFile(InputFile)
 
StrGroup = f.ReadAll
f.Close
arrGroups = Split(StrGroup,vbCrLf)
 
For Each agroup in arrGroups
 
''On Error Resume Next
 
Set objGroup = GetObject("LDAP://"& agroup)
objGroup.GetInfo
 
arrMemberOf = objGroup.GetEx("member")
 
WScript.Echo "Group Name :" & objGroup.get("name")
objExcel.Cells(intRow, 1).Value = objGroup.get("name")
objExcel.Cells(intRow, 6).Value = objGroup.get("description")
'' Gets ManagedBY attribute of a Group
Set ObjManager = GetObject("LDAP://"& objGroup.get("managedby"))
WScript.Echo "Manager of Group is : " & ObjManager.get("name")
 
''WScript.Echo "Manager is :" & objGroup.get("managedby")
''WScript.Echo "DL Email is :" & objGroup.get("mail")
WScript.echo "Members of the group are : "
For Each strMember in arrMemberOf
 
Set objMember = GetObject("LDAP://"& strMember)
objGroup.Get("name")
 
set objLogon = objMember.Get("lastLogontimestamp")
intLogonTime = objLogon.HighPart * (2^32) + objLogon.LowPart
intLogonTime = intLogonTime / (60 * 10000000)
intLogonTime = intLogonTime / 1440
 
TEST =intLogonTime + #1/1/1601#
 
WScript.echo objMember.Get("name") & " " & TEST
objExcel.Cells(intRow, 2).Value = objMember.Get("GivenName")
objExcel.Cells(intRow, 3).Value = objMember.Get("sn")
objExcel.Cells(col, 4).Value = objMember.Get("name")
objExcel.Cells(col, 5).Value = TEST
objExcel.Cells(col, 6).Value = objMember.Get("description")
Col = Col + 1
intRow = intRow +1
 
objFile1.writeline objMember.Get("name") & "|" & objMember.Get("GivenName") & "|" & objMember.Get("sn")
Next
Col = Col + 1
intRow = intRow +1
agroup = Null
objGroup = Null
TEST = null
Next
 
objExcel.Cells.EntireColumn.AutoFit
objexcel.Cells.EntireRow.AutoFit
objWorkbook.SaveAs "C:\YourPath\Groupmembership.xlsx"
objExcel.Quit

Hope this helps the larger user community.


See Also