Public/Get-sqmAgentJobScheduleReport.ps1
|
<#
.SYNOPSIS Generates a comprehensive SQL Agent Job Schedule Report. .DESCRIPTION Creates an HTML report showing all SQL Agent jobs with detailed schedule information, execution history, and performance metrics. Report includes: - Job Name (enabled/disabled status) - Schedule (start time, interval, frequency) - Last Execution (time, status, duration) - Next Scheduled Execution - Average Job Duration - Last Error Message (if failed) .PARAMETER SqlInstance SQL Server instance name. Default: current computer. .PARAMETER SqlCredential PSCredential for the SQL Server connection. .PARAMETER OutputPath Folder path for HTML report output. Default: C:\System\WinSrvLog\MSSQL Creates filename: AgentJobSchedule_<instance>_<timestamp>.html .PARAMETER OutputCsv Also export data as CSV file. .PARAMETER EnableException Throw exceptions immediately instead of logging. .PARAMETER NoOpen Do not automatically open the HTML report after creation. .EXAMPLE Get-sqmAgentJobScheduleReport -SqlInstance "SQL-Server1" .EXAMPLE Get-sqmAgentJobScheduleReport -SqlInstance "SQL-Server1" -OutputPath "C:\Reports" -OutputCsv .NOTES Requires: - dbatools module - SQL Server 2016+ with Agent enabled - Access to msdb database - Invoke-sqmLogging function .LINK Get-sqmAgentJobHistory Invoke-sqmSetupReport #> function Get-sqmAgentJobScheduleReport { [CmdletBinding()] [OutputType([PSCustomObject])] param ( [Parameter(Mandatory = $false)] [string]$SqlInstance = $env:COMPUTERNAME, [Parameter(Mandatory = $false)] [System.Management.Automation.PSCredential]$SqlCredential, [Parameter(Mandatory = $false)] [string]$OutputPath = 'C:\System\WinSrvLog\MSSQL', [Parameter(Mandatory = $false)] [switch]$OutputCsv, [Parameter(Mandatory = $false)] [switch]$EnableException, [Parameter(Mandatory = $false)] [switch]$NoOpen ) begin { $functionName = $MyInvocation.MyCommand.Name $timestamp = Get-Date -Format 'yyyyMMdd-HHmmss' # Validate dbatools if (-not (Get-Module dbatools -ListAvailable -ErrorAction SilentlyContinue)) { $errMsg = "dbatools module not found. Install: Install-Module dbatools -Force" Invoke-sqmLogging -Message $errMsg -FunctionName $functionName -Level "ERROR" if ($EnableException) { throw $errMsg } else { return } } # Create output directory if needed if (-not (Test-Path $OutputPath)) { try { $null = New-Item -ItemType Directory -Path $OutputPath -Force -ErrorAction Stop Invoke-sqmLogging -Message "Created output directory: $OutputPath" ` -FunctionName $functionName -Level "INFO" } catch { $errMsg = "Failed to create output directory '$OutputPath': $($_.Exception.Message)" Invoke-sqmLogging -Message $errMsg -FunctionName $functionName -Level "ERROR" if ($EnableException) { throw $errMsg } else { return } } } # Verify directory is writable try { $testFile = Join-Path $OutputPath ".sqmtest" $null = "test" | Out-File -FilePath $testFile -Encoding UTF8 -Force -ErrorAction Stop Remove-Item $testFile -Force -ErrorAction SilentlyContinue } catch { $errMsg = "Output directory '$OutputPath' is not writable: $($_.Exception.Message)" Invoke-sqmLogging -Message $errMsg -FunctionName $functionName -Level "ERROR" if ($EnableException) { throw $errMsg } else { return } } $jobData = @() $cleanInstance = $SqlInstance -replace '\\.*$', '' # Remove \INSTANCE suffix for filename } process { try { Invoke-sqmLogging -Message "Starting Agent Job Report for $SqlInstance" ` -FunctionName $functionName -Level "INFO" # Get all SQL Agent jobs $jobs = Get-DbaAgentJob -SqlInstance $SqlInstance -SqlCredential $SqlCredential ` -ErrorAction Stop if (-not $jobs) { Invoke-sqmLogging -Message "No SQL Agent jobs found on $SqlInstance" ` -FunctionName $functionName -Level "WARNING" } # Get job execution history from msdb. # # Frueher wurde ALLES (Zeitplaene UND Historie) in einem einzigen GROUP BY ueber # sj.name/ss.* zusammengefasst. Das hatte drei nachweisbare Fehler, alle gegen eine # echte SQL 2022-Instanz reproduziert: # # 1. Ein Job OHNE jemals gelaufene (oder vollstaendig geloeschte/purgierte) Historie # lieferte ueber den LEFT JOIN eine Zeile mit jh.run_status = NULL. Die Bedingung # "WHEN jh.run_status = 1 THEN 'Success' ELSE 'Failed'" faellt bei NULL in den # ELSE-Zweig (NULL = 1 ist UNKNOWN, nicht TRUE) - ein Job, der noch nie gelaufen # ist, wurde also als 'Failed' gemeldet statt als "noch nie gelaufen". # 2. MAX() auf die Ergebnis-STRINGS 'Success'/'Failed' aggregiert alphabetisch, nicht # zeitlich - 'Success' > 'Failed'. Ein Job, dessen LETZTER Lauf tatsaechlich # fehlgeschlagen ist, aber irgendwann in seiner Historie schon einmal erfolgreich # war, wurde als 'Success' gemeldet (belegt: Lauf 1 erfolgreich, Lauf 2 danach mit # absichtlichem Fehler - die alte Abfrage lieferte weiterhin 'Success'). # 3. Ein Job mit ZWEI ODER MEHR Zeitplaenen wird durch den JOIN auf sysjobschedules # vervielfacht (2 Zeitplaene x N Historienzeilen), GROUP BY sj.name, ss.* kollabiert # das in mehrere Zeilen PRO JOB statt einer. $jobHistoryData | Where-Object JobName # -eq ... lieferte dadurch mehrere Zeilen, $history.LastRunDate/.LastRunStatus/... # wurden zu ARRAYS statt Einzelwerten - belegt: LastExecution zeigte "Never" (die # [string]-Konvertierung eines Array mit zwei Datumswerten ergibt einen durch # Leerzeichen getrennten String, den TryParse als Ganzzahl ablehnt), Schedule zeigte # "No Schedule" trotz zweier vorhandener Zeitplaene, LastStatus wurde zu einem # Array wie {Success, Success} statt eines einzelnen Werts. # # Die Loesung trennt beide Fragen technisch voneinander: eine CTE mit ROW_NUMBER() # OVER (PARTITION BY job_id ORDER BY instance_id DESC) liefert GENAU EINE Zeile je Job # - den tatsaechlich letzten Lauf, instance_id ist SQL Agents eigener, garantiert # monoton steigender Zaehler und damit zuverlaessiger als ein Vergleich von # run_date/run_time. Die Zeitplaene werden separat auf einen reprsentativen Zeitplan # je Job reduziert (samt Gesamtzahl, falls mehrere existieren), statt die Historie zu # vervielfachen. Ein Job ohne Historie bekommt LastRunStatus = NULL (nicht 'Failed') # und wird in PowerShell als eigener Zustand "Never Run" behandelt. $jobHistoryQuery = @" ;WITH LastRun AS ( SELECT jh.job_id, jh.run_status, jh.run_date, jh.run_time, jh.message, ROW_NUMBER() OVER (PARTITION BY jh.job_id ORDER BY jh.instance_id DESC) AS rn FROM msdb.dbo.sysjobhistory jh WHERE jh.step_id = 0 ), DurationAgg AS ( SELECT jh.job_id, AVG(CAST( (jh.run_duration / 10000 * 3600) + ((jh.run_duration % 10000) / 100 * 60) + (jh.run_duration % 100) AS FLOAT)) AS AvgDurationSeconds FROM msdb.dbo.sysjobhistory jh WHERE jh.step_id = 0 GROUP BY jh.job_id ), ScheduleAgg AS ( SELECT sjs.job_id, COUNT(*) AS ScheduleCount, MIN(sjs.schedule_id) AS RepresentativeScheduleId FROM msdb.dbo.sysjobschedules sjs GROUP BY sjs.job_id ) SELECT sj.name AS JobName, sj.enabled AS IsEnabled, ISNULL(sch.ScheduleCount, 0) AS ScheduleCount, ss.name AS ScheduleName, ss.freq_type AS FrequencyType, ss.freq_interval AS FrequencyInterval, ss.freq_subday_type AS SubdayType, ss.freq_subday_interval AS SubdayInterval, ss.active_start_time AS ActiveStartTime, lr.run_date AS LastRunDate, lr.run_time AS LastRunTime, CASE WHEN lr.run_status IS NULL THEN NULL WHEN lr.run_status = 1 THEN 'Success' ELSE 'Failed' END AS LastRunStatus, da.AvgDurationSeconds, lr.message AS LastErrorMessage FROM msdb.dbo.sysjobs sj LEFT JOIN LastRun lr ON lr.job_id = sj.job_id AND lr.rn = 1 LEFT JOIN DurationAgg da ON da.job_id = sj.job_id LEFT JOIN ScheduleAgg sch ON sch.job_id = sj.job_id LEFT JOIN msdb.dbo.sysschedules ss ON ss.schedule_id = sch.RepresentativeScheduleId ORDER BY sj.name "@ $jobHistoryData = Invoke-DbaQuery -SqlInstance $SqlInstance ` -SqlCredential $SqlCredential ` -Database 'msdb' ` -Query $jobHistoryQuery ` -ErrorAction Stop # Process each job foreach ($job in $jobs) { # Get-DbaAgentJob und die Abfrage oben liefern beide GENAU eine Zeile je Job (siehe # Kommentar zur Query) - Where-Object liefert hier also ein einzelnes Objekt, kein # Array mehr, unabhaengig davon, wie viele Zeitplaene der Job hat. $history = $jobHistoryData | Where-Object { $_.JobName -eq $job.Name } | Select-Object -First 1 # Parse schedule frequency $scheduleText = if ($history -and $history.ScheduleCount -gt 0) { $text = _ConvertJobSchedule -FrequencyType $history.FrequencyType ` -FrequencyInterval $history.FrequencyInterval ` -SubdayType $history.SubdayType ` -SubdayInterval $history.SubdayInterval ` -StartTime $history.ActiveStartTime # Mehr als ein Zeitplan: nur der erste (nach schedule_id) wird angezeigt, das # wird kenntlich gemacht statt es stillschweigend zu verschweigen. if ([int]$history.ScheduleCount -gt 1) { $text += " (+$([int]$history.ScheduleCount - 1) weitere(r) Zeitplan/Zeitplaene)" } $text } else { 'No Schedule' } # Parse last execution $lastRunDateTime = if ($history.LastRunDate) { _ConvertJobRunTime -RunDate $history.LastRunDate -RunTime $history.LastRunTime } else { $null } # Calculate average duration (safe conversion from SQL NULL/empty) $avgDuration = if ($history.AvgDurationSeconds -and [int]::TryParse([string]$history.AvgDurationSeconds, [ref]$null)) { $seconds = [int][string]$history.AvgDurationSeconds "$([math]::Floor($seconds / 60))m $($seconds % 60)s" } else { 'N/A' } # Next execution estimate $nextExecution = if ($lastRunDateTime -and $scheduleText -ne 'No Schedule') { _EstimateNextExecution -LastRun $lastRunDateTime -Schedule $scheduleText } else { 'N/A' } # Kein Datensatz in sysjobhistory (nlist LastRunDate) heisst "noch nie gelaufen", # NICHT "fehlgeschlagen" - das ist die zentrale Korrektur dieser Funktion. Vorher # wurde genau dieser Fall (jh.run_status ist NULL, weil kein Lauf existiert) durch # "ELSE 'Failed'" in der T-SQL-CASE-Anweisung faelschlich als Fehlschlag gemeldet. $lastStatus = if (-not $lastRunDateTime) { 'Never Run' } elseif ($history.LastRunStatus -eq 'Success') { 'Success' } elseif ($history.LastRunStatus -eq 'Failed') { 'Failed' } else { 'Unknown' } $jobData += [PSCustomObject]@{ JobName = $job.Name Enabled = if ($job.IsEnabled) { 'Yes' } else { 'No' } ScheduleName = if ($history.ScheduleName) { $history.ScheduleName } else { 'Not Scheduled' } Schedule = $scheduleText LastExecution = if ($lastRunDateTime) { $lastRunDateTime } else { 'Never' } LastStatus = $lastStatus NextExecution = $nextExecution AvgDuration = $avgDuration LastError = if ($history.LastErrorMessage -and $lastStatus -eq 'Failed') { $cleanMsg = ($history.LastErrorMessage -replace '\[.*?\]', '' -replace '\r\n', ' ').Trim() if ($cleanMsg.Length -gt 100) { $cleanMsg.Substring(0, 100) } else { $cleanMsg } } else { 'N/A' } RawAvgSeconds = if ($history.AvgDurationSeconds -and [int]::TryParse([string]$history.AvgDurationSeconds, [ref]$null)) { [int][string]$history.AvgDurationSeconds } else { 0 } } } # Export CSV if requested if ($OutputCsv -and $jobData) { $csvPath = Join-Path $OutputPath "AgentJobSchedule_${cleanInstance}_${timestamp}.csv" try { $jobData | Export-Csv -Path $csvPath -NoTypeInformation -Force -ErrorAction Stop Invoke-sqmLogging -Message "CSV exported to: $csvPath" ` -FunctionName $functionName -Level "INFO" } catch { $csvErr = "Failed to write CSV to '$csvPath': $($_.Exception.Message)" Invoke-sqmLogging -Message $csvErr -FunctionName $functionName -Level "ERROR" throw $csvErr } } # Generate HTML report - always create file $htmlPath = Join-Path $OutputPath "AgentJobSchedule_${cleanInstance}_${timestamp}.html" try { # Call HTML generation function $html = _GenerateAgentJobHtml -JobData $jobData -SqlInstance $SqlInstance -Timestamp $timestamp # Write to file with explicit error handling if ([string]::IsNullOrEmpty($html)) { $html = "<html><body><h1>Error: HTML generation returned empty</h1></body></html>" } [System.IO.File]::WriteAllText($htmlPath, $html, [System.Text.Encoding]::UTF8) Invoke-sqmLogging -Message "HTML report created: $htmlPath (Size: $([System.IO.FileInfo]::new($htmlPath).Length) bytes)" ` -FunctionName $functionName -Level "INFO" Invoke-sqmOpenReport -HtmlFile $htmlPath -NoOpen:$NoOpen } catch { $fileErr = "HTML report creation failed: $($_.Exception.Message)" Invoke-sqmLogging -Message $fileErr -FunctionName $functionName -Level "ERROR" if ($EnableException) { throw $fileErr } } } catch { $errMsg = "Error generating Agent Job Report: $($_.Exception.Message)" Invoke-sqmLogging -Message $errMsg -FunctionName $functionName -Level "ERROR" if ($EnableException) { throw } else { return } } } end { # Return all job data collected from process block # Note: If reports failed to write, error will have been thrown in process block # Only reach here if process completed successfully return $jobData } } # ============ Helper Functions ============ function _ConvertJobSchedule { param( [object]$FrequencyType, [object]$FrequencyInterval, [object]$SubdayType, [object]$SubdayInterval, [object]$StartTime ) # Convert to int, handle NULL/empty [int]$FrequencyType = if ([int]::TryParse($FrequencyType, [ref]$null)) { [int]$FrequencyType } else { 0 } [int]$FrequencyInterval = if ([int]::TryParse($FrequencyInterval, [ref]$null)) { [int]$FrequencyInterval } else { 0 } [int]$SubdayType = if ([int]::TryParse($SubdayType, [ref]$null)) { [int]$SubdayType } else { 0 } [int]$SubdayInterval = if ([int]::TryParse($SubdayInterval, [ref]$null)) { [int]$SubdayInterval } else { 0 } [int]$StartTime = if ([int]::TryParse($StartTime, [ref]$null)) { [int]$StartTime } else { 0 } if (-not $FrequencyType) { return 'No Schedule' } # Frequency Type: 1=Once, 4=Daily, 8=Weekly, 16=Monthly, 32=Monthly Relative, 64=When Agent starts, 128=When CPU idle $freqDesc = switch ($FrequencyType) { 1 { 'One Time' } 4 { "Daily (every $FrequencyInterval day(s))" } 8 { 'Weekly' } 16 { "Monthly (day $FrequencyInterval)" } 32 { 'Monthly (relative)' } 64 { 'When SQL Agent starts' } 128 { 'When CPU is idle' } default { "Frequency Type: $FrequencyType" } } # Add subday interval if present # freq_subday_type: 1=AtSpecifiedTime, 2=Seconds, 4=Minutes, 8=Hours, 16=Days if ($SubdayType -and $SubdayInterval) { $subdayDesc = switch ($SubdayType) { 1 { '' } # At specified time only - no subday interval 2 { "every $SubdayInterval second(s)" } 4 { "every $SubdayInterval minute(s)" } 8 { "every $SubdayInterval hour(s)" } 16 { "every $SubdayInterval day(s)" } default { '' } } if ($subdayDesc) { $freqDesc += " $subdayDesc" } } # Add start time (format: HHMMSS) if ($StartTime -gt 0) { $hours = [int][math]::Floor($StartTime / 10000) $minutes = [int][math]::Floor(($StartTime % 10000) / 100) $seconds = [int]($StartTime % 100) # Format as HH:MM or HH:MM:SS if seconds present if ($seconds -gt 0) { $timeStr = "{0:D2}:{1:D2}:{2:D2}" -f $hours, $minutes, $seconds } else { $timeStr = "{0:D2}:{1:D2}" -f $hours, $minutes } $freqDesc += " @ $timeStr" } return $freqDesc } function _ConvertJobRunTime { param( [object]$RunDate, [object]$RunTime ) # Safe conversion from SQL NULL/empty [int]$RunDate = if ([int]::TryParse([string]$RunDate, [ref]$null)) { [int][string]$RunDate } else { 0 } [int]$RunTime = if ([int]::TryParse([string]$RunTime, [ref]$null)) { [int][string]$RunTime } else { 0 } # If date is 0 (NULL/empty from SQL), return null if ($RunDate -eq 0) { return $null } try { # Pad with zeros to ensure correct length $dateStr = $RunDate.ToString().PadLeft(8, '0') $timeStr = $RunTime.ToString().PadLeft(6, '0') # Validate length before substring if ($dateStr.Length -lt 8 -or $timeStr.Length -lt 6) { return 'Invalid DateTime Format' } $year = [int]$dateStr.Substring(0, 4) $month = [int]$dateStr.Substring(4, 2) $day = [int]$dateStr.Substring(6, 2) $hour = [int]$timeStr.Substring(0, 2) $minute = [int]$timeStr.Substring(2, 2) $second = [int]$timeStr.Substring(4, 2) $dt = New-Object DateTime($year, $month, $day, $hour, $minute, $second) return $dt.ToString('yyyy-MM-dd HH:mm:ss') } catch { return 'Invalid DateTime' } } function _EstimateNextExecution { param( [datetime]$LastRun, [string]$Schedule ) # Simple heuristics based on schedule text if ($Schedule -match 'Daily.*every (\d+)') { $days = [int]$matches[1] return ($LastRun.AddDays($days)).ToString('yyyy-MM-dd HH:mm') } elseif ($Schedule -match 'every (\d+) hour') { $hours = [int]$matches[1] return ($LastRun.AddHours($hours)).ToString('yyyy-MM-dd HH:mm') } elseif ($Schedule -match 'every (\d+) minute') { $minutes = [int]$matches[1] return ($LastRun.AddMinutes($minutes)).ToString('yyyy-MM-dd HH:mm') } else { return 'N/A' } } function _GenerateAgentJobHtml { param( [PSCustomObject[]]$JobData, [string]$SqlInstance, [string]$Timestamp ) if (-not $JobData) { $JobData = @() } $reportDate = Get-Date -Format 'yyyy-MM-dd HH:mm:ss' $successCount = @($JobData | Where-Object { $_.LastStatus -eq 'Success' }).Count $failureCount = @($JobData | Where-Object { $_.LastStatus -eq 'Failed' }).Count $disabledCount = @($JobData | Where-Object { $_.Enabled -eq 'No' }).Count $html = @" <!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>SQL Agent Job Schedule Report</title> <style> * { margin: 0; padding: 0; box-sizing: border-box; } body { font-family: 'Segoe UI', Arial, sans-serif; background: #0f172a; color: #e2e8f0; padding: 20px; } .container { max-width: 1600px; margin: 0 auto; } .header { background: linear-gradient(160deg, #1e3a8a 0%, #2e5090 100%); padding: 30px; border-radius: 12px; margin-bottom: 30px; border-left: 4px solid #3b82f6; } .header h1 { color: #60a5fa; font-size: 2em; margin-bottom: 10px; } .header p { color: #94a3b8; font-size: 0.95em; } .stats { display: grid; grid-template-columns: repeat(auto-fit, minmax(150px, 1fr)); gap: 15px; margin-bottom: 30px; } .stat-card { background: #0b1e3d; border: 1px solid #3b82f6; padding: 20px; border-radius: 8px; text-align: center; } .stat-value { font-size: 2.2em; font-weight: bold; color: #34d399; margin-bottom: 5px; } .stat-label { color: #94a3b8; font-size: 0.9em; } .controls { display: flex; gap: 10px; margin-bottom: 20px; } .controls input { background: #0b1e3d; border: 1px solid #3b82f6; color: #e2e8f0; padding: 10px; border-radius: 6px; flex: 1; max-width: 300px; } .controls button { background: #1e3a8a; border: 1px solid #3b82f6; color: #60a5fa; padding: 10px 20px; border-radius: 6px; cursor: pointer; font-weight: bold; } .controls button:hover { background: #2e5090; } table { width: 100%; border-collapse: collapse; background: #0b1e3d; border-radius: 8px; overflow: hidden; margin-bottom: 30px; } th { background: #1e3a8a; color: #60a5fa; padding: 15px; text-align: left; font-weight: 600; border-bottom: 2px solid #3b82f6; } td { padding: 12px 15px; border-bottom: 1px solid #2d5a8c; } tr:hover { background: #1a2f4d; } .job-name { color: #60a5fa; font-weight: 600; font-family: monospace; } .status-success { color: #34d399; font-weight: bold; } .status-failed { color: #ef4444; font-weight: bold; } .status-unknown { color: #94a3b8; } .enabled-yes { color: #34d399; } .enabled-no { color: #f59e0b; } .footer { color: #94a3b8; font-size: 0.85em; text-align: center; margin-top: 50px; padding-top: 20px; border-top: 1px solid #3b82f6; } </style> </head> <body> <div class="container"> <div class="header"> <h1>📊 SQL Agent Job Schedule Report</h1> <p>Server: <strong>$SqlInstance</strong> | Generated: $reportDate</p> </div> <div class="stats"> <div class="stat-card"> <div class="stat-value">$($JobData.Count)</div> <div class="stat-label">Total Jobs</div> </div> <div class="stat-card"> <div class="stat-value" style="color: #34d399;">$successCount</div> <div class="stat-label">Last Success</div> </div> <div class="stat-card"> <div class="stat-value" style="color: #ef4444;">$failureCount</div> <div class="stat-label">Last Failed</div> </div> <div class="stat-card"> <div class="stat-value" style="color: #f59e0b;">$disabledCount</div> <div class="stat-label">Disabled</div> </div> </div> <div class="controls"> <input type="text" id="searchBox" placeholder="Search job name..." onkeyup="filterTable()"> <button onclick="sortTable('JobName')">Sort by Name</button> <button onclick="sortTable('AvgDuration')">Sort by Duration</button> </div> <table id="jobTable"> <thead> <tr> <th style="width: 20%;">Job Name</th> <th style="width: 8%;">Status</th> <th style="width: 8%;">Enabled</th> <th style="width: 20%;">Schedule</th> <th style="width: 14%;">Last Execution</th> <th style="width: 12%;">Next Execution</th> <th style="width: 10%;">Avg Duration</th> <th style="width: 18%;">Last Error</th> </tr> </thead> <tbody> "@ # Add table rows foreach ($job in $JobData) { $statusClass = switch ($job.LastStatus) { 'Success' { 'status-success' } 'Failed' { 'status-failed' } 'Never Run' { 'status-unknown' } default { 'status-unknown' } } $enabledClass = switch ($job.Enabled) { 'Yes' { 'enabled-yes' } 'No' { 'enabled-no' } } $html += @" <tr> <td class="job-name">$($job.JobName)</td> <td class="$statusClass">$($job.LastStatus)</td> <td class="$enabledClass">$($job.Enabled)</td> <td style="font-size: 0.9em; color: #94a3b8;">$($job.Schedule)</td> <td style="color: #5dade2;">$($job.LastExecution)</td> <td style="color: #5dade2;">$($job.NextExecution)</td> <td style="font-weight: 600;">$($job.AvgDuration)</td> <td style="color: #f59e0b; font-size: 0.85em;">$($job.LastError)</td> </tr> "@ } $html += @" </tbody> </table> <div class="footer"> <p>Generated by sqmSQLTool | Get-sqmAgentJobScheduleReport</p> <p>Report ID: $Timestamp</p> </div> </div> <script> function filterTable() { const input = document.getElementById('searchBox'); const filter = input.value.toLowerCase(); const table = document.getElementById('jobTable'); const rows = table.getElementsByTagName('tr'); for (let i = 1; i < rows.length; i++) { const jobName = rows[i].cells[0].textContent.toLowerCase(); rows[i].style.display = jobName.includes(filter) ? '' : 'none'; } } function sortTable(column) { const table = document.getElementById('jobTable'); const tbody = table.querySelector('tbody'); const rows = Array.from(tbody.querySelectorAll('tr')); const columnIndex = { 'JobName': 0, 'LastStatus': 1, 'AvgDuration': 6 }[column] || 0; rows.sort((a, b) => { const aText = a.cells[columnIndex].textContent.trim(); const bText = b.cells[columnIndex].textContent.trim(); return aText.localeCompare(bText); }); rows.forEach(row => tbody.appendChild(row)); } </script> </body> </html> "@ return $html } |