Script that scans IP range and gets user information to Excel sheet. Recycles information

Question

I need help with a VBS script that produces an Excel sheet with specific user information.

It works... Sort of. The problem is that it seems to recycle information producing inaccurate results. Anybody know how I would go about making the script leave areas in the Excel document blank when no information is available? I know it's possible, just need a nudge in the right direction.

Thank you!

On Error Resume Next

Dim FSO
Dim objStream

Const TriStateFalse = 0
Const FILE_NAME = "Users.csv"

Set FSO = CreateObject("Scripting.FileSystemObject")

Set objStream = FSO.CreateTextFile(FILE_NAME, _
True, TristateFalse)

strSubnetPrefix = "192.168.1."
intBeginSubnet = 1
intEndSubnet = 254

For i = intBeginSubnet To intEndSubnet
strComputer = strSubnetPrefix & i
    'strcomputer = inputbox("Enter Computer Name or IP")
    if strcomputer = "" then
        wscript.quit
    else

    Set objPing = GetObject("winmgmts:{impersonationLevel=impersonate}").ExecQuery _
        ("select * from Win32_PingStatus where address = '" & strcomputer & "'")
    For Each objStatus in objPing
        If IsNull(objStatus.StatusCode) or objStatus.StatusCode<>0 Then 
            'request timed out
            'msgbox(strcomputer & " did not reply" & vbcrlf & vbcrlf & _
                    '"Please check the name and try again")
        else

            set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & _
        strComputer & "\root\cimv2")
            Set colSettings = objWMIService.ExecQuery("Select * from Win32_ComputerSystem")
            For Each objComputer in colSettings 
                                    objStream.WriteLine objComputer.name & "," & objcomputer.username & "," & objcomputer.domain _
                & "," & strcomputer
                'msgbox("System Name: " & objComputer.Name & vbcrlf & "User Logged in : " & _
                'objcomputer.username  & vbcrlf & "Domain: " & objComputer.Domain)
            Next
        end if
    next
    end if
Next

Msgbox("Done Collecting")

set objwmiservice = nothing
set colsettings = nothing
set objping = nothing

Hard to tell from you snippet, but could it be that you are not re-setting your object before populating it with new data? If you don't clear out the old data, it will be retained. — Ron Rosenfeld, Jul 10 '14 at 17:53

score 0 · Answer 1 · edited May 23 '17 at 11:57

You use the EVIL global On Error Resume Next. That means: all errors are ignored/hidden and the script continues (more or less happily) in a for all practical purposes undefined state. Demo script:

Option Explicit

Dim a : a = Array(1,0,2)

Bad a
Good a

Sub Bad(a)
    Dim i, n
   On Error Resume Next
    For i = 0 To UBound(a)
        n = 4712 / a(i)
        WScript.Echo "Bad", i, a(i), n
    Next
End Sub

Sub Good(a)
    Dim i, n
    For i = 0 To UBound(a)
      On Error Resume Next
        n = 4712 / a(i)
        If Err.Number Then n = "value to use in case of error"
      On Error GoTo 0
        WScript.Echo "Good", i, a(i), n
    Next
End Sub

output:

cscript oern.vbs
Bad 0 1 4712
Bad 1 0 4712  <--- assignment failed, 'old' value of n retained, no clue about problem
Bad 2 2 2356
Good 0 1 4712
Good 1 0 value to use in case of error
Good 2 2 2356

The strictly local OERN makes sure that the specific problem (division by zero, ping failure) is dealt with, and all other exceptions are reported, so the program can be improved.

further food for thought

score 0 · Answer 2 · answered Aug 30 '14 at 17:43

Your WMI call variables need to be reset to nothing before you set them again. This script should work better.

On Error Resume Next

Dim FSO
Dim objStream

Const TriStateFalse = 0
Const FILE_NAME = "Users.csv"

Set FSO = CreateObject("Scripting.FileSystemObject")

Set objStream = FSO.CreateTextFile(FILE_NAME, _
True, TristateFalse)

strSubnetPrefix = "192.168.1."
intBeginSubnet = 1
intEndSubnet = 254

For i = intBeginSubnet To intEndSubnet
strComputer = strSubnetPrefix & i
    'strcomputer = inputbox("Enter Computer Name or IP")
    if strcomputer = "" then
        wscript.quit
    else

    Set objPing = GetObject("winmgmts:{impersonationLevel=impersonate}").ExecQuery _
        ("select * from Win32_PingStatus where address = '" & strcomputer & "'")
    For Each objStatus in objPing
        If IsNull(objStatus.StatusCode) or objStatus.StatusCode<>0 Then 
            'request timed out
            'msgbox(strcomputer & " did not reply" & vbcrlf & vbcrlf & _
                    '"Please check the name and try again")
        else

            set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & _
        strComputer & "\root\cimv2")
            Set colSettings = objWMIService.ExecQuery("Select * from Win32_ComputerSystem")
            For Each objComputer in colSettings 
                                    objStream.WriteLine objComputer.name & "," & objcomputer.username & "," & objcomputer.domain _
                & "," & strcomputer
                'msgbox("System Name: " & objComputer.Name & vbcrlf & "User Logged in : " & _
                'objcomputer.username  & vbcrlf & "Domain: " & objComputer.Domain)
            Next
            set objwmiservice = nothing
            set colsettings = nothing
        end if
    next
    end if
    set objping = nothing
Next

Msgbox("Done Collecting")

Script that scans IP range and gets user information to Excel sheet. Recycles information

2 Answers2

Linked