primary.2.cfg

# vim: syntax=sql
[auto_discovery_60]
minutes: 60
inst.lld: select cast(node_id as string)||':'||  address"{#INST_NAME}"
          from crdb_internal.gossip_nodes

db.lld: SELECT datname "{#PDB}" FROM pg_database

parm.lld: select 'cluster' "{#INST_NAME}", s.variable "{#PARAMETER}"
          from crdb_internal.cluster_settings s
          where s.type in ('b','f','i')     

p_ts.lld: select cast(nr.node_id as string)||':'|| nr.value "{#PDB}", s.store_id "{#TS_NAME}"
          from crdb_internal.node_runtime_info nr
          join crdb_internal.kv_store_status s on (nr.node_id = s.node_id)
          where nr.component = 'DB' and nr.field = 'Host'

service.lld: SELECT d.datname "{#PDB}", '' "{#INST_NAME}", '' "{#SERVICE_NAME}"
             FROM pg_database d
             where d.datistemplate = 'false'

[checks_01m]
minutes: 1
inst.uptime: select 'inst['|| cast(node_id as string)||':'||address||',uptime]', 
             extract(epoch from current_timestamp) - started_at::numeric::integer
             from crdb_internal.gossip_nodes
db.openmode: select 'db['||datname||',openstatus]', 3 from pg_database
scn: select 'db[current_scn]',
             sum(value)
     from crdb_internal.node_metrics
     where name in ('sql.txn.rollback.count', 'sql.txn.abort.count', 'sql.txn.commit.count')
     union all
     select 'db[delta_scn]',
             sum(value)
     from crdb_internal.node_metrics
     where name in ('sql.txn.rollback.count', 'sql.txn.abort.count', 'sql.txn.commit.count')


[checks_05m]
minutes: 5
parm.val: select 'parm[cluster,'|| s.variable ||','||
           case when s.variable like '%size'  then 'size'
                when s.variable like '%bytes' then 'size'
           else 'value'
           end ||']',
                  case when s.value = 'false' then '0'
                       when s.value = 'true'  then '1'
                       else s.value::numeric
                  end as setting
                  from crdb_internal.cluster_settings s
                  where s.type in ('b','f','i')     
service.cnt: SELECT 'service['||datname||',,,sess]', count(*)
             FROM pg_stat_activity
             group by datname
[checks_60m]
minutes: 60
p_ts: select 'p_ts['||cast(s.node_id as string)||':'||s.value||','|| cast(s.store_id as string)||
         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
        ,case when k = 1 then cast(s.used as string)
              WHEN k = 2 THEN cast(s.maxsize as string)
              WHEN k = 3 THEN cast(s.used as string)
              WHEN k = 4 THEN cast( (s.available/s.maxsize) * 100 as string)
              WHEN k = 5 THEN cast( (s.available/s.maxsize) * 100 as string)
         end
      from
      (select nr.node_id, nr.value, si.store_id
              , si.used::int, si.available + si.used as maxsize, si.available::int
              from crdb_internal.node_runtime_info nr
              join crdb_internal.kv_store_status si on (nr.node_id = si.node_id)
              where nr.component = 'DB' and nr.field = 'Host') s
      cross join
      (SELECT row_number() over () as k FROM pg_tables limit  5) k