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.
@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
