4 Replies Latest reply on Aug 6, 2013 2:04 PM by a001615

    VBScript issue running command remotely

    a001615

      I've created a vbscript monitor template that executes a sqlplus command on a remote windows server.

      Running the vbs script locally, with modifications for local paths, works just fine.

      But when executed from the SW poller, it seems as though its not running at all.

      Is there something I'm missing with the way SW handles vbscripts? It seems like its executing the script from the poller, instead of pushing to the remote server and executing there.

      Does anyone know a way around this?  Or is there an issue with my WshShell.exec command?

      I've included the vbs script below

      .

      Summary : Given the server name and oracle instance name.

      Determine temp directory, determine server name, create a .sql command file on remote server with spool option. Execute the sqlplus command.

      Open spooled file. and this is where it fails. So it creates the .sql file just fine on remote system, but i dont think its executing the sqlplus command.

       

      Troubleshooting

      I've tried turning off spool and using redirect to a file option as a part of the sqlcmd variable and the wshshell.exec parameters, neither of which work from SW, but all work locally.

      I've also tried setting up an ODBC for Oracle connection without success.

      I've tried escaping various characters in the sqlcmd

      I've tried changing around sqlplus command

      I've tried using the pipe symbol in the command

      I've tried utilizing wshremote functions of vbscript without success. (creating script on poller and using wshremote to push to remote server and executing there)

      I've tried capturing stderr and stdout from the vbscript, but they are always null.

      I've opened a ticket with SW, but they referred me to the powershell doco and said they cant help with scripts, but I was really just asking how SW handles vbscript.

      Permissions for the template credentials are domain and have access to the servers.

      When I manually create the output file, the script executes as expected calculating free space and etc.

       

      Hoping someone can help..

      Thanks in advance.

       

      Parameters : \\${IP}\C$\Temp {Instance Name}

      Option Explicit

      Dim LOGIN, DBName, SQLSelect, TmpDir, TmpDirSet, ArchiveDrive, EndofServer, ServerName, SQLDir

      Dim WshShell, oExec, SQLOutput, fso, objfile, Disk, DiskFree, DiskTotal, DiskPercent

      Set fso = CreateObject("Scripting.FileSystemObject")

      Set WshShell = Wscript.CreateObject("WScript.Shell")

      TmpDirSet = False

      EndofServer = instr(3,Wscript.Arguments(0),"\",0)

      ServerName = mid(Wscript.Arguments(0),1,EndofServer)

      '---------------------------

      '-- Determine Temp directory

      '---------------------------

      if fso.folderexists(ServerName & "C$\Temp") then

              SQLDir = "C:\Temp"

        TmpDir = ServerName & "C$\Temp"

        TmpDirSet = True

      end if

      if fso.folderexists(ServerName & "C$\Windows\Temp") and TmpDirSet = False then

              SQLDir = "C:\Windows\Temp"

        TmpDir = ServerName & "C$\windows\Temp"

        TmpDirSet = True

      end if

      if fso.folderexists(ServerName & "C$\WinNT\Temp") and TmpDirSet = False then

              SQLDir = "C:\WinNT\Temp"

        TmpDir = ServerName & "C$\WinNT\Temp"

        TmpDirSet = True

      end if

      If TmpDirSet = False then

        WScript.Echo "Message: Unable to determine temp directory"

              WScript.Echo "Statistic: 0"

              Wscript.Quit(0)

      end if

      '-----------------------------------

      '-- Setup Connection Info for Oracle

      '-----------------------------------

      LOGIN="userid/password"

      DBName=Wscript.Arguments(1)

      '----------------------------------

      '-- Create Tempory SQL command file

      '----------------------------------

      Set objFile = fso.CreateTextFile(TmpDir & "\s_" & DBNAME & ".sql", true)

      objFile.writeline("SPOOL " & SQLDir & "\sqloutput.log;")

      objFile.writeline("select value from v$parameter where name = 'log_archive_dest' and value is not null")

      objFile.writeline("union all")

      objFile.writeline("select value from v$parameter where name = 'log_archive_dest_1' and value is not null")

      objFile.writeline("union all")

      objFile.writeline("select value from v$parameter where name = 'log_archive_dest_2' and value is not null;")

      objFile.writeline("SPOOL OFF;")

      objFile.writeline("quit;")

      objFile.Close

      '----------------------------

      '-- Execute SQLPlus Statement

      '---------------------------- 

      dim sqlcmd

      wscript.sleep(10000)

      sqlcmd = ServerName & "C$\oracle\ora81\bin\sqlplus -s " & LOGIN & "@" & DBName & " < " & "C:\Temp\s_" & DBName & ".sql"

      Set oExec = WshShell.Exec(sqlcmd)

      '-------------------

      '--Retrieve location

      '-------------------

      Set objFile = fso.OpenTextFile(TmpDir & "\sqloutput.log", 1)

      Do While objFile.AtEndOfStream = False

          SQLOutput = objfile.readline()

        if Instr(SQLOutput,":\") > 0 then

        ArchiveDrive = mid(SQLOutPut,1,1)

        Exit Do

        end if

      Loop

      objFile.Close

      set Disk = fso.GetDrive(ServerName & ArchiveDrive & "$")

      DiskTotal = Disk.TotalSize

      DiskFree = Disk.FreeSpace

      DiskPercent = (100 - ((DiskFree/DiskTotal)*100))

      Wscript.echo "Statistic : " & FormatNumber(DiskPercent,2)

      Wscript.echo "Message : " & SQLOutput

      '----------------------------------

      '-- Delete Tempory SQl command file

      '----------------------------------

      'fso.DeleteFile(TmpDir & "\solarwinds_" & DBNAME & ".sql")

      'fso.deleteFile(TmpDir & "sqloutput.log")

        • Re: VBScript issue running command remotely
          aLTeReGo

          The issue you are likely running into is that scripts executed by SAM script monitors or the Advanced Alert Manager are executed under the local Windows system account on the Orion server, which has no ability to access network resources. This is the reason why the script probably executes fine under your own account, but fails when run in Orion under the local system account. It get around this you will need to include some level of user impersonation into your script. This is handled natively in PowerShell, but requires some additional code to impersonate a user account within a vbscript. 

           

          Your only other option would be to explicitly grant the local system account permissions on the resources it needs to execute properly, no differently than you would for a user account operating in a least privileged environment.

          • Re: VBScript issue running command remotely
            a001615

            Thanks for the additional info...

            I've updated the vbscript from wshshell.exec to wshshell.run with the following change

             

            sqlcmd = "runas /username:domain\domainAdminaccount " & """cmd /c " & ServerName & "C$\oracle\ora81\bin\sqlplus -s " & LOGIN & "@" & DBName & " < " & TmpDir & "\s_" & DBName & ".sql > " & TmpDir & "\sqloutput.log"""

            oExec = WshShell.run(sqlcmd,0,TRUE)

            Wscript.Sleep 100

            wshShell.AppActivate "Runas"

            Wscript.Sleep 300

            wshShell.SendKeys "accountpassword"

            Wscript.Sleep 100

            wshShell.SendKeys "~"

            wscript.sleep(10000)

             

            It appears to be running the sqlplus command, but then I get an OS error saying  "sqlplus has stopped, close the program."

            which is bizarre because the script runs and completes normally just fine. There must be a timeout somewhere or another issue I'm not seeing.