TypesDB "/usr/share/collectd/types.db.mssql"

<LoadPlugin dbi>
    Interval 15
</LoadPlugin>

<Plugin dbi>
    <Query "perfstats">
	Statement "select replace(rtrim(counter_name),' ','_') as counter_name, replace(rtrim(instance_name),' ','_') as instance_name,cntr_value from sys.dm_os_performance_counters where (counter_name in ('SQL Compilations/sec','SQL Re-Compilations/sec','User Connections','Batch Requests/sec','Logouts/sec','Logins/sec','Processes blocked','Latch Waits/sec','Full Scans/sec','Index Searches/sec','Page Splits/sec','Page Lookups/sec','Page Reads/sec','Page Writes/sec','Readahead Pages/sec','Lazy Writes/sec','Checkpoint Pages/sec','Database Cache Memory (KB)','Log Pool Memory (KB)','Optimizer Memory (KB)','SQL Cache Memory (KB)','Connection Memory (KB)','Lock Memory (KB)', 'Memory broker clerk size','Page life expectancy')) or (instance_name in ('_Total','Column store object pool') and counter_name in ('Transactions/sec','Write Transactions/sec','Log Flushes/sec','Log Flush Wait Time','Lock Timeouts/sec','Number of Deadlocks/sec','Lock Waits/sec','Latch Waits/sec','Memory broker clerk size','Log Bytes Flushed/sec','Bytes Sent to Replica/sec','Log Send Queue','Bytes Sent to Transport/sec','Sends to Replica/sec','Bytes Sent to Transport/sec','Sends to Transport/sec','Bytes Received from Replica/sec','Receives from Replica/sec','Flow Control Time (ms/sec)','Flow Control/sec','Resent Messages/sec','Redone Bytes/sec') or (object_name = 'SQLServer:Database Replica' and counter_name in ('Log Bytes Received/sec','Log Apply Pending Queue','Redone Bytes/sec','Recovery Queue','Log Apply Ready Queue') and instance_name = '_Total')) or (object_name = 'SQLServer:Database Replica' and counter_name in ('Transaction Delay'))"
	<Result>
            Type "sql_server_perf_stats"
            InstancesFrom "counter_name" "instance_name"
            ValuesFrom "cntr_value"
        </Result>
    </Query>

    <Query "waitstats">
        Statement "WITH WaitCategoryStats ( wait_category, wait_type, wait_time_ms, waiting_tasks_count, max_wait_time_ms) AS ( SELECT CASE WHEN wait_type LIKE 'LCK%' THEN 'LOCKS' WHEN wait_type LIKE 'PAGEIO%' THEN 'PAGE I/O LATCH' WHEN wait_type LIKE 'PAGELATCH%' THEN 'PAGE LATCH (non-I/O)' WHEN wait_type LIKE 'LATCH%' THEN 'LATCH (non-buffer)' WHEN wait_type LIKE 'LATCH%' THEN 'LATCH (non-buffer)' ELSE wait_type END AS wait_category, wait_type, wait_time_ms, waiting_tasks_count, max_wait_time_ms FROM sys.dm_os_wait_stats WHERE wait_type NOT IN ('LAZYWRITER_SLEEP', 'CLR_AUTO_EVENT' , 'CLR_MANUAL_EVENT', 'REQUEST_FOR_DEADLOCK_SEARCH', 'BACKUPTHREAD', 'CHECKPOINT_QUEUE' , 'EXECSYNC', 'FFT_RECOVERY', 'SNI_CRITICAL_SECTION', 'SOS_PHYS_PAGE_CACHE' , 'CXROWSET_SYNC', 'DAC_INIT', 'DIRTY_PAGE_POLL', 'PWAIT_ALL_COMPONENTS_INITIALIZED' , 'MSQL_XP', 'WAIT_FOR','DBMIRRORING_CMD','DBMIRROR_DBM_EVENT','DBMIRROR_EVENTS_QUEUE','DBMIRROR_WORKER_QUEUE', 'XE_TIMER_EVENT','XE_DISPATCHER_WAIT','WAITFOR_TASKSHUTDOWN','WAIT_FOR_RESULTS' ,'SQLTRACE_INCREMENTAL_FLUSH_SLEEP','WAITFOR' ,'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP','QDS_PERSIST_TASK_MAIN_LOOP_SLEEP','HADR_FILESTREAM_IOMGR_IOCOMPLETION','LOGMGR_QUEUE','FSAGENT' ) AND wait_type NOT LIKE 'PREEMPTIVE%' AND wait_type NOT LIKE 'SQLTRACE%' AND wait_type NOT LIKE 'SLEEP%' AND wait_type NOT LIKE 'FT_%' AND wait_type NOT LIKE 'XE%' AND wait_type NOT LIKE 'BROKER%' AND wait_type NOT LIKE 'DISPATCHER%' AND wait_type NOT LIKE 'PWAIT%' AND wait_type NOT LIKE 'SP_SERVER%') select  wait_category, sum(wait_time_ms) as wait_time_ms, sum(waiting_tasks_count) as waiting_tasks_count, max(max_wait_time_ms) as max_wait_time_ms from WaitCategoryStats where wait_time_ms >100 group by wait_category"
        <Result>
            Type "sql_server_wait_stats"
            InstancesFrom "wait_category"
            ValuesFrom "wait_time_ms" "waiting_tasks_count" "max_wait_time_ms"
        </Result>
    </Query>

    <Database "master">
        Host "MSSQL"
        Driver "freetds"
        DriverOption "host" "fake_host.eu-central-1.rds.amazonaws.com"
        DriverOption "username" "fake_user"
        DriverOption "password" "fake_pass"
        Query "perfstats"
	#Query "waitstats"
    </Database>
</Plugin>

dbi plugin: cdbi_read_database_query (master, perfstats): dbi_result_next_row failed: dbi_conn_error failed with status -6