def gen_instance_size_recommendations(workbook, header_format, val_format):
def transform(h, v):
if h == "cpu_usage":
try:
return "%.3f%%" % (float(v) * 100)
except ValueError:
pass
return v
with utils.csv_folder(IN_INSTANCE_SIZE_RECOMMENDATIONS_DIR) as source:
worksheet = workbook.add_worksheet("Instance size recommendations")
worksheet.set_column("A:E", 25)
worksheet.set_column("F:F", 20)
worksheet.set_column("G:G", 18)
worksheet.set_column("H:H", 35)
worksheet.merge_range("A1:F1", "Instance", header_format)
worksheet.merge_range("G1:G2", "Recommended", header_format)
worksheet.merge_range("H1:H2", "Reason", header_format)
refs = {
"account": [0, "Account"],
"id": [1, "ID"],
"name": [2, "Name"],
"size": [3, "Type"],
"lifecycle": [4, "Lifecycle"],
"cpu_usage": [5, "CPU Utilization (Avg.)"],
"recommendation": [6, "Recommendation"],
"reason": [7, "Reason"]
}
for i in refs.values():
worksheet.write(1, i[0], i[1], header_format)
for i, line in zip(itertools.count(2), source):
for h, v in line.items():
worksheet.write(i, refs[h][0], transform(h, v), val_format)
def gen_reservation_usage_summary(workbook, header_format, val_format):
with utils.csv_folder(IN_RESERVATION_USAGE_DIR) as records:
worksheet = workbook.add_worksheet("Reservation usage summary")
worksheet.set_column("A:J", 18)
worksheet.merge_range("A1:D1", "Reservation", header_format)
worksheet.merge_range("E1:F1", "Count", header_format)
worksheet.merge_range("G1:A1", "Cost per instance", header_format)
worksheet.merge_range("J1:J2", "Monthly losses", header_format)
cur_format = workbook.add_format()
cur_format.set_align("center")
cur_format.set_align("vcenter")
cur_format.set_border()
cur_format.set_num_format(NUMFORMAT_CURRENCY)
refs = {
"instance_type": [0, "Instance type", str, val_format],
"availability_zone": [1, "Availability zone", str, val_format],
"tenancy": [2, "Tenancy", str, val_format],
"product": [3, "Product", str, val_format],
"count_used": [4, "Running", int, val_format],
"count": [5, "Reserved", int, val_format],
"cost_upfront": [6, "Upfront", float, cur_format],
"cost_hourly": [7, "Hourly", float, cur_format],
"effective_cost": [8, "Effective", float, cur_format],
"monthly_losses": [9, "Monthly losses", float, cur_format],
}
for v in refs.values():
worksheet.write(1, v[0], v[1], header_format)
for i, line in zip(itertools.count(2), records):
for h, v in line.items():
worksheet.write(i, refs[h][0], refs[h][2](v), refs[h][3])
effective_cost = float(line["cost_upfront"]) / 720 + float(
line["cost_hourly"])
worksheet.write_formula(
i,
refs["effective_cost"][0],
"=G{}/720+H{}".format(*[i + 1] * 2),
refs["effective_cost"][3],
effective_cost,
)
worksheet.write(
i,
refs["monthly_losses"][0],
"=(F{}-E{})*I{}*720".format(*[i + 1] * 3),
refs["monthly_losses"][3],
(float(line["count"]) - float(line["count_used"])) *
effective_cost * 720,
)
def reservation_usage_summary():
def effective_cost(sheet, row, column, field):
return Formula('={}/720+{}'.format(
sheet.field_address('cost_upfront', row, 2),
sheet.field_address('cost_hourly', row, 2),
))
def monthly_losses(sheet, row, column, field):
return Formula('({reserved}-{used})*{effective}*720'.format(
reserved=sheet.field_address('count_reserved', row, 2),
used=sheet.field_address('count_used', row, 2),
effective=sheet.field_address('effective_cost', row, 2),
))
fields = (
FieldGroup('Reservation', (
Field('instance_type', 'instance_type', str, 'Instance type',
None),
Field('availability_zone', 'availability_zone', str,
'Availability zone', None),
Field('tenancy', 'tenancy', str, 'Tenancy', None),
Field('product', 'product', str, 'Product', None),
)),
FieldGroup('Count', (
Field('count_reserved', 'count', int, 'Reserved', None),
Field('count_used', 'count_used', int, 'Used', None),
)),
FieldGroup('Cost per instance', (
Field('cost_upfront', 'cost_upfront', float, 'Upfront',
NUMFORMAT_CURRENCY),
Field('cost_hourly', 'cost_hourly', float, 'Hourly',
NUMFORMAT_CURRENCY),
Field('effective_cost', 'effective_cost', effective_cost,
'Effective', NUMFORMAT_CURRENCY),
)),
Field('monthly_losses', 'monthly_losses', monthly_losses,
'Monthly losses', NUMFORMAT_CURRENCY),
)
with utils.csv_folder(IN_RESERVATION_USAGE_DIR) as records:
sheet = Sheet(
source=records,
fields=fields,
sheet_id=3,
)
sheet.properties['title'] = 'Reservation usage summary'
return sheet.to_dict()
def instance_size_recommendations():
fields = (
FieldGroup('Instance', (
Field('account', 'account', str, 'Account', None),
Field('id', 'id', str, 'ID', None),
Field('name', 'name', str, 'Name', None),
Field('size', 'size', str, 'Type', None),
Field('lifecycle', 'lifecycle', str, 'Lifecycle', None),
)),
Field('recommendation', 'recommendation', str, 'Recommended', None),
)
with utils.csv_folder(IN_INSTANCE_SIZE_RECOMMENDATIONS_DIR) as source:
sheet = Sheet(
source=source,
fields=fields,
sheet_id=4,
)
sheet.properties['title'] = 'Instance size recommendations'
return sheet.to_dict()
import csv
from datetime import datetime
from collections import defaultdict
import dateutil.relativedelta
import utils
USAGECOST_DIR='in/usagecost'
METADATA_DIR='out/instance-metadata'
OUT_PATH_INSTANCES = 'out/last-month/ec2_instances.csv'
OUT_PATH_BANDWIDTH = 'out/last-month/ec2_bandwidth.csv'
BEGIN_LAST_MONTH = (datetime.now() + dateutil.relativedelta.relativedelta(months=-1)).replace(day=1, hour=0, minute=0, second=0, microsecond=0)
END_LAST_MONTH = (BEGIN_LAST_MONTH + dateutil.relativedelta.relativedelta(months=1, days=-1)).replace(hour=23, minute=59, second=59, microsecond=999999)
with utils.csv_folder(METADATA_DIR) as records:
instance_name = defaultdict(str)
for record in records:
instance_name[record['instance_id']] = record['name']
with utils.csv_folder(USAGECOST_DIR) as records:
resource_id_missing = False
instance_usage_records = defaultdict(float)
bandwidth_usage_records = defaultdict(float)
for record in records:
if 'lineItem/ResourceId' not in record:
if resource_id_missing == False:
print("Error: the billing report does not export the ResourceId")
resource_id_missing = True
continue
if record['lineItem/ProductCode'] == 'AmazonEC2':
def gen_reserved_summary(workbook, header_format, val_format):
with utils.csv_folder(IN_INSTANCE_RESERVATION_USAGE_DIR) as records:
worksheet = workbook.add_worksheet("Reserved instance summary")
worksheet.freeze_panes(2, 0)
worksheet.set_column("A:O", 15)
worksheet.merge_range("A1:D1", "Reservation", header_format)
worksheet.merge_range("E1:F1", "Count", header_format)
worksheet.merge_range("G1:I1", "Cost per instance", header_format)
worksheet.merge_range("J1:L1", "Total monthly cost", header_format)
worksheet.merge_range("M1:N1", "Savings over on demand", header_format)
green_format = workbook.add_format()
green_format.set_color(COLOR_GREEN_FG)
green_format.set_bg_color(COLOR_GREEN_BG)
cur_format = workbook.add_format()
cur_format.set_align("center")
cur_format.set_align("vcenter")
cur_format.set_border()
cur_format.set_num_format(NUMFORMAT_CURRENCY)
per_format = workbook.add_format()
per_format.set_align("center")
per_format.set_align("vcenter")
per_format.set_border()
per_format.set_num_format(NUMFORMAT_PERCENT)
refs = {
"instance_type": [0, "Instance type", str, val_format],
"availability_zone": [1, "Availability zone", str, val_format],
"tenancy": [2, "Tenancy", str, val_format],
"product": [3, "Product", str, val_format],
"count": [4, "Running", int, val_format],
"count_reserved": [5, "Reserved", int, val_format],
"cost_ondemand": [6, "On demand", float, cur_format],
"cost_reserved_worst": [7, "Worst reserved", float, cur_format],
"cost_reserved_best": [8, "Best reserved", float, cur_format],
"cost_monthly_ondemand": [9, "On demand", float, cur_format],
"cost_monthly_reserved_worst":
[10, "Worst reserved", float, cur_format],
"cost_monthly_reserved_best":
[11, "Best reserved", float, cur_format],
"savings_reserved_worst":
[12, "Worst reserved", float, per_format],
"savings_reserved_best": [13, "Best reserved", float, per_format],
}
for v in refs.values():
worksheet.write(1, v[0], v[1], header_format)
for i, line in zip(itertools.count(2), records):
for h, v in line.items():
worksheet.write(i, refs[h][0], refs[h][2](v), refs[h][3])
for h in ("cost_monthly_ondemand", "cost_monthly_reserved_worst",
"cost_monthly_reserved_best"):
res = float(line["count"]) * \
float(line["cost_" + h[13:]]) * 720
worksheet.write_formula(
i,
refs[h][0],
"=E{}*{}{}*720".format(i + 1,
chr(ord('A') + refs[h][0] - 3),
i + 1),
refs[h][3],
res,
)
for h in ("savings_reserved_worst", "savings_reserved_best"):
res = 1 - float(line[h.replace("savings", "cost")]) / float(
line["cost_ondemand"])
worksheet.write_formula(
i,
refs[h][0],
"=1-{}{}/G{}".format(chr(ord('A') + refs[h][0] - 5), i + 1,
i + 1),
refs[h][3],
res,
)
worksheet.conditional_format(
"F{}".format(i + 1), {
"type": "cell",
"criteria": "equal to",
"value": "E{}".format(i + 1),
"format": green_format,
})
import json
import utils
USAGECOST_DIR = 'in/usagecost'
OUT_PATH = 'out/instance-history.csv'
USAGE = 'BoxUsage'
def updated(base, addend):
base = base.copy()
base.update(addend)
return base
with utils.csv_folder(USAGECOST_DIR) as records:
box_usage_records = (record for record in records
if USAGE in record['lineItem/UsageType'])
simplified_lineitems = ((record['product/instanceType'],
round(float(record['lineItem/UsageAmount']))
if record['lineItem/UsageAmount'] else 0,
record['lineItem/UsageStartDate'])
for record in box_usage_records)
histogram = {
date: updated(
collections.defaultdict(int), {
instancetype: sum(lineitem[1]
for lineitem in instancetype_lineitems)
for instancetype, instancetype_lineitems in itertools.groupby(
sorted(
def reserved_summary():
def cost_monthly(sheet, row, column, field):
base_hourly = sheet.field_index('cost_ondemand')
base_monthly = sheet.field_index('cost_monthly_ondemand')
return Formula('={}*{}*720'.format(
sheet.field_address('count', row, 2),
sheet.address(base_hourly + (column - base_monthly), row),
))
def savings_monthly(sheet, row, column, field):
base_ondemand = sheet.field_index('cost_ondemand')
base_reserved = sheet.field_index('cost_reserved_worst')
base_savings = sheet.field_index('savings_reserved_worst')
return Formula('=1-{}/{}'.format(
sheet.address(base_reserved + (column - base_savings), row),
sheet.field_address('cost_ondemand', row, 2),
))
fields = (FieldGroup('Reservation', (
Field('instance_type', 'instance_type', str, 'Instance type', None),
Field('availability_zone', 'availability_zone', str,
'Availability zone', None),
Field('tenancy', 'tenancy', str, 'Tenancy', None),
Field('product', 'product', str, 'Product', None),
)), Field('count', 'count', int, 'Count', '0'),
Field('count_reserved', 'count_reserved', int,
'Count (reserved)', '0'),
FieldGroup('Hourly cost per instance', (
Field('cost_ondemand', 'cost_ondemand', float, 'On demand',
NUMFORMAT_CURRENCY),
Field('cost_reserved_worst', 'cost_reserved_worst', float,
'Worst reserved', NUMFORMAT_CURRENCY),
Field('cost_reserved_best', 'cost_reserved_best', float,
'Best reserved', NUMFORMAT_CURRENCY),
)),
FieldGroup('Monthly cost total', (
Field('cost_monthly_ondemand', 'cost_monthly_ondemand',
cost_monthly, 'On demand', NUMFORMAT_CURRENCY),
Field('cost_monthly_reserved_worst',
'cost_monthly_reserved_worst', cost_monthly,
'Worst reserved', NUMFORMAT_CURRENCY),
Field('cost_monthly_reserved_best',
'cost_monthly_reserved_best', cost_monthly,
'Best reserved', NUMFORMAT_CURRENCY),
)),
FieldGroup('Savings over on demand', (
Field('savings_reserved_worst', 'savings_reserved_worst',
savings_monthly, 'Worst reserved', NUMFORMAT_PERCENT),
Field('savings_reserved_best', 'savings_reserved_best',
savings_monthly, 'Best reserved', NUMFORMAT_PERCENT),
)))
conditional_format = (ConditionalFormat(
'CUSTOM_FORMULA',
'=(INDIRECT(ADDRESS(ROW(), COLUMN() - 1)) = INDIRECT(ADDRESS(ROW(), COLUMN())))',
{
'backgroundColor': COLOR_GREEN_BG,
'textFormat': {
'foregroundColor': COLOR_GREEN_FG,
},
}), )
with utils.csv_folder(IN_INSTANCE_RESERVATION_USAGE_DIR) as records:
sheet = Sheet(source=records,
fields=fields,
sheet_id=1,
fields_conditional_formats=tuple(
ColumnConditionalFormat(column, conditional_format)
for column in field_flatten(FieldRoot(fields))
if column.name == 'count_reserved'))
sheet.properties['title'] = 'Reserved instance summary'
return sheet.to_dict()
