# Collect-SQLErrorLogs.ps1
# Uses CMS as source of truth
# Version 1.0

param(
    [string]$SqlInstance = "YOURSERVER01",
    [string]$Database = "DBAOpsRepository"
)

Write-Host "=== DBAOps SQL Error Log Collector ===" -ForegroundColor Cyan
Write-Host "Started: $(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')"

# Get servers from CMS - SOURCE OF TRUTH
$cmsQuery = @"
SELECT server_name AS ServerName
FROM msdb.dbo.sysmanagement_shared_registered_servers_internal
WHERE server_name NOT LIKE '%\%'
  AND server_name <> @@SERVERNAME
ORDER BY server_name
"@

try {
    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = "Server=$SqlInstance;Database=msdb;Integrated Security=True;TrustServerCertificate=True"
    $connection.Open()
    
    $command = $connection.CreateCommand()
    $command.CommandText = $cmsQuery
    $adapter = New-Object System.Data.SqlClient.SqlDataAdapter $command
    $dataset = New-Object System.Data.DataSet
    $adapter.Fill($dataset) | Out-Null
    $connection.Close()
    
    $Servers = $dataset.Tables[0]
    Write-Host "Found $($Servers.Rows.Count) servers from CMS" -ForegroundColor Green
}
catch {
    Write-Host "ERROR getting servers from CMS: $_" -ForegroundColor Red
    exit 1
}

$collected = 0
$errors = 0

foreach ($row in $Servers.Rows) {
    $ServerName = $row.ServerName
    Write-Host "  Checking: $ServerName" -ForegroundColor Gray -NoNewline
    
    try {
        # Get recent error log entries (last 24 hours, errors and warnings only)
        $errorLogQuery = @"
EXEC sp_readerrorlog 0, 1, NULL, NULL;
"@
        
        $conn = New-Object System.Data.SqlClient.SqlConnection
        $conn.ConnectionString = "Server=$ServerName;Database=master;Integrated Security=True;TrustServerCertificate=True;Connect Timeout=10"
        $conn.Open()
        
        $cmd = $conn.CreateCommand()
        $cmd.CommandText = $errorLogQuery
        $cmd.CommandTimeout = 30
        
        $adapt = New-Object System.Data.SqlClient.SqlDataAdapter $cmd
        $ds = New-Object System.Data.DataSet
        $adapt.Fill($ds) | Out-Null
        $conn.Close()
        
        $results = $ds.Tables[0]
        
        # Filter for errors/warnings in last 24 hours
        $cutoff = (Get-Date).AddHours(-24)
        $filteredResults = $results.Rows | Where-Object { 
            $_.LogDate -gt $cutoff -and 
            ($_.Text -like '*Error*' -or $_.Text -like '*Warning*' -or $_.Text -like '*Fail*' -or $_.Text -like '*Login failed*')
        }
        
        if ($filteredResults) {
            $repoConn = New-Object System.Data.SqlClient.SqlConnection
            $repoConn.ConnectionString = "Server=$SqlInstance;Database=$Database;Integrated Security=True;TrustServerCertificate=True"
            $repoConn.Open()
            
            foreach ($r in $filteredResults | Select-Object -First 50) {
                $logText = ($r.Text -replace "'", "''")
                if ($logText.Length -gt 4000) { $logText = $logText.Substring(0, 4000) }
                
                $insertSQL = @"
IF NOT EXISTS (SELECT 1 FROM logs.SQLErrorLog WHERE ServerName = '$ServerName' AND LogDate = '$($r.LogDate)' AND LogText = '$logText')
INSERT INTO logs.SQLErrorLog (ServerName, LogDate, ProcessInfo, LogText, CollectedAt)
VALUES ('$ServerName', '$($r.LogDate)', '$($r.ProcessInfo)', '$logText', GETDATE())
"@
                
                try {
                    $insertCmd = $repoConn.CreateCommand()
                    $insertCmd.CommandText = $insertSQL
                    $insertCmd.ExecuteNonQuery() | Out-Null
                    $collected++
                } catch { }
            }
            $repoConn.Close()
            Write-Host " - $($filteredResults.Count) entries" -ForegroundColor Green
        } else {
            Write-Host " - 0 errors" -ForegroundColor Gray
        }
    }
    catch {
        Write-Host " - ERROR" -ForegroundColor Yellow
        $errors++
    }
}

Write-Host ""
Write-Host "=== Collection Complete ===" -ForegroundColor Cyan
Write-Host "  Log entries collected: $collected"
Write-Host "  Servers with errors: $errors"
