#!/bin/bash

SQLCMD="/opt/mssql-tools18/bin/sqlcmd"
USER="prtg_sql"
PASS="StrongPassword!"
SERVER="localhost"

RESULT=$($SQLCMD -S "$SERVER" -U "$USER" -P "$PASS" -C \
-h -1 -W -s "|" -Q "
SET NOCOUNT ON;

SELECT
  /* Total DB Size (MB) – per current DB */
  (SELECT SUM(size)*8/1024 FROM sys.database_files),

  /* Transactions/sec (_Total) */
  (SELECT cntr_value
   FROM sys.dm_os_performance_counters
   WHERE counter_name='Transactions/sec'
     AND instance_name='_Total'),

  /* Batch Requests/sec */
  (SELECT SUM(cntr_value)
   FROM sys.dm_os_performance_counters
   WHERE counter_name='Batch Requests/sec'),

  /* Buffer Cache Hit Ratio (%) */
  (
    SELECT
      CAST(
        100.0 *
        MAX(CASE WHEN counter_name='Buffer cache hit ratio' THEN cntr_value END) /
        NULLIF(MAX(CASE WHEN counter_name='Buffer cache hit ratio base' THEN cntr_value END),0)
      AS DECIMAL(5,2))
    FROM sys.dm_os_performance_counters
  ),

  /* Page Life Expectancy – instance rỗng */
  (SELECT cntr_value
   FROM sys.dm_os_performance_counters
   WHERE counter_name='Page life expectancy'
     AND instance_name=''),

  /* Lock Waits/sec */
  (SELECT COALESCE(SUM(cntr_value),0)
   FROM sys.dm_os_performance_counters
   WHERE counter_name='Lock Waits/sec'),

  /* Deadlocks/sec */
  (SELECT COALESCE(SUM(cntr_value),0)
   FROM sys.dm_os_performance_counters
   WHERE counter_name='Number of Deadlocks/sec');
")

IFS='|' read DBSIZE TRANS BATCH BCHR PLE LOCK DEAD <<< "$RESULT"

echo "<prtg>"

echo "<result>
  <channel>Total DB Size</channel>
  <value>${DBSIZE:-0}</value>
  <unit>MegaByte</unit>
</result>"

echo "<result>
  <channel>Transactions</channel>
  <value>${TRANS:-0}</value>
  <unit>Count</unit>
  <mode>Rate</mode>
</result>"

echo "<result>
  <channel>Batch Requests</channel>
  <value>${BATCH:-0}</value>
  <unit>Count</unit>
  <mode>Rate</mode>
</result>"

echo "<result>
  <channel>Buffer Cache Hit</channel>
  <value>${BCHR:-0}</value>
  <unit>Percent</unit>
  <float>1</float> 
</result>"

echo "<result>
  <channel>Page Life Expectancy</channel>
  <value>${PLE:-0}</value>
  <unit>Seconds</unit>
</result>"

echo "<result>
  <channel>Lock Waits</channel>
  <value>${LOCK:-0}</value>
  <unit>Count</unit>
  <mode>Rate</mode>
</result>"

echo "<result>
  <channel>Deadlocks</channel>
  <value>${DEAD:-0}</value>
  <unit>Count</unit>
  <mode>Rate</mode>
</result>"

echo "<text>SQL Performance OK</text>"
echo "</prtg>"