claudetools/clients/instrumental-music-center/scripts/imc1-aimsql-enum.ps1

$ErrorActionPreference = 'Continue'
$ProgressPreference   = 'SilentlyContinue'

function Section($name) { "`n===== $name =====" }

# ---------- Resolve sqlcmd up front ----------
$sqlcmd = (Get-Command sqlcmd -ErrorAction SilentlyContinue).Source
if (-not $sqlcmd) {
    foreach ($p in @(
        'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\sqlcmd.exe',
        'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\sqlcmd.exe',
        'C:\Program Files (x86)\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\sqlcmd.exe',
        'C:\Program Files\Microsoft SQL Server\150\Tools\Binn\SQLCMD.EXE',
        'C:\Program Files\Microsoft SQL Server\140\Tools\Binn\SQLCMD.EXE',
        'C:\Program Files\Microsoft SQL Server\130\Tools\Binn\SQLCMD.EXE'
    )) {
        if (Test-Path $p) { $sqlcmd = $p; break }
    }
}
"sqlcmd: $sqlcmd"

# ---------- TASK 1: AIMSQL ENUMERATION (READ-ONLY) ----------

Section 'STEP 1: Service + process state'
Get-Service 'MSSQL$AIMSQL','SQLAgent$AIMSQL' -ErrorAction SilentlyContinue | Format-Table Name,Status,StartType -AutoSize

$svcCim = Get-CimInstance Win32_Service -Filter "Name='MSSQL`$AIMSQL'" -ErrorAction SilentlyContinue
$aimsqlPid = $null
if ($svcCim) { $aimsqlPid = [int]$svcCim.ProcessId }
"MSSQL`$AIMSQL PID (live from CIM): $aimsqlPid"
"MSSQL`$AIMSQL StartName (service account): $($svcCim.StartName)"

if ($aimsqlPid) {
    Get-Process -Id $aimsqlPid -ErrorAction SilentlyContinue |
        Select-Object Id,StartTime,
            @{n='WSMB';e={[math]::Round($_.WorkingSet64/1MB,1)}},
            @{n='VMMB';e={[math]::Round($_.VirtualMemorySize64/1MB,1)}},
            @{n='PrivateMB';e={[math]::Round($_.PrivateMemorySize64/1MB,1)}},
            Handles,@{n='Threads';e={$_.Threads.Count}},CPU |
        Format-List
} else {
    "No live PID for MSSQL`$AIMSQL"
}

Section 'STEP 2: Listening port + active TCP connections to AIMSQL'
if ($aimsqlPid) {
    "--- State summary ---"
    Get-NetTCPConnection -OwningProcess $aimsqlPid -ErrorAction SilentlyContinue |
        Group-Object State | Select-Object Name,Count | Format-Table -AutoSize

    "--- Listening sockets ---"
    Get-NetTCPConnection -OwningProcess $aimsqlPid -State Listen -ErrorAction SilentlyContinue |
        Select-Object LocalAddress,LocalPort | Format-Table -AutoSize

    "--- Established connections (remote talking to AIMSQL) ---"
    $est = Get-NetTCPConnection -OwningProcess $aimsqlPid -State Established -ErrorAction SilentlyContinue
    if ($est) {
        $est | Select-Object RemoteAddress,RemotePort,LocalAddress,LocalPort,CreationTime |
            Sort-Object RemoteAddress | Format-Table -AutoSize

        "--- Reverse-resolve unique remote IPs ---"
        $est.RemoteAddress | Sort-Object -Unique | ForEach-Object {
            $ip = $_
            $name = $null
            try { $name = [System.Net.Dns]::GetHostEntry($ip).HostName } catch {}
            [PSCustomObject]@{ RemoteIP=$ip; HostName=$name }
        } | Format-Table -AutoSize
    } else {
        "(no established connections to AIMSQL right now)"
    }

    "--- UDP endpoints for this PID ---"
    Get-NetUDPEndpoint -OwningProcess $aimsqlPid -ErrorAction SilentlyContinue |
        Select-Object LocalAddress,LocalPort | Format-Table -AutoSize
} else {
    "No PID — skipping TCP enumeration"
}

Section 'STEP 3: Windows-auth sqlcmd to .\AIMSQL'
$loginOk = $false
if ($sqlcmd) {
    $verOut = & $sqlcmd -S '.\AIMSQL' -E -d master -h-1 -W -l 5 -Q "SET NOCOUNT ON; SELECT @@VERSION;" 2>&1
    $verOut
    "---"
    & $sqlcmd -S '.\AIMSQL' -E -d master -h-1 -W -l 5 -Q "SET NOCOUNT ON; SELECT @@SERVERNAME AS server_name, SERVERPROPERTY('InstanceName') AS instance, SERVERPROPERTY('Edition') AS edition, SERVERPROPERTY('ProductVersion') AS product_version, SERVERPROPERTY('Collation') AS collation;" 2>&1
    if ($LASTEXITCODE -eq 0 -and ($verOut -join "`n") -notmatch 'Login failed') { $loginOk = $true }
}
"loginOk=$loginOk"

Section 'STEP 4: Databases on AIMSQL (sys.databases + sys.master_files)'
if ($loginOk -and $sqlcmd) {
    "--- sys.databases ---"
    & $sqlcmd -S '.\AIMSQL' -E -d master -h-1 -W -l 5 -Q "SET NOCOUNT ON; SELECT name, database_id, state_desc, recovery_model_desc, create_date, compatibility_level FROM sys.databases ORDER BY database_id;" 2>&1

    "--- sys.master_files (user DBs only, database_id > 4) ---"
    & $sqlcmd -S '.\AIMSQL' -E -d master -h-1 -W -l 5 -Q "SET NOCOUNT ON; SELECT database_id, type_desc, name AS logical_name, physical_name, size*8/1024 AS size_mb FROM sys.master_files WHERE database_id > 4 ORDER BY database_id, type;" 2>&1
} else {
    "Skipped (login failed) — will rely on file-system fallback below"
}

Section 'STEP 4b: File-system DB enumeration (fallback / cross-check) — AIMSQL DATA dirs'
$aimReg = Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\*\Setup' -ErrorAction SilentlyContinue |
          Where-Object { $_.PSChildName -match '^MSSQL\d+\.' } |
          ForEach-Object {
              $instKey = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$($_.PSChildName)"
              $instName = (Get-ItemProperty -Path $instKey -ErrorAction SilentlyContinue).Name
              [PSCustomObject]@{ Folder=$_.PSChildName; InstanceName=$instName; SqlDataRoot=$_.SqlDataRoot; SQLBinRoot=$_.SQLBinRoot }
          } | Where-Object { $_.InstanceName -eq 'AIMSQL' }
$aimReg | Format-Table -AutoSize -Wrap

$dataDirs = @()
$dataDirs += $aimReg | Where-Object { $_.SqlDataRoot } | ForEach-Object { Join-Path $_.SqlDataRoot 'MSSQL\DATA' }
$dataDirs += 'C:\Program Files\Microsoft SQL Server\MSSQL*.AIMSQL\MSSQL\DATA'
$dataDirs += 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL*.AIMSQL\MSSQL\DATA'
$dataDirs += 'S:\*AIMSQL*\MSSQL\DATA'
$dataDirs = $dataDirs | Where-Object { $_ } | Select-Object -Unique
"--- DATA dir candidates ---"
$dataDirs

"--- .mdf / .ldf / .ndf under AIMSQL DATA dirs ---"
foreach ($dir in $dataDirs) {
    Get-ChildItem -Path $dir -Include *.mdf,*.ldf,*.ndf -Recurse -ErrorAction SilentlyContinue |
        Select-Object FullName,@{n='SizeMB';e={[math]::Round($_.Length/1MB,1)}},LastWriteTime |
        Format-Table -AutoSize
}

Section 'STEP 5: Active sessions on AIMSQL'
if ($loginOk -and $sqlcmd) {
    & $sqlcmd -S '.\AIMSQL' -E -d master -h-1 -W -l 5 -Q "SET NOCOUNT ON; SELECT s.session_id, s.login_name, s.host_name, s.program_name, s.client_interface_name, s.login_time, s.last_request_end_time, s.status, c.client_net_address FROM sys.dm_exec_sessions s LEFT JOIN sys.dm_exec_connections c ON s.session_id = c.session_id WHERE s.is_user_process = 1 ORDER BY s.login_time;" 2>&1
} else {
    "Skipped (login failed)"
}

Section 'STEP 6: AIMSQL ERRORLOG location + tail'
$el = Get-ChildItem -Path 'C:\Program Files\Microsoft SQL Server\','C:\Program Files (x86)\Microsoft SQL Server\','S:\','D:\','E:\' -Recurse -Filter ERRORLOG -ErrorAction SilentlyContinue |
      Where-Object { $_.FullName -like '*AIMSQL*MSSQL\Log\ERRORLOG' -and $_.FullName -notlike '*SQLEXPRESS*' } |
      Select-Object -First 5
$el | Select-Object FullName,LastWriteTime,Length | Format-Table -AutoSize

$primary = $el | Sort-Object LastWriteTime -Descending | Select-Object -First 1
if ($primary) {
    "--- ERRORLOG path: $($primary.FullName) (LastWriteTime: $($primary.LastWriteTime)) ---"
    "--- Tail 200 lines ---"
    Get-Content $primary.FullName -Tail 200 -ErrorAction SilentlyContinue
} else {
    "No AIMSQL ERRORLOG located"
}

Section 'STEP 7: Memory cap (sp_configure read-only)'
if ($loginOk -and $sqlcmd) {
    & $sqlcmd -S '.\AIMSQL' -E -d master -h-1 -W -l 5 -Q "SET NOCOUNT ON; SELECT name, value, value_in_use, minimum, maximum FROM sys.configurations WHERE name IN ('max server memory (MB)','min server memory (MB)','show advanced options') ORDER BY name;" 2>&1
} else {
    "Skipped (login failed)"
}

Section 'STEP 8: Sanity — AIMSQL backup folders + .bak files'
"--- AIMSQL-named directories ---"
Get-ChildItem -Path 'C:\Program Files\Microsoft SQL Server\','S:\','E:\' -Recurse -Filter '*AIMSQL*' -Directory -ErrorAction SilentlyContinue |
    Select-Object FullName | Format-Table -AutoSize -Wrap

"--- .bak files under AIMSQL Backup dirs ---"
$bakRoots = @(
    'C:\Program Files\Microsoft SQL Server\MSSQL*.AIMSQL\MSSQL\Backup',
    'S:\*AIMSQL*\MSSQL\Backup',
    'S:\Backup',
    'S:\Backups'
)
foreach ($r in $bakRoots) {
    Get-ChildItem -Path $r -Filter '*.bak' -Recurse -ErrorAction SilentlyContinue |
        Select-Object FullName,@{n='SizeMB';e={[math]::Round($_.Length/1MB,1)}},LastWriteTime |
        Format-Table -AutoSize
}

# ---------- TASK 2: UNREGISTER SCHEDULED TASK (AUTHORIZED CHANGE) ----------

Section 'TASK 2: Unregister AIMSQL_Restart_20260506_0230 scheduled task'
$task = Get-ScheduledTask -TaskName 'AIMSQL_Restart_20260506_0230' -ErrorAction SilentlyContinue
if ($task) {
    $info = $task | Get-ScheduledTaskInfo
    "Found task — last run: $($info.LastRunTime), result: $($info.LastTaskResult), next run: $($info.NextRunTime)"
    try {
        Unregister-ScheduledTask -TaskName 'AIMSQL_Restart_20260506_0230' -Confirm:$false -ErrorAction Stop
        "Unregistered successfully."
    } catch {
        "Unregister FAILED: $_"
    }
    "--- Confirm gone ---"
    $check = Get-ScheduledTask -TaskName 'AIMSQL_Restart_20260506_0230' -ErrorAction SilentlyContinue
    if ($check) {
        "STILL PRESENT (failure)"
    } else {
        "Confirmed: task no longer registered."
    }
} else {
    "Task not found — already removed?"
}

Section 'TASK 2b: Audit-trail artifacts (do NOT delete)'
"--- aimsql-restart.ps1 ---"
$ps = Get-Item C:\Windows\Temp\aimsql-restart.ps1 -ErrorAction SilentlyContinue
if ($ps) { $ps | Select-Object FullName, Length, LastWriteTime | Format-List } else { "MISSING C:\Windows\Temp\aimsql-restart.ps1" }
"--- aimsql-restart.log ---"
$lg = Get-Item C:\Windows\Temp\aimsql-restart.log -ErrorAction SilentlyContinue
if ($lg) { $lg | Select-Object FullName, Length, LastWriteTime | Format-List } else { "MISSING C:\Windows\Temp\aimsql-restart.log" }

Section 'DONE'
"Completed at: $(Get-Date -Format 'yyyy-MM-dd HH:mm:ss zzz')"