Solved

VBS script adding in an additional filter

Posted on 2011-02-18
12
515 Views
Last Modified: 2012-05-11
I found the script at the bottom of this post on the site www.rlmueller.net (a cool site for various AD scripts).  However I want to add another filter so that the only groups that are returned are ones whose names start with "internet".  So I think the line I need to add is:

strFilter = "(&(objectCategory=group)(objectClass=group)(cn=internet*))"

I've never worked with vb scripts longer than a few lines, so I can't figure out where to add this line in.  I tried adding it below the line:

strFilter = "(&(objectCategory=person)(objectClass=user))"

But if I try that, it returns an error when it gets to this line:

arrbytSIDs = objUser.Get("tokenGroups")


Can anyone tell me how to do this?  I'd appreciate it.  

----------------------------------------------------------------------
Here's the full script in its original form:

' UsersGroups.vbs
' VBScript program to document security group membership of all users.
' Reveals nested group and primary group membership. Does not reveal
' distribution group membership or cross-domain group membership.
'
' ----------------------------------------------------------------------
' Copyright (c) 2009 Richard L. Mueller
' Hilltop Lab web site - http://www.rlmueller.net
' Version 1.0 - May 8, 2009
'
' You have a royalty-free right to use, modify, reproduce, and
' distribute this script file in any way you find useful, provided that
' you agree that the copyright owner above has no warranty, obligations,
' or liability for such use.

Option Explicit

Dim adoCommand, adoConnection, strBase, strFilter, strAttributes
Dim objRootDSE, strDNSDomain, strQuery, adoRecordset, strName, strDN
Dim strExcelpath, objExcel, objSheet, intRow, intCol, objUser
Dim arrbytSIDs, objGroupList, j, arrstrGroupSIDs(), objGroup
Dim strGroupName

' Spreadsheet file name to be created.
strExcelPath = "c:\Scripts\UsersGroups.xls"

' Bind to Excel.
Set objExcel = CreateObject("Excel.Application")

' Create new workbook.
objExcel.Workbooks.Add

' Bind to worksheet.
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
objSheet.Name = "Domain Users"

' Write column headings.
objSheet.Cells(1, 1).Value = "sAMAccountName"
objSheet.Cells(1, 2).Value = "Distinguished Name"
objSheet.Cells(1, 3).Value = "Group Memberships"

' Dictionary object to keep track of group SID values.
Set objGroupList = CreateObject("Scripting.Dictionary")
objGroupList.CompareMode = vbTextCompare

' Setup ADO objects.
Set adoCommand = CreateObject("ADODB.Command")
Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.Provider = "ADsDSOObject"
adoConnection.Open "Active Directory Provider"
adoCommand.ActiveConnection = adoConnection

' Search entire Active Directory domain.
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")
strBase = "<LDAP://" & strDNSDomain & ">"

' Filter on user objects.
strFilter = "(&(objectCategory=person)(objectClass=user))"


' Comma delimited list of attribute values to retrieve.
' Cannot retrieve tokenGroups with ADO.
strAttributes = "sAMAccountName,distinguishedName"

' Construct the LDAP syntax query.
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
adoCommand.CommandText = strQuery
adoCommand.Properties("Page Size") = 100
adoCommand.Properties("Timeout") = 30
adoCommand.Properties("Cache Results") = False

' Run the query.
Set adoRecordset = adoCommand.Execute

' Enumerate the resulting recordset.
intRow = 2
Do Until adoRecordset.EOF
    ' Retrieve values and display.
    strName = adoRecordset.Fields("sAMAccountName").Value
    objSheet.Cells(intRow, 1).Value = strName
    strDN = adoRecordset.Fields("distinguishedName").Value
    strDN = Replace(strDN, "/", "\/")
    objSheet.Cells(intRow, 2).Value = strDN
    ' Bind to the user object.
    Set objUser = GetObject("LDAP://" & strDN)
    ' Retrieve tokenGroups attribute.
    objUser.GetInfoEx Array("tokenGroups"), 0
    arrbytSIDs = objUser.Get("tokenGroups")
    If (UBound(arrbytSIDs) = -1) Then
        ' No group SID values, do nothing.
    ElseIf (TypeName(arrbytSIDs) = "Byte()") Then
        ' One group SID.
        ReDim arrstrGroupSIDs(0)
        arrstrGroupSIDs(0) = OctetToHexStr(arrbytSIDs)
        ' Check if this group encountered before.
        If (objGroupList.Exists(arrstrGroupSIDs(0)) = False) Then
            ' Save group SID and name in dictionary object.
            Set objGroup = GetObject("LDAP://<SID=" & arrstrGroupSIDs(0) & ">")
            strGroupName = objGroup.distinguishedName
            objGroupList.Add arrstrGroupSIDs, strGroupName
            objSheet.Cells(intRow, 3).Value = strGroupName
        Else
            ' Retrieve group name from dictionary object.
            strGroupName = objGroupList(arrstrGroupSIDs(0))
            objSheet.Cells(intRow, 3).Value = strGroupName
        End If
    Else
        ' More than one SID value in the array.
        intCol = 3
        ReDim arrstrGroupSIDs(UBound(arrbytSIDs))
        For j = 0 To UBound(arrbytSIDs)
            arrstrGroupSIDs(j) = OctetToHexStr(arrbytSIDs(j))
            ' Check if this group encountered before.
            If (objGroupList.Exists(arrstrGroupSIDs(j)) = False) Then
                ' Save group SID and name in dictionary object.
                Set objGroup = GetObject("LDAP://<SID=" & arrstrGroupSIDs(j) & ">")
                strGroupName = objGroup.distinguishedName
                objGroupList.Add arrstrGroupSIDs(j), strGroupName
                objSheet.Cells(intRow, intCol).Value = strGroupName
            Else
                ' Retrieve group name from dictionary object.
                strGroupName = objGroupList(arrstrGroupSIDs(j))
                objSheet.Cells(intRow, intCol).Value = strGroupName
            End If
            intCol = intCol + 1
        Next        
    End If
    ' Move to the next record in the recordset.
    intRow = intRow + 1
    adoRecordset.MoveNext
Loop

' Save spreadsheet and close the workbook.
objExcel.ActiveWorkbook.SaveAs strExcelPath
objExcel.ActiveWorkbook.Close

' Quit Excel and clean up.
objExcel.Application.Quit
adoRecordset.Close
adoConnection.Close

Function OctetToHexStr(ByVal arrbytOctet)
    ' Function to convert OctetString (byte array) to Hex string.
    Dim k
    OctetToHexStr = ""
    For k = 1 To Lenb(arrbytOctet)
        OctetToHexStr = OctetToHexStr _
            & Right("0" & Hex(Ascb(Midb(arrbytOctet, k, 1))), 2)
    Next
End Function



0
Comment
Question by:cmg-support
  • 6
  • 4
  • 2
12 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34943477
What happens when you change the line

objSheet.Cells(intRow, intCol).Value = strGroupName

to

If UCase(Left(Trim(strGroupName), 8)) = "INTERNET" Then
    objSheet.Cells(intRow, intCol).Value = strGroupName
End If

Open in new window


Sid
0
 

Author Comment

by:cmg-support
ID: 34944455
Hi Sid.  Thanks for the reply.  

The line you said to replace appears twice.  When I replace the first instance with what you suggested, it doesn't seem to do anything.  When I replace the second instance, which appears under:  

            Else
                ' Retrieve group name from dictionary object.
                strGroupName = objGroupList(arrstrGroupSIDs(j))

It returns all users, which is good.  If the user is a member of a group that starts with 'Internet', it displays all groups they are a member of.  If they aren't a member of an internet group, it doesn't display any groups.  

This is close to what I'd like, but is there a way, for those that are members of groups that start with 'Internet', to only display those groups?  I don't want to see the other groups they belong to.  

If this isn't possible, what you suggested still does help.  It just would be a lot less cleanup if I didn't have to go through and delete all the other groups, since this is something I'm going to have run often.

Thanks again!  
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34944499
Sorry I forgot to mention that you have to replace all instances of

objSheet.Cells(intRow, intCol).Value = strGroupName

and also this

objSheet.Cells(intRow, 3).Value = strGroupName

so

objSheet.Cells(intRow, 3).Value = strGroupName

becomes

If UCase(Left(Trim(strGroupName), 8)) = "INTERNET" Then
    objSheet.Cells(intRow, 3).Value = strGroupName
End If

Open in new window


Sid
0
 

Author Comment

by:cmg-support
ID: 34945415
Hi Sid.  I made those changes, but when I run it, it doesn't return any groups at all -- it lists all users, but nothing is in the group column.  Any ideas?  Sorry to be a pain, but this is all new for me, so I don't know enough to fiddle with it to see what I might be able to change to make it work.  Below is what the script looks like now:

Thanks again.
 
Option Explicit

Dim adoCommand, adoConnection, strBase, strFilter, strAttributes
Dim objRootDSE, strDNSDomain, strQuery, adoRecordset, strName, strDN
Dim strExcelpath, objExcel, objSheet, intRow, intCol, objUser
Dim arrbytSIDs, objGroupList, j, arrstrGroupSIDs(), objGroup
Dim strGroupName

' Spreadsheet file name to be created.
strExcelPath = "c:\Scripts\UsersGroups.xls"

' Bind to Excel.
Set objExcel = CreateObject("Excel.Application")

' Create new workbook.
objExcel.Workbooks.Add

' Bind to worksheet.
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
objSheet.Name = "Domain Users"

' Write column headings.
objSheet.Cells(1, 1).Value = "sAMAccountName"
objSheet.Cells(1, 2).Value = "Distinguished Name"
objSheet.Cells(1, 3).Value = "Group Memberships"

' Dictionary object to keep track of group SID values.
Set objGroupList = CreateObject("Scripting.Dictionary")
objGroupList.CompareMode = vbTextCompare

' Setup ADO objects.
Set adoCommand = CreateObject("ADODB.Command")
Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.Provider = "ADsDSOObject"
adoConnection.Open "Active Directory Provider"
adoCommand.ActiveConnection = adoConnection

' Search entire Active Directory domain.
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")
strBase = "<LDAP://" & strDNSDomain & ">"

' Filter on user objects.
strFilter = "(&(objectCategory=person)(objectClass=user))"


' Comma delimited list of attribute values to retrieve.
' Cannot retrieve tokenGroups with ADO.
strAttributes = "sAMAccountName,distinguishedName"

' Construct the LDAP syntax query.
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
adoCommand.CommandText = strQuery
adoCommand.Properties("Page Size") = 100
adoCommand.Properties("Timeout") = 30
adoCommand.Properties("Cache Results") = False

' Run the query.
Set adoRecordset = adoCommand.Execute

' Enumerate the resulting recordset.
intRow = 2
Do Until adoRecordset.EOF
    ' Retrieve values and display.
    strName = adoRecordset.Fields("sAMAccountName").Value
    objSheet.Cells(intRow, 1).Value = strName
    strDN = adoRecordset.Fields("distinguishedName").Value
    strDN = Replace(strDN, "/", "\/")
    objSheet.Cells(intRow, 2).Value = strDN
    ' Bind to the user object.
    Set objUser = GetObject("LDAP://" & strDN)
    ' Retrieve tokenGroups attribute.
    objUser.GetInfoEx Array("tokenGroups"), 0
    arrbytSIDs = objUser.Get("tokenGroups")
    If (UBound(arrbytSIDs) = -1) Then
        ' No group SID values, do nothing.
    ElseIf (TypeName(arrbytSIDs) = "Byte()") Then
        ' One group SID.
        ReDim arrstrGroupSIDs(0)
        arrstrGroupSIDs(0) = OctetToHexStr(arrbytSIDs)
        ' Check if this group encountered before.
        If (objGroupList.Exists(arrstrGroupSIDs(0)) = False) Then
            ' Save group SID and name in dictionary object.
            Set objGroup = GetObject("LDAP://<SID=" & arrstrGroupSIDs(0) & ">")
            strGroupName = objGroup.distinguishedName
            objGroupList.Add arrstrGroupSIDs, strGroupName
If UCase(Left(Trim(strGroupName), 8)) = "INTERNET" Then
    objSheet.Cells(intRow, 3).Value = strGroupName
End If 

        Else
            ' Retrieve group name from dictionary object.
            strGroupName = objGroupList(arrstrGroupSIDs(0))
If UCase(Left(Trim(strGroupName), 8)) = "INTERNET" Then
    objSheet.Cells(intRow, 3).Value = strGroupName
End If 

        End If
    Else
        ' More than one SID value in the array.
        intCol = 3
        ReDim arrstrGroupSIDs(UBound(arrbytSIDs))
        For j = 0 To UBound(arrbytSIDs)
            arrstrGroupSIDs(j) = OctetToHexStr(arrbytSIDs(j))
            ' Check if this group encountered before.
            If (objGroupList.Exists(arrstrGroupSIDs(j)) = False) Then
                ' Save group SID and name in dictionary object.
                Set objGroup = GetObject("LDAP://<SID=" & arrstrGroupSIDs(j) & ">")
                strGroupName = objGroup.distinguishedName
                objGroupList.Add arrstrGroupSIDs(j), strGroupName
If UCase(Left(Trim(strGroupName), 8)) = "INTERNET" Then
    objSheet.Cells(intRow, intCol).Value = strGroupName
End If 

            Else
                ' Retrieve group name from dictionary object.
                strGroupName = objGroupList(arrstrGroupSIDs(j))
If UCase(Left(Trim(strGroupName), 8)) = "INTERNET" Then
    objSheet.Cells(intRow, intCol).Value = strGroupName
End If 

            End If
            intCol = intCol + 1
        Next        
    End If
    ' Move to the next record in the recordset.
    intRow = intRow + 1
    adoRecordset.MoveNext
Loop

' Save spreadsheet and close the workbook.
objExcel.ActiveWorkbook.SaveAs strExcelPath
objExcel.ActiveWorkbook.Close

' Quit Excel and clean up.
objExcel.Application.Quit
adoRecordset.Close
adoConnection.Close

Function OctetToHexStr(ByVal arrbytOctet)
    ' Function to convert OctetString (byte array) to Hex string.
    Dim k
    OctetToHexStr = ""
    For k = 1 To Lenb(arrbytOctet)
        OctetToHexStr = OctetToHexStr _
            & Right("0" & Hex(Ascb(Midb(arrbytOctet, k, 1))), 2)
    Next
End Function

Open in new window

0
 
LVL 15

Expert Comment

by:markdmac
ID: 34947146
How about a simpler script like this?
On Error Resume Next
 
strComputer = "SERVERNAME"
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
 
Set colItems = objWMIService.ExecQuery _
    ("Select * from Win32_Group Where Name Like '%Internet%'")
 
For Each objItem in colItems
	WScript.echo objItem.Name
	Set objGroup =  GetObject("LDAP://" & SearchDistinguishedName(objItem.Name))
	objGroup.GetInfo
 
	arrMemberOf = objGroup.GetEx("member")
 
	Report = Report & objItem.Name & " Members:" & vbCrLf
	For Each strMember in arrMemberOf
	    Report = Report & vbTab & strMember & vbCrLf
	Next
	Report = Report & "*****************************" & vbCrLf
	
Next

Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.CreateTextFile ("InternetGroups.txt", ForWriting)
ts.write Report
ts.Close


Public Function SearchDistinguishedName(ByVal vSAN)
    ' Function:     SearchDistinguishedName
    ' Description:  Searches the DistinguishedName for a given SamAccountName
    ' Parameters:   ByVal vSAN - The SamAccountName to search
    ' Returns:      The DistinguishedName Name
    Dim oRootDSE, oConnection, oCommand, oRecordSet

    Set oRootDSE = GetObject("LDAP://rootDSE")
    Set oConnection = CreateObject("ADODB.Connection")
    oConnection.Open "Provider=ADsDSOObject;"
    Set oCommand = CreateObject("ADODB.Command")
    oCommand.ActiveConnection = oConnection
    oCommand.CommandText = "<LDAP://" & oRootDSE.get("defaultNamingContext") & _
        ">;(&(objectCategory=Group)(samAccountName=" & vSAN & "));distinguishedName;subtree"
    Set oRecordSet = oCommand.Execute
    On Error Resume Next
    SearchDistinguishedName = oRecordSet.Fields("DistinguishedName")
    On Error GoTo 0
    oConnection.Close
    Set oRecordSet = Nothing
    Set oCommand = Nothing
    Set oConnection = Nothing
    Set oRootDSE = Nothing
End Function

Open in new window

0
 

Author Comment

by:cmg-support
ID: 34951745
Thanks Markdmac, but when I try your script, I just get:

*****************************
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 15

Expert Comment

by:markdmac
ID: 34952041
Did you change servername on line 3?  Also on line 8 it is looking for groups witht he word Internet in them.  Adjust as needed.
0
 

Author Comment

by:cmg-support
ID: 34952123
Oops sorry about that.  It works now -- listing who is a member of each group with Internet in the title.  However, what I need is a list of all users and display the Internet group they are a part of, or blank if they aren't a member.  
0
 
LVL 15

Expert Comment

by:markdmac
ID: 34952372
Oh sorry, I missed that as the requirement.  No problem.  That is even easier.  Will be happy to provide code for that.  Should have a break at work in a few hours and can quickly bang that out.

To ensure I am clear, you want to list all domain users.  Check their group memberships and if the group contains the word Internet, you want it to be listed.  Right?  If there are no Internet groups just list the username.  
0
 

Author Comment

by:cmg-support
ID: 34952394
Yep, exactly.  Thanks!!!!!
0
 
LVL 15

Accepted Solution

by:
markdmac earned 500 total points
ID: 34958302
OK, Here you go.  Sorry for the delay.  Super busy day.  This one will dump the info you requested into a CSV file so you can review the results in Excel.
'==========================================================================
'
' NAME: ListUsers.vbs
'
' AUTHOR: Mark D. MacLachlan , The Spider's Parlor
' URL: http://www.thespidersparlor.com
' DATE  : 12/4/2007
' COPYRIGHT (c) 2007 All Rights Reserved
'
' COMMENT: 
'
'    THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF
'    ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO
'    THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
'    PARTICULAR PURPOSE.
'
'    IN NO EVENT SHALL THE SPIDER'S PARLOR AND/OR ITS RESPECTIVE SUPPLIERS 
'    BE LIABLE FOR ANY SPECIAL, INDIRECT OR CONSEQUENTIAL DAMAGES OR ANY
'    DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS,
'    WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS
'    ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR PERFORMANCE
'    OF THIS CODE OR INFORMATION.
'
'==========================================================================
On Error Resume Next

Dim qQuery, objConnection, objCommand, objRecordSet, obj
Dim oRootDSE, strDomain

Set oRootDSE = GetObject("LDAP://rootDSE")
strDomain = oRootDSE.get("defaultNamingContext")

' other categories = computer, user, printqueue, group
qQuery = "<LDAP://" & strDomain &">;" & _
		"(objectCategory=person)" & _
       ";distinguishedName,name;subtree"

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Open "Provider=ADsDSOObject;"
objCommand.ActiveConnection = objConnection
objCommand.CommandText = qQuery
Set objRecordSet = objCommand.Execute

While Not objRecordSet.EOF
    Report = Report & objRecordSet.Fields("name") 
    Set objUser = GetObject("LDAP://" & objRecordSet.Fields("distinguishedName"))
    For Each strGroup In objUser.Groups
    	If InStr(UCase(strGroup.Name),"INTERNET") >0 Then
    		Report = Report & "," & Right(strGroup.Name,Len(strGroup.Name)-3)
    	End If
    Next
    Report = Report & vbCrLf
    Set objUser = Nothing
    objRecordSet.MoveNext
Wend
objConnection.Close
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.CreateTextFile ("InternetGroups.csv", ForWriting)
ts.write Report
ts.Close

Open in new window

0
 

Author Comment

by:cmg-support
ID: 34960894
That's exactly it!  Thank you!!!
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now