forked from andyfase/CURdashboard
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathanalyzeCUR.config
199 lines (190 loc) · 7.11 KB
/
analyzeCUR.config
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
197
198
[general]
namespace = "CUR"
[athena]
database_name = 'cur'
table_prefix = "autocur"
create_database = "create database if not exists `cur` comment \"AutoDBR Athena Database\""
create_table = """
create external table if not exists `**DBNAME**.**PREFIX**_**DATE**` (
**COLUMNS**
)
STORED AS PARQUET
LOCATION '**S3**' \
"""
[ri]
enableRIanalysis = false
enableRITotalUtilization = true # Set this to true to get a total RI percentage utilization value.
riPercentageThreshold = 5 # Ignore un-used RI's where percentage of under-use lower than this value
riTotalThreshold = 5 # Ignore un-used RI's where total number of RI's (per instance type) is below this.
cwNameTotal = "riTotalUtilization"
cwName = "riUnderUtilization"
cwDimension = "instance"
cwDimensionTotal = "total"
cwType = "Percent"
sql = """
SELECT distinct
COALESCE(
regexp_extract(itemdescription,'per(?:\\sOn Demand)*\\s(.*?)(?:\\s\\(Amazon VPC\\))*,*\\s([a-z]\\d\\.\\d*\\w+)\\s', 1),
regexp_extract(itemdescription,'^([a-z]\\d\\.\\d*\\w+)\\s(.*?)(?:\\s\\(Amazon VPC\\))*\\sSpot',2)
) AS platform,
COALESCE(
regexp_extract(itemdescription,'per(?:\\sOn Demand)*\\s(.*?)(?:\\s\\(Amazon VPC\\))*,*\\s([a-z]\\d\\.\\d*\\w+)\\s', 2),
regexp_extract(itemdescription,'^([a-z]\\d\\.\\d*\\w+)\\s(.*?)(?:\\s\\(Amazon VPC\\))*\\sSpot',1)
) AS instance,
substr(usagestartdate, 1, 13) AS date,
availabilityzone AS az,
count(*) AS hours
FROM dbr.autodbr_**DATE**
WHERE productname = 'Amazon Elastic Compute Cloud'
AND operation like '%RunInstances%'
AND usagetype like '%Usage%'
AND reservedinstance = 'Y'
AND split_part(usagetype, ':', 2) is not NULL
AND length(availabilityzone) > 1
AND length(usagestartdate) > 1
AND try_cast(usagestartdate as timestamp) IS NOT NULL
AND try_cast(usagestartdate as timestamp) > now() - interval '72' hour
AND try_cast(usagestartdate as timestamp) < now()
GROUP BY
COALESCE(
regexp_extract(itemdescription,'per(?:\\sOn Demand)*\\s(.*?)(?:\\s\\(Amazon VPC\\))*,*\\s([a-z]\\d\\.\\d*\\w+)\\s', 1),
regexp_extract(itemdescription,'^([a-z]\\d\\.\\d*\\w+)\\s(.*?)(?:\\s\\(Amazon VPC\\))*\\sSpot',2)
),
COALESCE(
regexp_extract(itemdescription,'per(?:\\sOn Demand)*\\s(.*?)(?:\\s\\(Amazon VPC\\))*,*\\s([a-z]\\d\\.\\d*\\w+)\\s', 2),
regexp_extract(itemdescription,'^([a-z]\\d\\.\\d*\\w+)\\s(.*?)(?:\\s\\(Amazon VPC\\))*\\sSpot',1)
),
substr(usagestartdate, 1, 13),
availabilityzone
"""
[ri.ignore] ## Ignore un-used RI's in this map/hash
"t2.micro" = 1
"m1.small" = 1 # This has to be ignored as RI usage in DBR file for this instance type is not accurate
[metricConfig]
[metricConfig.substring]
"hourly" = "13"
"daily" = "10"
[[metrics]]
## Count of Instance purchase types (RI, Spot, onDemand)"
enabled = false
hourly = true
daily = true
type = "dimension-per-row"
cwName = "InstancePurchaseType"
cwDimension = "type"
cwType = "Count"
sql = """
SELECT
coalesce(nullif("pricing/term", ''), substr(split_part("lineitem/usagetype", ':', 1), strpos("lineitem/usagetype", '-')+1)) as dimension,
substr("lineitem/usagestartdate",1,**INTERVAL**) as date,
sum("lineitem/usageamount") as value
FROM **DBNAME**.autocur_**DATE**
WHERE length("lineitem/usagestartdate") > 1
AND date(from_iso8601_timestamp("lineitem/usagestartdate")) >= date(now()) - interval '2' day
AND from_iso8601_timestamp("lineitem/usagestartdate") < now()
AND "product/productname" = 'Amazon Elastic Compute Cloud'
AND "lineitem/operation" like 'RunInstances%'
AND "lineitem/resourceid" like 'i-%'
AND "lineitem/usagetype" like '%Usage%'
GROUP BY
coalesce(nullif("pricing/term", ''), substr(split_part("lineitem/usagetype", ':', 1), strpos("lineitem/usagetype", '-')+1)),
substr("lineitem/usagestartdate",1,**INTERVAL**)
ORDER BY substr("lineitem/usagestartdate",1,**INTERVAL**) DESC
"""
[[metrics]]
## Summary of Overall Cost per hour
enabled = true
hourly = true
daily = true
type = "dimension-per-row"
cwName = "TotalCost"
cwDimension = "cost"
cwType = "None"
sql = """
SELECT
'total' as dimension,
substr("lineitem/usagestartdate",1,**INTERVAL**) as date,
sum("lineitem/blendedcost") as value
FROM **DBNAME**.autocur_**DATE**
WHERE length("lineitem/usagestartdate") > 1
AND date(from_iso8601_timestamp("lineitem/usagestartdate")) >= date(now()) - interval '2' day
AND from_iso8601_timestamp("lineitem/usagestartdate") < now()
GROUP BY
substr("lineitem/usagestartdate",1,**INTERVAL**)
ORDER BY substr("lineitem/usagestartdate",1,**INTERVAL**) DESC\
"""
[[metrics]]
## Summary of Cost per service per hour
enabled = true
hourly = true
daily = true
type = "dimension-per-row"
cwName = "ServiceCost"
cwDimension = "servicecost"
cwType = "None"
sql = """
SELECT
"product/productname" as dimension,
substr("lineitem/usagestartdate",1,**INTERVAL**) as date,
sum("lineitem/blendedcost") as value
FROM **DBNAME**.autocur_**DATE**
WHERE length("lineitem/usagestartdate") > 1
AND date(from_iso8601_timestamp("lineitem/usagestartdate")) >= date(now()) - interval '2' day
AND from_iso8601_timestamp("lineitem/usagestartdate") < now()
GROUP BY
"product/productname",
substr("lineitem/usagestartdate",1,**INTERVAL**)
HAVING sum("lineitem/blendedcost") > 0.1
ORDER BY substr("lineitem/usagestartdate",1,**INTERVAL**) DESC \
"""
[[metrics]]
## Cost per account
enabled = true
hourly = true
daily = true
type = "dimension-per-row"
cwName = "AccountCost"
cwDimension = "accountcost"
cwType = "None"
sql = """
SELECT
"lineitem/usageaccountid" as dimension,
substr("lineitem/usagestartdate",1,**INTERVAL**) as date,
sum("lineitem/blendedcost") as value
FROM **DBNAME**.autocur_**DATE**
WHERE length("lineitem/usagestartdate") > 1
AND date(from_iso8601_timestamp("lineitem/usagestartdate")) >= date(now()) - interval '2' day
AND from_iso8601_timestamp("lineitem/usagestartdate") < now()
GROUP BY
"lineitem/usageaccountid",
substr("lineitem/usagestartdate",1,**INTERVAL**)
ORDER BY substr("lineitem/usagestartdate",1,**INTERVAL**) DESC \
"""
[[metrics]]
## Count of Instance Types
enabled = true
hourly = true
daily = false
type = "dimension-per-row"
cwName = "InstanceType"
cwDimension = "instancecount"
cwType = "Count"
sql = """
SELECT
split_part("lineitem/usagetype", ':', 2) as dimension,
substr("lineitem/usagestartdate",1,**INTERVAL**) as date,
sum("lineitem/usageamount") as value
FROM **DBNAME**.autocur_**DATE**
WHERE length("lineitem/usagestartdate") > 1
AND date(from_iso8601_timestamp("lineitem/usagestartdate")) >= date(now()) - interval '2' day
AND from_iso8601_timestamp("lineitem/usagestartdate") < now()
AND "product/productname" = 'Amazon Elastic Compute Cloud'
AND "lineitem/operation" like 'RunInstances%'
AND "lineitem/resourceid" like 'i-%'
AND "lineitem/usagetype" like '%Usage%'
GROUP BY
split_part("lineitem/usagetype", ':', 2),
substr("lineitem/usagestartdate",1,**INTERVAL**)
HAVING sum("lineitem/usageamount") > 0.1
ORDER BY substr("lineitem/usagestartdate",1,**INTERVAL**) DESC \
"""