forked from ikzelf/zbxdb
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathprimary.11.cfg
186 lines (177 loc) · 6.4 KB
/
primary.11.cfg
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
# 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
parm.lld: SELECT @@servicename "{#INST_NAME}", c.name "{#PARAMETER}"
FROM sys.configurations c
where c.value <> 0
inst.lld: select name "{#INST_NAME}" from Sys.Servers
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}", i.name "{#INST_NAME}", d.name "{#SERVICE_NAME}"
FROM master.dbo.sysdatabases d, Sys.Servers i
[checks_01m]
minutes: 1
inst.uptime: SELECT 'inst['+name+',uptime]', [ms_ticks]/1000 seconds_since_restart
FROM sys.[dm_os_sys_info], sys.servers
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
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['+i.name+','+c.name+',value]' kkey, c.value
FROM sys.configurations c
, Sys.Servers i
where c.value <> 0
service.cnt: SELECT 'service['+db_name(d.dbid)+','+i.name+','+db_name(d.dbid)+',sess]', count(*)
FROM sys.sysprocesses d, sys.servers i
WHERE dbid > 0
GROUP BY dbid, i.name
[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
[checks_60m]
minutes: 60
p_ts: IF OBJECT_ID('tempdb..##all_databases') IS NOT NULL
DROP TABLE ##all_databases;
create table ##all_databases
(
dbname sysname
, group_name varchar(128)
, file_p bigint
, used_p bigint
, free_p bigint
, max_mb bigint
, file_name varchar(128)
);
exec sp_msforeachdb '
use [?]
insert into ##all_databases
SELECT db_name(),
coalesce(b.groupname,''logs'') AS ''File Group'',
a.Size [size_p],
FILEPROPERTY(a.Name,''SpaceUsed'') AS [used_p],
(a.Size-FILEPROPERTY(a.Name,''SpaceUsed'')) AS [free_p],
case when a.maxsize = 0 then a.size
when a.maxsize = -1 then case when b.groupname is null then 2097152
else 8*2097152
end
else a.maxsize/128
end as [max_mb],
Name
FROM dbo.sysfiles a (NOLOCK)
left outer JOIN sysfilegroups b (NOLOCK) ON a.groupid = b.groupid
ORDER BY b.groupname
';
with metrics as (
select ''+case when dbname = 'tempdb' then 't_ts'
else 'p_ts'
end + '['+dbname+','+ group_name+','+
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 (file_p*1024*8)
when k = 2 then (max_mb*1024*1024)
when k = 3 then (used_p*1024*8)
when k = 4 then round(((file_p - used_p)*100)/file_p,2)
when k = 5 then round((((file_p - used_p) + ((max_mb*128) - file_p))*100) / (max_mb*128),2)
end as vvalue
from ##all_databases
cross join
(select top 5 row_number() over (order by table_name) k from information_schema.tables) k
)
select kkey, sum(vvalue)
from metrics
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]'
instname: SELECT 'zbxdb[connect,instance_name]', @@servername
fullversion: SELECT 'full_version', ltrim(rtrim(replace(replace(replace(@@VERSION,
char(13),' '),char(10),''),char(9),' ')))
[checks_720m]
minutes: 720
version: select 'inst['+i.name+',version]', serverproperty('ProductVersion') from sys.servers i