VBS Script to get AD Group Description and Notes Fields

pizzaman7
pizzaman7 used Ask the Experts™
on
Hi,

Sorry for the messy snippets of code here but I have been struggling with this.  I would like, at the least, to gather user input and search AD for the specific group and echo the description and notes field information in that group.

Something like this (this does not work of course):

dim objGroup
Set objSysInfo = CreateObject("ADSystemInfo")
strInput = UserInput( "Enter Group Name:" )
On Error Resume Next
Set objGroup = GetObject("LDAP://CN=" & strInput & ",ou=Win7,ou=standard,ou=Groups,ou=Corporate,dc=domain.com")
WScript.Echo objGroup.AdsPath
WScript.Echo objGroup.name & vbtab & objGroup.info & "  ||  " & objGroup.description
Function UserInput( myPrompt )
    ' Check if the script runs in CSCRIPT.EXE
    If UCase( Right( WScript.FullName, 12 ) ) = "\CSCRIPT.EXE" Then
        ' If so, use StdIn and StdOut
        WScript.StdOut.Write myPrompt & " "
        UserInput = WScript.StdIn.ReadLine
    Else
        ' If not, use InputBox( )
        UserInput = InputBox( myPrompt )
    End If
End Function

I found this script and it will pull up the the description and notes information for all the groups assigned to my user account and it looks pretty accurate to me.

Set objSysInfo = CreateObject("ADSystemInfo")
strUser = objSysInfo.UserName
Set objUser = GetObject("LDAP://" & strUser)
WScript.Echo objUser.AdsPath
For Each strGroup In objUser.Groups
      WScript.Echo strGroup.name & vbtab & strGroup.info & "  ||  " & strGroup.description
Next

What would be really ideal is to gather a column in a current spreadsheet called "Win7 Computer Access - Power Generation (PG) only.xls" and before a search for this information and populate the next two columns with the decription and notes of each group.  Right now the groups are listed in Column D.  Therefore after retrieving the information it would populate the next two columns of the same spreadsheet with this information.  Something roughly like this:

objExcel.Cells(intRow, 3).Value = objItem. Description
objExcel.Cells(intRow, 4).Value = objItem. Version

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
intRow = 2

Set Fso = CreateObject("Scripting.FileSystemObject")
Set objWorkbook = objExcel.Workbooks.Open("C:\Win7 Computer Access - Power Generation (PG) only.xls")
Set InputFile = objWorkbook
Do Until objExcel.Cells(intRow,1).Value = ""
strComputer = objExcel.Cells(intRow, 1).Value

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_OperatingSystem")

For Each objItem in colItems
objExcel.Cells(intRow, 2).Value = objItem.Caption
intRow = intRow + 1
Next
Loop

objExcel.Range("A1:B1").Select
objExcel.Selection.Interior.ColorIndex = 19
objExcel.Selection.Font.ColorIndex = 11
objExcel.Selection.Font.Bold = True
objExcel.Cells.EntireColumn.AutoFit

MsgBox "Done"

The reason for this is to get more information on the groups to better understand why some people have certain groups and to better apply the groups to new people coming in who will do similar work as the existing employees.  Thanks in advance for any assistance you may grant me !
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2014
Commented:
We could do something a little more streamlined by putting a macro into the Excel file to do the lookup.  See the sample attached and see how it uses a formula to call a function that will retrieve the information for the group name.

Regards,

Rob.
Sample.xlsm
pizzaman7Consultant

Author

Commented:
This is so simple !  Works great.  For the sake of simplicity I think I may just put the formula into each cell I need it in and reference the other cell that has the group name information.  The users then won't need to run a macro.

I can't believe that the answer was as simple as this.  Nice work !
Most Valuable Expert 2012
Top Expert 2014

Commented:
No problem.  I developed this approach some time go in an effort to avoid having VBScript open an Excel spreadsheet, read one column, query AD, and write results to another column.  It makes more sense to do it all in one place.

Thanks for the grade.

Regards,

Rob.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial