Link to home
Start Free TrialLog in
Avatar of TheBartolini
TheBartoliniFlag for United States of America

asked on

Having trouble with output to CSV with a VBS file

I need to create a report of many machines to see what service pack they have.
I found a script on EE to do that. However I need to login to all the machines using the local ADMIN account.
I am using the following RUNAS command;

runas /noprofile /env /user:%computername%\administrator "c:\windows\system32\cscript.exe whatsp.vbs /list:input.txt > spresult.csv"

I get the prompt for the password as expected BUT the output is displayed in a CMD window instead of creating the CSV file.

What is wrong with the syntax???

Here is the script:

// EDITOR'S NOTE: View the code using your browser's Source View.
// Prior to using this file, copy and paste  
// the contents below into a plain text editor, then
// save the files as SPInventory.wsf; ' don't include the
// ".txt" extension in the final file.

<?xml version="1.0" ?>
<package>
      <job id="ListServicePack" prompt="no">
            <?job error="false" debug="false" ?>
            <runtime>
                  <description>
Lists installed service pack version on one or more computers.

Use only one of the following:
  /list:filename    : text file containing one computer name per line
  /container:ouname : name of an OU containing computer accounts
  /computer:name    : run command against single specified computer

Other arguments are optional.
                  </description>
                  <named helpstring="Text file to pull computer names from" name="list" required="false" type="string"/>
                  <named helpstring="OU to pull computer names from" name="container" required="false" type="string"/>
                  <named helpstring="Run command against single specified computer" name="computer" required="false" type="string"/>
                  <named helpstring="Path and filename to write output to" name="output" required="false" type="string"/>
            </runtime>
            <object id="fso" progid="Scripting.FileSystemObject"/>
            <script id="MultiComputer" language="VBScript">
<![CDATA[
'----------------------------------------------------------
' List sp level
'----------------------------------------------------------
' Supported: 2003,XP,2000,NT4

'make sure we're running from CScript, not WScript
If LCase(Right(WScript.FullName,11)) <> "cscript.exe" Then
      If MsgBox("This script is designed to work with CScript, but you are running it under WScript. " & _
       "This script may produce a large number of dialog boxes when running under WScript, which you may " & _
       "find to be inefficient. Do you want to continue anyway?",4+256+32,"Script host warning") = 7 Then
            WScript.Echo "Tip: Run ""Cscript //h:cscript"" from a command-line to make CScript the default scripting host."
            WScript.Quit
      End If
End If

'count arguments
Dim iArgs
If WScript.Arguments.Named.exists("computer") Then iArgs = iArgs + 1
If WScript.Arguments.Named.exists("container") Then iArgs = iArgs + 1
If WScript.Arguments.Named.exists("list") Then iArgs = iArgs + 1
If iArgs <> 1 Then
      WScript.Echo "Must specify either /computer, /container, or /list arguments."
      WScript.Echo "May not specify more than one of these arguments."
      WScript.Echo "Run command again with /? argument for assistance."
      WScript.Quit
End If

'if ping requested, make sure we're on XP or later
Dim bPingAvailable, oLocalWMI, cWindows, oWindows
bPingAvailable = False
Set oLocalWMI = GetObject("winmgmts:\\.\root\cimv2")
Set cWindows = oLocalWMI.ExecQuery("Select BuildNumber from Win32_OperatingSystem",,48)
For Each oWindows In cWindows
      If oWindows.BuildNumber >= 2600 Then
            bPingAvailable = True
      End If
Next

'was ping requested?
If WScript.Arguments.Named.Exists("ping") Then
      If bPingAvailable Then
            Verbose "will attempt to ping all connections to improve performance"
      Else
            WScript.Echo "*** /ping not supported prior to Windows XP"
      End If
End If

'output report header
If WScript.Arguments.Named.Exists("output") Then
    LogFile WScript.Arguments.Named("output"),"computer,os,spversion",True
Else
    WScript.Echo "Computer, OperatingSystem, SP Version"
End If  

'either /list, /computer, or /container was specified:
Dim sName
If WScript.Arguments.Named("list") <> "" Then
      'specified list - read names from file
      Dim oFSO, oTS
      Verbose "Reading names from file " & WScript.Arguments.Named("list")
      Set oFSO = WScript.CreateObject("Scripting.FileSystemObject")
      On Error Resume Next
      Set oTS = oFSO.OpenTextFile(WScript.Arguments.Named("list"))
      If Err <> 0 Then
            WScript.Echo "Error opening " & WScript.Arguments.Named("list")
            WScript.Echo Err.Description
            WScript.Quit
      End If
      Do Until oTS.AtEndOfStream
            sName = oTS.ReadLine
            TakeAction sName
      Loop
      oTS.Close

Elseif WScript.Arguments.Named("container") <> "" Then
      'specified container - read names from AD
      Dim oObject, oRoot, oChild
      Verbose "Reading names from AD container " & WScript.Arguments.Named("container")
      On Error Resume Next
      Set oRoot = GetObject("LDAP://rootDSE")
      If Err <> 0 Then
            WScript.Echo "Error connecting to default Active Directory domain"
            WScript.Echo Err.Description
            WScript.Quit
      End If
      Set oObject = GetObject("LDAP://ou=" & WScript.Arguments.Named("container") & _
       "," & oRoot.Get("defaultNamingContext"))
      If Err <> 0 Then
            WScript.Echo "Error opening organizational unit " & WScript.Arguments.Named("container")
            WScript.Echo Err.Description
            WScript.Quit
      End If
      WorkWithOU oObject

Elseif WScript.Arguments.Named("computer") <> "" Then
      'specified single computer
      Verbose "Running command against " & WScript.Arguments.Named("computer")
      TakeAction WScript.Arguments.Named("computer")

End If

'display output so user will know script finished
WScript.Echo "Command completed."

' ----------------------------------------------------------------------
' Sub WorkWithOU
'
' Iterates child objects in OU; calls itself to handle sub-OUs If
' /recurse argument supplied
' ----------------------------------------------------------------------
Sub WorkWithOU(oObject)
      For Each oChild In oObject
            Select Case oChild.Class
                  Case "computer"
                        TakeAction Right(oChild.Name,len(oChild.name)-3)
                  Case "user"
                  Case "organizationalUnit"
                        If WScript.Arguments.Named.Exists("recurse") Then
                              'recursing sub-OU
                              Verbose "Working In " & oChild.Name
                              WorkWithOU oChild
                        End If
            End Select
      Next
End Sub

' ----------------------------------------------------------------------
' Sub TakeAction
'
' Makes connection and performs command-specific code
' ----------------------------------------------------------------------
Sub TakeAction(sName)

      'verbose output?
      Verbose "Connecting to " & sName

      'ping before connecting?
      If WScript.Arguments.Named.Exists("ping") Then
            If Not TestPing(sName,bPingAvailable) Then
                  LogBadConnect(sName)
                  Exit Sub
            End If
      End If

 
      '#############################################
      '#          COMMAND CODE GOES HERE           #
      '#-------------------------------------------#
      '#                                           #

    Dim cFixes, oFix, sOutput
    Verbose " Connecting to WMI on " & sName
    Set cFixes = QueryWMI(sName,"root\cimv2","Select * From Win32_OperatingSystem","","")
    If Not IsObject(cFixes) Then
        WScript.Echo " *** Couldn't connect to WMI on " & sName
    Else
        For Each oFix In cFixes
            sOutput = sName & "," & oFix.Version & "," & oFix.ServicePackMajorVersion & "." & oFix.ServicePackMinorVersion
            If WScript.Arguments.Named.Exists("output") Then
                LogFile WScript.Arguments.Named("output"),sOutput,False
            Else
                WScript.Echo sOutput
            End If
        Next
      End If

    '#                                           #
      '#-------------------------------------------#
      '#             END COMMAND CODE              #
      '#############################################

End Sub

' ----------------------------------------------------------------------
' Sub LogBadConnect
'
' Logs failed connections to a log file. Will append if file already exists.
' ----------------------------------------------------------------------
Sub LogBadConnect(sName)
      If WScript.arguments.Named.Exists("log") Then
            Dim oLogFSO, oLogFile
            Set oLogFSO = WScript.CreateObject("Scripting.FileSystemObject")
            On Error Resume Next
            Set oLogFile = oLogFSO.OpenTextFile(WScript.Arguments.Named("log"),8,True)
            If Err <> 0 Then
                  WScript.Echo " *** Error opening log file to log an unreachable computer"
                  WScript.Echo " " & Err.Description
            Else
                  oLogFile.WriteLine sName
                  oLogFile.Close
                  Verbose " Logging " & sName & " as unreachable"
            End If
      End If
End Sub


' ----------------------------------------------------------------------
' Function TestPing
'
' Tests connectivity to a given name or address; returns true or False
' ----------------------------------------------------------------------
Function TestPing(sName,bPingAvailable)
      If Not bPingAvailable Then
            WScript.Echo " Ping functionality not available prior to Windows XP"
            Exit Function
      End If
      Dim cPingResults, oPingResult
      Verbose " Pinging " & sName
      Set cPingResults = GetObject("winmgmts://./root/cimv2").ExecQuery("SELECT * FROM Win32_PingStatus WHERE Address = '" & sName & "'")
      For Each oPingResult In cPingResults
            If oPingResult.StatusCode = 0 Then
                  TestPing = True
                  Verbose "  Success"
            Else
                  TestPing = False
                  Verbose "  *** FAILED"
            End If
      Next
End Function

' ----------------------------------------------------------------------
' Sub Verbose
'
' Outputs status messages if /verbose argument supplied
' ----------------------------------------------------------------------
Sub Verbose(sMessage)
      If WScript.Arguments.Named.Exists("verbose") Then
            WScript.Echo sMessage
      End If
End Sub

' ----------------------------------------------------------------------
' Sub LogFile
'
' Outputs specified text to specified logfile. Set Overwrite=True To
' overwrite existing file, otherwise file will be appended to.
' Each call to this sub is a fresh look at the file, so don't Set
' Overwrite=True except at the beginning of your script.
' ----------------------------------------------------------------------
Sub LogFile(sFile,sText,bOverwrite)
      Dim oFSOOut,oTSOUt,iFlag
      If bOverwrite Then
            iFlag = 0
      Else
            iFlag = 8
      End If
      Set oFSOOut = WScript.CreateObject("Scripting.FileSystemObject")
      On Error Resume Next
      Set oTSOUt = oFSOOut.OpenTextFile(sFile,iFlag)
      If Err <> 0 Then
            WScript.Echo "*** Error logging to " & sFile
            WScript.Echo "    " & Err.Description
      Else
            oTSOUt.WriteLine sText
            oTSOUt.Close
      End If
End Sub

' ----------------------------------------------------------------------
' Function QueryWMI
'
' Executes WMI query and returns results. User and Password may be
' passed as empty strings to use current credentials; pass just a blank
' username to prompt for the password
' ----------------------------------------------------------------------
Function QueryWMI(sName,sNamespace,sQuery,sUser,sPassword)
      Dim oWMILocator, oWMIService, cInstances
      On Error Resume Next

      'create locator
      Set oWMILocator = CreateObject("WbemScripting.SWbemLocator")

      If sUser = "" Then

            'no user - connect w/current credentials
            Set oWMIService = oWMILocator.ConnectServer(sName,sNamespace)
            If Err <> 0 Then
                  WScript.Echo "*** Error connecting to WMI on " & sName
                  WScript.Echo "    " & Err.Description
                  Set QueryWMI = Nothing
                  Exit Function
            End If    

      Else

            'user specified
            If sUser <> "" And sPassword = "" Then

                  'no password - need to prompt for password
                  If LCase(Right(WScript.FullName,11)) = "cscript.exe" Then

                        'cscript - attempt to use ScriptPW.Password object
                        Dim oPassword
                        Set oPassword = WScript.CreateObject("ScriptPW.Password")
                        If Err <> 0 Then
                              WScript.Echo " *** Cannot prompt for password prior to Windows XP"
                              WScript.Echo "     Either ScriptPW.Password object not present on system, Or"
                              WScript.Echo "     " & Err.Description
                              WScript.Echo "     Will try to proceed with blank password"
                        Else
                              WScript.Echo "Enter password for user '" & sUser & "' on '" & sName & "'."
                              sPassword = oPassword.GetPassword()
                        End If
                  Else

                        'wscript - prompt with InputBox()
                        sPassword = InputBox("Enter password for user '" & sUser & "' on '" & sName & "'." & vbcrlf & vbcrlf & _
                         "WARNING: Password will echo to the screen. Run command with CScript to avoid this.")
                  End if
            End If

            'try to connect using credentials provided
            Set oWMIService = oWMILocator.ConnectServer(sName,sNamespace,sUser,sPassword)
            If Err <> 0 Then
                  WScript.Echo " *** Error connecting to WMI on " & sName
                  WScript.Echo "     " & Err.Description
                  Set QueryWMI = Nothing
                  Exit Function
            End If    
      End If

      'execute query
      Set cInstances = oWMIService.ExecQuery(sQuery,,48)
      If Err <> 0 Then
            WScript.Echo "*** Error executing query "
            WScript.Echo "     " & sQuery
            WScript.Echo "     " & Err.Description
            Set QueryWMI = Nothing
            Exit Function
      Else
            Set QueryWMI = cInstances
      End If    

End Function

' ----------------------------------------------------------------------
' Function QueryADSI
'
' Executes ADSI query. Expects variable sQuery to include a COMPLETE
' query beginning with the provider LDAP:// or WinNT://. The query String
' may include a placeholder for the computer name, such as "%computer%".
' Include the placeholder in variable sPlaceholder to have it replaced
' with the current computer name. E.g.,
'   sQuery = "WinNT://%computer%/Administrator,user"
'   sPlaceholder = "%computer%
' Will query each computer targeted by the script and query their local
' Administrator user accounts.
' ----------------------------------------------------------------------
Function QueryADSI(sName,sQuery,sPlaceholder)

      Dim oObject
      sQuery = Replace(sQuery,sPlaceholder,sName)
      On Error Resume Next
      Verbose " Querying " & sQuery
      Set oObject = GetObject(sQuery)
      If Err <> 0 Then
            WScript.Echo " *** Error executing ADSI query"
            WScript.Echo "     " & sQuery
            WScript.Echo "     " & Err.Description
            Set QueryADSI = Nothing
      Else
            Set QueryADSI = oObject
      End If

End Function
]]>
            </script>
      </job>
</package>



Avatar of deadite
deadite
Flag of United States of America image

****"c:\windows\system32\cscript.exe whatsp.vbs /list:input.txt > spresult.csv"

Try making that line:

"c:\windows\system32\cscript.exe whatsp.vbs /list:input.txt" > spresult.csv  

Or

"c:\windows\system32\cscript.exe whatsp.vbs" /list:input.txt > spresult.csv


I think that may fix your problem.
ASKER CERTIFIED SOLUTION
Avatar of deadite
deadite
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Also, here are a few links you may be interested in:

How Can I Determine if a Computer is Running Windows Server 2003 and if Service Pack 1 is Installed?
http://www.microsoft.com/technet/scriptcenter/resources/qanda/mar06/hey0302.mspx

How Can I Tell Which Service Packs Have Been Installed on a Computer?
http://www.microsoft.com/technet/scriptcenter/resources/qanda/sept04/hey0929.mspx

How Can I Use the RunAs Command to Run a Script Under Alternate User Credentials?
http://www.microsoft.com/technet/scriptcenter/resources/qanda/apr06/hey0428.mspx

How Can I Determine Which Account a Script is Running Under?
http://www.microsoft.com/technet/scriptcenter/resources/qanda/feb06/hey0216.mspx

How Can I Run a Script Under Alternate Credentials?
http://www.microsoft.com/technet/scriptcenter/resources/qanda/dec04/hey1213.mspx
Avatar of TheBartolini

ASKER

I got an error for Line: 183 Char: 5

Object not a collection
 800A01C3
Hey, havn't had time to test the code with alternate credentials on remote machines.... but this should do the trick:

when prompted for the username enter the name of the 1st computer in your list followed by the local administrator account.  For example:

computer-01\administrator

I tried it on 2 computers on a network and entered "administrator" and the local admin password, and got that same message.  I then entered computer-01\administrator and the local admin password, but it worked on both machines since they had the same local admin password.
Thanks for taking time to help out. I am trying to absorb as much as I can from your example.I still
I still get the same error message for Line: 184

How can I check to see if the error is accurate? Maybe something is missing.

Bart
OK I got it now. Somehow the users changed the password.
Also I get a pop-up message box for every entry written to the TXT file.

Is there a way to have just one FINAL POPUP at the very end of the whole job?
OK I resolved the multiple pop ups as well.
the only part left is if there is a way to have the script give up faster on machines that are powered off or have the wrong password.
I have another question.

Can you alter the script to indicate machines that are not online and the ones that have changed the local admin password?
As far as giving up faster on machines, I've never seen anyway around that.  I once tried pinging the machine then executing the script if it pings... but you still get delays with that.

For altering the script to mark the machine offline, the only way I can think of is the ping the machine, then if it fails mark it as offline... otherwise have it get the info.  Here is a script you can use to add a ping command.

http://www.microsoft.com/technet/scriptcenter/resources/qanda/sept04/hey0914.mspx

I'm sure there is room for improvement, but for a script written in 15 min... I think it does a fair job
For 15 minutes it is excellent in my book. I learned a lot from it. I have to get better at sectioning off certain tasks. Thanks very much
I have been trying to mask the password in this code. Seems a bit tedious forme.
What exactly would I have too add to do that?