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

param(
    [string]$SqlInstance = "YOURSERVER",
    [string]$Database = "JBRhoylleRepository"
)

Write-Host "=== DBAOps Expensive Query 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 {
        $expQuery = @"
SELECT TOP 20
    DB_NAME(qt.dbid) AS DatabaseName,
    LEFT(qt.text, 500) AS QueryText,
    qs.execution_count AS ExecutionCount,
    qs.total_worker_time / 1000 AS TotalCPU_ms,
    qs.total_elapsed_time / 1000 AS TotalDuration_ms,
    qs.total_logical_reads AS TotalLogicalReads,
    qs.last_execution_time AS LastExecuted
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
WHERE qs.last_execution_time > DATEADD(HOUR, -24, GETDATE())
ORDER BY qs.total_worker_time DESC
"@
        
        $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 = $expQuery
        $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]
        
        if ($results.Rows.Count -gt 0) {
            # Insert into repository
            $repoConn = New-Object System.Data.SqlClient.SqlConnection
            $repoConn.ConnectionString = "Server=$SqlInstance;Database=$Database;Integrated Security=True;TrustServerCertificate=True"
            $repoConn.Open()
            
            foreach ($r in $results.Rows) {
                $queryText = ($r.QueryText -replace "'", "''")
                $insertSQL = "INSERT INTO perf.ExpensiveQueries (ServerName, DatabaseName, QueryText, ExecutionCount, TotalCPU_ms, TotalDuration_ms, TotalLogicalReads, LastExecuted, CollectedAt) VALUES ('$ServerName', '$($r.DatabaseName)', '$queryText', $($r.ExecutionCount), $($r.TotalCPU_ms), $($r.TotalDuration_ms), $($r.TotalLogicalReads), '$($r.LastExecuted)', GETDATE())"
                
                try {
                    $insertCmd = $repoConn.CreateCommand()
                    $insertCmd.CommandText = $insertSQL
                    $insertCmd.ExecuteNonQuery() | Out-Null
                } catch { }
            }
            $repoConn.Close()
            $collected += $results.Rows.Count
            Write-Host " - $($results.Rows.Count) queries" -ForegroundColor Green
        } else {
            Write-Host " - 0 queries" -ForegroundColor Gray
        }
    }
    catch {
        Write-Host " - ERROR" -ForegroundColor Yellow
        $errors++
    }
}

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