ساخت گزارش HTML از وضعیت Jobهای SQL Server Agent با PowerShell
Jobهای SQL Server Agent هسته اصلی وظایف اتوماسیون در SQL Server محسوب می‌شوند. نظارت بر وضعیت آن‌ها برای حفظ یک محیط SQL Server سالم حیاتی است. در حالی که روش‌های مختلفی برای نظارت بر این Jobها وجود دارد، تولید یک گزارش HTML ساده می‌تواند برای نمای کلی سریع و اشتراک‌گذاری با ذی‌نفعان بسیار مؤثر باشد. این مقاله شما را در ساخت چنین گزارشی با استفاده از PowerShell و T-SQL راهنمایی می‌کند و اطمینان می‌دهد که تصویری واضح از وضعیت Jobهای SQL Server Agent خود خواهید داشت.
اخیراً مقاله‌ای در مورد استفاده از تاریخچه Jobهای SQL Server Agent برای بررسی وضعیت Jobها منتشر کردم. PowerShell در این مقاله وضعیت فعلی Jobهای در حال اجرای SQL Server Agent را به دست می‌آورد. این اسکریپت از دستور OUT-GRIDVIEW برای نمایش یک رابط کاربری گرافیکی زیبا برای داده‌ها استفاده می‌کند. بیایید نگاهی بیندازیم:
get-service -displayname "sql server agent*" | out-gridview -passthru -title "Select Instance to check job status for" | ForEach-Object {Invoke-Sqlcmd -query "exec msdb.dbo.sp_help_job" -serverinstance $_.Name.Replace('SQL Server Agent (','').Replace(')','')} | out-gridview -title "SQL Agent Jobs"
اسکریپت بالا به عنوان یک رابط کاربری گرافیکی عالی کار می‌کند، اما اگر یک گزارش HTML بخواهید چه؟ ما قصد داریم گزارش HTML را از ابتدا بسازیم. ابتدا باید متغیرها را برای سربرگ و گزارش کلی HTML تعریف کنیم:
$htmlreport = ""
$htmlreport += "<html>"
$htmlreport += "<head>"
$htmlreport += "<Title>SQL Server Agent Job Status Report</Title>"
$htmlreport += "<style>"
$htmlreport += "body { font-family: Arial, Helvetica, sans-serif;}"
$htmlreport += "table { border-collapse: collapse; width: 100%;}"
$htmlreport += "td, th { border: 1px solid #ddd; padding: 8px;}"
$htmlreport += "tr:nth-child(even){background-color: #f2f2f2;}"
$htmlreport += "tr:hover {background-color: #ddd;}"
$htmlreport += "th { padding-top: 12px; padding-bottom: 12px; text-align: left; background-color: #4CAF50; color: white;}"
$htmlreport += "</style>"
$htmlreport += "</head>"
$htmlreport += "<body>"
$htmlreport += "<h1>SQL Server Agent Job Status Report</h1>"
$htmlreport += "<p>This report was generated on: $(Get-Date -Format 'MM/dd/yyyy HH:mm:ss')</p>"
حالا که ساختار HTML را داریم، باید داده‌های Jobهای SQL Server Agent را جمع‌آوری کنیم. این کار با استفاده از یک کوئری T-SQL انجام می‌شود که اطلاعات مربوط به هر Job، مانند نام، وضعیت فعال بودن و نتیجه آخرین اجرا را بازیابی می‌کند. سپس نتایج را پیمایش کرده تا آن‌ها را برای گزارش HTML خود فرمت‌بندی کنیم:
$sqlquery = "
SELECT
j.name AS JobName,
j.enabled AS Enabled,
CASE
WHEN sjh.run_status = 0 THEN 'Failed'
WHEN sjh.run_status = 1 THEN 'Succeeded'
WHEN sjh.run_status = 2 THEN 'Retry'
WHEN sjh.run_status = 3 THEN 'Canceled'
WHEN sjh.run_status = 4 THEN 'In Progress'
ELSE 'Unknown'
END AS LastRunStatus,
msdb.dbo.agent_datetime(sjh.run_date, sjh.run_time) AS LastRunDateTime
FROM
msdb.dbo.sysjobs j
LEFT JOIN
msdb.dbo.sysjobhistory sjh ON j.job_id = sjh.job_id
AND sjh.instance_id = (SELECT MAX(instance_id) FROM msdb.dbo.sysjobhistory WHERE job_id = j.job_id)
ORDER BY
j.name;
"
ما باید تمام نمونه‌های (instances) سرویس‌های SQL Server Agent را در ماشین به دست آوریم، سپس از طریق هر یک از آن‌ها تکرار کنیم تا وضعیت Job را دریافت کنیم. این کار تضمین می‌کند که گزارش تمام نمونه‌های فعال SQL Server Agent را پوشش می‌دهد:
get-service -displayname "sql server agent*" | ForEach-Object {
$servername = $_.Name.Replace('SQL Server Agent (','').Replace(')','');
$htmlreport += "<h2>Job Status for SQL Server Instance: $($servername)</h2>";
$jobs = Invoke-Sqlcmd -query $sqlquery -serverinstance $servername;
if ($jobs) {
$htmlreport += "<table>";
$htmlreport += "<tr><th>Job Name</th><th>Enabled</th><th>Last Run Status</th><th>Last Run Date/Time</th></tr>";
foreach ($job in $jobs) {
$enabledStatus = if ($job.Enabled -eq 1) { 'Yes' } else { 'No' };
$htmlreport += "<tr>";
$htmlreport += "<td>$($job.JobName)</td>";
$htmlreport += "<td>$($enabledStatus)</td>";
$htmlreport += "<td>$($job.LastRunStatus)</td>";
$htmlreport += "<td>$($job.LastRunDateTime)</td>";
$htmlreport += "</tr>";
}
$htmlreport += "</table>";
} else {
$htmlreport += "<p>No SQL Server Agent Jobs found for this instance.</p>";
}
}
در نهایت، بدنه HTML را می‌بندیم و گزارش را در یک فایل خروجی می‌گیریم. سپس می‌توانید این فایل را در هر مرورگر وب باز کنید تا وضعیت Jobهای SQL Server Agent خود را مشاهده کنید:
$htmlreport += "</body>";
$htmlreport += "</html>";
$htmlreport | Out-File -FilePath "C:\temp\SQLAgentJobStatusReport.html";
Invoke-Item "C:\temp\SQLAgentJobStatusReport.html";
با ترکیب تمام قطعات، در ادامه اسکریپت کامل PowerShell برای تولید گزارش HTML آورده شده است:
# Initialize HTML report variable
$htmlreport = ""
$htmlreport += "<html>"
$htmlreport += "<head>"
$htmlreport += "<Title>SQL Server Agent Job Status Report</Title>"
$htmlreport += "<style>"
$htmlreport += "body { font-family: Arial, Helvetica, sans-serif;}"
$htmlreport += "table { border-collapse: collapse; width: 100%;}"
$htmlreport += "td, th { border: 1px solid #ddd; padding: 8px;}"
$htmlreport += "tr:nth-child(even){background-color: #f2f2f2;}"
$htmlreport += "tr:hover {background-color: #ddd;}"
$htmlreport += "th { padding-top: 12px; padding-bottom: 12px; text-align: left; background-color: #4CAF50; color: white;}"
$htmlreport += "</style>"
$htmlreport += "</head>"
$htmlreport += "<body>"
$htmlreport += "<h1>SQL Server Agent Job Status Report</h1>"
$htmlreport += "<p>This report was generated on: $(Get-Date -Format 'MM/dd/yyyy HH:mm:ss')</p>"
# SQL Query to get job details
$sqlquery = "
SELECT
j.name AS JobName,
j.enabled AS Enabled,
CASE
WHEN sjh.run_status = 0 THEN 'Failed'
WHEN sjh.run_status = 1 THEN 'Succeeded'
WHEN sjh.run_status = 2 THEN 'Retry'
WHEN sjh.run_status = 3 THEN 'Canceled'
WHEN sjh.run_status = 4 THEN 'In Progress'
ELSE 'Unknown'
END AS LastRunStatus,
msdb.dbo.agent_datetime(sjh.run_date, sjh.run_time) AS LastRunDateTime
FROM
msdb.dbo.sysjobs j
LEFT JOIN
msdb.dbo.sysjobhistory sjh ON j.job_id = sjh.job_id
AND sjh.instance_id = (SELECT MAX(instance_id) FROM msdb.dbo.sysjobhistory WHERE job_id = j.job_id)
ORDER BY
j.name;
"
# Loop through SQL Server Agent instances and get job status
get-service -displayname "sql server agent*" | ForEach-Object {
$servername = $_.Name.Replace('SQL Server Agent (','').Replace(')','');
$htmlreport += "<h2>Job Status for SQL Server Instance: $($servername)</h2>";
$jobs = Invoke-Sqlcmd -query $sqlquery -serverinstance $servername;
if ($jobs) {
$htmlreport += "<table>";
$htmlreport += "<tr><th>Job Name</th><th>Enabled</th><th>Last Run Status</th><th>Last Run Date/Time</th></tr>";
foreach ($job in $jobs) {
$enabledStatus = if ($job.Enabled -eq 1) { 'Yes' } else { 'No' };
$htmlreport += "<tr>";
$htmlreport += "<td>$($job.JobName)</td>";
$htmlreport += "<td>$($enabledStatus)</td>";
$htmlreport += "<td>$($job.LastRunStatus)</td>";
$htmlreport += "<td>$($job.LastRunDateTime)</td>";
$htmlreport += "</tr>";
}
$htmlreport += "</table>";
} else {
$htmlreport += "<p>No SQL Server Agent Jobs found for this instance.</p>";
}
}
# Close HTML and output to file
$htmlreport += "</body>";
$htmlreport += "</html>";
# Specify the output path
$outputPath = "C:\temp\SQLAgentJobStatusReport.html";
# Check if the directory exists, if not, create it
$outputDirectory = Split-Path $outputPath -Parent;
if (!(Test-Path -Path $outputDirectory)) {
New-Item -Path $outputDirectory -ItemType Directory -Force;
}
$htmlreport | Out-File -FilePath $outputPath -Encoding UTF8;
Invoke-Item $outputPath;
این اسکریپت PowerShell راهی قدرتمند برای تولید یک گزارش HTML دقیق از وضعیت Jobهای SQL Server Agent شما در چندین نمونه (instance) فراهم می‌کند. با خودکارسازی تولید این گزارش، می‌توانید از نظارت به موقع و مدیریت فعال محیط SQL Server خود اطمینان حاصل کنید. می‌توانید کوئری SQL یا استایل‌بندی HTML را برای مطابقت با نیازهای گزارش‌دهی خاص خود سفارشی کنید. نظارت موفقی داشته باشید!