chiefgeek
a1a17e81a1
Includes updated JS challenge scripts with Claude-User whitelist, same-site referer bypass, Blackbox-Exporter allowed bot, and all new exporters, cheat sheets, and automation scripts.
639 lines
25 KiB
PowerShell
639 lines
25 KiB
PowerShell
<#
|
|
.SYNOPSIS
|
|
SQL Server Prometheus Metrics Exporter
|
|
.DESCRIPTION
|
|
Prometheus exporter for SQL Server - buffer cache hit ratio, page life
|
|
expectancy, batch requests, SQL compilations, lock waits, tempdb usage,
|
|
database sizes, log space, backup age, connections, memory, CPU, and
|
|
wait stats. Exports metrics as Prometheus-compatible text format.
|
|
.PARAMETER Mode
|
|
Output mode: 'stdout' (default), 'textfile', or 'http'
|
|
.PARAMETER Port
|
|
HTTP port for http mode (default: 9399)
|
|
.PARAMETER ServerInstance
|
|
SQL Server instance (default: localhost)
|
|
.PARAMETER TextfileDir
|
|
Directory for textfile collector output (default: C:\ProgramData\node_exporter)
|
|
.PARAMETER InstallScheduledTask
|
|
Switch to create a scheduled task for auto-start on system boot
|
|
.PARAMETER TaskIntervalMinutes
|
|
Interval in minutes for the scheduled task (default: 2)
|
|
.NOTES
|
|
Author: Phil Connor
|
|
Contact: contact@mylinux.work
|
|
Website: https://mylinux.work
|
|
License: MIT
|
|
Version: 1.0
|
|
|
|
Metrics Exported:
|
|
Core Status:
|
|
- mssql_up
|
|
- mssql_exporter_info{version}
|
|
|
|
Performance:
|
|
- mssql_buffer_cache_hit_ratio
|
|
- mssql_page_life_expectancy_seconds
|
|
- mssql_batch_requests_total
|
|
- mssql_sql_compilations_total
|
|
- mssql_sql_recompilations_total
|
|
- mssql_lock_waits_total
|
|
- mssql_deadlocks_total
|
|
|
|
Tempdb:
|
|
- mssql_tempdb_data_size_bytes
|
|
- mssql_tempdb_log_size_bytes
|
|
|
|
Databases:
|
|
- mssql_database_size_bytes{database}
|
|
- mssql_database_log_used_percent{database}
|
|
- mssql_database_backup_age_hours{database}
|
|
|
|
Connections:
|
|
- mssql_connections_total
|
|
- mssql_connections_by_database{database}
|
|
|
|
Memory:
|
|
- mssql_memory_buffer_pool_bytes
|
|
- mssql_memory_plan_cache_bytes
|
|
|
|
CPU:
|
|
- mssql_cpu_usage_percent
|
|
|
|
Wait Stats:
|
|
- mssql_wait_stats{wait_type}
|
|
|
|
Exporter:
|
|
- mssql_exporter_duration_seconds
|
|
- mssql_exporter_last_run_timestamp
|
|
#>
|
|
|
|
param(
|
|
[ValidateSet('stdout', 'textfile', 'http')]
|
|
[string]$Mode = 'stdout',
|
|
|
|
[int]$Port = 9399,
|
|
|
|
[string]$ServerInstance = 'localhost',
|
|
|
|
[string]$TextfileDir = 'C:\ProgramData\node_exporter',
|
|
|
|
[switch]$InstallScheduledTask,
|
|
|
|
[int]$TaskIntervalMinutes = 2
|
|
)
|
|
|
|
# Create a scheduled task to run this script every $TaskIntervalMinutes minutes
|
|
if ($InstallScheduledTask) {
|
|
$taskName = "MssqlMetricsExporter"
|
|
$existingTask = Get-ScheduledTask -TaskName $taskName -ErrorAction SilentlyContinue
|
|
|
|
if (-not $existingTask) {
|
|
$taskAction = New-ScheduledTaskAction -Execute "powershell.exe" -Argument "-NoProfile -ExecutionPolicy Bypass -File `"$($MyInvocation.MyCommand.Path)`" -Mode textfile -ServerInstance `"$ServerInstance`""
|
|
|
|
if (-not $TaskIntervalMinutes -or $TaskIntervalMinutes -le 0) {
|
|
throw "TaskIntervalMinutes must be a positive integer"
|
|
}
|
|
|
|
$taskTrigger = New-ScheduledTaskTrigger -Once -At (Get-Date).AddMinutes(1) -RepetitionInterval (New-TimeSpan -Minutes $TaskIntervalMinutes) -RepetitionDuration (New-TimeSpan -Days 365)
|
|
$taskPrincipal = New-ScheduledTaskPrincipal -UserId "SYSTEM" -LogonType ServiceAccount -RunLevel Highest
|
|
|
|
try {
|
|
Write-Host "Creating scheduled task: $taskName"
|
|
Register-ScheduledTask -TaskName $taskName -Action $taskAction -Trigger $taskTrigger -Principal $taskPrincipal -Description "Exports SQL Server metrics for Prometheus every $TaskIntervalMinutes minutes"
|
|
|
|
$createdTask = Get-ScheduledTask -TaskName $taskName -ErrorAction SilentlyContinue
|
|
if (-not $createdTask) {
|
|
throw "Failed to verify scheduled task creation"
|
|
}
|
|
Write-Host "Successfully created scheduled task: $taskName" -ForegroundColor Green
|
|
} catch {
|
|
Write-Error "Failed to create scheduled task: $($_.Exception.Message)"
|
|
throw
|
|
}
|
|
} else {
|
|
Write-Host "Scheduled task '$taskName' already exists, skipping creation"
|
|
}
|
|
}
|
|
|
|
$ErrorActionPreference = 'SilentlyContinue'
|
|
|
|
# ============================================================================
|
|
# HELPER FUNCTIONS
|
|
# ============================================================================
|
|
|
|
function Get-UnixTimestamp {
|
|
[int][double]::Parse((Get-Date -UFormat '%s'))
|
|
}
|
|
|
|
function Format-MetricValue {
|
|
param([double]$Value, [int]$Decimals = 2)
|
|
[math]::Round($Value, $Decimals)
|
|
}
|
|
|
|
function Invoke-SqlQuery {
|
|
param(
|
|
[string]$Query,
|
|
[string]$Database = 'master'
|
|
)
|
|
|
|
# Try Invoke-Sqlcmd first (SqlServer module)
|
|
try {
|
|
$result = Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Database -Query $Query -ErrorAction Stop -QueryTimeout 10
|
|
return $result
|
|
} catch { }
|
|
|
|
# Fallback to sqlcmd.exe
|
|
try {
|
|
$output = & sqlcmd.exe -S $ServerInstance -d $Database -Q $Query -h -1 -W -s "|" 2>$null
|
|
if ($LASTEXITCODE -ne 0) { return $null }
|
|
return $output | Where-Object { $_ -and $_.Trim() -ne '' -and $_ -notmatch '^\-+' -and $_ -notmatch 'rows affected' }
|
|
} catch {
|
|
return $null
|
|
}
|
|
}
|
|
|
|
# ============================================================================
|
|
# SQL SERVER METRICS
|
|
# ============================================================================
|
|
|
|
function Get-MssqlMetrics {
|
|
$sb = [System.Text.StringBuilder]::new()
|
|
|
|
# --- Buffer Cache Hit Ratio ---
|
|
[void]$sb.AppendLine('# HELP mssql_buffer_cache_hit_ratio Buffer cache hit ratio percentage')
|
|
[void]$sb.AppendLine('# TYPE mssql_buffer_cache_hit_ratio gauge')
|
|
try {
|
|
$bcQuery = @"
|
|
SELECT
|
|
MAX(CASE WHEN counter_name = 'Buffer cache hit ratio' THEN cntr_value END) AS ratio,
|
|
MAX(CASE WHEN counter_name = 'Buffer cache hit ratio base' THEN cntr_value END) AS base
|
|
FROM sys.dm_os_performance_counters
|
|
WHERE object_name LIKE '%Buffer Manager%'
|
|
AND counter_name IN ('Buffer cache hit ratio', 'Buffer cache hit ratio base')
|
|
"@
|
|
$bc = Invoke-SqlQuery -Query $bcQuery
|
|
if ($bc) {
|
|
$ratio = if ($bc.base -gt 0) { Format-MetricValue (($bc.ratio / $bc.base) * 100) } else { 0 }
|
|
[void]$sb.AppendLine("mssql_buffer_cache_hit_ratio $ratio")
|
|
} else {
|
|
[void]$sb.AppendLine("mssql_buffer_cache_hit_ratio 0")
|
|
}
|
|
} catch {
|
|
[void]$sb.AppendLine("mssql_buffer_cache_hit_ratio 0")
|
|
}
|
|
[void]$sb.AppendLine('')
|
|
|
|
# --- Page Life Expectancy ---
|
|
[void]$sb.AppendLine('# HELP mssql_page_life_expectancy_seconds Page life expectancy in seconds')
|
|
[void]$sb.AppendLine('# TYPE mssql_page_life_expectancy_seconds gauge')
|
|
try {
|
|
$pleQuery = "SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name LIKE '%Buffer Manager%' AND counter_name = 'Page life expectancy'"
|
|
$ple = Invoke-SqlQuery -Query $pleQuery
|
|
$pleVal = if ($ple) { $ple.cntr_value } else { 0 }
|
|
[void]$sb.AppendLine("mssql_page_life_expectancy_seconds $pleVal")
|
|
} catch {
|
|
[void]$sb.AppendLine("mssql_page_life_expectancy_seconds 0")
|
|
}
|
|
[void]$sb.AppendLine('')
|
|
|
|
# --- Batch Requests/sec ---
|
|
[void]$sb.AppendLine('# HELP mssql_batch_requests_total Batch requests per second')
|
|
[void]$sb.AppendLine('# TYPE mssql_batch_requests_total gauge')
|
|
try {
|
|
$brQuery = "SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name LIKE '%SQL Statistics%' AND counter_name = 'Batch Requests/sec'"
|
|
$br = Invoke-SqlQuery -Query $brQuery
|
|
$brVal = if ($br) { $br.cntr_value } else { 0 }
|
|
[void]$sb.AppendLine("mssql_batch_requests_total $brVal")
|
|
} catch {
|
|
[void]$sb.AppendLine("mssql_batch_requests_total 0")
|
|
}
|
|
[void]$sb.AppendLine('')
|
|
|
|
# --- SQL Compilations/sec ---
|
|
[void]$sb.AppendLine('# HELP mssql_sql_compilations_total SQL compilations per second')
|
|
[void]$sb.AppendLine('# TYPE mssql_sql_compilations_total gauge')
|
|
try {
|
|
$scQuery = "SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name LIKE '%SQL Statistics%' AND counter_name = 'SQL Compilations/sec'"
|
|
$sc = Invoke-SqlQuery -Query $scQuery
|
|
$scVal = if ($sc) { $sc.cntr_value } else { 0 }
|
|
[void]$sb.AppendLine("mssql_sql_compilations_total $scVal")
|
|
} catch {
|
|
[void]$sb.AppendLine("mssql_sql_compilations_total 0")
|
|
}
|
|
[void]$sb.AppendLine('')
|
|
|
|
# --- SQL Recompilations/sec ---
|
|
[void]$sb.AppendLine('# HELP mssql_sql_recompilations_total SQL recompilations per second')
|
|
[void]$sb.AppendLine('# TYPE mssql_sql_recompilations_total gauge')
|
|
try {
|
|
$srQuery = "SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name LIKE '%SQL Statistics%' AND counter_name = 'SQL Re-Compilations/sec'"
|
|
$sr = Invoke-SqlQuery -Query $srQuery
|
|
$srVal = if ($sr) { $sr.cntr_value } else { 0 }
|
|
[void]$sb.AppendLine("mssql_sql_recompilations_total $srVal")
|
|
} catch {
|
|
[void]$sb.AppendLine("mssql_sql_recompilations_total 0")
|
|
}
|
|
[void]$sb.AppendLine('')
|
|
|
|
# --- Lock Waits/sec ---
|
|
[void]$sb.AppendLine('# HELP mssql_lock_waits_total Lock waits per second')
|
|
[void]$sb.AppendLine('# TYPE mssql_lock_waits_total gauge')
|
|
try {
|
|
$lwQuery = "SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name LIKE '%Locks%' AND counter_name = 'Lock Waits/sec' AND instance_name = '_Total'"
|
|
$lw = Invoke-SqlQuery -Query $lwQuery
|
|
$lwVal = if ($lw) { $lw.cntr_value } else { 0 }
|
|
[void]$sb.AppendLine("mssql_lock_waits_total $lwVal")
|
|
} catch {
|
|
[void]$sb.AppendLine("mssql_lock_waits_total 0")
|
|
}
|
|
[void]$sb.AppendLine('')
|
|
|
|
# --- Deadlocks ---
|
|
[void]$sb.AppendLine('# HELP mssql_deadlocks_total Number of deadlocks detected')
|
|
[void]$sb.AppendLine('# TYPE mssql_deadlocks_total gauge')
|
|
try {
|
|
$dlQuery = "SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name LIKE '%Locks%' AND counter_name = 'Number of Deadlocks/sec' AND instance_name = '_Total'"
|
|
$dl = Invoke-SqlQuery -Query $dlQuery
|
|
$dlVal = if ($dl) { $dl.cntr_value } else { 0 }
|
|
[void]$sb.AppendLine("mssql_deadlocks_total $dlVal")
|
|
} catch {
|
|
[void]$sb.AppendLine("mssql_deadlocks_total 0")
|
|
}
|
|
[void]$sb.AppendLine('')
|
|
|
|
# --- Tempdb Sizes ---
|
|
[void]$sb.AppendLine('# HELP mssql_tempdb_data_size_bytes Tempdb data file size in bytes')
|
|
[void]$sb.AppendLine('# TYPE mssql_tempdb_data_size_bytes gauge')
|
|
[void]$sb.AppendLine('')
|
|
[void]$sb.AppendLine('# HELP mssql_tempdb_log_size_bytes Tempdb log file size in bytes')
|
|
[void]$sb.AppendLine('# TYPE mssql_tempdb_log_size_bytes gauge')
|
|
try {
|
|
$tdQuery = "SELECT type, SUM(size) * 8192 AS size_bytes FROM sys.master_files WHERE database_id = 2 GROUP BY type"
|
|
$td = Invoke-SqlQuery -Query $tdQuery
|
|
$dataSize = 0
|
|
$logSize = 0
|
|
if ($td) {
|
|
foreach ($row in $td) {
|
|
if ($row.type -eq 0) { $dataSize = $row.size_bytes }
|
|
if ($row.type -eq 1) { $logSize = $row.size_bytes }
|
|
}
|
|
}
|
|
[void]$sb.AppendLine("mssql_tempdb_data_size_bytes $dataSize")
|
|
[void]$sb.AppendLine("mssql_tempdb_log_size_bytes $logSize")
|
|
} catch {
|
|
[void]$sb.AppendLine("mssql_tempdb_data_size_bytes 0")
|
|
[void]$sb.AppendLine("mssql_tempdb_log_size_bytes 0")
|
|
}
|
|
[void]$sb.AppendLine('')
|
|
|
|
# --- Per-Database Size ---
|
|
[void]$sb.AppendLine('# HELP mssql_database_size_bytes Database size in bytes')
|
|
[void]$sb.AppendLine('# TYPE mssql_database_size_bytes gauge')
|
|
try {
|
|
$dbSizeQuery = "SELECT DB_NAME(database_id) AS db_name, SUM(size) * 8192 AS size_bytes FROM sys.master_files GROUP BY database_id"
|
|
$dbSizes = Invoke-SqlQuery -Query $dbSizeQuery
|
|
if ($dbSizes) {
|
|
foreach ($row in $dbSizes) {
|
|
$dbName = $row.db_name -replace '["]', ''
|
|
[void]$sb.AppendLine("mssql_database_size_bytes{database=`"$dbName`"} $($row.size_bytes)")
|
|
}
|
|
}
|
|
} catch { }
|
|
[void]$sb.AppendLine('')
|
|
|
|
# --- Per-Database Log Used % ---
|
|
[void]$sb.AppendLine('# HELP mssql_database_log_used_percent Log space used percentage per database')
|
|
[void]$sb.AppendLine('# TYPE mssql_database_log_used_percent gauge')
|
|
try {
|
|
$logQuery = @"
|
|
SELECT
|
|
DB_NAME(database_id) AS db_name,
|
|
CAST(used_log_space_in_percent AS DECIMAL(5,2)) AS log_pct
|
|
FROM sys.dm_db_log_space_usage
|
|
"@
|
|
# dm_db_log_space_usage is per-database, query from each
|
|
$dbListQuery = "SELECT name FROM sys.databases WHERE state_desc = 'ONLINE' AND database_id > 4"
|
|
$dbList = Invoke-SqlQuery -Query $dbListQuery
|
|
if ($dbList) {
|
|
foreach ($db in $dbList) {
|
|
$dbName = $db.name -replace '["]', ''
|
|
try {
|
|
$logPctQuery = "SELECT CAST(used_log_space_in_percent AS DECIMAL(5,2)) AS log_pct FROM sys.dm_db_log_space_usage"
|
|
$logPct = Invoke-SqlQuery -Query $logPctQuery -Database $dbName
|
|
if ($logPct) {
|
|
$pct = Format-MetricValue $logPct.log_pct
|
|
[void]$sb.AppendLine("mssql_database_log_used_percent{database=`"$dbName`"} $pct")
|
|
}
|
|
} catch { }
|
|
}
|
|
}
|
|
} catch { }
|
|
[void]$sb.AppendLine('')
|
|
|
|
# --- Backup Age ---
|
|
[void]$sb.AppendLine('# HELP mssql_database_backup_age_hours Hours since last full backup per database')
|
|
[void]$sb.AppendLine('# TYPE mssql_database_backup_age_hours gauge')
|
|
try {
|
|
$bkQuery = @"
|
|
SELECT
|
|
d.name AS db_name,
|
|
ISNULL(DATEDIFF(HOUR, MAX(b.backup_finish_date), GETDATE()), -1) AS age_hours
|
|
FROM sys.databases d
|
|
LEFT JOIN msdb.dbo.backupset b ON d.name = b.database_name AND b.type = 'D'
|
|
WHERE d.database_id > 4 AND d.state_desc = 'ONLINE'
|
|
GROUP BY d.name
|
|
"@
|
|
$bk = Invoke-SqlQuery -Query $bkQuery
|
|
if ($bk) {
|
|
foreach ($row in $bk) {
|
|
$dbName = $row.db_name -replace '["]', ''
|
|
[void]$sb.AppendLine("mssql_database_backup_age_hours{database=`"$dbName`"} $($row.age_hours)")
|
|
}
|
|
}
|
|
} catch { }
|
|
[void]$sb.AppendLine('')
|
|
|
|
# --- Connections Total ---
|
|
[void]$sb.AppendLine('# HELP mssql_connections_total Total user connections')
|
|
[void]$sb.AppendLine('# TYPE mssql_connections_total gauge')
|
|
try {
|
|
$connQuery = "SELECT COUNT(*) AS cnt FROM sys.dm_exec_sessions WHERE is_user_process = 1"
|
|
$conn = Invoke-SqlQuery -Query $connQuery
|
|
$connVal = if ($conn) { $conn.cnt } else { 0 }
|
|
[void]$sb.AppendLine("mssql_connections_total $connVal")
|
|
} catch {
|
|
[void]$sb.AppendLine("mssql_connections_total 0")
|
|
}
|
|
[void]$sb.AppendLine('')
|
|
|
|
# --- Connections Per Database ---
|
|
[void]$sb.AppendLine('# HELP mssql_connections_by_database User connections per database')
|
|
[void]$sb.AppendLine('# TYPE mssql_connections_by_database gauge')
|
|
try {
|
|
$connDbQuery = "SELECT DB_NAME(database_id) AS db_name, COUNT(*) AS cnt FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND database_id > 0 GROUP BY database_id"
|
|
$connDb = Invoke-SqlQuery -Query $connDbQuery
|
|
if ($connDb) {
|
|
foreach ($row in $connDb) {
|
|
$dbName = $row.db_name -replace '["]', ''
|
|
[void]$sb.AppendLine("mssql_connections_by_database{database=`"$dbName`"} $($row.cnt)")
|
|
}
|
|
}
|
|
} catch { }
|
|
[void]$sb.AppendLine('')
|
|
|
|
# --- Memory ---
|
|
[void]$sb.AppendLine('# HELP mssql_memory_buffer_pool_bytes Buffer pool memory in bytes')
|
|
[void]$sb.AppendLine('# TYPE mssql_memory_buffer_pool_bytes gauge')
|
|
try {
|
|
$bpQuery = "SELECT SUM(pages_kb) * 1024 AS size_bytes FROM sys.dm_os_memory_clerks WHERE type = 'MEMORYCLERK_SQLBUFFERPOOL'"
|
|
$bp = Invoke-SqlQuery -Query $bpQuery
|
|
$bpVal = if ($bp -and $bp.size_bytes) { $bp.size_bytes } else { 0 }
|
|
[void]$sb.AppendLine("mssql_memory_buffer_pool_bytes $bpVal")
|
|
} catch {
|
|
[void]$sb.AppendLine("mssql_memory_buffer_pool_bytes 0")
|
|
}
|
|
[void]$sb.AppendLine('')
|
|
|
|
[void]$sb.AppendLine('# HELP mssql_memory_plan_cache_bytes Plan cache memory in bytes')
|
|
[void]$sb.AppendLine('# TYPE mssql_memory_plan_cache_bytes gauge')
|
|
try {
|
|
$pcQuery = "SELECT SUM(pages_kb) * 1024 AS size_bytes FROM sys.dm_os_memory_clerks WHERE type IN ('CACHESTORE_SQLCP', 'CACHESTORE_OBJCP')"
|
|
$pc = Invoke-SqlQuery -Query $pcQuery
|
|
$pcVal = if ($pc -and $pc.size_bytes) { $pc.size_bytes } else { 0 }
|
|
[void]$sb.AppendLine("mssql_memory_plan_cache_bytes $pcVal")
|
|
} catch {
|
|
[void]$sb.AppendLine("mssql_memory_plan_cache_bytes 0")
|
|
}
|
|
[void]$sb.AppendLine('')
|
|
|
|
# --- CPU Usage ---
|
|
[void]$sb.AppendLine('# HELP mssql_cpu_usage_percent SQL Server CPU usage percentage')
|
|
[void]$sb.AppendLine('# TYPE mssql_cpu_usage_percent gauge')
|
|
try {
|
|
$cpuQuery = @"
|
|
SELECT TOP 1
|
|
SQLProcessUtilization AS cpu_pct
|
|
FROM (
|
|
SELECT
|
|
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLProcessUtilization,
|
|
TIMESTAMP
|
|
FROM (
|
|
SELECT TIMESTAMP, CONVERT(XML, record) AS record
|
|
FROM sys.dm_os_ring_buffers
|
|
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
|
|
AND record LIKE '%<SystemHealth>%'
|
|
) AS x
|
|
) AS y
|
|
ORDER BY TIMESTAMP DESC
|
|
"@
|
|
$cpu = Invoke-SqlQuery -Query $cpuQuery
|
|
$cpuVal = if ($cpu) { $cpu.cpu_pct } else { 0 }
|
|
[void]$sb.AppendLine("mssql_cpu_usage_percent $cpuVal")
|
|
} catch {
|
|
[void]$sb.AppendLine("mssql_cpu_usage_percent 0")
|
|
}
|
|
[void]$sb.AppendLine('')
|
|
|
|
# --- Wait Stats ---
|
|
[void]$sb.AppendLine('# HELP mssql_wait_stats Cumulative wait time in milliseconds by wait type')
|
|
[void]$sb.AppendLine('# TYPE mssql_wait_stats gauge')
|
|
try {
|
|
$waitQuery = @"
|
|
SELECT TOP 10
|
|
wait_type,
|
|
wait_time_ms
|
|
FROM sys.dm_os_wait_stats
|
|
WHERE wait_type NOT IN (
|
|
'WAITFOR', 'SLEEP_TASK', 'BROKER_RECEIVE_WAITFOR', 'CLR_AUTO_EVENT',
|
|
'CLR_MANUAL_EVENT', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SQLTRACE_BUFFER_FLUSH',
|
|
'XE_TIMER_EVENT', 'XE_DISPATCHER_WAIT', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
|
|
'BROKER_EVENTHANDLER', 'CHECKPOINT_QUEUE', 'BROKER_TO_FLUSH',
|
|
'BROKER_TASK_STOP', 'SP_SERVER_DIAGNOSTICS_SLEEP', 'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
|
|
'DIRTY_PAGE_POLL', 'DISPATCHER_QUEUE_SEMAPHORE', 'REQUEST_FOR_DEADLOCK_SEARCH',
|
|
'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE', 'BROKER_TRANSMITTER'
|
|
)
|
|
ORDER BY wait_time_ms DESC
|
|
"@
|
|
$waits = Invoke-SqlQuery -Query $waitQuery
|
|
if ($waits) {
|
|
foreach ($row in $waits) {
|
|
$wt = $row.wait_type -replace '["]', ''
|
|
[void]$sb.AppendLine("mssql_wait_stats{wait_type=`"$wt`"} $($row.wait_time_ms)")
|
|
}
|
|
}
|
|
} catch { }
|
|
[void]$sb.AppendLine('')
|
|
|
|
$sb.ToString()
|
|
}
|
|
|
|
# ============================================================================
|
|
# COLLECT ALL METRICS
|
|
# ============================================================================
|
|
|
|
function Get-AllMetrics {
|
|
$scriptStart = Get-Date
|
|
$sb = [System.Text.StringBuilder]::new()
|
|
|
|
# Exporter up - test SQL connectivity
|
|
[void]$sb.AppendLine('# HELP mssql_up SQL Server reachability (1=up, 0=down)')
|
|
[void]$sb.AppendLine('# TYPE mssql_up gauge')
|
|
try {
|
|
$test = Invoke-SqlQuery -Query "SELECT 1 AS test"
|
|
$upVal = if ($test) { 1 } else { 0 }
|
|
[void]$sb.AppendLine("mssql_up $upVal")
|
|
} catch {
|
|
[void]$sb.AppendLine("mssql_up 0")
|
|
# Still emit duration and timestamp
|
|
$scriptEnd = Get-Date
|
|
$duration = Format-MetricValue ($scriptEnd - $scriptStart).TotalSeconds
|
|
[void]$sb.AppendLine('')
|
|
[void]$sb.AppendLine('# HELP mssql_exporter_duration_seconds Time to generate all metrics')
|
|
[void]$sb.AppendLine('# TYPE mssql_exporter_duration_seconds gauge')
|
|
[void]$sb.AppendLine("mssql_exporter_duration_seconds $duration")
|
|
[void]$sb.AppendLine('')
|
|
[void]$sb.AppendLine('# HELP mssql_exporter_last_run_timestamp Unix timestamp of last run')
|
|
[void]$sb.AppendLine('# TYPE mssql_exporter_last_run_timestamp gauge')
|
|
[void]$sb.AppendLine("mssql_exporter_last_run_timestamp $(Get-UnixTimestamp)")
|
|
return $sb.ToString()
|
|
}
|
|
[void]$sb.AppendLine('')
|
|
|
|
# Exporter info
|
|
[void]$sb.AppendLine('# HELP mssql_exporter_info Exporter version information')
|
|
[void]$sb.AppendLine('# TYPE mssql_exporter_info gauge')
|
|
[void]$sb.AppendLine('mssql_exporter_info{version="1.0"} 1')
|
|
[void]$sb.AppendLine('')
|
|
|
|
# Collect SQL Server metrics
|
|
[void]$sb.Append((Get-MssqlMetrics))
|
|
|
|
# Exporter runtime
|
|
$scriptEnd = Get-Date
|
|
$duration = Format-MetricValue ($scriptEnd - $scriptStart).TotalSeconds
|
|
$timestamp = Get-UnixTimestamp
|
|
|
|
[void]$sb.AppendLine('# HELP mssql_exporter_duration_seconds Time to generate all metrics')
|
|
[void]$sb.AppendLine('# TYPE mssql_exporter_duration_seconds gauge')
|
|
[void]$sb.AppendLine("mssql_exporter_duration_seconds $duration")
|
|
[void]$sb.AppendLine('')
|
|
[void]$sb.AppendLine('# HELP mssql_exporter_last_run_timestamp Unix timestamp of last successful run')
|
|
[void]$sb.AppendLine('# TYPE mssql_exporter_last_run_timestamp gauge')
|
|
[void]$sb.AppendLine("mssql_exporter_last_run_timestamp $timestamp")
|
|
[void]$sb.AppendLine('')
|
|
|
|
$sb.ToString()
|
|
}
|
|
|
|
# ============================================================================
|
|
# HTTP SERVER MODE
|
|
# ============================================================================
|
|
|
|
function Start-HttpServer {
|
|
param([int]$ListenPort)
|
|
|
|
$prefix = "http://+:$ListenPort/"
|
|
$listener = [System.Net.HttpListener]::new()
|
|
$listener.Prefixes.Add($prefix)
|
|
|
|
try {
|
|
$listener.Start()
|
|
Write-Host "Starting SQL Server metrics exporter on port $ListenPort..." -ForegroundColor Green
|
|
Write-Host "Metrics available at http://localhost:$ListenPort/metrics"
|
|
|
|
while ($listener.IsListening) {
|
|
$context = $listener.GetContext()
|
|
$request = $context.Request
|
|
$response = $context.Response
|
|
|
|
if ($request.Url.AbsolutePath -eq '/metrics') {
|
|
$metrics = Get-AllMetrics
|
|
$buffer = [System.Text.Encoding]::UTF8.GetBytes($metrics)
|
|
$response.ContentType = 'text/plain; version=0.0.4; charset=utf-8'
|
|
}
|
|
else {
|
|
$html = @"
|
|
<!DOCTYPE html>
|
|
<html>
|
|
<head><title>SQL Server Metrics Exporter v1.0</title></head>
|
|
<body>
|
|
<h1>SQL Server Metrics Exporter v1.0</h1>
|
|
<p><a href="/metrics">Metrics</a></p>
|
|
<h2>Metrics</h2>
|
|
<ul>
|
|
<li>Buffer cache hit ratio and page life expectancy</li>
|
|
<li>Batch requests, compilations, recompilations</li>
|
|
<li>Lock waits and deadlocks</li>
|
|
<li>Tempdb usage</li>
|
|
<li>Per-database size, log space, backup age</li>
|
|
<li>Connections (total and per database)</li>
|
|
<li>Memory (buffer pool, plan cache)</li>
|
|
<li>CPU usage</li>
|
|
<li>Top wait stats</li>
|
|
</ul>
|
|
</body>
|
|
</html>
|
|
"@
|
|
$buffer = [System.Text.Encoding]::UTF8.GetBytes($html)
|
|
$response.ContentType = 'text/html; charset=utf-8'
|
|
}
|
|
|
|
$response.ContentLength64 = $buffer.Length
|
|
$response.OutputStream.Write($buffer, 0, $buffer.Length)
|
|
$response.OutputStream.Close()
|
|
}
|
|
}
|
|
catch {
|
|
Write-Error "HTTP server error: $_"
|
|
Write-Error "If access denied, run: netsh http add urlacl url=http://+:$ListenPort/ user=Everyone"
|
|
}
|
|
finally {
|
|
if ($listener.IsListening) {
|
|
$listener.Stop()
|
|
}
|
|
}
|
|
}
|
|
|
|
# ============================================================================
|
|
# MAIN EXECUTION
|
|
# ============================================================================
|
|
|
|
switch ($Mode) {
|
|
'http' {
|
|
Start-HttpServer -ListenPort $Port
|
|
}
|
|
'textfile' {
|
|
$OutputFile = Join-Path $TextfileDir 'mssql_metrics.prom'
|
|
|
|
$outputDir = Split-Path $OutputFile -Parent
|
|
if (-not (Test-Path $outputDir)) {
|
|
New-Item -Path $outputDir -ItemType Directory -Force | Out-Null
|
|
}
|
|
|
|
$tempFile = Join-Path $outputDir ".mssql_metrics.$PID.tmp"
|
|
|
|
try {
|
|
$metrics = Get-AllMetrics
|
|
$metrics | Out-File -FilePath $tempFile -Encoding utf8 -NoNewline
|
|
|
|
$lineCount = ($metrics -split "`n").Count
|
|
if ($lineCount -lt 10) {
|
|
Remove-Item -Path $tempFile -Force -ErrorAction SilentlyContinue
|
|
Write-Error "Metrics file too small ($lineCount lines), keeping previous"
|
|
exit 1
|
|
}
|
|
|
|
Move-Item -Path $tempFile -Destination $OutputFile -Force
|
|
Write-Host "Metrics written to $OutputFile ($lineCount lines)" -ForegroundColor Green
|
|
}
|
|
catch {
|
|
Remove-Item -Path $tempFile -Force -ErrorAction SilentlyContinue
|
|
Write-Error "Failed to generate metrics: $_"
|
|
exit 1
|
|
}
|
|
}
|
|
default {
|
|
Get-AllMetrics | Write-Output
|
|
}
|
|
}
|