0 Replies Latest reply on Jul 3, 2018 8:04 AM by mkharris528

    SQL Job last run template

    mkharris528

      I am looking to create a template that tells us the last time a job was run, I have most of it done, but I am not great with powershell templates yet and am still learning.  The parts in bold I need to pass through using inherit creds from node.  I can run the command from powershell to get a result but have not come up with a way to get solarwinds to give me a result.  I only get NaN, (Not a Number), I think I am about 90% there, I just need the last piece.

       

      Invoke-Sqlcmd -Query "select @@version" -ServerInstance <servername> -Username sa -Password <password>

      $SQL_query= @"

         DECLARE @current_time DATETIME

         DECLARE @last_execution_time DATETIME

         SET @current_time = GETDATE()

         SELECT  @last_execution_time =MAX(msdb.dbo.agent_datetime(run_date, run_time))

         FROM msdb.dbo.sysjobs         j

         JOIN msdb.dbo.sysjobhistory   jh

         ON j.job_id = jh.job_id

         WHERE j.[name] = 'DBA.intelligent database backup'

         SELECT DATEDIFF(mi,@last_execution_time,@current_time)

      "@

         $SQL_RESULT=Invoke-Sqlcmd -Query $SQL_query -ServerInstance us02uatdb72 -Database "msdb"

         $SQL_RESULT

      write-host "Message.QUERY: Last Job completion";

              write-host "Statistic.QUERY: $($SQL_RESULT)"

       

       

      exit 0