14
Dec
Dec
During a project I was asked to create reports about specific Active Directory values of all the users / members in a specific (application) group. I am quite sure that this script can be written in less code (especially in Powershell), but it does the trick.
It first checks the ‘base’ of the group you are targeting and reads all the members of that group. It then recursively retrieves all the members of any sub / linked groups. All these results are written to a nice defined Excel sheet to handout to your Projectleader, Manager or whatever 
If you want to add more (default) LDAP properties to the report, you can use the following link to look them up:
- LDAP Properties
create_group_report.vbs
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 | ''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' AUTHOR : http://www.hican.net - @hicannet ' DATE : 23-03-2011 ' COMMENT : This script creates an Excel with ' different kind of information about all ' the users / members of a specific AD Group. ''''''''''''''''''''''''''''''''''''''''''''''''''''''' Option Explicit Const xlContinuous = 1 Const xlThin = 2 Dim objExcel, objWorkbook, objWorksheet Dim objCommand, objRecordSet, objConnection Dim objMember, sGroupDN, sTargetGroupDN, objGroup Dim intRow, sCount, sOutputFile, objUAC Set objConnection = CreateObject("ADODB.Connection") Set objCommand = CreateObject("ADODB.Command") Set objRecordSet = CreateObject("ADODB.Recordset") objConnection.Provider = "ADsDSOObject" objConnection.Open "Active Directory Provider" Set objCommand.ActiveConnection = objConnection 'Give the LDAP DN / CN for a group here, for example: 'LDAP://CN=TestGroup,OU=Groups,DC=hican,DC=net sTargetGroupDN = "<GROUPNAME>" sOutputFile = "<FILENAME.XLS>" Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True Set objWorkbook = objExcel.Workbooks.Add() Set objWorksheet = objWorkbook.Worksheets(1) intRow = 2 sCount = 1 createUserList(sTargetGroupDN) objExcel.Range("A1:H1").autofilter objExcel.Range("A1").Select objExcel.ActiveWorkbook.SaveAs sOutputFile objExcel.ActiveWorkbook.Close objExcel.Application.Quit Wscript.Quit Function createUserList(sGroupDN) Set objGroup = GetObject(sGroupDN) objWorksheet.Cells(sCount,1) = "sAMAccountName" objWorksheet.Cells(sCount,1).BorderAround xlContinuous, xlThin objWorksheet.Cells(sCount,2) = "First Name" objWorksheet.Cells(sCount,2).BorderAround xlContinuous, xlThin objWorksheet.Cells(sCount,3) = "Last Name" objWorksheet.Cells(sCount,3).BorderAround xlContinuous, xlThin objWorksheet.Cells(sCount,4) = "Employee Number" objWorksheet.Cells(sCount,4).BorderAround xlContinuous, xlThin objWorksheet.Cells(sCount,5) = "Email Address" objWorksheet.Cells(sCount,5).BorderAround xlContinuous, xlThin objWorksheet.Cells(sCount,6) = "IP Telephone Number" objWorksheet.Cells(sCount,6).BorderAround xlContinuous, xlThin objWorksheet.Cells(sCount,7) = "Country" objWorksheet.Cells(sCount,7).BorderAround xlContinuous, xlThin objWorksheet.Cells(sCount,8) = "Disabled" objWorksheet.Cells(sCount,8).BorderAround xlContinuous, xlThin objWorksheet.Range("A1:H1").Font.Bold = True objWorksheet.Range("A1:H1").Interior.ColorIndex = 15 For Each objMember in objGroup.Members If (LCase(objMember.Class) = "user") Then objWorksheet.Cells(intRow, 1).Value = objMember.sAMAccountName objWorksheet.Cells(intRow, 2).Value = objMember.GivenName objWorksheet.Cells(intRow, 3).Value = objMember.SN objWorksheet.Cells(intRow, 4).Value = "" objWorksheet.Cells(intRow, 5).Value = objMember.Mail objWorksheet.Cells(intRow, 6).Value = objMember.ipPhone objWorksheet.Cells(intRow, 7).Value = objMember.c & " - " & objMember.l If objMember.userAccountControl = 514 Then objUAC = "Yes" Else objUAC = "" End If objWorksheet.Cells(intRow, 8).Value = objUAC objWorksheet.Range("A1:H1").Select objWorksheet.Cells.EntireColumn.AutoFit Else ' This is to retrieve members recursively ' Comment the Function to only get the 'base' createUserList(objMember.AdsPath) intRow = intRow - 1 End If intRow = intRow + 1 Next Set objGroup = Nothing End Function |