Powershell script to run against many servers and send combine output as email

Question

Say I wanted to run select @@version against 3 SQL instances ( SQLA, SQLB and SQLC) .. I want an email that shows the combined output of these results as follows

SQLA   SQL 2005..

SQLB   SQL 2008,,

SQLC   SQL 2008...

What I want is not exactly the above output, but that template would give me a good idea of how to accomplish collecting results against multiple servers, appending that content and send that combined output in an email. I have seen a few examples online on how to send email, but they only connect to one server, collect the results and send the output. What i want is running it against many servers, collecting all that together and sending one email with the combined results.

 

Answer 1

check this Script which is to check failed Failed SQL Agent Job output in excel sheet from multiple server make the changes as your need

and include the Email combined.

#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 "P:\My Documents\DBA\Scripts\Powershell\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"
	  $Sheet.Cells.Item($intRow,4) = "Job History"


     #Format the column headers
     for ($col = 1; $col –le 4; $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
     $jobHistoryFilter=New-Object ('Microsoft.SqlServer.Management.Smo.Agent.JobHistoryFilter')
     $jobHistoryFilter.JobID=$job.JobID
	 $jobHistory = $srv.EnumjobHistory($jobHistoryFilter)
	  #$jobHistoryFilter.OutComeTypes = 'Failed'
	  #$srv.JobServer.EnumJobHistory($jobHistoryFilter)
     #Formatting using Excel 


ForEach ($job in $jobs)  
{ 


       # Formatting for the failed jobs 
       if ($job.LastRunOutcome -eq 0) 
       { 
           $fgColor = 3 
		   $Sheet.Cells.Item($intRow, 1) =  $job.Name 
       $Sheet.Cells.Item($intRow, 2) = $job.LastRunOutcome.ToString() 
       $Sheet.Cells.item($intRow, 2).Interior.ColorIndex = $fgColor 
       $Sheet.Cells.Item($intRow, 3) =  $job.LastRunDate
	   #$Sheet.Cells.Item($intRow, 4) = $jobHistoryFilter.JobID
       
       $intRow ++ 
       } 
       else 
         
 { }
       
   
} 
   $intRow ++ 


}

$Sheet.UsedRange.EntireColumn.AutoFit()
cls

 

---

Nag Pal MCTS/MCITP (SQL Server 2005/2008) :: Please Mark Answer/vote if it is helpful ::

Answer 2

You can take a look at this sample http://sethusrinivasan.wordpress.com/2012/01/11/powershell-script-to-run-query-against-many-servers-and-send-combined-output-as-email/

 

Thanks

Sethu Srinivasan [MSFT]

SQL Server

http://blogs.msdn.com/sqlagent