1 Reply Latest reply on May 22, 2015 1:12 PM by bluefunelemental

    Bootstrap no more - How to monitor Orion from outside of SolarWinds

    mlandman

      We reacently had several stalls - object last poll and next poll times we old, data was not being collected, nodes were not being updated. I deveolped an external to SolarWinds monitor to alert me when the last update is over 15minutes behind now.:

       

      I developed this query against the SW DB:

       

      Select abs(datediff(MINUTE, TimeStamp, GETUTCDATE())) as 'SinceLastUpdate',

      DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), timestamp) AS LastUpdate

      from APM_CurrentApplicationStatus


      join APM_Application app on app.id = APM_CurrentApplicationStatus.ApplicationID

      where applicationid =19 -- My Orion SAM application is ID #19, pick something that works for you

      order by LastUpdate

       

      Once I verified this worked and returned correct information, in local time (will work where you are thanks to GETUTCDATE() ), I had a script made by our SCOM guy:

       

      #==================================================================================================
      #===========     FUNCTIONS            ===========
      #==================================================================================================
      Function EndScript
      {
        $EventMessage = "End Script."
        Write-EventLog -LogName $LogName -EntryType $InfoLogType -EventId $InfoLogID -Source $EventSource -Message $EventMessage
        Exit
      }

      #==================================================================================================
      # Create necessary variables to use throughout the script
      $ErrorActionPreference = "SilentlyContinue"
      $WshShell = New-Object -ComObject "WScript.Shell"
      $scriptName = $MyInvocation.MyCommand.Name
      $strFilenameWithoutExtension = [System.IO.Path]::GetFileNameWithoutExtension($scriptName)

      $LogName   = "Application"
      $WarnLogType  = "Warning"
      $InfoLogType  = "Information"
      $ErrorLogType  = "Error"
      $WarnLogID   = "650"
      $InfoLogID   = "600"
      $ErrorLogID  = "700"
      $EventSource  = $strFilenameWithoutExtension

      If (!(test-path HKLM:\SYSTEM\CurrentControlSet\Services\Eventlog\Application\$EventSource))
        {
         new-eventlog -logname $LogName -source $EventSource
        }

      #==================================================================================================
      # Begin main code here
      $user = "MsSQL_User"
      $sqlUserPassword = "MsSQL_User's_Password_here"
      $database = "SolarWinds_DB_Name"
      $dataSource = "Database_with_instance"
      $connectionstring = "Server=$dataSource;uid=$user;pwd=$sqlUserPassword;Database=$database;Integrated Security=False"
      $connection = New-Object System.Data.SqlClient.SqlConnection
      $connection.ConnectionString = $connectionstring
      $connection.Open()

      $query = "select abs(datediff(MINUTE, TimeStamp, GETUTCDATE())) as 'SinceLastUpdate', DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), timestamp) AS LastUpdate from APM_CurrentApplicationStatus where applicationid =19"
      $command = $connection.CreateCommand()
      $command.CommandText = $query

      $result = $command.ExecuteReader()
      $table = new-object “System.Data.DataTable”
      $table.Load($result)
      $format = @{Expression={$_.SinceLastUpdate};Label="SinceLastUpdate";width=10},@{Expression={$_.LastUpdate};Label="LastUpdate"; width=30}
      [string]$MinSinceLastUpdate = $table | select SinceLastUpdate
      [string]$LastUpdate = $table | select LastUpdate
      $connection.Close()

      $arrLastUpdate = $LastUpdate.Split("=")
      $FinalResult_LastUpdate = $arrLastUpdate[1].Replace("}","")

      $arrMinSinceLastUpdate = $MinSinceLastUpdate.Split("=")
      [int]$FinalResult_MinSinceLastUpdate = $arrMinSinceLastUpdate[1].Replace("}","")

      If ($FinalResult_MinSinceLastUpdate -gt 15)
      {
        $command = "[Set a call here to your alerting system]-subject ""SolarWinds System Stall"" -message ""SolarWinds systems not updated for $FinalResult_MinSinceLastUpdate minutes . The last update was $FinalResult_LastUpdate"""
        #Write-Host $command
        Invoke-Expression $command
      }

      # End main code
      #==================================================================================================

       

      I set a schedualed job to run every 15 minutes and now I am alerted if the system stalls!

       

      Enjoy