primary.13.cfg

# vim: syntax=sql
[auto_discovery_60]
minutes: 60
rman.lld: select distinct bs.database_name + '_' + bs.type "{#OBJ_TYPE}"
					from msdb.dbo.backupset bs join  sys.databases db on(bs.database_name=db.name)
					where bs.database_name <> 'tempdb'
					and ((bs.type='L' and db.recovery_model_desc != 'SIMPLE')
					or bs.type != 'L')
db.lld: SELECT name "{#PDB}" FROM master.dbo.sysdatabases

jobs.lld: select j.name as "{#JOBNAME}" from msdb.dbo.sysjobs_view j

parm.lld: SELECT @@servicename "{#INST_NAME}", c.name "{#PARAMETER}"
        FROM sys.configurations c
        where c.value <> 0

inst.lld: SELECT @@servicename "{#INST_NAME}"

p_ts.lld: IF OBJECT_ID('tempdb..##all_databases') IS NOT NULL
             DROP TABLE  ##all_databases;
  create table ##all_databases
  (

    dbname sysname
  , group_name varchar(128)
  );
  exec sp_msforeachdb '
  use [?]
  insert into ##all_databases
  SELECT distinct db_name(),
  coalesce(b.groupname,''logs'') AS ''File Group''
  FROM dbo.sysfiles a (NOLOCK)
  left outer JOIN sysfilegroups b (NOLOCK) ON a.groupid = b.groupid
  ORDER BY db_name(), coalesce(b.groupname,''logs'')
  ';
  select dbname "{#PDB}", group_name "{#TS_NAME}" from ##all_databases where dbname != 'tempdb'

t_ts.lld: IF OBJECT_ID('tempdb..##all_databases') IS NOT NULL
             DROP TABLE ##all_databases;
  create table ##all_databases
  (
    dbname sysname
  , group_name varchar(128)
  );
  exec sp_msforeachdb '
  use [?]
  insert into ##all_databases
  SELECT distinct db_name(),
  coalesce(b.groupname,''logs'') AS ''File Group''
  FROM dbo.sysfiles a (NOLOCK)
  left outer JOIN sysfilegroups b (NOLOCK) ON a.groupid = b.groupid
  ORDER BY db_name(), coalesce(b.groupname,''logs'')
  ';
  select dbname "{#PDB}", group_name "{#TS_NAME}" from ##all_databases where dbname = 'tempdb'

service.lld: SELECT d.name "{#PDB}", @@servicename "{#INST_NAME}", d.name "{#SERVICE_NAME}"
             FROM master.dbo.sysdatabases d
[checks_01m]
minutes: 1
inst.uptime: SELECT 'inst['+@@servicename+',uptime]', [ms_ticks]/1000 seconds_since_restart
             FROM sys.[dm_os_sys_info]
db.openmode: SELECT 'db['+name+',openstatus]', case
             when state_desc = 'ONLINE' and is_read_only = 'False' then 3
             when state_desc = 'ONLINE' and is_read_only = 'True' then 2
             else state
             end FROM sys.databases
cpu.i.util: SELECT TOP(1)
						'inst['+@@servicename+',CPU_utilization]' as _key,
						SQLProcessUtilization AS [SQLServer_CPU_Utilization]
				FROM (SELECT record.value('(./Record/@id)[1]','int')AS record_id,
				record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]','int')AS [SQLProcessUtilization]
				FROM (SELECT convert(xml, record) AS [record]
				FROM sys.dm_os_ring_buffers
				WHERE ring_buffer_type =N'RING_BUFFER_SCHEDULER_MONITOR'AND record LIKE'%%')AS x )AS y

cpu.db.util: WITH DB_CPU AS
  (SELECT DatabaseID,
      DB_Name(DatabaseID)AS [DatabaseName],
          SUM(total_worker_time)AS [CPU_Time(Ms)]
          FROM  sys.dm_exec_query_stats AS qs
          CROSS APPLY(SELECT  CONVERT(int, value)AS [DatabaseID]
                FROM  sys.dm_exec_plan_attributes(qs.plan_handle)
                      WHERE attribute =N'dbid')AS epa GROUP BY DatabaseID)
                      SELECT  'db['+DatabaseName+',CPU_utilization]' _key,
                        CAST([CPU_Time(Ms)] * 1.0 /SUM([CPU_Time(Ms)]) OVER()* 100.0 AS DECIMAL(5, 2))AS
                        value
                        FROM  DB_CPU
                        WHERE not DB_Name(DatabaseID) is null
scn: SELECT   'db[current_scn]', transaction_id
       FROM sys.dm_tran_current_transaction
     union all
     select 'db[delta_scn]', transaction_id
      FROM sys.dm_tran_current_transaction

[checks_05m]
minutes: 5
parm.val:  select 'parm['+@@servicename+','+c.name+',value]' kkey, c.value
          FROM sys.configurations c
          where c.value <> 0

service.cnt: SELECT 'service['+db_name(d.dbid)+','+@@servicename+','+db_name(d.dbid)+',sess]', count(*)
        FROM sys.sysprocesses d
        WHERE d.dbid > 0
        GROUP BY d.dbid

jobstatus: SELECT 'job['+j.NAME+','+ case when k = 1 then 'enabled'
                      when k = 2 then 'status'
                      when k = 3 then 'age'
                 else 'error'
                 end + ']',
                 case when k = 1 then j.enabled
                      when k = 2 then
       CASE
         WHEN jh.run_status IN ( 0, 1, 2, 3, 4 ) THEN jh.run_status
        --  0: Failed --  1: Succeeded --  2: Retry --  3: Canceled --  4: Running -- -1: Unknow error
         ELSE ( CASE
                  WHEN ja.run_requested_date IS NOT NULL
                       AND ja.stop_execution_date IS NULL THEN 4
                  ELSE -1
                END ) END
                when k = 3 then coalesce(DATEDIFF(s, ja.run_requested_date, getdate()),-1)
        end
	FROM   (msdb.dbo.sysjobactivity ja
					LEFT JOIN msdb.dbo.sysjobhistory jh
								 ON ja.job_history_id = jh.instance_id)
				 JOIN msdb.dbo.sysjobs_view j
					 ON ja.job_id = j.job_id
		cross join
		(select top 3 row_number() over (order by table_name) k from information_schema.tables) k
	WHERE  ja.session_id = (SELECT Max(session_id) FROM   msdb.dbo.sysjobactivity)
t_ts: IF OBJECT_ID('tempdb..##all_databases') IS NOT NULL
             DROP TABLE ##all_databases;
  create table ##all_databases
  (
    dbname sysname
  , filegroup varchar(128)
  , fsize_p bigint
  , used_p bigint
  , free_p bigint
  , maxsize_p bigint
  , volume_id sysname
  , vol_size_mb bigint
  , name  varchar(128)
  );
  use tempdb
  insert into ##all_databases
  select db_name() dbname
  , coalesce(g.groupname,'logs') AS group_name
  , df.size fsize
  , FILEPROPERTY(df.Name,'SpaceUsed') used
  , df.size - FILEPROPERTY(df.Name,'SpaceUsed') free
  , case when (convert(bigint,df.maxsize)*8) > (v.total_bytes/1024) then -1
    else case when df.growth = 0 then df.size
         else df.maxsize
         end
    end
  , v.volume_id, v.total_bytes/1024/1024 vol_size
  , df.name
  from dbo.sysfiles df (NOLOCK)
  CROSS APPLY sys.dm_os_volume_stats(db_id(), fileid) v
  left outer JOIN sysfilegroups g (NOLOCK) ON df.groupid = g.groupid;
  with adjusted_maxsizes as
  ( select filegroup
         , name
         , fsize_p
         , case maxsize_p
           when -1 then
             case row_number() over (partition by dbname,filegroup,volume_id order by maxsize_p,name)
             when 1 then
               (vol_size_mb*1024)/8
             else
               0
             end
           else
             case count( case when nullif(maxsize_p, -1) is null then 'J'
                         else null
                         end) over (partition by dbname,filegroup, volume_id)
             when 0 then
               maxsize_p
             else
               0
             end
           end maxsize_p
         , used_p
         , free_p
         , dbname
         , volume_id
      from ##all_databases
  )
  , sums as (select dbname, filegroup
       , sum(fsize_p) fsize_p
       , sum(maxsize_p) maxsize_p
       , sum(used_p) used_p
       , sum(free_p) free_p
    from adjusted_maxsizes
   group by dbname
       , filegroup
   )
   ,metrics as (
  select case when dbname = 'tempdb' then 't_ts'
              else 'p_ts'
         end + '['+dbname+','+ filegroup+','+
  case WHEN k = 1 THEN 'filesize'
       WHEN k = 2 THEN 'maxsize'
       WHEN k = 3 THEN 'usedbytes'
       WHEN k = 4 THEN 'pctfree'
       WHEN k = 5 THEN 'pctfreeMAX'
  end + ']' as kkey,
  case when k = 1 then (fsize_p*1024*8)
       when k = 2 then (maxsize_p*1024*8)
       when k = 3 then (used_p*1024*8)
       when k = 4 then round(((free_p)*100)/fsize_p,2)
       when k = 5 then round(((free_p + maxsize_p - fsize_p)*100) / maxsize_p,2)
  end as vvalue
  from sums
  cross join
  (select top 5 row_number() over (order by table_name) k from information_schema.tables) k
  )
  select kkey, sum(vvalue)
  from metrics
  where kkey like 't_ts%'
  group by kkey
[checks_10m]
minutes: 10
backup: with backups as(
      select
        distinct
        tbs.database_name
        , tt.*
      from msdb.dbo.backupset tbs
        outer apply (
          select
            top(1)
              bs.backup_size input
            , bs.compressed_backup_size output
            , bs.backup_start_date
            , datediff(s,backup_start_date,getdate()) age
            , datediff(s, bs.backup_start_date, bs.backup_finish_date) ela
            , bs.type
          from msdb.dbo.backupset bs
          where bs.database_name = tbs.database_name
            and bs.type = tbs.type
          order by bs.backup_start_date desc
        ) tt
      where tbs.type = tt.type
        and tbs.database_name <> 'tempdb'
      )
      select 'rman['+b.database_name+'_'+b.type+','+
               case when k = 1 then 'age'
                    when k = 2 then 'ela'
                    when k = 3 then 'input'
                    when k = 4 then 'output'
               end+']', case when k = 1 then b.age
                              when k = 2 then b.ela
                              when k = 3 then b.input
                              when k = 4 then b.output
                        end
            from backups b
      cross join
        (select top 4 row_number() over (order by table_name) k from information_schema.tables) k
hagroups: select 'db['+db.name+',slavestatus]',
            case when SERVERPROPERTY ('IsHadrEnabled') = 0 then 13
            else
                sum(case when ha.synchronization_health = 2 then 0
                         when ha.synchronization_health >= 0 then 1
                         else 42
                    end)
            end
        from sys.databases db
        left join sys.dm_hadr_database_replica_states ha
        on (ha.database_id = db.database_id)
        where db.name not in ('tempdb','master','model','msdb')
        group by db.name, ha.synchronization_health
[checks_60m]
minutes: 60
instname: SELECT 'zbxdb[connect,instance_name]', @@servername
p_ts: IF OBJECT_ID('tempdb..##all_databases') IS NOT NULL
             DROP TABLE ##all_databases;
  create table ##all_databases
  (
    dbname sysname
  , filegroup varchar(128)
  , fsize_p bigint
  , used_p bigint
  , free_p bigint
  , maxsize_p bigint
  , volume_id sysname
  , vol_size_mb bigint
  , name  varchar(128)
  );
  exec sp_msforeachdb '
  use [?]
  insert into ##all_databases
  select db_name() dbname
  , coalesce(g.groupname,''logs'') AS group_name
  , df.size fsize
  , FILEPROPERTY(df.Name,''SpaceUsed'') used
  , df.size - FILEPROPERTY(df.Name,''SpaceUsed'') free
  , case when (convert(bigint,df.maxsize)*8) > (v.total_bytes/1024) then -1
    else case when df.growth = 0 then df.size
         else df.maxsize
         end
    end
  , v.volume_id, v.total_bytes/1024/1024 vol_size
  , df.name
  from dbo.sysfiles df (NOLOCK)
  CROSS APPLY sys.dm_os_volume_stats(db_id(), fileid) v
  left outer JOIN sysfilegroups g (NOLOCK) ON df.groupid = g.groupid
  ';
  with adjusted_maxsizes as
  ( select filegroup
         , name
         , fsize_p
         , case maxsize_p
           when -1 then
             case row_number() over (partition by dbname,filegroup,volume_id order by maxsize_p,name)
             when 1 then
               (vol_size_mb*1024)/8
             else
               0
             end
           else
             case count( case when nullif(maxsize_p, -1) is null then 'J'
                         else null
                         end) over (partition by dbname,filegroup, volume_id)
             when 0 then
               maxsize_p
             else
               0
             end
           end maxsize_p
         , used_p
         , free_p
         , dbname
         , volume_id
      from ##all_databases
  )
  , sums as (select dbname, filegroup
       , sum(fsize_p) fsize_p
       , sum(maxsize_p) maxsize_p
       , sum(used_p) used_p
       , sum(free_p) free_p
    from adjusted_maxsizes
   group by dbname
       , filegroup
   )
   ,metrics as (
  select case when dbname = 'tempdb' then 't_ts'
              else 'p_ts'
         end + '['+dbname+','+ filegroup+','+
  case WHEN k = 1 THEN 'filesize'
       WHEN k = 2 THEN 'maxsize'
       WHEN k = 3 THEN 'usedbytes'
       WHEN k = 4 THEN 'pctfree'
       WHEN k = 5 THEN 'pctfreeMAX'
  end + ']' as kkey,
  case when k = 1 then (fsize_p*1024*8)
       when k = 2 then (maxsize_p*1024*8)
       when k = 3 then (used_p*1024*8)
       when k = 4 then round(((free_p)*100)/fsize_p,2)
       when k = 5 then round(((free_p + maxsize_p - fsize_p)*100) / maxsize_p,2)
  end as vvalue
  from sums
  cross join
  (select top 5 row_number() over (order by table_name) k from information_schema.tables) k
  )
  select kkey, sum(vvalue)
  from metrics
  where kkey not like 't_ts%'
  group by kkey
  union all
    select 'db[filesize]', sum(vvalue) from metrics where kkey like '%,filesize]'
      union all
        select 'db[usedbytes]', sum(vvalue) from metrics where kkey like '%,usedbytes]'

fullversion: SELECT 'full_version', ltrim(rtrim(replace(replace(replace(@@VERSION,
                 char(13),' '),char(10),''),char(9),' ')))

[checks_720m]
minutes: 720
version: select 'inst['+@@servicename+',version]', serverproperty('ProductVersion') from sys.servers i
          where i.name = @@servername