Speed up query

Oh, and I'm not sure yet, as the current script I have hasn't finished, but it could probably go faster if all domain groups and their members were enumerated once, it looks like they are being looked up each time they are present on a computer, at least from the wireshark sniffing I have going to watch the progress.
If I knew how to script, I'd write it to:
look up all computers in the domain
  create list of computers in arrays of 10
    look up all groups and the members in the domain
      start separate process, 0-4 (max 8) working on the arrays of 10
       connect to all computers in the domain
         look at who is in the local admin group of the computers
That might not be a good way, I don't know, thought I'd put it out there ;) Programatically, I have no idea how that would be done via vbs/wmi and or powershell.
-rich

what do you mean :

expand local and domain groups if they are members of the local administrators group

do you want all computers include servers ?

Multi-threading, I can see the reasoning, I've used it successfully in the past for big WMI queries. Although with VbScript it's nothing more complex than maintaining a set of separate scripts and a launcher.

Would you consider moving up to a more powerful language? Rather tempted to suggest C#.

Chris

I'm not opposed to using separate scripts, or even files. I'd think it might be easier to use two different files, like saving all the computer names (servers, pc's etc..) to one file, and then also saving domain groups and their members to another file. Then connect to each computer in the domain, have a look at who is in the local admin group, then use the file that contains any domain groups that may be listed, and naturally look up members of local groups if any.
This may also aid in the ease of creating the multi-threading. Also writing to seperate files can give flexibility in what language is then used to parse these files and do the matching. I'm not opposed to other languages like C#, whatever would be useful for anyone looking to do the same thing, and whatever the expert is comfortable with.
-rich

I've looked at this script, but it doesn't seem to work for me even as a test. I changed the ldap query to what has worked for me in the past, but no joy
http://www.microsoft.com/technet/scriptcenter/scripts/templates/default.mspx?mfr=true
Output:
 could not be reached.
 could not be reached.
 could not be reached.
 could not be reached.
etc... Never sends the actual pings...
This one will get the list of the computers, but not connect to them...
http://www.microsoft.com/technet/scriptcenter/scripts/templates/default.mspx?mfr=true
I've inserted various other scripts between the "insert your code -- end portions, with no more success. I can see from wireshark that the list of pcs is being pulled, but not acted on after that.
-rich

The links didn't work so it's very difficult to say why :)

Chris

Didn't notice that... sorry
Ping and connect:
http://www.microsoft.com/technet/scriptcenter/scripts/templates/adsi/basic/tmabvb31.mspx
just connect, no ping:
http://www.microsoft.com/technet/scriptcenter/scripts/templates/adsi/basic/tmabvb12.mspx
-rich

They only use Name to connect, if you didn't have a DNS suffix for the domain on your PC it would fail. I prefer to use dnsHostName, removes at least one bit of ambiguity.

Chris

That was true(dns suffix missing), but still same things... no actual icmp being sent, not trying to connect to machines...

So what do you think... it looks like one can dump a list of computers (servers and all) fairly quickly, and I'd assume you could look up domain groups and their members pretty fast as well. What about then breaking up the list of computers into a few process's, that ping and attempt to connect and look for who is listed in the local admins...
-rich

Ok, spent all day mangling and "frankinstein'ing" this code together, but so far so good...
It pings, it connects and let's me know who is in the local admin group, but it doesn't recursively look up groups and their members... I was hoping not to settle for that...
Improvements needed, and very welcome if anyone has any idea's. It is faster as far as I can tell, or at least I get instant results, than the powershell script.
Change the Ldap strings to suit for testing (line 13)
Oh and I know it's ugly ;)
-rich

On Error Resume Next
Dim objGroup, strComputer, objMember
 
Const ADS_SCOPE_SUBTREE = 2
 
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand =   CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
 
Set objCommand.ActiveConnection = objConnection
objCommand.CommandText = _
"Select Name From 'LDAP://DC=some,DC=company,DC=com' Where objectClass='computer'"
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst
 
 
Do Until objRecordSet.EOF
strComputer = objRecordSet.Fields("Name").Value
 
Set objShell = CreateObject("WScript.Shell")
strCommand = "%comspec% /c ping -n 3 -w 1000 " & strComputer & ""
Set objExecObject = objShell.Exec(strCommand)
 
Do While Not objExecObject.StdOut.AtEndOfStream
strText = objExecObject.StdOut.ReadAll()
If Instr(strText, "Reply") > 0 Then
 
' =====================================================================
' Insert your code here
' =====================================================================
 
Set objWMIService = GetObject _
("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery _
("Select * From Win32_OperatingSystem")
For Each objItem in ColItems
Wscript.Echo strComputer & ": " & objItem.Caption
Next
 
Set objGroup = GetObject("WinNT://" & strComputer & "/Administrators,group")
For Each objMember In objGroup.Members
Wscript.Echo objMember.Name  & " is a local administrator."
Next
 
 
' =====================================================================
' End
' =====================================================================
 
Else
Wscript.Echo strComputer & " could not be reached."
End If
Loop
objRecordSet.MoveNext
Loop

Select allOpen in new window

Like this... recursion with loop prevention using the WinNT provider.

Chris

Option Explicit
 
Const ADS_SCOPE_SUBTREE = 2
 
Sub GetMembers(strADSPath)
  ' Recursive subroutine to return group members
 
  Set objGroup = GetObject(strADSPath)
 
  For Each objMember in objGroup.Members
    If Not objInfLoopPrevention.Exists(objMember.ADSPath) Then
      objInfLoopPrevention.Add objMember.ADSPath, ""
      If objMember.Class = "Group" Then
        GetMembers(objMember.ADSPath)
      Else
        WScript.Echo objMember.ADSPath
      End If
    End If
  Next
 
  Set objGroup = Nothing
End Sub
 
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.CommandText = _
  "Select Name From 'LDAP://DC=some,DC=company,DC=com' WHERE objectClass='computer'"
 
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
 
Dim objRecordSet : Set objRecordSet = objCommand.Execute
 
Do Until objRecordSet.EOF
  Dim strComputer : strComputer = objRecordSet.Fields("Name").Value
 
  Dim objShell : Set objShell = CreateObject("WScript.Shell")
  Dim strCommand : strCommand = "%comspec% /c ping -n 3 -w 1000 " & strComputer & ""
  Dim objExecObject : Set objExecObject = objShell.Exec(strCommand)
 
  Do While Not objExecObject.StdOut.AtEndOfStream
    Dim strText : strText = objExecObject.StdOut.ReadAll()
    If Instr(strText, "Reply") > 0 Then
      ' =====================================================================
      ' Insert your code here
      ' =====================================================================
 
      On Error Resume Next : Err.Clear
      Dim objWMIService : Set objWMIService = GetObject _
        ("winmgmts:\\" & strComputer & "\root\cimv2")
      Dim colItems : Set colItems = objWMIService.ExecQuery _
        ("Select * From Win32_OperatingSystem")
      If Err.Number <> 0 Then
        WScript.Echo strComputer & ": Error connecting to WMI"
      Else
        Dim objItem
        For Each objItem in ColItems
          Wscript.Echo strComputer & ": " & objItem.Caption
        Next
      End If
      On Error Goto 0
 
      Dim objInfLoopPrevention : Set objInfLoopPrevention = CreateObject("Scripting.Dictionary")
 
      WScript.Echo "Local Administrators:"
      GetMembers("WinNT://" & strComputer & "/Administrators,group")
 
      Set objInfLoopPrevention = Nothing
 
      ' =====================================================================
      ' End
      ' =====================================================================
    Else
      Wscript.Echo strComputer & " could not be reached."
    End If
  Loop
  objRecordSet.MoveNext
Loop

Select allOpen in new window

C:\Sandbox\admin-lookup.vbs(8, 3) Microsoft VBScript runtime error: Variable is undefined: 'objGroup'
That was the origianal error, it would echo the computer name an OS, then give that error and exit.

And, can a count of the total 'computers' retrieved, that are going to be attempted be echoed to the top, so it's easier to make sure it tired them all... I changed the script to csv output as well.

Also, ahem, I was able to make the script work, on error resume next, removed "Option Explicit"
However I'd like to not have to force it, rather have the corrections made if possible ;)
So far so good! I think it is almost done, it would still be nice to launch some more process's to speed up further, but I may put that to another question.

On Error Resume Next
 
Const ForAppending = 2
 
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objLogFile = objFSO.CreateTextFile("admins_list.csv", ForWriting, True)
 
Const ADS_SCOPE_SUBTREE = 2
 
Sub GetMembers(strADSPath)
  ' Recursive subroutine to return group members
 
  Set objGroup = GetObject(strADSPath)
 
  For Each objMember in objGroup.Members
    If Not objInfLoopPrevention.Exists(objMember.ADSPath) Then
      objInfLoopPrevention.Add objMember.ADSPath, ""
      If objMember.Class = "Group" Then
        GetMembers(objMember.ADSPath)
      Else
        objLogFile.Write objMember.ADSPath & ", "
      End If
    End If
  Next
 
  Set objGroup = Nothing
End Sub
 
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.CommandText = _
  "Select Name From 'LDAP://DC=some,DC=company,DC=com' WHERE objectClass='computer'"
 
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
 
Dim objRecordSet : Set objRecordSet = objCommand.Execute
 
Do Until objRecordSet.EOF
  Dim strComputer : strComputer = objRecordSet.Fields("Name").Value
 
  Dim objShell : Set objShell = CreateObject("WScript.Shell")
  Dim strCommand : strCommand = "%comspec% /c ping -n 2 -w 800 " & strComputer & ""
  Dim objExecObject : Set objExecObject = objShell.Exec(strCommand)
 
  Do While Not objExecObject.StdOut.AtEndOfStream
    Dim strText : strText = objExecObject.StdOut.ReadAll()
    If Instr(strText, "Reply") > 0 Then
      ' =====================================================================
      ' Insert your code here
      ' =====================================================================
 
      On Error Resume Next : Err.Clear
      Dim objWMIService : Set objWMIService = GetObject ("winmgmts:\\" & strComputer & "\root\cimv2")
      Dim colItems : Set colItems = objWMIService.ExecQuery ("Select * From Win32_OperatingSystem")
      If Err.Number <> 0 Then
        objLogFile.Write strComputer & ", Error connecting to WMI"
        objLogFile.Writeline
      Else
        Dim objItem
        For Each objItem in ColItems
          objLogFile.Write strComputer & ", " & objItem.Caption
        Next
        objLogFile.Writeline
      End If
      On Error Goto 0
 
      Dim objInfLoopPrevention : Set objInfLoopPrevention = CreateObject("Scripting.Dictionary")
 
      objLogFile.Write "Local Administrators, "
      GetMembers("WinNT://" & strComputer & "/Administrators,group")
 
      Set objInfLoopPrevention = Nothing
 
      ' =====================================================================
      ' End
      ' =====================================================================
    Else
      objLogFile.Write strComputer & ", could not be reached."
      objLogFile.Writeline
    End If
  Loop
  objRecordSet.MoveNext
Loop
objLogFile.Close

Select allOpen in new window

Oops. Just needed Dim objGroup adding as below. Fixed here.

I popped in basic error handling for the GetMembers subroutine as well. I figure there's a chance it might fail to connect even if the system responds to Ping.

Chris

Option Explicit
 
Const ADS_SCOPE_SUBTREE = 2
 
Sub GetMembers(strADSPath)
  ' Recursive subroutine to return group members
 
  On Error Resume Next : Err.Clear
  Dim objGroup : Set objGroup = GetObject(strADSPath)
  If Err.Number = 0 Then 
    For Each objMember in objGroup.Members
      If Not objInfLoopPrevention.Exists(objMember.ADSPath) Then
        objInfLoopPrevention.Add objMember.ADSPath, ""
        If objMember.Class = "Group" Then
          GetMembers(objMember.ADSPath)
        Else
          WScript.Echo objMember.ADSPath
        End If
      End If
    Next
  End If
  On Error Goto 0
 
  Set objGroup = Nothing
End Sub
 
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.CommandText = _
  "Select Name From 'LDAP://DC=some,DC=company,DC=com' WHERE objectClass='computer'"
 
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
 
Dim objRecordSet : Set objRecordSet = objCommand.Execute
 
Do Until objRecordSet.EOF
  Dim strComputer : strComputer = objRecordSet.Fields("Name").Value
 
  Dim objShell : Set objShell = CreateObject("WScript.Shell")
  Dim strCommand : strCommand = "%comspec% /c ping -n 3 -w 1000 " & strComputer & ""
  Dim objExecObject : Set objExecObject = objShell.Exec(strCommand)
 
  Do While Not objExecObject.StdOut.AtEndOfStream
    Dim strText : strText = objExecObject.StdOut.ReadAll()
    If Instr(strText, "Reply") > 0 Then
      ' =====================================================================
      ' Insert your code here
      ' =====================================================================
 
      On Error Resume Next : Err.Clear
      Dim objWMIService : Set objWMIService = GetObject _
        ("winmgmts:\\" & strComputer & "\root\cimv2")
      Dim colItems : Set colItems = objWMIService.ExecQuery _
        ("Select * From Win32_OperatingSystem")
      If Err.Number <> 0 Then
        WScript.Echo strComputer & ": Error connecting to WMI"
      Else
        Dim objItem
        For Each objItem in ColItems
          Wscript.Echo strComputer & ": " & objItem.Caption
        Next
      End If
      On Error Goto 0
 
      Dim objInfLoopPrevention : Set objInfLoopPrevention = CreateObject("Scripting.Dictionary")
 
      WScript.Echo "Local Administrators:"
      GetMembers("WinNT://" & strComputer & "/Administrators,group")
 
      Set objInfLoopPrevention = Nothing
 
      ' =====================================================================
      ' End
      ' =====================================================================
    Else
      Wscript.Echo strComputer & " could not be reached."
    End If
  Loop
  objRecordSet.MoveNext
Loop

Select allOpen in new window

Thanks, the admins aren't populating :( The script I *hacked* was working in that regard. When the hacked version was working, it was throwing out WinNT://domain/username ... can the winnt:// be trimmed, and can a "count" of the total computers be echo'd at the beginning?
thanks again!
-rich

Sorry, I missed another Dim out.

Trimmed off the WinNT:// part and added an echo of the Record Count before it starts looping through.

Chris

Option Explicit
 
Const ADS_SCOPE_SUBTREE = 2
 
Sub GetMembers(strADSPath)
  ' Recursive subroutine to return group members
 
  On Error Resume Next : Err.Clear
  Dim objGroup : Set objGroup = GetObject(strADSPath)
  If Err.Number = 0 Then 
    Dim objMember
    For Each objMember in objGroup.Members
      If Not objInfLoopPrevention.Exists(objMember.ADSPath) Then
        objInfLoopPrevention.Add objMember.ADSPath, ""
        If objMember.Class = "Group" Then
          GetMembers(objMember.ADSPath)
        Else
          WScript.Echo Replace(objMember.ADSPath, "WinNT://" , "")
        End If
      End If
    Next
  End If
  On Error Goto 0
 
  Set objGroup = Nothing
End Sub
 
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.CommandText = _
  "Select Name From 'LDAP://DC=some,DC=company,DC=com' WHERE objectClass='computer'"
 
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
 
Dim objRecordSet : Set objRecordSet = objCommand.Execute
 
WScript.Echo "Number of Computers: " & objRecordSet.RecordCount
 
Do Until objRecordSet.EOF
  Dim strComputer : strComputer = objRecordSet.Fields("Name").Value
 WScript.Echo strComputer
  Dim objShell : Set objShell = CreateObject("WScript.Shell")
  Dim strCommand : strCommand = "%comspec% /c ping -n 3 -w 1000 " & strComputer & ""
  Dim objExecObject : Set objExecObject = objShell.Exec(strCommand)
 
  Do While Not objExecObject.StdOut.AtEndOfStream
    Dim strText : strText = objExecObject.StdOut.ReadAll()
    If Instr(strText, "Reply") > 0 Then
      ' =====================================================================
      ' Insert your code here
      ' =====================================================================
 
      On Error Resume Next : Err.Clear
      Dim objWMIService : Set objWMIService = GetObject _
        ("winmgmts:\\" & strComputer & "\root\cimv2")
      Dim colItems : Set colItems = objWMIService.ExecQuery _
        ("Select * From Win32_OperatingSystem")
      If Err.Number <> 0 Then
        WScript.Echo strComputer & ": Error connecting to WMI"
      Else
        Dim objItem
        For Each objItem in ColItems
          Wscript.Echo strComputer & ": " & objItem.Caption
        Next
      End If
      On Error Goto 0
 
      Dim objInfLoopPrevention : Set objInfLoopPrevention = CreateObject("Scripting.Dictionary")
 
      WScript.Echo "Local Administrators:"
      GetMembers("WinNT://" & strComputer & "/Administrators,group")
 
      Set objInfLoopPrevention = Nothing
 
      ' =====================================================================
      ' End
      ' =====================================================================
    Else
      Wscript.Echo strComputer & " could not be reached."
    End If
  Loop
  objRecordSet.MoveNext
Loop

Select allOpen in new window

That is working well! Does it make sense (speed wise) to look up all domain groups and break down their members (if not empty) perhaps before running a script like the above, so that this script can then look to that file to do the domain group break downs, instead of having to keep looking up "domain admins", "backup operators" etc... I can make that a separate question if you think it's warranted. You'd still have to look at the computers themselves for local groups and members...
-rich

It depends how big the domain is I guess :)

If the domain is small, it does make sense because you're not thinking about a lot of data.

Doing that is extremely questionable in larger domains. I'd drop back to my original script, removing the focus on Administrators and simply enumerating the local groups on the system. Although the search would be a bit more focused than just all computers in the domain.

Chris

Ok, last thing, I promise for this one ;) Can it toggle the break-down on/off via command line.. can I list the just the admin users, no break down of group members, or run the script and use a /bdg or something to break down the group users?
I asked something similar for a different script here:
https://www.experts-exchange.com/questions/23546376/cli-arguments-to-run-cerain-sections-of-vbs-wmi-script.html
I am going to open another Q in a few...
-rich

Sure, we can just add a flag in to say whether or not to use recursion. With this it simply echoes the group as a member rather than trying to get membership from there.

Chris

Option Explicit
 
Const ADS_SCOPE_SUBTREE = 2
Const ENABLE_RECURSION = False
 
Sub GetMembers(strADSPath)
  ' Recursive subroutine to return group members
 
  On Error Resume Next : Err.Clear
  Dim objGroup : Set objGroup = GetObject(strADSPath)
  If Err.Number = 0 Then 
    Dim objMember
    For Each objMember in objGroup.Members
      If Not objInfLoopPrevention.Exists(objMember.ADSPath) Then
        objInfLoopPrevention.Add objMember.ADSPath, ""
        If objMember.Class = "Group" And ENABLE_RECURSION = True Then
          GetMembers(objMember.ADSPath)
        Else
          WScript.Echo Replace(objMember.ADSPath, "WinNT://" , "")
        End If
      End If
    Next
  End If
  On Error Goto 0
 
  Set objGroup = Nothing
End Sub
 
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.CommandText = _
  "Select Name From 'LDAP://DC=some,DC=company,DC=com' WHERE objectClass='computer'"
 
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
 
Dim objRecordSet : Set objRecordSet = objCommand.Execute
 
WScript.Echo "Number of Computers: " & objRecordSet.RecordCount
 
Do Until objRecordSet.EOF
  Dim strComputer : strComputer = objRecordSet.Fields("Name").Value
 WScript.Echo strComputer
  Dim objShell : Set objShell = CreateObject("WScript.Shell")
  Dim strCommand : strCommand = "%comspec% /c ping -n 3 -w 1000 " & strComputer & ""
  Dim objExecObject : Set objExecObject = objShell.Exec(strCommand)
 
  Do While Not objExecObject.StdOut.AtEndOfStream
    Dim strText : strText = objExecObject.StdOut.ReadAll()
    If Instr(strText, "Reply") > 0 Then
      ' =====================================================================
      ' Insert your code here
      ' =====================================================================
 
      On Error Resume Next : Err.Clear
      Dim objWMIService : Set objWMIService = GetObject _
        ("winmgmts:\\" & strComputer & "\root\cimv2")
      Dim colItems : Set colItems = objWMIService.ExecQuery _
        ("Select * From Win32_OperatingSystem")
      If Err.Number <> 0 Then
        WScript.Echo strComputer & ": Error connecting to WMI"
      Else
        Dim objItem
        For Each objItem in ColItems
          Wscript.Echo strComputer & ": " & objItem.Caption
        Next
      End If
      On Error Goto 0
 
      Dim objInfLoopPrevention : Set objInfLoopPrevention = CreateObject("Scripting.Dictionary")
 
      WScript.Echo "Local Administrators:"
      GetMembers("WinNT://" & strComputer & "/Administrators,group")
 
      Set objInfLoopPrevention = Nothing
 
      ' =====================================================================
      ' End
      ' =====================================================================
    Else
      Wscript.Echo strComputer & " could not be reached."
    End If
  Loop
  objRecordSet.MoveNext
Loop

Select allOpen in new window

Your the man... you make this look so easy, I love it!
-rich

That is perfect... I could keep you busy for weeks writing stuff like this...
I've just posted another question here:
https://www.experts-exchange.com/questions/24137674/speed-up-group-membership.html
I have 1000+ computers in several domains, 90% are pingable, so it's quite slow still even without recursion, let's explore ways to speed it up more!
-rich