Extract Members of an AD Group to Excel
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.