Installs sql exporter and configurations

install-sql-exporter.ps1

$version = "0.9.1"
$AgentURL = "https://github.com/burningalchemist/sql_exporter/releases/download/${version}/sql_exporter-${version}.windows-amd64.zip"
$AbsoluteCurrPath = $(Get-Location).Path
$AbsolutePathZip = "${AbsoluteCurrPath}\tmp\sql-exporter\sql_exporter.zip"
$ServiceName = "sql_exporter"
$TempDirectoryToCreate =  "$AbsoluteCurrPath\tmp\sql-exporter"
$SQL_EXPORTER_PATH = "C:\sql-exporter"
$ExporterDirectoryToCreate = "C:\sql-exporter"
# Default is localhost, can be changed if the exporter needs to be remote   
# 'sqlserver://${DbUser}:${DbPassword}@${DbAddress}:${DbPort}'
$DbUser = "prom"
$DbPassword = "[CHANGEME]"
$DbAddress = "127.0.0.1"
$DbPort = "1433"



# create temp directories
if (-not (Test-Path -LiteralPath $TempDirectoryToCreate)) {
    try {
        New-Item -Path $TempDirectoryToCreate -ItemType Directory -ErrorAction Stop | Out-Null #-Force
    } catch {
        Write-Error -Message "Unable to create directory '$TempDirectoryToCreate'. Error was: $_" -ErrorAction Stop
    }
    "Successfully created directory '$TempDirectoryToCreate'."
} else {
    "Directory already exists"
}

# create exporter directory
if (-not (Test-Path -LiteralPath $ExporterDirectoryToCreate)) {
    try {
        New-Item -Path $ExporterDirectoryToCreate -ItemType Directory -ErrorAction Stop | Out-Null #-Force
    } catch {
        Write-Error -Message "Unable to create directory '$ExporterDirectoryToCreate'. Error was: $_" -ErrorAction Stop
    }
    "Successfully created directory '$ExporterDirectoryToCreate'."
} else {
    "Directory already exists"
}

# download specify msi to temp directory
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
Invoke-WebRequest $AgentURL -OutFile $AbsolutePathZip

# extract the files to a folder 
Expand-Archive -LiteralPath $AbsolutePathZip -DestinationPath $TempDirectoryToCreate -Force
#Get-ChildItem $AbsolutePathZip -Filter *.zip | ForEach-Object {Expand-Archive $_.FullName "$AbsolutePathZip\$($_.Basename)" -Force}

# copy extracted files to exporter directory
Copy-Item -Path "$TempDirectoryToCreate\sql_exporter-*windows-amd64\sql_exporter.exe" -Destination "$SQL_EXPORTER_PATH" -Recurse

# write out config yml
Write-Host "# SQL Exporter config"
$sqlexporteryml=@"
# Global defaults.
global:
  # Subtracted from Prometheus' scrape_timeout to give us some headroom and prevent Prometheus from timing out first.
  scrape_timeout_offset: 500ms
  # Minimum interval between collector runs: by default (0s) collectors are executed on every scrape.
  min_interval: 0s
  # Maximum number of open connections to any one target. Metric queries will run concurrently on multiple connections,
  # as will concurrent scrapes.
  max_connections: 3
  # Maximum number of idle connections to any one target. Unless you use very long collection intervals, this should
  # always be the same as max_connections.
  max_idle_connections: 3
  # Maximum number of maximum amount of time a connection may be reused. Expired connections may be closed lazily before reuse.
  # If 0, connections are not closed due to a connection's age.
  max_connection_lifetime: 5m

# The target to monitor and the collectors to execute on it.
target:
  # Data source name always has a URI schema that matches the driver name. In some cases (e.g. MySQL)
  # the schema gets dropped or replaced to match the driver expected DSN format.
  data_source_name: 'sqlserver://${DbUser}:${DbPassword}@${DbAddress}:${DbPort}'

  # Collectors (referenced by name) to execute on the target.
  collectors: [mssql_standard]

# Collector files specifies a list of globs. One collector definition is read from each matching file.
collector_files: 
  - "*.collector.yml"
"@
#Write-Host $sqlexporteryml
$sqlexporteryml| Out-File C:\sql-exporter\sql_exporter.yml -Force

Write-Host "# SQL Exporter mssql collector config"
$mssqlcollectoryml=@"
# A collector defining standard metrics for Microsoft SQL Server.
#
# It is required that the SQL Server user has the following permissions:
#
#   GRANT VIEW ANY DEFINITION TO
#   GRANT VIEW SERVER STATE TO
#
collector_name: mssql_standard

# Similar to global.min_interval, but applies to the queries defined by this collector only.
#min_interval: 0s

metrics:
  - metric_name: mssql_local_time_seconds
    type: gauge
    help: 'Local time in seconds since epoch (Unix time).'
    values: [unix_time]
    query: |
      SELECT DATEDIFF(second, '19700101', GETUTCDATE()) AS unix_time

  - metric_name: mssql_connections
    type: gauge
    help: 'Number of active connections.'
    key_labels:
      - db
    values: [count]
    query: |
      SELECT DB_NAME(sp.dbid) AS db, COUNT(sp.spid) AS count
      FROM sys.sysprocesses sp
      GROUP BY DB_NAME(sp.dbid)

  #
  # Collected from sys.dm_os_performance_counters
  #
  - metric_name: mssql_deadlocks
    type: counter
    help: 'Number of lock requests that resulted in a deadlock.'
    values: [cntr_value]
    query: |
      SELECT cntr_value
      FROM sys.dm_os_performance_counters WITH (NOLOCK)
      WHERE counter_name = 'Number of Deadlocks/sec' AND instance_name = '_Total'

  - metric_name: mssql_user_errors
    type: counter
    help: 'Number of user errors.'
    values: [cntr_value]
    query: |
      SELECT cntr_value
      FROM sys.dm_os_performance_counters WITH (NOLOCK)
      WHERE counter_name = 'Errors/sec' AND instance_name = 'User Errors'

  - metric_name: mssql_kill_connection_errors
    type: counter
    help: 'Number of severe errors that caused SQL Server to kill the connection.'
    values: [cntr_value]
    query: |
      SELECT cntr_value
      FROM sys.dm_os_performance_counters WITH (NOLOCK)
      WHERE counter_name = 'Errors/sec' AND instance_name = 'Kill Connection Errors'

  - metric_name: mssql_page_life_expectancy_seconds
    type: gauge
    help: 'The minimum number of seconds a page will stay in the buffer pool on this node without references.'
    values: [cntr_value]
    query: |
      SELECT top(1) cntr_value
      FROM sys.dm_os_performance_counters WITH (NOLOCK)
      WHERE counter_name = 'Page life expectancy'

  - metric_name: mssql_batch_requests
    type: counter
    help: 'Number of command batches received.'
    values: [cntr_value]
    query: |
      SELECT cntr_value
      FROM sys.dm_os_performance_counters WITH (NOLOCK)
      WHERE counter_name = 'Batch Requests/sec'

  - metric_name: mssql_log_growths
    type: counter
    help: 'Number of times the transaction log has been expanded, per database.'
    key_labels:
      - db
    values: [cntr_value]
    query: |
      SELECT rtrim(instance_name) AS db, cntr_value
      FROM sys.dm_os_performance_counters WITH (NOLOCK)
      WHERE counter_name = 'Log Growths' AND instance_name <> '_Total'

  - metric_name: mssql_buffer_cache_hit_ratio
    type: gauge
    help: 'Ratio of requests that hit the buffer cache'
    values: [cntr_value]
    query: |
      SELECT cntr_value
      FROM sys.dm_os_performance_counters
      WHERE [counter_name] = 'Buffer cache hit ratio'

  - metric_name: mssql_checkpoint_pages_sec
    type: gauge
    help: 'Checkpoint Pages Per Second'
    values: [cntr_value]
    query: |
      SELECT cntr_value
      FROM sys.dm_os_performance_counters
      WHERE [counter_name] = 'Checkpoint pages/sec'

  #
  # Collected from sys.dm_io_virtual_file_stats
  #
  - metric_name: mssql_io_stall_seconds
    type: counter
    help: 'Stall time in seconds per database and I/O operation.'
    key_labels:
      - db
    value_label: operation
    values:
      - read
      - write
    query_ref: mssql_io_stall
  - metric_name: mssql_io_stall_total_seconds
    type: counter
    help: 'Total stall time in seconds per database.'
    key_labels:
      - db
    values:
      - io_stall
    query_ref: mssql_io_stall

  #
  # Collected from sys.dm_os_process_memory
  #
  - metric_name: mssql_resident_memory_bytes
    type: gauge
    help: 'SQL Server resident memory size (AKA working set).'
    values: [resident_memory_bytes]
    query_ref: mssql_process_memory

  - metric_name: mssql_virtual_memory_bytes
    type: gauge
    help: 'SQL Server committed virtual memory size.'
    values: [virtual_memory_bytes]
    query_ref: mssql_process_memory

  - metric_name: mssql_memory_utilization_percentage
    type: gauge
    help: 'The percentage of committed memory that is in the working set.'
    values: [memory_utilization_percentage]
    query_ref: mssql_process_memory

  - metric_name: mssql_page_fault_count
    type: counter
    help: 'The number of page faults that were incurred by the SQL Server process.'
    values: [page_fault_count]
    query_ref: mssql_process_memory

  #
  # Collected from sys.dm_os_sys_memory
  #
  - metric_name: mssql_os_memory
    type: gauge
    help: 'OS physical memory, used and available.'
    value_label: 'state'
    values: [used, available]
    query: |
      SELECT
        (total_physical_memory_kb - available_physical_memory_kb) * 1024 AS used,
        available_physical_memory_kb * 1024 AS available
      FROM sys.dm_os_sys_memory

  - metric_name: mssql_os_page_file
    type: gauge
    help: 'OS page file, used and available.'
    value_label: 'state'
    values: [used, available]
    query: |
      SELECT
        (total_page_file_kb - available_page_file_kb) * 1024 AS used,
        available_page_file_kb * 1024 AS available
      FROM sys.dm_os_sys_memory

queries:
  # Populates `mssql_io_stall` and `mssql_io_stall_total`
  - query_name: mssql_io_stall
    query: |
      SELECT
        cast(DB_Name(a.database_id) as varchar) AS [db],
        sum(io_stall_read_ms) / 1000.0 AS [read],
        sum(io_stall_write_ms) / 1000.0 AS [write],
        sum(io_stall) / 1000.0 AS io_stall
      FROM
        sys.dm_io_virtual_file_stats(null, null) a
      INNER JOIN sys.master_files b ON a.database_id = b.database_id AND a.file_id = b.file_id
      GROUP BY a.database_id

  # Populates `mssql_resident_memory_bytes`, `mssql_virtual_memory_bytes`, `mssql_memory_utilization_percentage` and
  # `mssql_page_fault_count`.
  - query_name: mssql_process_memory
    query: |
      SELECT
        physical_memory_in_use_kb * 1024 AS resident_memory_bytes,
        virtual_address_space_committed_kb * 1024 AS virtual_memory_bytes,
        memory_utilization_percentage,
        page_fault_count
      FROM sys.dm_os_process_memory
"@
#Write-Host $mssqlcollectoryml
$mssqlcollectoryml| Out-File C:\sql-exporter\mssql.collector.yml -Force

# open port for firewall
New-NetFirewallRule -Name "sql_exporter" -DisplayName "SQL_EXPORTER TCP PORT 9399" -Direction inbound -Profile Any -Action Allow -LocalPort 9399 -Protocol TCP
$rule = "SQL_EXPORTER TCP PORT 9399"
Get-NetFirewallRule -DisplayName $rule | ft -Property Name, DisplayName, @{Name='Protocol';Expression={($PSItem | Get-NetFirewallPortFilter).Protocol}}, @{Name='LocalPort';Expression={($PSItem | Get-NetFirewallPortFilter).LocalPort}}, @{Name='RemotePort';Expression={($PSItem | Get-NetFirewallPortFilter).RemotePort}}, @{Name='RemoteAddress';Expression={($PSItem | Get-NetFirewallAddressFilter).RemoteAddress}}, Enabled, Profile, Direction, Action

# enable the service
New-Service -name "$ServiceName" `
-BinaryPathName "${SQL_EXPORTER_PATH}\sql_exporter.exe -config.file ${SQL_EXPORTER_PATH}\sql_exporter.yml" `
-StartupType Automatic `
-DisplayName "$ServiceName"

# Check Status of Service
$Service = Get-Service -Name "$ServiceName"
if($Service.Status -eq "running"){
  Write-Host "$ServiceName is running"  
} else {
    Write-Host "$ServiceName status is: $Service.Status"
}

# remove temp setup file
if(Test-Path $TempDirectoryToCreate -PathType Container){
    Remove-Item -Recurse -Force $TempDirectoryToCreate
} else {
    Write-Host "TMP path not available"  
}