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 = (Join-Path (Get-sqmDefaultOutputPath) 'AgentJobSchedule'), [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.freq_relative_interval AS RelativeInterval, ss.freq_recurrence_factor AS RecurrenceFactor, ss.active_start_date AS ActiveStartDate, ss.active_start_time AS ActiveStartTime, ss.active_end_time AS ActiveEndTime, 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 ` -RelativeInterval $history.RelativeInterval ` -RecurrenceFactor $history.RecurrenceFactor ` -StartDate $history.ActiveStartDate ` -StartTime $history.ActiveStartTime ` -EndTime $history.ActiveEndTime # 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]$RelativeInterval, [object]$RecurrenceFactor, [object]$StartDate, [object]$StartTime, [object]$EndTime ) # Convert to int, handle NULL/empty [int]$FrequencyType = if ([int]::TryParse([string]$FrequencyType, [ref]$null)) { [int][string]$FrequencyType } else { 0 } [int]$FrequencyInterval = if ([int]::TryParse([string]$FrequencyInterval, [ref]$null)) { [int][string]$FrequencyInterval } else { 0 } [int]$SubdayType = if ([int]::TryParse([string]$SubdayType, [ref]$null)) { [int][string]$SubdayType } else { 0 } [int]$SubdayInterval = if ([int]::TryParse([string]$SubdayInterval, [ref]$null)) { [int][string]$SubdayInterval } else { 0 } [int]$RelativeInterval = if ([int]::TryParse([string]$RelativeInterval, [ref]$null)) { [int][string]$RelativeInterval } else { 0 } [int]$RecurrenceFactor = if ([int]::TryParse([string]$RecurrenceFactor, [ref]$null)) { [int][string]$RecurrenceFactor } else { 0 } [int]$StartDate = if ([int]::TryParse([string]$StartDate, [ref]$null)) { [int][string]$StartDate } else { 0 } [int]$StartTime = if ([int]::TryParse([string]$StartTime, [ref]$null)) { [int][string]$StartTime } else { 0 } # active_end_time fehlt/NULL = bis Tagesende (SQL Agent-Default 235959) [int]$EndTime = if ([int]::TryParse([string]$EndTime, [ref]$null)) { [int][string]$EndTime } else { 235959 } if (-not $FrequencyType) { return 'No Schedule' } # HHMMSS -> HH:MM bzw. HH:MM:SS $fmtTime = { param([int]$t) $h = [int][math]::Floor($t / 10000) $m = [int][math]::Floor(($t % 10000) / 100) $s = [int]($t % 100) if ($s -gt 0) { '{0:D2}:{1:D2}:{2:D2}' -f $h, $m, $s } else { '{0:D2}:{1:D2}' -f $h, $m } } # sysschedules.freq_interval bei Weekly ist eine Bitmaske: 1=So, 2=Mo, 4=Di, 8=Mi, 16=Do, 32=Fr, 64=Sa. # Frueher wurde hier nur 'Weekly' ausgegeben - die eigentliche Information (WELCHER Tag) fehlte. # Anzeige beginnt mit Montag. # Absichtlich ein Array statt [ordered]@{}: OrderedDictionary interpretiert einen [int]-Index als # POSITION, nicht als Schluessel - $od[2] waere der dritte Eintrag, nicht Bit 2. $weekDays = @( @(2, 'Monday'), @(4, 'Tuesday'), @(8, 'Wednesday'), @(16, 'Thursday'), @(32, 'Friday'), @(64, 'Saturday'), @(1, 'Sunday') ) # freq_interval bei Monthly relative (32): 1..7 = So..Sa, 8 = Tag, 9 = Wochentag, 10 = Wochenendtag $relativeDays = @{ 1 = 'Sunday'; 2 = 'Monday'; 3 = 'Tuesday'; 4 = 'Wednesday'; 5 = 'Thursday'; 6 = 'Friday'; 7 = 'Saturday'; 8 = 'day'; 9 = 'weekday'; 10 = 'weekend day' } # freq_relative_interval: 1=First, 2=Second, 4=Third, 8=Fourth, 16=Last $relativeOrdinals = @{ 1 = 'first'; 2 = 'second'; 4 = 'third'; 8 = 'fourth'; 16 = 'last' } $freqDesc = switch ($FrequencyType) { 1 { if ($StartDate -gt 0) { $d = $StartDate.ToString().PadLeft(8, '0') "One Time on $($d.Substring(0,4))-$($d.Substring(4,2))-$($d.Substring(6,2))" } else { 'One Time' } } 4 { "Daily (every $FrequencyInterval day(s))" } 8 { $days = @($weekDays | Where-Object { $FrequencyInterval -band $_[0] } | ForEach-Object { $_[1] }) $dayText = if ($days.Count -eq 7) { 'all days' } elseif ($days.Count -gt 0) { $days -join ', ' } else { "unknown day(s) (mask $FrequencyInterval)" } if ($RecurrenceFactor -gt 1) { "Weekly (every $RecurrenceFactor weeks) on $dayText" } else { "Weekly on $dayText" } } 16 { if ($RecurrenceFactor -gt 1) { "Monthly (every $RecurrenceFactor months) on day $FrequencyInterval" } else { "Monthly on day $FrequencyInterval" } } 32 { $ord = if ($relativeOrdinals.ContainsKey($RelativeInterval)) { $relativeOrdinals[$RelativeInterval] } else { "?($RelativeInterval)" } $day = if ($relativeDays.ContainsKey($FrequencyInterval)) { $relativeDays[$FrequencyInterval] } else { "?($FrequencyInterval)" } if ($RecurrenceFactor -gt 1) { "Monthly (every $RecurrenceFactor months) on the $ord $day" } else { "Monthly on the $ord $day" } } 64 { 'When SQL Agent starts' } 128 { 'When CPU is idle' } default { "Frequency Type: $FrequencyType" } } # Agent-Start / CPU idle haben keine Uhrzeit if ($FrequencyType -in 64, 128) { return $freqDesc } # freq_subday_type: 1=AtSpecifiedTime, 2=Seconds, 4=Minutes, 8=Hours $subdayUnit = switch ($SubdayType) { 2 { 'second(s)' } 4 { 'minute(s)' } 8 { 'hour(s)' } default { $null } } if ($subdayUnit -and $SubdayInterval -gt 0 -and $FrequencyType -ne 1) { $freqDesc += " every $SubdayInterval $subdayUnit" # Zeitfenster nur nennen, wenn es nicht der ganze Tag ist if ($StartTime -gt 0 -or $EndTime -lt 235959) { $freqDesc += " between $(& $fmtTime $StartTime) and $(& $fmtTime $EndTime)" } } else { # Einmal am Tag: Uhrzeit immer nennen, auch 00:00 (vorher bei Mitternacht unterschlagen) $freqDesc += " @ $(& $fmtTime $StartTime)" } 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 } |