def cloud_cost_with_discount(month, accounts, cloud_name):
account = "%" + accounts + "%"
account_like = accounts + "-%"
sb.Truncate(0)
sb.Seek(0)
if accounts == "822924222578":
for j in range(len(cloud_name)):
sb.Append(
"OR resource_tags_user_fc_cloud_name NOT LIKE '%s%s%s' " %
(percent, cloud_name[j], percent))
cloud_names = sb
else:
for j in range(len(cloud_name)):
sb.Append("OR resource_tags_user_fc_cloud_name LIKE '%s%s%s' " %
(percent, cloud_name[j], percent))
cloud_names = sb
return ("SELECT\
date_format(bill_billing_period_start_date,'%s') as billing_from_date,\
SUM(blended_cost) AS blended_cost, SUM(unblended_cost) AS unblended_cost, SUM(amortised_cost) AS amortized_cost,\
line_item_usage_account_id as account\
FROM aws_costs_view\
WHERE line_item_usage_account_id LIKE '%s'\
AND bill_billing_period_start_date >= from_iso8601_timestamp('%sT00:00:00.000Z')\
AND bill_billing_period_end_date <= from_iso8601_timestamp('%sT00:00:00.000Z') \
AND (resource_tags_user_fc_cloud_name LIKE '%s' %s)\
GROUP BY 1,5; " % (year_format, account, (gtd.getDate(month)[0]),
(gtd.getDate(month)[1]), account_like, cloud_names))
def organisation_cost_queries(month, with_discount):
if with_discount:
organsiation_cost_response = clientce.get_cost_and_usage(
TimePeriod={
'Start': ("%s" % (gtd.getDate(month)[0])),
'End': ("%s" % (gtd.getDate(month)[1]))
},
Granularity='MONTHLY',
Metrics=["BlendedCost", "UnblendedCost", "AmortizedCost"],
Filter={
"Not": {
"Dimensions": {
"Key":
"RECORD_TYPE",
"Values": [
"Refund", "Enterprise Discount Program Discount",
"Credit"
],
}
}
})
csv.write_cost_to_csv(prop.reference_filename_with_discount,
organsiation_cost_response)
else:
organsiation_cost_response = clientce.get_cost_and_usage(
TimePeriod={
'Start': ("%s" % (gtd.getDate(month)[0])),
'End': ("%s" % (gtd.getDate(month)[1]))
},
Granularity='MONTHLY',
Metrics=["BlendedCost", "UnblendedCost", "AmortizedCost"],
)
csv.write_cost_to_csv(prop.reference_filename_without_discount,
organsiation_cost_response)
def ath_query_wo_discounts(month):
query_wo_discounts = (
"SELECT date_format(bill_billing_period_start_date,'%s') as billing_from_date, SUM(blended_cost) as blended_cost, SUM(unblended_cost) as unblended_cost, SUM(amortised_cost) as amortized_cost \
FROM aws_costs_view WHERE \
bill_billing_period_start_date >= from_iso8601_timestamp('%sT00:00:00.000Z') \
AND \
bill_billing_period_end_date <= from_iso8601_timestamp('%sT00:00:00.000Z') Group by 1;"
% (year_format, (gtd.getDate(month)[0]), (gtd.getDate(month)[1])))
return query_wo_discounts
def account_costs_with_discounts(month):
return (
"SELECT line_item_usage_account_id as account, SUM(blended_cost) as blended_cost, \
SUM(unblended_cost) as unblended_cost, SUM(amortised_cost) as amortized_cost \
FROM aws_costs_view WHERE \
bill_billing_period_start_date >= from_iso8601_timestamp('%sT00:00:00.000Z') \
AND \
bill_billing_period_end_date <= from_iso8601_timestamp('%sT00:00:00.000Z') \
GROUP BY line_item_usage_account_id ORDER BY 1;" %
((gtd.getDate(month)[0]), (gtd.getDate(month)[1])))
def environment_cost_live(month, accounts):
account = "%" + accounts + "%"
return (
"SELECT date_format(bill_billing_period_start_date,'%s') as billing_from_date,\
SUM(blended_cost) AS blended_cost, SUM(unblended_cost) AS unblended_cost, SUM(amortised_cost) AS amortized_cost, \
line_item_usage_account_id as account \
FROM aws_costs_view \
WHERE line_item_usage_account_id LIKE '%s' \
AND resource_tags_user_fc_env_type IN ('production', 'sandbox', 'staging', 'production-sandbox-staging-test','test','ap-southeast-2','eu-west-1','AdrianSpecial') \
AND bill_billing_period_start_date >= from_iso8601_timestamp('%sT00:00:00.000Z') \
AND bill_billing_period_end_date <= from_iso8601_timestamp('%sT00:00:00.000Z') \
GROUP BY 1,5;" % (year_format, account, (gtd.getDate(month)[0]),
(gtd.getDate(month)[1])))
def environment_cost_non_live(month, accounts):
account = "%" + accounts + "%"
return (
"SELECT date_format(bill_billing_period_start_date,'%s') as billing_from_date,\
SUM(blended_cost) AS blended_cost, SUM(unblended_cost) AS unblended_cost, SUM(amortised_cost) AS amortized_cost, \
line_item_usage_account_id as account \
FROM aws_costs_view \
WHERE line_item_usage_account_id LIKE '%s' \
AND resource_tags_user_fc_env_type IN ('ci','common','dedicated','dev', 'dev3', 'dev4', 'engineering', 'internal', 'operations', 'prod', 'shared','testtest', 'test2') \
AND bill_billing_period_start_date >= from_iso8601_timestamp('%sT00:00:00.000Z') \
AND bill_billing_period_end_date <= from_iso8601_timestamp('%sT00:00:00.000Z') \
GROUP BY 1,5; " % (year_format, account, (gtd.getDate(month)[0]),
(gtd.getDate(month)[1])))
def service_cost_query(month, accounts):
account = "%" + accounts + "%"
return (
"SELECT date_format(bill_billing_period_start_date,'%s') as billing_from_date, \
ROUND(SUM(blended_cost),0) AS blended_cost, ROUND(SUM(unblended_cost),0) AS unblended_cost, ROUND(SUM(amortised_cost),0) AS amortized_cost, \
line_item_usage_account_id as account, \
line_item_product_code as service \
FROM aws_costs_view \
WHERE line_item_usage_account_id LIKE '%s' \
AND bill_billing_period_start_date >= from_iso8601_timestamp('%sT00:00:00.000Z') \
AND bill_billing_period_end_date <= from_iso8601_timestamp('%sT00:00:00.000Z') \
AND line_item_line_item_type IN ('Fee', 'Usage', 'DiscountedUsage') \
GROUP BY 1,5,6;" % (year_format, account, (gtd.getDate(month)[0]),
(gtd.getDate(month)[1])))
def get_challenger_costs(sts,
rolename,
account_id,
month,
file,
with_discounts=False):
costs_by_accounts = {}
with tempfile.NamedTemporaryFile() as temporary_file:
athena_query_results_file_name = temporary_file.name
athena_query = acq.account_costs_without_discounts(month)
if with_discounts:
athena_query = acq.account_costs_with_discounts(month)
aqe.athena_to_s3(sts, acq.params, athena_query,
athena_query_results_file_name, rolename, account_id)
with open(file, 'a+') as csv_file:
writer = csv.writer(csv_file, delimiter=',')
with open(athena_query_results_file_name) as f:
reader = csv.reader(f,
delimiter=',',
skipinitialspace=True,
doublequote=True)
next(reader, None)
for row in reader:
# extract account id, ex: Shared Dublin 2 (504325195528)
account_id = re.findall('[0-9]{5,}', row[0])[0]
blended_cost = util.roundoff(row[1])
unblended_cost = util.roundoff(row[2])
amortized_cost = util.roundoff(row[3])
costs_by_accounts[account_id] = {
'blended': blended_cost,
'unblended': unblended_cost,
'amortized': amortized_cost
}
writer.writerow([
gtd.getDate(month)[0], account_id, blended_cost,
unblended_cost, amortized_cost
])
return costs_by_accounts
def __compare_and_save_to_disk(reference_costs, challenger_costs, month,
discrepancy_output_file):
all_match = True
billing_month = gtd.getDate(month)[0]
with open(discrepancy_output_file, 'a+') as csv_file:
writer = csv.writer(csv_file,
delimiter=',',
quoting=csv.QUOTE_NONE,
escapechar=',',
doublequote=True)
# fail if the number of entries is different
if not len(challenger_costs) == len(reference_costs):
writer.writerow([
billing_month,
'number of rows in reference and challenger does not match'
])
# exit immediately without comparing any value
return False
for account_id in reference_costs:
reference = reference_costs[account_id]
challenger = challenger_costs[account_id]
if (reference['blended'] == challenger['blended']
and reference['unblended'] == challenger['unblended']
and reference['amortized'] == challenger['amortized']):
print('all values match for account: ', account_id)
else:
print(
'Reference and challenger values are different for account:',
account_id, ', reference: ', reference, 'challenger: ',
challenger)
writer.writerow([
billing_month, account_id, reference['blended'],
challenger['blended'], reference['unblended'],
challenger['unblended'], reference['amortized'],
challenger['amortized']
])
all_match = False
return all_match
def get_reference_costs(client, month, file, with_discounts):
def write_to_disk(cost_response, billing_month, file):
reference_costs_by_account = {}
with open(file, 'a+') as csv_file:
writer = csv.writer(csv_file, delimiter=',')
for account_cost in cost_response['ResultsByTime'][0][
'Groups']:
account_id = account_cost['Keys'][0]
unblended_cost = util.roundoff(
account_cost['Metrics']['UnblendedCost']['Amount'])
blended_cost = util.roundoff(
account_cost['Metrics']['BlendedCost']['Amount'])
amortized_cost = util.roundoff(
account_cost['Metrics']['AmortizedCost']['Amount'])
reference_costs_by_account[account_id] = {
'unblended': unblended_cost,
'blended': blended_cost,
'amortized': amortized_cost
}
writer.writerow([
billing_month, account_id, blended_cost,
unblended_cost, amortized_cost
])
return reference_costs_by_account
if with_discounts:
response = client.get_cost_and_usage(
TimePeriod={
'Start': ("%s" % (gtd.getDate(month)[0])),
'End': ("%s" % (gtd.getDate(month)[1]))
},
Granularity='MONTHLY',
Metrics=["BlendedCost", "UnblendedCost", "AmortizedCost"],
GroupBy=[{
'Type': 'DIMENSION',
'Key': 'LINKED_ACCOUNT'
}])
else:
response = client.get_cost_and_usage(
TimePeriod={
'Start': ("%s" % (gtd.getDate(month)[0])),
'End': ("%s" % (gtd.getDate(month)[1]))
},
Granularity='MONTHLY',
Metrics=["BlendedCost", "UnblendedCost", "AmortizedCost"],
GroupBy=[{
'Type': 'DIMENSION',
'Key': 'LINKED_ACCOUNT'
}],
Filter={
"Not": {
"Dimensions": {
"Key":
"RECORD_TYPE",
"Values": [
"Refund",
"Enterprise Discount Program Discount",
"Credit"
],
}
}
})
return write_to_disk(response, gtd.getDate(month)[0], file)
def cloud_cost_queries(account, cloud, month, tagged):
if tagged:
if account=="822924222578":
filters = {
"And": [{
"Dimensions": {
"Key": "LINKED_ACCOUNT",
"Values": [("%s" % (account))]
}
},
{
"Not": {
'Tags': {
'Key': 'fc.cloud.name',
'Values': cloud
}
}
}
],
}
else:
filters = {
"And": [{
"Dimensions": {
"Key": "LINKED_ACCOUNT",
"Values": [("%s" % (account))]
}
},
{
'Tags': {
'Key': 'fc.cloud.name',
'Values': cloud
}
}
],
}
cloud_cost_response = clientce.get_cost_and_usage(
TimePeriod={
'Start':("%s" % (gtd.getDate(month)[0])),
'End': ("%s" % (gtd.getDate(month)[1]))
},
Granularity='MONTHLY',
Metrics=["BlendedCost", "UnblendedCost", "AmortizedCost"],
Filter = filters
)
print(cloud_cost_response)
csv.write_cost_to_csv(prop.reference_filename_with_discount, cloud_cost_response, account)
else:
if account=="822924222578":
filters = {
"And": [{
"Dimensions": {
"Key": "LINKED_ACCOUNT",
"Values": [("%s" % (account))]
}
},
{
"Not":{
"Dimensions": {
"Key": "RECORD_TYPE",
"Values": ["Refund", "Enterprise Discount Program Discount", "Credit"],
}
}
}]
}
else:
filters = {
"And": [{
"Dimensions": {
"Key": "LINKED_ACCOUNT",
"Values": [("%s" % (account))]
}
},
{
'Tags': {
'Key': 'fc.cloud.name',
'Values': cloud
}
},
{
"Not": {
"Dimensions": {
"Key": "RECORD_TYPE",
"Values": ["Refund", "Enterprise Discount Program Discount", "Credit"],
}
}
}
],
}
cloud_cost_response = clientce.get_cost_and_usage(
TimePeriod={
'Start':("%s" % (gtd.getDate(month)[0])),
'End': ("%s" % (gtd.getDate(month)[1]))
},
Granularity='MONTHLY',
Metrics=["BlendedCost", "UnblendedCost", "AmortizedCost"],
Filter = filters
)
print(cloud_cost_response)
csv.write_cost_to_csv(prop.reference_filename_without_discount, cloud_cost_response, account)
def environment_cost_queries(account, month, tagged):
print("Cost Explorer query...")
if tagged:
environment_cost_response = clientce.get_cost_and_usage(
TimePeriod={
'Start':("%s" % (gtd.getDate(month)[0])),
'End': ("%s" % (gtd.getDate(month)[1]))
},
Granularity='MONTHLY',
Metrics=["BlendedCost", "UnblendedCost", "AmortizedCost"],
Filter = {
"And": [{
"Dimensions": {
"Key": "LINKED_ACCOUNT",
"Values": [("%s" % (account))]
}
},
{
"Not": {
'Tags': {
'Key': 'fc.env.type',
'Values': [
"ci","common","dedicated","dev", "dev3", "dev4", "engineering", "internal", "operations", "prod", "shared", "testtest", "test2","wow"
]
}
}
}
],
}
)
print(environment_cost_response)
csv.write_cost_to_csv(prop.reference_filename_with_discount, environment_cost_response, account=account)
else:
environment_cost_response = clientce.get_cost_and_usage(
TimePeriod={
'Start':("%s" % (gtd.getDate(month)[0])),
'End': ("%s" % (gtd.getDate(month)[1]))
},
Granularity='MONTHLY',
Metrics=["BlendedCost", "UnblendedCost", "AmortizedCost"],
Filter = {
"And": [{
"Dimensions": {
"Key": "LINKED_ACCOUNT",
"Values": [("%s" % (account))]
},
},{
'Tags': {
'Key': 'fc.env.type',
'Values': [
"ci","common","dedicated","dev", "dev3", "dev4", "engineering", "internal", "operations", "prod", "shared", "testtest", "test2"
]
}
}
],
},
)
print(environment_cost_response)
csv.write_cost_to_csv(prop.reference_filename_without_discount, environment_cost_response, account=account)