We help IT Professionals succeed at work.

Why is my VB script limiting results to 1500 ?

ISG_Query
ISG_Query used Ask the Experts™
on
Hi all-knowing ones !

We use the vb script below to extract the membership of a Global security group. We have a few groups with over 2000 users in them. When used, we seem unable to get it to give us more than 1500 names, regardless of the two search parameter settings. Please could you advise if this is a limitation, or can we fix it ?

-----------------------------------------------------------------------------------------------------------------
Set objShell = CreateObject("Wscript.Shell")
Set objFS = CreateObject("Scripting.FileSystemObject")

'*** Ask for username
strUser = Inputbox("Please enter the name of the Security Group you wish to list.")

If strUser = "" then
      Msgbox "No GroupID entered! Script will now exit."
      WScript.Quit
End If

'*** Declare variables and constants

Dim con
Dim rs
Dim Com
Dim objDataScript
Const DATA_FOLDER = "C:\Scripts"
Const DATA_SCRIPT = "C:\Scripts\AD Security Group Membership results.txt"
GetADUserDetails = 0


'*** Setup an ADO query to get the values from the target object.
Set con = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Set Com = CreateObject("ADODB.Command")

'*** Open a Connection object.
con.Provider = "ADsDSOObject"
con.Open "Active Directory Provider"

'*** Create a command object on this connection.
Set Com.ActiveConnection = con

'*** Collect ADsPath, sAMAccountName and cn details for target object
Com.CommandText = "<LDAP://invicta.cantium.net>;(samAccountName=" & strUser & ");ADsPath,sAMAccountName,cn"

'*** Set the preferences for Search.
Com.Properties("Page Size") = 2000
Com.Properties("Timeout") = 60

'*** Execute the query.
Set rs = Com.Execute
If rs.RecordCount = 1 Then
      strPath = rs.Fields(0).Value '*** Store path to users account
Else
      GetADUserDetails = 1 '*** User not found
End If



If GetADUserDetails = 1 then
      Msgbox "Folder name entered not found in directory."
      WScript.Quit
Else       '*** User exists so continue
      If Not objFS.FolderExists(DATA_FOLDER) Then            '*** Check for folder existence, if not found then create
            Set objFolder = objFS.CreateFolder(DATA_FOLDER)
      End If
      
      If objFS.FileExists(DATA_SCRIPT) Then      '*** File exists so append information to it
            On Error Resume Next
            Set objDataScript = objFS.OpenTextFile(DATA_SCRIPT,8,True)
            objDataScript.WriteBlankLines (1)
            objDataScript.WriteLine "**********************************"
            objDataScript.WriteLine "*            " & strUser
            objDataScript.WriteLine "**********************************"
            Set objUser = GetObject(strPath)

            arrMemberOf = objUser.GetEx("member")
            For Each Group in arrMemberOf
          Set objGroup = GetObject("LDAP://" & Group)
                                                
          GroupCN = objGroup.CN
          objDataScript.WriteLine ""& GroupCN
                                                
            Next
            objDataScript.Close
            MsgBox "Finished!"
            
      Else
            Set objDataScript = objFS.CreateTextFile(DATA_SCRIPT,True)      '*** File does not exist so create and then write to it
            On Error Resume Next
            Set objDataScript = objFS.OpenTextFile(DATA_SCRIPT,True)
            objDataScript.WriteLine "**********************************"
            objDataScript.WriteLine "*            " & strUser
            objDataScript.WriteLine "**********************************"
            Set objUser = GetObject(strPath)

            arrMemberOf = objUser.GetEx("member")            '*** Get users group membership
      For Each Group in arrMemberOf                        '*** Loop through groups
          Set objGroup = GetObject("LDAP://" & Group)
                  '*** Check group is dist type or universal sec group
          GroupCN = objGroup.CN                                    '*** Get group name
          objDataScript.WriteLine ""& GroupCN            '*** Write group name
      Next
            objDataScript.Close
            MsgBox "Finished!"                                          '*** Indicate completion of process
            
      End If
End If

-----------------------------------------------------------------------------------------------------------------

Many thanks. Any help will be hugely appreciated.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Lee OsborneSenior Infrastructure Engineer

Commented:
This should give you some more information. The limit for retrieving a large query is 1,500 results in Windows Server 2003.

http://msdn.microsoft.com/en-us/library/ms180907.aspx

Lee
Chris DentPowerShell Developer
Top Expert 2010

Commented:

Rather than do this:

arrMemberOf = objUser.GetEx("member")

You might be better running a search for the members with:

(memberOf=CN=GroupName,OU=somewhere,DC=domain,DC=com)

It gets around the 1500 limit and removes the need for you to do range retrieval.

Chris

Author

Commented:
Excellent ! Thank you both for your replies.

Chris-Dent : As a newbie, I'm unsure as to the changes I'd need to make to use your method. Could you guide me to the correct syntax to replace what I have ?

Thanks again guys.
Chris DentPowerShell Developer
Top Expert 2010

Commented:

Sure, it would to something like this.

One of the changes I've made is this:

objCommand.Properties("Page Size") = 1000

There's no point in setting the value higher than 1000 as the real limit is in AD. It is enough to pass a page size value in to enable paging.

Please yell if you have any questions.

Chris
Option Explicit

' *** Declare variables and constants

Const DATA_FOLDER = "C:\Scripts"
Const DATA_SCRIPT = "C:\Scripts\AD Security Group Membership results.txt"

Const DOMAIN_NAME = "invicta.cantium.net"

' *** Ask for username

Dim strUser : strUser = Inputbox("Please enter the name of the Security Group you wish to list.")

If strUser = "" then
  Msgbox "No GroupID entered! Script will now exit."
  WScript.Quit
End If

' *** Setup an ADO query to get the values from the target object.

Dim objConnection : Set objConnection = CreateObject("ADODB.Connection")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"

Dim objCommand : Set objCommand = CreateObject("ADODB.Command")
Set objCommand.ActiveConnection = objConnection
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Timeout") = 60

' *** Collect ADsPath, sAMAccountName and cn details for target object

objCommand.CommandText = "<LDAP://" & DOMAIN_NAME & ">;(samAccountName=" & strUser & ");distinguishedName,sAMAccountName,cn"

' *** Execute the query.
Dim objRS : Set objRS = objCommand.Execute
If objRS.RecordCount <> 1 Then

  Msgbox "Folder name entered not found in directory."
  WScript.Quit

Else

  Dim objFSO : Set objFSO = CreateObject("Scripting.FileSystemObject")

  ' *** Check for folder existence, if not found then create

  If Not objFSO.FolderExists(DATA_FOLDER) Then
    Dim objFolder : Set objFolder = objFSO.CreateFolder(DATA_FOLDER)
  End If

  Dim objDataScript 
  If objFSO.FileExists(DATA_SCRIPT) Then

    ' *** File exists so append information to it

    Set objDataScript = objFSO.OpenTextFile(DATA_SCRIPT, 8, True)
    objDataScript.WriteBlankLines(1)

  Else

    '*** File does not exist so create and then write to it

    Set objDataScript = objFSO.CreateTextFile(DATA_SCRIPT, True)

  End If

  objDataScript.WriteLine "**********************************"
  objDataScript.WriteLine "*            " & strUser
  objDataScript.WriteLine "**********************************"

  ' *** Store path to users account

  Dim strDN : strDN = objRS.Fields("distinguishedName").Value

  ' *** Get the groups this user belongs to

  objCommand.CommandText = "<LDAP://" & DOMAIN_NAME & ">;(member=" & strDN & ");Name,cn,groupType"
  Set objRS = objCommand.Execute

  Do Until objRS.EOF

    objDataScript.WriteLine objRS.Fields("cn").Value

    objRS.MoveNext

  Loop

End If

' *** Indicate completion of process
MsgBox "Finished!"

Open in new window

Author

Commented:
Hey Chris

I've tried your text as-is, and I get the group name in the header, which is written to the file, but no results ping back beyond the header. The search also comes back very quickly saying it is finished.

Is there anything else I need to customise ?  It all looks the same and the domain name is correct.

Thank you
Chris DentPowerShell Developer
Top Expert 2010

Commented:

I was a bit confused about one aspect actually. You reference this as objUser, but then you're pulling the member attribute?

arrMemberOf = objUser.GetEx("member")            '*** Get users group membership

A group would have the member attribute, but not a user. Are you actually passing a group name in and expecting the members of that group back? Mine is written to return the groups a user belongs to.

Chris

Author

Commented:
Hi. Apologies for the confusion - I pulled apart an older script, so some of the references and help texts / comments are still inaccurate.

I am passing in a group name and wanting a list of users who are in the group.

"Member" is the reference to the "Members" tab in the AD object, where the users list is. The old script connects to the object and pulls the list from this tab.

Thanks
PowerShell Developer
Top Expert 2010
Commented:

Got it, sorry for the confusion... updated :)

Chris
Option Explicit

' *** Declare variables and constants

Const DATA_FOLDER = "C:\Scripts"
Const DATA_SCRIPT = "C:\Scripts\AD Security Group Membership results.txt"

Const DOMAIN_NAME = "invicta.cantium.net"

' *** Ask for  group name

Dim strGroup : strGroup= Inputbox("Please enter the name of the Security Group you wish to list.")

If strGroup = "" then
  Msgbox "No GroupID entered! Script will now exit."
  WScript.Quit
End If

' *** Setup an ADO query to get the values from the target object.

Dim objConnection : Set objConnection = CreateObject("ADODB.Connection")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"

Dim objCommand : Set objCommand = CreateObject("ADODB.Command")
Set objCommand.ActiveConnection = objConnection
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Timeout") = 60

' *** Collect ADsPath, sAMAccountName and cn details for target object

objCommand.CommandText = "<LDAP://" & DOMAIN_NAME & ">;(samAccountName=" & strGroup & ");distinguishedName,sAMAccountName,cn"

' *** Execute the query.
Dim objRS : Set objRS = objCommand.Execute
If objRS.RecordCount <> 1 Then

  Msgbox "Folder name entered not found in directory."
  WScript.Quit

Else

  Dim objFSO : Set objFSO = CreateObject("Scripting.FileSystemObject")

  ' *** Check for folder existence, if not found then create

  If Not objFSO.FolderExists(DATA_FOLDER) Then
    Dim objFolder : Set objFolder = objFSO.CreateFolder(DATA_FOLDER)
  End If

  Dim objDataScript 
  If objFSO.FileExists(DATA_SCRIPT) Then

    ' *** File exists so append information to it

    Set objDataScript = objFSO.OpenTextFile(DATA_SCRIPT, 8, True)
    objDataScript.WriteBlankLines(1)

  Else

    '*** File does not exist so create and then write to it

    Set objDataScript = objFSO.CreateTextFile(DATA_SCRIPT, True)

  End If

  objDataScript.WriteLine "**********************************"
  objDataScript.WriteLine "*            " & strGroup
  objDataScript.WriteLine "**********************************"

  ' *** Store path to users account

  Dim strDN : strDN = objRS.Fields("distinguishedName").Value

  ' *** Get the groups this user belongs to

  objCommand.CommandText = "<LDAP://" & DOMAIN_NAME & ">;(memberOf=" & strDN & ");Name,cn"
  Set objRS = objCommand.Execute

  Do Until objRS.EOF

    objDataScript.WriteLine objRS.Fields("cn").Value

    objRS.MoveNext

  Loop

End If

' *** Indicate completion of process
MsgBox "Finished!"

Open in new window

Author

Commented:
Awesome advice ! Many thanks Chris. It works really well and really fast.

Any suggestions where I can learn more ? Books / sites ?
Chris DentPowerShell Developer
Top Expert 2010

Commented:

A small collection of links :)

MS Scripting Guys: http://technet.microsoft.com/en-us/scriptcenter/default.aspx
Guy Thomas: http://www.computerperformance.co.uk/
Richard Mueller: http://www.rlmueller.net/
ActiveXperts: http://www.activexperts.com/activmonitor/windowsmanagement/adminscripts/

Now you have all those...

If you're only just moving into scripting you might look at PowerShell instead. All we have above can be refined down to this:


$Group = "Some Group"
$File = "AD Security Group Membership results.txt"
"**********************************" >> $File
"*            $Group" >> $File
"**********************************" >> $File
Get-QADGroupMember $Group | ForEach-Object { $_.SamAccountName >> $File }


Essentially that's one line, the first are fluff :)

Chris