# =============================================================================
# Collect-ServerAlerts.ps1
# =============================================================================
# Purpose: Collect alerts from SQL Server error logs and other sources
# Date: January 19, 2026
# Schedule: Run every 15-30 minutes via SQL Agent or Task Scheduler
# =============================================================================

param(
    [string]$RepositoryInstance = "YOURSERVER01",
    [string]$RepositoryDatabase = "DBAOpsRepository",
    [string]$OutputPath = "E:\DBAOps\Logs",
    [int]$ConnectionTimeout = 30,
    [int]$QueryTimeout = 120,
    [int]$ErrorLogHoursBack = 24,
    [switch]$VerboseOutput
)

$ErrorActionPreference = "Continue"
$startTime = Get-Date
$timestamp = Get-Date -Format "yyyyMMdd_HHmmss"

# Ensure output directory exists
if (-not (Test-Path $OutputPath)) {
    New-Item -ItemType Directory -Path $OutputPath -Force | Out-Null
}

$logFile = Join-Path $OutputPath "AlertCollection_$timestamp.log"

function Write-Log {
    param([string]$Message, [string]$Level = "INFO")
    $logEntry = "$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss') [$Level] $Message"
    Add-Content -Path $logFile -Value $logEntry
    
    switch ($Level) {
        "ERROR" { Write-Host $Message -ForegroundColor Red }
        "WARNING" { Write-Host $Message -ForegroundColor Yellow }
        "SUCCESS" { Write-Host $Message -ForegroundColor Green }
        default { if ($VerboseOutput) { Write-Host $Message -ForegroundColor Gray } }
    }
}

Write-Host "`n===================================================================" -ForegroundColor Cyan
Write-Host "Server Alert Collection" -ForegroundColor Cyan
Write-Host "===================================================================" -ForegroundColor Cyan
Write-Host "Start Time: $startTime" -ForegroundColor Yellow
Write-Host "Repository: $RepositoryInstance.$RepositoryDatabase" -ForegroundColor Yellow
Write-Host "Looking back: $ErrorLogHoursBack hours" -ForegroundColor Yellow
Write-Host ""

Write-Log "Alert collection started"

# =============================================================================
# Get List of Active SQL Servers
# =============================================================================

Write-Log "Retrieving list of SQL Servers..."
Write-Host "Retrieving list of SQL Servers..." -ForegroundColor Gray

try {
    $servers = Invoke-Sqlcmd -ServerInstance $RepositoryInstance `
                             -Database $RepositoryDatabase `
                             -TrustServerCertificate `
                             -ConnectionTimeout $ConnectionTimeout `
                             -Query @"
SELECT ServerName
FROM config.ServerInventory
WHERE IsActive = 1
AND (ServerType = 'SQL Server' OR ServerType IS NULL)
AND SQLVersion IS NOT NULL
AND SQLVersion != ''
ORDER BY ServerName
"@
    
    Write-Log "Found $($servers.Count) SQL Servers to query"
    Write-Host "Found $($servers.Count) SQL Servers to query" -ForegroundColor Green
} catch {
    Write-Log "Failed to retrieve server list: $($_.Exception.Message)" "ERROR"
    exit 1
}

# =============================================================================
# Query to Get Error Log Entries (Severity 17+)
# =============================================================================

$errorLogQuery = @"
DECLARE @HoursBack INT = $ErrorLogHoursBack;
DECLARE @StartDate DATETIME = DATEADD(HOUR, -@HoursBack, GETDATE());

-- Create temp table for error log
CREATE TABLE #ErrorLog (
    LogDate DATETIME,
    ProcessInfo NVARCHAR(100),
    LogText NVARCHAR(MAX)
);

-- Read current error log
INSERT INTO #ErrorLog
EXEC sp_readerrorlog 0, 1, NULL, NULL, @StartDate, NULL;

-- Return significant errors
SELECT 
    @@SERVERNAME AS ServerName,
    LogDate AS AlertTime,
    CASE 
        WHEN LogText LIKE '%Severity: 25%' OR LogText LIKE '%Severity: 24%' OR LogText LIKE '%Severity: 23%' OR LogText LIKE '%Severity: 22%' OR LogText LIKE '%Severity: 21%' THEN 'Critical'
        WHEN LogText LIKE '%Severity: 20%' OR LogText LIKE '%Severity: 19%' OR LogText LIKE '%Severity: 18%' OR LogText LIKE '%Severity: 17%' THEN 'High'
        WHEN LogText LIKE '%Error:%' OR LogText LIKE '%failed%' THEN 'Medium'
        WHEN LogText LIKE '%Warning%' THEN 'Low'
        ELSE 'Info'
    END AS Severity,
    CASE 
        WHEN LogText LIKE '%Login failed%' THEN 'Login Failure'
        WHEN LogText LIKE '%I/O%' OR LogText LIKE '%disk%' THEN 'Disk I/O'
        WHEN LogText LIKE '%memory%' THEN 'Memory'
        WHEN LogText LIKE '%backup%' THEN 'Backup'
        WHEN LogText LIKE '%deadlock%' THEN 'Deadlock'
        WHEN LogText LIKE '%corruption%' OR LogText LIKE '%DBCC%' THEN 'Corruption'
        WHEN LogText LIKE '%Severity:%' THEN 'SQL Error'
        ELSE 'General'
    END AS AlertType,
    'SQL Error Log' AS AlertSource,
    LogText AS AlertMessage
FROM #ErrorLog
WHERE 
    -- Filter for significant events
    (
        LogText LIKE '%Error:%'
        OR LogText LIKE '%Severity:%'
        OR LogText LIKE '%failed%'
        OR LogText LIKE '%Warning%'
        OR LogText LIKE '%I/O error%'
        OR LogText LIKE '%deadlock%'
        OR LogText LIKE '%corruption%'
    )
    -- Exclude noise
    AND LogText NOT LIKE '%This is an informational message%'
    AND LogText NOT LIKE '%Log was backed up%'
    AND LogText NOT LIKE '%DBCC CHECKDB%found 0 errors%'
    AND LogText NOT LIKE '%Setting database option%'
ORDER BY LogDate DESC;

DROP TABLE #ErrorLog;
"@

# =============================================================================
# Query to Get Failed Jobs in Last X Hours
# =============================================================================

$failedJobsQuery = @"
DECLARE @HoursBack INT = $ErrorLogHoursBack;

SELECT 
    @@SERVERNAME AS ServerName,
    msdb.dbo.agent_datetime(h.run_date, h.run_time) AS AlertTime,
    'High' AS Severity,
    'Job Failure' AS AlertType,
    'SQL Agent' AS AlertSource,
    'Job [' + j.name + '] failed. Step: ' + CAST(h.step_id AS VARCHAR) + ' - ' + h.step_name + '. Message: ' + LEFT(h.message, 500) AS AlertMessage
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
WHERE h.run_status = 0  -- Failed
AND h.step_id > 0  -- Exclude job outcome row
AND msdb.dbo.agent_datetime(h.run_date, h.run_time) >= DATEADD(HOUR, -@HoursBack, GETDATE())
ORDER BY msdb.dbo.agent_datetime(h.run_date, h.run_time) DESC;
"@

# =============================================================================
# Query to Check Disk Space
# =============================================================================

$diskSpaceQuery = @"
SELECT 
    @@SERVERNAME AS ServerName,
    GETDATE() AS AlertTime,
    CASE 
        WHEN CAST(available_bytes AS FLOAT) / CAST(total_bytes AS FLOAT) * 100 < 5 THEN 'Critical'
        WHEN CAST(available_bytes AS FLOAT) / CAST(total_bytes AS FLOAT) * 100 < 10 THEN 'High'
        WHEN CAST(available_bytes AS FLOAT) / CAST(total_bytes AS FLOAT) * 100 < 20 THEN 'Medium'
        ELSE 'Low'
    END AS Severity,
    'Disk Space' AS AlertType,
    'System Monitor' AS AlertSource,
    'Drive ' + volume_mount_point + ' has ' + 
        CAST(CAST(available_bytes / 1073741824.0 AS DECIMAL(10,2)) AS VARCHAR) + ' GB free (' +
        CAST(CAST(CAST(available_bytes AS FLOAT) / CAST(total_bytes AS FLOAT) * 100 AS DECIMAL(5,2)) AS VARCHAR) + '% free)' AS AlertMessage
FROM sys.dm_os_volume_stats(DB_ID(), 1)
WHERE CAST(available_bytes AS FLOAT) / CAST(total_bytes AS FLOAT) * 100 < 20;
"@

# =============================================================================
# Collect Alerts from Each Server
# =============================================================================

$allAlerts = @()
$successCount = 0
$failCount = 0

foreach ($server in $servers) {
    $serverName = $server.ServerName
    Write-Log "Collecting alerts from: $serverName"
    
    if ($VerboseOutput) {
        Write-Host "  Collecting from: $serverName" -ForegroundColor Gray -NoNewline
    }
    
    $serverAlerts = @()
    
    try {
        # Collect Error Log Alerts
        try {
            $errorAlerts = Invoke-Sqlcmd -ServerInstance $serverName `
                                         -Database "master" `
                                         -TrustServerCertificate `
                                         -ConnectionTimeout $ConnectionTimeout `
                                         -QueryTimeout $QueryTimeout `
                                         -Query $errorLogQuery `
                                         -ErrorAction SilentlyContinue
            if ($errorAlerts) {
                $serverAlerts += $errorAlerts
            }
        } catch {
            Write-Log "  Error reading error log from $serverName : $($_.Exception.Message)" "WARNING"
        }
        
        # Collect Failed Job Alerts
        try {
            $jobAlerts = Invoke-Sqlcmd -ServerInstance $serverName `
                                       -Database "msdb" `
                                       -TrustServerCertificate `
                                       -ConnectionTimeout $ConnectionTimeout `
                                       -QueryTimeout $QueryTimeout `
                                       -Query $failedJobsQuery `
                                       -ErrorAction SilentlyContinue
            if ($jobAlerts) {
                $serverAlerts += $jobAlerts
            }
        } catch {
            Write-Log "  Error reading job history from $serverName : $($_.Exception.Message)" "WARNING"
        }
        
        # Collect Disk Space Alerts
        try {
            $diskAlerts = Invoke-Sqlcmd -ServerInstance $serverName `
                                        -Database "master" `
                                        -TrustServerCertificate `
                                        -ConnectionTimeout $ConnectionTimeout `
                                        -QueryTimeout $QueryTimeout `
                                        -Query $diskSpaceQuery `
                                        -ErrorAction SilentlyContinue
            if ($diskAlerts) {
                $serverAlerts += $diskAlerts
            }
        } catch {
            Write-Log "  Error checking disk space on $serverName : $($_.Exception.Message)" "WARNING"
        }
        
        if ($serverAlerts.Count -gt 0) {
            $allAlerts += $serverAlerts
            Write-Log "  Found $($serverAlerts.Count) alerts on $serverName"
            if ($VerboseOutput) {
                Write-Host " - $($serverAlerts.Count) alerts" -ForegroundColor $(if ($serverAlerts.Count -gt 5) { 'Yellow' } else { 'Green' })
            }
        } else {
            Write-Log "  No alerts found on $serverName"
            if ($VerboseOutput) {
                Write-Host " - No alerts" -ForegroundColor Green
            }
        }
        $successCount++
        
    } catch {
        Write-Log "  Failed to connect to $serverName : $($_.Exception.Message)" "ERROR"
        if ($VerboseOutput) {
            Write-Host " - FAILED" -ForegroundColor Red
        }
        $failCount++
    }
}

Write-Host ""
Write-Host "Collection complete: $successCount succeeded, $failCount failed" -ForegroundColor $(if ($failCount -gt 0) { 'Yellow' } else { 'Green' })
Write-Log "Collection complete: $successCount succeeded, $failCount failed"

# =============================================================================
# Insert Alerts into Repository
# =============================================================================

if ($allAlerts.Count -gt 0) {
    Write-Host ""
    Write-Host "Inserting $($allAlerts.Count) alerts into repository..." -ForegroundColor Yellow
    Write-Log "Inserting $($allAlerts.Count) alerts into repository"
    
    try {
        $insertCount = 0
        $skipCount = 0
        
        foreach ($alert in $allAlerts) {
            # Check for duplicates (same server, time, message)
            $checkQuery = @"
SELECT COUNT(*) AS Cnt
FROM alerts.RecentAlerts
WHERE ServerName = '$($alert.ServerName)'
AND AlertTime = '$($alert.AlertTime)'
AND AlertMessage = '$(($alert.AlertMessage -replace "'", "''").Substring(0, [Math]::Min(200, $alert.AlertMessage.Length)))'
"@
            
            $exists = Invoke-Sqlcmd -ServerInstance $RepositoryInstance `
                                    -Database $RepositoryDatabase `
                                    -TrustServerCertificate `
                                    -Query $checkQuery
            
            if ($exists.Cnt -eq 0) {
                $insertQuery = @"
INSERT INTO alerts.RecentAlerts (ServerName, AlertTime, Severity, AlertType, AlertSource, AlertMessage, CollectedDate)
VALUES (
    '$($alert.ServerName)',
    '$($alert.AlertTime)',
    '$($alert.Severity)',
    '$($alert.AlertType)',
    '$($alert.AlertSource)',
    '$(($alert.AlertMessage -replace "'", "''"))',
    GETDATE()
)
"@
                
                Invoke-Sqlcmd -ServerInstance $RepositoryInstance `
                              -Database $RepositoryDatabase `
                              -TrustServerCertificate `
                              -Query $insertQuery
                
                $insertCount++
            } else {
                $skipCount++
            }
        }
        
        Write-Host "âœ“ Inserted $insertCount new alerts (skipped $skipCount duplicates)" -ForegroundColor Green
        Write-Log "Inserted $insertCount new alerts (skipped $skipCount duplicates)" "SUCCESS"
        
    } catch {
        Write-Host "âœ— Error inserting alerts: $($_.Exception.Message)" -ForegroundColor Red
        Write-Log "Error inserting alerts: $($_.Exception.Message)" "ERROR"
    }
} else {
    Write-Host "No new alerts to insert" -ForegroundColor Gray
    Write-Log "No new alerts to insert"
}

# =============================================================================
# Cleanup Old Alerts (Keep 30 days)
# =============================================================================

Write-Host ""
Write-Host "Cleaning up old alerts (>30 days)..." -ForegroundColor Gray

try {
    $cleanupQuery = @"
DELETE FROM alerts.RecentAlerts
WHERE AlertTime < DATEADD(DAY, -30, GETDATE())
"@
    
    $result = Invoke-Sqlcmd -ServerInstance $RepositoryInstance `
                            -Database $RepositoryDatabase `
                            -TrustServerCertificate `
                            -Query $cleanupQuery
    
    Write-Log "Old alerts cleaned up"
} catch {
    Write-Log "Error cleaning up old alerts: $($_.Exception.Message)" "WARNING"
}

# =============================================================================
# Summary
# =============================================================================

$endTime = Get-Date
$duration = $endTime - $startTime

# Get alert summary
try {
    $summary = Invoke-Sqlcmd -ServerInstance $RepositoryInstance `
                             -Database $RepositoryDatabase `
                             -TrustServerCertificate `
                             -Query @"
SELECT 
    Severity,
    COUNT(*) AS AlertCount
FROM alerts.RecentAlerts
WHERE AlertTime >= DATEADD(DAY, -1, GETDATE())
GROUP BY Severity
ORDER BY 
    CASE Severity 
        WHEN 'Critical' THEN 1 
        WHEN 'High' THEN 2 
        WHEN 'Medium' THEN 3 
        WHEN 'Low' THEN 4 
        ELSE 5 
    END
"@
} catch {
    $summary = $null
}

Write-Host ""
Write-Host "===================================================================" -ForegroundColor Cyan
Write-Host "ALERT COLLECTION SUMMARY" -ForegroundColor Cyan
Write-Host "===================================================================" -ForegroundColor Cyan
Write-Host "Servers Queried: $($servers.Count)" -ForegroundColor White
Write-Host "  Successful: $successCount" -ForegroundColor Green
Write-Host "  Failed: $failCount" -ForegroundColor $(if ($failCount -gt 0) { 'Red' } else { 'Green' })
Write-Host "Total Alerts Found: $($allAlerts.Count)" -ForegroundColor White
Write-Host ""

if ($summary) {
    Write-Host "ALERTS BY SEVERITY (Last 24 Hours):" -ForegroundColor Yellow
    foreach ($s in $summary) {
        $color = switch ($s.Severity) {
            'Critical' { 'Red' }
            'High' { 'DarkRed' }
            'Medium' { 'Yellow' }
            'Low' { 'Gray' }
            default { 'White' }
        }
        Write-Host "  $($s.Severity): $($s.AlertCount)" -ForegroundColor $color
    }
    Write-Host ""
}

Write-Host "Duration: $($duration.TotalSeconds.ToString('F1')) seconds" -ForegroundColor White
Write-Host "Log File: $logFile" -ForegroundColor Gray
Write-Host "===================================================================" -ForegroundColor Cyan
Write-Host ""

Write-Log "Alert collection completed in $($duration.TotalSeconds.ToString('F1')) seconds"
