Checking SQL Server Agent jobs using Windows PowerShell

Win FREE SQL Server Books from Apress

USE msdb 
GO 
SELECT 
   j.[name] AS [JobName], 
   run_status = CASE h.run_status 
   WHEN 0 THEN 'Failed'
   WHEN 1 THEN 'Succeeded'
   WHEN 2 THEN 'Retry'
   WHEN 3 THEN 'Canceled'
   WHEN 4 THEN 'In progress'
   END,
   h.run_date AS LastRunDate,  
   h.run_time AS LastRunTime
FROM sysjobhistory h 
   INNER JOIN sysjobs j ON h.job_id = j.job_id 
       WHERE j.enabled = 1  
       AND h.instance_id IN 
       (SELECT MAX(h.instance_id) 
           FROM sysjobhistory h GROUP BY (h.job_id))
GO 

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$rvs = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST\SQL2000"

#Create an instance of the Jobs object collection from the JobServer property
#And pipes that to the filter Where-Object cmdlet to retrieve only those jobs that are enabled but failed
$srv.JobServer.Jobs | Where-Object {$_.IsEnabled -eq $TRUE} | Select Name,LastRunOutcome, LastRunDate
 

#Create a new Excel object using COM
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $True

$Excel = $Excel.Workbooks.Add()
$Sheet = $Excel.Worksheets.Item(1)

#Counter variable for rows
$intRow = 1

#Read thru the contents of the SQL_Servers.txt file
foreach ($instance in get-content "D:\SQL_Servers.txt")
{

     #Create column headers
     $Sheet.Cells.Item($intRow,1) = "INSTANCE NAME:"
     $Sheet.Cells.Item($intRow,2) = $instance
     $Sheet.Cells.Item($intRow,1).Font.Bold = $True
     $Sheet.Cells.Item($intRow,2).Font.Bold = $True

     $intRow++

      $Sheet.Cells.Item($intRow,1) = "JOB NAME"
      $Sheet.Cells.Item($intRow,2) = "LAST RUN OUTCOME"
      $Sheet.Cells.Item($intRow,3) = "LAST RUN DATE"

     #Format the column headers
     for ($col = 1; $col –le 3; $col++)
     {
          $Sheet.Cells.Item($intRow,$col).Font.Bold = $True
          $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
          $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
     }

     $intRow++
      #######################################################
     #This script gets SQL Server Agent job status information using PowerShell

     [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

     # Create an SMO connection to the instance
     $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance

     $jobs=$srv.JobServer.Jobs

     #Formatting using Excel

ForEach ($job in $jobs) 
{

       # Formatting for the failed jobs
       if ($job.LastRunOutcome -eq 0)
       {
           $fgColor = 3
       }
       else
       {
           $fgColor = 0
       }