PowerShell: Generate a report of failed SQL Agent jobs

$cms ="CMSServer"
$serverlist = invoke-sqlcmd -serverinstance $cms -database 'CMSCentralDB' -query "
    SELECT DISTINCT ServerName FROM AllSQLServers"

$qry = "
USE [msdb]

SELECT  @@SERVERNAME ServerName,
        j.name JobName,
        js.last_outcome_message LastOutcomeMessage,
        LastRunTime = dbo.agent_datetime(
            CASE WHEN js.last_run_date = 0 THEN NULL ELSE js.last_run_date END, 
            CASE WHEN js.last_run_time = 0 THEN NULL ELSE js.last_run_time END) 
FROM    dbo.sysjobs j
        INNER JOIN dbo.sysjobservers js 
        ON j.job_id = js.job_id
  WHERE js.last_run_outcome = 0
  AND j.name like 'dba%'
  AND j.job_id NOT IN (  
select job_id from msdb.dbo.sysjobactivity
where start_execution_date IS NOT NULL AND stop_execution_date IS NULL
AND session_id = (SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity) )
"

$results = new-object object[] 0

foreach ($server in $serverlist)
{ 
$server.ServerName
trap [Exception] {continue}   
     $results += invoke-sqlcmd -Query $qry –serverinstance $server.ServerName -database 'msdb'    
}

foreach ($r in $results)  
{      
        $ServerName = $r.ServerName
        $JobName = $r.JobName
        $LastOutcomeMessage = $r.LastOutcomeMessage
        $LastRunTime = $r.LastRunTime
          
        invoke-sqlcmd -serverinstance $cms -database 'CMSCentralDB' -query " 
                 insert dbo.SQLAgentJobsFailed (ServerName, JobName, LastOutcomeMessage, LastRunTime) select  '$ServerName','$JobName','$LastOutcomeMessage','$LastRunTime'"
}

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s