def get_billing_data_per_year(year, output_type):
if output_type == 'day':
billing_data = db_session.query(func.unix_timestamp(Usage.usage_date), func.sum(Usage.cost), Usage.usage_value,
Usage.measurement_unit). \
filter(func.extract('year', Usage.usage_date) == year).group_by(func.unix_timestamp(Usage.usage_date))
else:
billing_data = db_session.query(func.extract(output_type, Usage.usage_date), func.sum(Usage.cost)). \
filter(func.extract('year', Usage.usage_date) == year).group_by(func.extract(output_type, Usage.usage_date))
return billing_data
def get_billing_data_per_year_month(year, value_to_match, output_type):
if year == value_to_match:
billing_data = db_session.query(Usage.project_id, func.sum(Usage.cost)). \
filter(func.extract('year', Usage.usage_date) == year).group_by(Usage.project_id)
else:
billing_data = db_session.query(Usage.project_id, func.sum(Usage.cost)). \
filter(func.extract('year', Usage.usage_date) == year,
func.extract(output_type, Usage.usage_date) == value_to_match).group_by(Usage.project_id)
return billing_data
def get_billing_data_per_year_per_center(year, project_ids, output_type):
if output_type == 'week':
billing_data = db_session.query(Usage.project_id, func.extract(output_type, Usage.usage_date),
func.sum(Usage.cost)). \
filter(func.extract('year', Usage.usage_date) == year, Usage.project_id.in_(project_ids)). \
group_by(func.extract(output_type, Usage.usage_date))
else:
billing_data = db_session.query(func.extract(output_type, Usage.usage_date), func.sum(Usage.cost)). \
filter(func.extract('year', Usage.usage_date) == year, Usage.project_id.in_(project_ids)). \
group_by(func.extract(output_type, Usage.usage_date))
return billing_data
def get_billing_data_per_resource_all_project_per_day_week(year, value_to_match, project_ids, resource, output_type):
if output_type == 'day':
billing_data = db_session.query(func.unix_timestamp(Usage.usage_date), func.sum(Usage.cost), Usage.usage_value,
Usage.measurement_unit). \
filter(func.extract('year', Usage.usage_date) == year, Usage.project_id.in_(project_ids),
Usage.resource_type == resource, func.extract('week', Usage.usage_date) == value_to_match). \
group_by(func.unix_timestamp(Usage.usage_date))
elif output_type == 'week':
billing_data = db_session.query(Usage.project_id, func.extract(output_type, Usage.usage_date),
func.sum(Usage.cost)). \
filter(func.extract('year', Usage.usage_date) == year, Usage.project_id.in_(project_ids),
Usage.resource_type == resource, func.extract('week', Usage.usage_date) == value_to_match). \
group_by(func.extract(output_type, Usage.usage_date))
return billing_data
def load_usage_table():
year = request.args.get('year', None)
month = request.args.get('month', None)
project = request.args.get('project', None)
data = db_session.query(Usage).all()
data = [x.__dict__ for x in data]
# year = 4 digit year
if year is not None:
data = filter(lambda x: x['usage_date'].year == int(year), data)
# month = month num
if month is not None:
data = filter(lambda x: x['usage_date'].month == int(month), data)
# project = project_name
if project is not None:
data = filter(
lambda x: x['resource_uri'].split('/')[1] == str(project), data)
for x in data:
x['usage_date'] = x['usage_date'].strftime('%Y-%m-%d')
del x['_sa_instance_state']
data = list(data)
resp = Response(response=simplejson.dumps(data),
status=200,
mimetype='application/json')
return resp
def get_billing_data_per_year_week_day(year, value_to_match, output_type, project_ids):
if output_type == 'day':
billing_data = db_session.query(func.unix_timestamp(Billing.usage_date), func.sum(Billing.cost),
Billing.usage_value,
Billing.measurement_unit). \
filter(func.extract('year', Billing.usage_date) == year, Billing.project_id.in_(project_ids),
func.extract('week', Billing.usage_date) == value_to_match). \
group_by(func.extract(output_type, Billing.usage_date))
elif output_type == 'week':
billing_data = db_session.query(Billing.project_id, func.extract(output_type, Billing.usage_date),
func.sum(Billing.cost)). \
filter(func.extract('year', Billing.usage_date) == year,
func.extract('week', Billing.usage_date) == value_to_match).group_by(
func.extract(output_type, Billing.usage_date), Billing.project_id)
return billing_data
def get_billing_data_per_resource_month_week_day_center(year, value_to_match, project_id, output_type):
if output_type == 'day':
billing_data = db_session.query(func.unix_timestamp(Usage.usage_date),
func.sum(Usage.cost), Usage.usage_value,
Usage.measurement_unit). \
filter(func.extract('year', Usage.usage_date) == year,
func.extract('month', Usage.usage_date) == value_to_match, Usage.project_id == project_id). \
group_by(func.extract(output_type, Usage.usage_date))
else:
billing_data = db_session.query(Usage.resource_type, func.extract(output_type, Usage.usage_date),
func.sum(Usage.cost)). \
filter(func.extract('year', Usage.usage_date) == year, Usage.project_id == project_id,
func.extract('month', Usage.usage_date) == value_to_match).group_by(
func.extract(output_type, Usage.usage_date), Usage.resource_type)
return billing_data
def load_usage_table():
year = request.args.get('year', None)
month = request.args.get('month', None)
project = request.args.get('project', None)
data = db_session.query(Usage).all()
data = [x.__dict__ for x in data]
# year = 4 digit year
if year is not None:
data = filter(lambda x: x['usage_date'].year == int(year), data)
# month = month num
if month is not None:
data = filter(lambda x: x['usage_date'].month == int(month), data)
# project = project_name
if project is not None:
data = filter(lambda x: x['resource_uri'].split('/')[1] == str(project), data)
for x in data:
x['usage_date'] = x['usage_date'].strftime('%Y-%m-%d')
del x['_sa_instance_state']
data = list(data)
resp = Response(response=simplejson.dumps(data), status=200, mimetype='application/json')
return resp
def load_instance_table():
body = request.get_json()
log.info("------- body: {0} -------".format(body))
machine_type = body.get('machine_type', None)
tags = body.get('tags', None)
project = body.get('project', None)
month = body.get('month', None)
year = body.get('year', None)
data = db_session.query(Instance).order_by(Instance.instanceId).all()
data = [x.__dict__ for x in data]
for x in data:
del x['_sa_instance_state']
instance_obj_list = data
instance_obj_list = build_objs(data)
for x in instance_obj_list:
if 'machineType' in x:
x['machineType'] = x['machineType'].split('/')[-1]
# project = project name
if project is not None:
project = project.lower()
instance_obj_list = filter(lambda x: x['project'] == project,
instance_obj_list)
# machine_type = just the machine type bit
if machine_type is not None:
instance_obj_list = filter(lambda x: x['machineType'] == machine_type,
instance_obj_list)
# tags should be filtered as AND
if tags is not None:
tags = [x.encode('UTF8') for x in tags]
tags = set(tags)
instance_obj_list = filter(
lambda x: 'tags.items' in x and tags.issubset(set(x['tags.items'])
), instance_obj_list)
if year is not None:
year = int(year)
instance_obj_list = filter(
lambda x: int(x['creationTimestamp'].split('-')[0]) == year,
instance_obj_list)
if month is not None:
month = int(month)
instance_obj_list = filter(
lambda x: int(x['creationTimestamp'].split('-')[1]) == month,
instance_obj_list)
resp = Response(response=simplejson.dumps(instance_obj_list),
status=200,
mimetype="application/json")
return resp
def get_billing_data_per_resource_month_center(year, value_to_match, project_id, output_type):
billing_data = db_session.query(Usage.resource_type, func.sum(Usage.cost)). \
filter(func.extract('year', Usage.usage_date) == year,
func.extract(output_type, Usage.usage_date) == value_to_match,
Usage.project_id == project_id, ). \
group_by(Usage.resource_type)
return billing_data
def get_billing_data_per_resource_per_project_per_week(year, value_to_match, project_id, resource, output_type):
if output_type == 'day':
billing_data = db_session.query(func.unix_timestamp(Billing.usage_date), func.sum(Billing.cost),
Billing.usage_value,
Billing.measurement_unit). \
filter(func.extract('year', Billing.usage_date) == year, Billing.project_id == project_id,
Billing.resource_type == resource, func.extract('week', Billing.usage_date) == value_to_match). \
group_by(func.unix_timestamp(Billing.usage_date))
elif output_type == 'week':
billing_data = db_session.query(Billing.project_id, func.extract('week', Billing.usage_date),
func.sum(Billing.cost)). \
filter(func.extract('year', Billing.usage_date) == year, Billing.project_id == project_id,
Billing.resource_type == resource, func.extract('week', Billing.usage_date) == value_to_match). \
group_by(func.extract('week', Billing.usage_date))
return billing_data
def get_billing_data_per_year_per_center_days(year, project_ids):
billing_data = db_session.query(func.unix_timestamp(Usage.usage_date), func.sum(Usage.cost),
Usage.usage_value,
Usage.measurement_unit). \
filter(func.extract('year', Usage.usage_date) == year, Usage.project_id.in_(project_ids)). \
group_by(func.unix_timestamp(Usage.usage_date))
return billing_data
def getProjectNames():
project_list = db_session.query(Project.project_name).distinct()
project_names = [item[0] for item in project_list]
resp = Response(response=simplejson.dumps(project_names),
status=200,
mimetype='application/json')
return resp
def get_billing_data_per_project_year(year, project_id, output_type):
if output_type == 'day':
billing_data = db_session.query(func.unix_timestamp(Billing.usage_date), func.sum(Billing.cost),
Billing.usage_value,
Billing.measurement_unit). \
filter(func.extract('year', Billing.usage_date) == year, Billing.project_id == project_id). \
group_by(func.unix_timestamp(Billing.usage_date))
elif output_type == 'week':
billing_data = db_session.query(Billing.project_id, func.extract(output_type, Billing.usage_date),
func.sum(Billing.cost)). \
filter(func.extract('year', Billing.usage_date) == year, Billing.project_id == project_id). \
group_by(func.extract(output_type, Billing.usage_date))
else:
billing_data = db_session.query(func.extract(output_type, Billing.usage_date), func.sum(Billing.cost)). \
filter(func.extract('year', Billing.usage_date) == year, Billing.project_id == project_id). \
group_by(func.extract(output_type, Billing.usage_date))
return billing_data
def get_billing_data_per_year_per_center_quarter(year, project_ids, quarter, output_type):
if output_type == 'day':
billing_data = db_session.query(func.unix_timestamp(Usage.usage_date), func.sum(Usage.cost), Usage.usage_value,
Usage.measurement_unit). \
filter(func.extract('year', Usage.usage_date) == year, Usage.project_id.in_(project_ids),
func.extract('quarter', Usage.usage_date) == quarter). \
group_by(func.extract(output_type, Usage.usage_date))
elif output_type == 'week':
billing_data = db_session.query(Usage.project_id, func.extract(output_type, Usage.usage_date),
func.sum(Usage.cost)). \
filter(func.extract('year', Usage.usage_date) == year, Usage.project_id.in_(project_ids),
func.extract('quarter', Usage.usage_date) == quarter). \
group_by(func.extract(output_type, Usage.usage_date))
else:
billing_data = db_session.query(func.extract(output_type, Usage.usage_date), func.sum(Usage.cost)). \
filter(func.extract('year', Usage.usage_date) == year, Usage.project_id.in_(project_ids),
func.extract('quarter', Usage.usage_date) == quarter). \
group_by(func.extract(output_type, Usage.usage_date))
return billing_data
def get_billing_data_per_resource_per_project(year, project_id, resource, output_type):
if output_type == 'day':
billing_data = db_session.query(func.unix_timestamp(Usage.usage_date), func.sum(Usage.cost), Usage.usage_value,
Usage.measurement_unit). \
filter(func.extract('year', Usage.usage_date) == year, Usage.project_id == project_id,
Usage.resource_type == resource). \
group_by(func.unix_timestamp(Usage.usage_date))
elif output_type == 'week':
billing_data = db_session.query(Usage.project_id, func.extract(output_type, Usage.usage_date),
func.sum(Usage.cost)). \
filter(func.extract('year', Usage.usage_date) == year, Usage.project_id == project_id,
Usage.resource_type == resource). \
group_by(func.month(Usage.usage_date))
else:
billing_data = db_session.query(func.extract(output_type, Usage.usage_date), func.sum(Usage.cost)). \
filter(func.extract('year', Usage.usage_date) == year, Usage.project_id == project_id,
Usage.resource_type == resource). \
group_by(func.month(Usage.usage_date))
return billing_data
def getAllProjectId():
print("blah")
data = db_session.query(Usage.resource_id).distinct()
# data = [x.__dict__ for x in data]
new_data = []
for (item) in data:
new_data.append(item[0])
resp = Response(response=simplejson.dumps(new_data),
status=200,
mimetype="application/json")
return resp
def getAllUsage():
print("blah")
data = db_session.query(Usage).all()
data = [x.__dict__ for x in data]
for x in data:
x['usage_date'] = x['usage_date'].strftime('%Y-%m-%d')
del x['_sa_instance_state']
resp = Response(response=simplejson.dumps(data),
status=200,
mimetype="application/json")
return resp
def getAllProjectId():
print("blah")
data = db_session.query(Usage.resource_id).distinct()
# data = [x.__dict__ for x in data]
new_data = []
for (item) in data:
new_data.append(item[0])
resp = Response(response=simplejson.dumps(new_data),
status=200,
mimetype="application/json")
return resp
def getAllUsage():
print("blah")
data = db_session.query(Usage).all()
data = [x.__dict__ for x in data]
for x in data:
x['usage_date'] = x['usage_date'].strftime('%Y-%m-%d')
del x['_sa_instance_state']
resp = Response(response=simplejson.dumps(data),
status=200,
mimetype="application/json")
return resp
def get_machine_types():
data = db_session.query(Instance).filter(
Instance.key == 'machineType').all()
data = (x.__dict__ for x in data)
machine_types_list = [y['value'].split('/')[-1] for y in data]
# remove duplicate machine types
machine_types_list = list(set(machine_types_list))
resp = Response(response=simplejson.dumps(machine_types_list),
status=200,
mimetype="application/json")
return resp
def get_tags():
data = db_session.query(
Instance.value).filter(Instance.key == "tags.items").distinct()
tmp = set()
for x in data:
x = tuple(x)
tmp.add(x[0])
data = list(tmp)
resp = Response(response=simplejson.dumps(data),
status=200,
mimetype="application/json")
return resp
def data_processor(job_type):
# get list of buckets; for each project, do try except
status = 200
message = dict(success=[], fail=[])
startTime = datetime.datetime.now()
lock_file = True
# get list of project names
project_list = db_session.query(Project.project_name).distinct()
project_names = (item[0] for item in project_list)
for b in project_names:
try:
bucket = b + "-usage-export"
archive_bucket = bucket + '-archive'
log.info('--------- THE BUCKET : {0} --------------'.format(bucket))
random_number = binascii.hexlify(os.urandom(32)).decode()
log.info(' RANDOM NUMBER --- {0}'.format(random_number))
# Get the application default credentials. When running locally, these are
# available after running `gcloud init`. When running on compute
# engine, these are available from the environment.
credentials = GoogleCredentials.get_application_default()
# Construct the service object for interacting with the Cloud Storage API -
# the 'storage' service, at version 'v1'.
# You can browse other available api services and versions here:
# https://developers.google.com/api-client-library/python/apis/
service = discovery.build('storage', 'v1', credentials=credentials)
# Make a request to buckets.get to retrieve a list of objects in the
# specified bucket.
req = service.buckets().get(bucket=bucket)
resp = req.execute()
# print(json.dumps(resp, indent=2))
# Create a request to objects.list to retrieve a list of objects.
fields_to_return = \
'nextPageToken,items(name,size,contentType,metadata(my-key))'
req = service.objects().list(bucket=bucket, fields=fields_to_return)
file_count = 0
log.info('Process {0} Start time --- {1}'.format(bucket, startTime))
# If you have too many items to list in one request, list_next() will
# automatically handle paging with the pageToken.
while req:
resp = req.execute()
# print(json.dumps(resp, indent=2))
if len(resp) == 0:
log.info('############################################################################################')
log.info('--------- THE BUCKET LIST IS EMPTY --------------')
log.info('--------- NO FILES TO PROCESS --------------')
log.info(resp)
log.info('############################################################################################')
else:
get_filenames(resp, service, random_number, bucket)
req = service.objects().list_next(req, resp)
message['success'].append(bucket)
except Exception as e:
log.error(' Error in getting Bucket Details - {0}'.format(e))
message['fail'].append({'bucket':bucket, 'error':str(e)})
status = 500
pass
endTime = datetime.datetime.now()
log.info('Process End time --- {0}'.format(endTime))
elapsedTime = endTime - startTime
time = 'Total Time to Process all the files -- {0}'.format(divmod(elapsedTime.total_seconds(), 60))
log.info(time)
log.info(' ARGS PASSED --- {0}'.format(job_type))
# if job_type == 'now':
# set_scheduler(os.environ.get('SCHEDULER_HOUR'), os.environ.get('SCHEDULER_MIN'))
response = dict(data=json.dumps(message), status=status, time=time)
return response
def getProjectNames():
project_list = db_session.query(Project.project_name).distinct()
project_names = [item[0] for item in project_list]
resp = Response(response=simplejson.dumps(project_names), status=200, mimetype='application/json')
return resp
def data_processor(job_type):
# get list of buckets; for each project, do try except
status = 200
message = dict(success=[], fail=[])
startTime = datetime.datetime.now()
lock_file = True
# get list of project names
project_list = db_session.query(Project.project_name).distinct()
project_names = (item[0] for item in project_list)
for b in project_names:
try:
bucket = b + "-usage-export"
archive_bucket = bucket + '-archive'
log.info(
'--------- THE BUCKET : {0} --------------'.format(bucket))
random_number = binascii.hexlify(os.urandom(32)).decode()
log.info(' RANDOM NUMBER --- {0}'.format(random_number))
# Get the application default credentials. When running locally, these are
# available after running `gcloud init`. When running on compute
# engine, these are available from the environment.
credentials = GoogleCredentials.get_application_default()
# Construct the service object for interacting with the Cloud Storage API -
# the 'storage' service, at version 'v1'.
# You can browse other available api services and versions here:
# https://developers.google.com/api-client-library/python/apis/
service = discovery.build('storage', 'v1', credentials=credentials)
# Make a request to buckets.get to retrieve a list of objects in the
# specified bucket.
req = service.buckets().get(bucket=bucket)
resp = req.execute()
# print(json.dumps(resp, indent=2))
# Create a request to objects.list to retrieve a list of objects.
fields_to_return = \
'nextPageToken,items(name,size,contentType,metadata(my-key))'
req = service.objects().list(bucket=bucket,
fields=fields_to_return)
file_count = 0
log.info('Process {0} Start time --- {1}'.format(
bucket, startTime))
# If you have too many items to list in one request, list_next() will
# automatically handle paging with the pageToken.
while req:
resp = req.execute()
# print(json.dumps(resp, indent=2))
if len(resp) == 0:
log.info(
'############################################################################################'
)
log.info(
'--------- THE BUCKET LIST IS EMPTY --------------')
log.info('--------- NO FILES TO PROCESS --------------')
log.info(resp)
log.info(
'############################################################################################'
)
else:
get_filenames(resp, service, random_number, bucket)
req = service.objects().list_next(req, resp)
message['success'].append(bucket)
except Exception as e:
log.error(' Error in getting Bucket Details - {0}'.format(e))
message['fail'].append({'bucket': bucket, 'error': str(e)})
status = 500
pass
endTime = datetime.datetime.now()
log.info('Process End time --- {0}'.format(endTime))
elapsedTime = endTime - startTime
time = 'Total Time to Process all the files -- {0}'.format(
divmod(elapsedTime.total_seconds(), 60))
log.info(time)
log.info(' ARGS PASSED --- {0}'.format(job_type))
# if job_type == 'now':
# set_scheduler(os.environ.get('SCHEDULER_HOUR'), os.environ.get('SCHEDULER_MIN'))
response = dict(data=json.dumps(message), status=status, time=time)
return response
def get_cost_centers(unique):
if unique:
center_list = db_session.query(Projects.cost_center).distinct()
else:
center_list = db_session.query(Projects).all()
return center_list
def get_resource_list_per_project(project_ids):
resource_list = db_session.query(Usage.resource_type). \
filter(Usage.project_id.in_(project_ids)). \
distinct()
return resource_list
def get_distinct_projects():
project_list = db_session.query(Usage.project_id).distinct()
return project_list
