Script that scans IP range and gets user information in Excel sheet. Processing information
I need help with a VBS script that creates an Excel sheet with specific user information.
It works ... Sorting. The problem is that it seems to be processing information to produce inaccurate results. Does anyone know how I would like to make script areas blank in an Excel document when no information is available? I know this is possible, it just needs to be nudged in the right direction.
Thank!
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
source to share
You are using the global value EVIL On Error Resume Next
. This means: all errors are ignored / hidden, and the script continues (more or less happily) for all practical purposes undefined. 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
Strictly local OERN ensures that the specific problem (division by zero, ping failure) and all other exceptions are fixed , so the program can be improved.
source to share
Your WMI call variables must be reset to zero before you set them again. This script should perform 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")
source to share