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)