如何将Powershell多段SQL监控结果整合至单封邮件正文发送

核心原理 #

你之前用Format-Table得到的是格式化显示对象，不是可直接拼接的纯文本，只需要用Out-String把四个结果转成保留格式的纯文本，拼接后直接作为邮件正文发送即可，无需先写入文件再读取。 #

完整修改后脚本 #

$DestInstance = "servername"
$DestDatabase = "master"
$smtpServer = "forwarder.mail.xerox.com"
$supportContact = "Andleeb.Hasan@xerox.com"

# 1. 查询SQL服务状态
$O1  = Get-WmiObject -Class Win32_Service -ComputerName $DestInstance | Where-Object {$_.Displayname -in "SQL Server (MSSQLSERVER)","SQL Server Agent (MSSQLSERVER)"} | Format-Table -Property Displayname, state -AutoSize

# 2. 查询磁盘状态
$O2= Get-WmiObject -Class Win32_logicaldisk -Filter "DriveType = '3'" | 
Select-Object -Property DeviceID, VolumeName,
@{L="Capacity";E={"{0:N2}" -f ($_.Size/1GB)}}, 
@{L='FreeSpaceGB';E={"{0:N2}" -f ($_.FreeSpace /1GB)}},
@{L='FreeSpacePerCent';E={"{0:N2}" -f (100*$_.FreeSpace /$_.size)}}|Format-table -Autosize

# 3. 查询数据库状态
$O3= Invoke-Sqlcmd -Database $DestDatabase -ServerInstance "$DestInstance" -Query "SELECT name as Database_Name, state_desc as Database_Status
FROM   sys.databases where name in ('Core01_Prod',
'EquipmentCatalogue',
'iL_Exposure_PRODUCTION',
'iL_Exposure_STAGING',
'LookUpTables',
'ReportServer',
'ReportServerTempDB'
) order by name" |Format-table -Autosize

# 4. 查询作业运行状态
$O4= Invoke-Sqlcmd -Database $DestDatabase -ServerInstance "$DestInstance" -Query ";WITH CTE_MostRecentJobRun AS 
 ( 
 -- For each job get the most recent run (this will be the one where Rnk=1) 
 SELECT job_id,run_status,run_date,run_time 
 ,RANK() OVER (PARTITION BY job_id ORDER BY run_date DESC,run_time DESC) AS Rnk 
 FROM MSDB.dbo.sysjobhistory 
 WHERE step_id=0 
 ) 
SELECT  
  name  AS [Job Name]
 ,CONVERT(VARCHAR,DATEADD(S,(run_time/10000)*60*60 /* hours */ 
  +((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */ 
  + (run_time - (run_time/100) * 100)  /* secs */, 
  CONVERT(DATETIME,RTRIM(run_date),113)),100) AS [Last Run]
 ,CASE WHEN enabled=1 THEN 'Enabled' 
     ELSE 'Disabled' 
  END [Job Status]
FROM     CTE_MostRecentJobRun MRJR 
JOIN     MSDB.dbo.sysjobs SJ 
ON       MRJR.job_id=sj.job_id 
WHERE    Rnk=1 
AND      run_status=1 -- 
ORDER BY [Last Run] desc " | Format-table -Autosize

# ====== 新增：结果拼接+发送邮件 ======
# 把所有格式化结果转成纯文本，保留表格对齐格式
$svcText = $O1 | Out-String
$diskText = $O2 | Out-String
$dbText = $O3 | Out-String
$jobText = $O4 | Out-String

# 拼接邮件正文
$mailBody = @"
=== SQL Server 巡检报告 $(Get-Date -Format 'yyyy年MM月dd日 HH:mm') ===
服务器名称：$DestInstance

---
### 1. SQL核心服务运行状态
$svcText

---
### 2. 服务器磁盘使用状态
$diskText

---
### 3. 业务数据库状态
$dbText

---
### 4. 最近成功运行作业列表
$jobText
"@

# 发送邮件
Send-MailMessage -From "SQL巡检告警<sql-monitor@xerox.com>" `
-To $supportContact `
-Subject "巡检报告：$DestInstance 运行状态 $(Get-Date -Format 'yyyy-MM-dd')" `
-Body $mailBody `
-Encoding UTF8 `
-SmtpServer $smtpServer

可选优化 #

如果需要更美观的HTML格式邮件，可以把查询结果（去掉Format-Table）用ConvertTo-Html命令转成HTML表格，正文设置为HTML格式即可，兼容性更好，不会出现文本对齐错乱的问题。

内容的提问来源于stack exchange，提问作者Aaron Finch