/
mysql.sp
196 lines (175 loc) · 6.85 KB
/
mysql.sp
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
187
188
189
190
191
192
193
194
195
196
variable "mysql_db_system_age_max_days" {
type = number
description = "The maximum number of days DB systems are allowed to run."
default = 90
}
variable "mysql_db_system_age_warning_days" {
type = number
description = "The number of days DB systems can be running before sending a warning."
default = 30
}
variable "mysql_db_system_avg_connections" {
type = number
description = "The minimum number of average connections per day required for DB systems to be considered in-use."
default = 2
}
variable "mysql_db_system_avg_cpu_utilization_high" {
type = number
description = "The average CPU utilization required for DB systems to be considered frequently used. This value should be higher than mysql_db_system_avg_cpu_utilization_low."
default = 50
}
variable "mysql_db_system_avg_cpu_utilization_low" {
type = number
description = "The average CPU utilization required for DB systems to be considered infrequently used. This value should be lower than mysql_db_system_avg_cpu_utilization_high."
default = 25
}
locals {
mysql_common_tags = merge(local.oci_thrifty_common_tags, {
service = "OCI/MySQL"
})
}
benchmark "mysql" {
title = "MySQL Checks"
description = "Thrifty developers checks old MySQL DB systems which were created over 90 days ago."
documentation = file("./controls/docs/mysql.md")
children = [
control.mysql_db_system_age,
control.mysql_db_system_low_connection_count,
control.mysql_db_system_low_usage
]
tags = merge(local.mysql_common_tags, {
type = "Benchmark"
})
}
control "mysql_db_system_age" {
title = "Old MySQL DB systems should be reviewed"
description = "Old MySQL DB systems should be reviewed and deleted if not required."
severity = "low"
sql = <<-EOQ
select
a.id as resource,
case
when date_part('day', now()-a.time_created) > $1 then 'alarm'
when date_part('day', now()-a.time_created) > $2 then 'info'
else 'ok'
end as status,
a.title || ' has been in use for ' || date_part('day', now()-a.time_created) || ' days.' as reason,
coalesce(c.name, 'root') as compartment
${replace(local.tag_dimensions_qualifier_sql, "__QUALIFIER__", "a.")}
${replace(local.common_dimensions_qualifier_sql, "__QUALIFIER__", "a.")}
from
oci_mysql_db_system as a
left join oci_identity_compartment as c on c.id = a.compartment_id
where
a.lifecycle_state <> 'DELETED';
EOQ
param "mysql_db_system_age_max_days" {
description = "The maximum number of days DB systems are allowed to run."
default = var.mysql_db_system_age_max_days
}
param "mysql_db_system_age_warning_days" {
description = "The number of days DB systems can be running before sending a warning."
default = var.mysql_db_system_age_warning_days
}
tags = merge(local.mysql_common_tags, {
class = "deprecated"
})
}
control "mysql_db_system_low_connection_count" {
title = "MySQL DB systems with a low number connections per day should be reviewed"
description = "These DB systems have very little usage in last 30 days and should be shutdown when not in use."
severity = "high"
sql = <<-EOQ
with mysql_db_usage as (
select
id,
round(sum(maximum)/count(maximum)) as avg_max,
count(maximum) as days
from
oci_mysql_db_system_metric_connections_daily
where
metric_name = 'CurrentConnections'
and date_part('day', now() - timestamp) <= 30
group by id
)
select
m.id as resource,
case
when u.avg_max is null then 'error'
when u.avg_max = 0 then 'alarm'
when u.avg_max < $1 then 'info'
else 'ok'
end as status,
case
when u.avg_max is null then 'Monitoring metrics not available for ' || m.title || '.'
when u.avg_max = 0 then m.title || ' has not been connected to in the last ' || days || ' day(s).'
else m.title || ' is averaging ' || u.avg_max || ' max connections/day in the last ' || days || ' day(s).'
end as reason,
coalesce(c.name, 'root') as compartment
${replace(local.tag_dimensions_qualifier_sql, "__QUALIFIER__", "m.")}
${replace(local.common_dimensions_qualifier_sql, "__QUALIFIER__", "m.")}
from
oci_mysql_db_system as m
left join mysql_db_usage as u on u.id = m.id
left join oci_identity_compartment as c on c.id = m.compartment_id
where
m.lifecycle_state <> 'DELETED';
EOQ
param "mysql_db_system_avg_connections" {
description = "The minimum number of average connections per day required for DB systems to be considered in-use."
default = var.mysql_db_system_avg_connections
}
tags = merge(local.mysql_common_tags, {
class = "unused"
})
}
control "mysql_db_system_low_usage" {
title = "MySQL DB systems with low CPU utilization should be reviewed"
description = "These DB systems have very little usage in last 30 days and should be shutdown when not in use."
severity = "high"
sql = <<-EOQ
with mysql_db_usage as (
select
id,
round(cast(sum(maximum)/count(maximum) as numeric), 1) as avg_max,
count(maximum) days
from
oci_mysql_db_system_metric_memory_utilization_daily
where
date_part('day', now() - timestamp) <=30
group by
id
)
select
display_name as resource,
case
when avg_max is null then 'error'
when avg_max <= $1 then 'alarm'
when avg_max <= $2 then 'info'
else 'ok'
end as status,
case
when avg_max is null then 'Monitoring metrics not available for ' || i.title || '.'
else i.title || ' is averaging ' || avg_max || '% max utilization over the last ' || days || ' days.'
end as reason,
coalesce(c.name, 'root') as compartment
${replace(local.tag_dimensions_qualifier_sql, "__QUALIFIER__", "i.")}
${replace(local.common_dimensions_qualifier_sql, "__QUALIFIER__", "i.")}
from
oci_mysql_db_system i
left join mysql_db_usage as u on u.id = i.id
left join oci_identity_compartment as c on c.id = i.compartment_id
where i.lifecycle_state <> 'DELETED';
EOQ
param "mysql_db_system_avg_cpu_utilization_low" {
description = "The average CPU utilization required for DB systems to be considered infrequently used. This value should be lower than mysql_db_system_avg_cpu_utilization_high."
default = var.mysql_db_system_avg_cpu_utilization_low
}
param "mysql_db_system_avg_cpu_utilization_high" {
description = "The average CPU utilization required for DB systems to be considered frequently used. This value should be higher than mysql_db_system_avg_cpu_utilization_low."
default = var.mysql_db_system_avg_cpu_utilization_high
}
tags = merge(local.mysql_common_tags, {
class = "unused"
})
}