def insert_data(usage_date, cost, project_id, resource_type, account_id,
usage_value, measurement_unit):
done = False
try:
usage = Usage(usage_date, cost, project_id, resource_type, account_id,
usage_value, measurement_unit)
db_session.add(usage)
db_session.commit()
done = True
except IntegrityError as e:
# log.info('---- DATA ALREADY IN DB --- UPDATE ------')
# log.info('{0}<---->{1}<----->{2}<------>{3}<------>{4}'.format(usage_date, cost, project_id, resource_type,usage_value))
db_session.rollback()
usage = Usage.query.filter_by(project_id=project_id,
usage_date=usage_date,
resource_type=resource_type).first()
usage.cost = cost
usage.usage_value = usage_value
usage.measurement_unit = measurement_unit
db_session.commit()
done = True
except Exception as e:
log.error(
' ------------- ERROR IN ADDING DATA TO THE DB ------------- {0}'.
format(e[0]))
return done
def insert_data(instanceId, key, value):
done = False
log.info('---- starting to add info to DB {0}, {1}, {2} ----'.format(
instanceId, key, value))
try:
log.info(
'--------------------- ADDED INFO TO DB ---------------------')
instance = Instance(instanceId=instanceId, key=key, value=value)
db_session.add(instance)
db_session.commit()
done = True
except IntegrityError as e:
log.info('---- DATA ALREADY IN DB --- UPDATE ------')
# log.info('instanceId = {0}<----> key = {1}<-----> value = {2}'.format(instanceId, key, value))
db_session.rollback()
instance = Instance.query.filter_by(instanceId=instanceId,
key=key).first()
instance.value = value
db_session.commit()
done = True
except Exception as e:
log.error(
' ------------- ERROR IN ADDING DATA TO THE DB ------------- {0}'.
format(e[0]))
return done
def insert_project_data(project_id, project_name):
done = False
try:
project = Project('other', project_id, project_name, 'other', '', '',
'', 0)
db_session.add(project)
db_session.commit()
done = True
except IntegrityError as e:
# log.info('---- Project DATA ALREADY IN DB --- UPDATE ------')
db_session.rollback()
project = Project.query.filter_by(project_id=project_id).first()
project.project_name = project_name
db_session.commit()
done = True
except Exception as e:
log.error(
' ------------- ERROR IN ADDING PROJECT DATA TO THE DB ------------- {0}'
.format(e[0]))
log.error(
' ------------- ERROR IN ADDING PROJECT DATA TO THE DB ------------- {0}<---->{1}'
.format(project_id, project_name))
return done
def insert_project__table_data(data):
project = dict()
try:
'''
update the Project table with project_name with data['projectName']
if there else use data['projectId'] if there else add as support
'''
if 'projectNumber' in data:
project_id = 'ID-' + str(data['projectNumber'])
if 'projectName' in data:
insert_done = insert_project_data(project_id,
data['projectName'])
else:
insert_done = insert_project_data(project_id, project_id)
else:
project_id = 'Not Available'
insert_done = insert_project_data(project_id, 'support')
if not insert_done:
log.info(data)
raise Exception("DB Error: Information not stored")
project = dict(message=' data has been added to db')
except Exception as e:
log.error('Error in inserting data into the DB -- {0}'.format(e[0]))
db_session.rollback()
traceback.print_exc()
return project
def insert_usage_data(data_list, filename, service):
usage = dict()
try:
data_count = 0
total_count = 0
log.info('---- Starting to Add/Update billing data -----')
for data in data_list:
total_count += 1
date = data['startTime']
resource_type = str(data['lineItemId']).replace("com.google.cloud/services", "").replace(
"com.googleapis/services", "")
account_id = str(data['accountId'])
usage_date = datetime.datetime.strptime(
date.split("-")[0] + '-' + date.split("-")[1] + '-' + date.split("-")[2], "%Y-%m-%dT%H:%M:%S")
# check if there is projectnumber else add it as Not available
if 'projectNumber' in data:
project_id = 'ID-' + str(data['projectNumber'])
else:
project_id = 'Not Available'
if len(data['measurements']) != 0:
usage_value = float(data['measurements'][0]['sum'])
measurement_unit = str(data['measurements'][0]['unit'])
else:
usage_value = float(0)
measurement_unit = str('none')
# check if credits is there if so then add it to cost
cost = float(data['cost']['amount'])
if 'credits' in data:
cost = float(data['cost']['amount'])
# log.info('CREDITS PRESENT FOR THIS DATA')
# log.info('cost before-- {0}'.format(cost))
for credit in data['credits']:
cost += float(credit['amount'])
# log.info('{0}<---->{1}<----->{2}<------>{3}'.format(usage_date, project_id, credit['amount'], cost))
# log.info('cost after -- {0}'.format(cost))
if cost == 0:
# log.info('--- COST is 0 --- NOT adding to DB')
continue
else:
# log.info('INSERTING DATA INTO DB -- {0}'.format(data))
insert_done = insert_data(usage_date, cost, project_id, resource_type, account_id, usage_value,
measurement_unit)
if not insert_done:
log.info(data)
continue
else:
data_count += 1
usage = dict(message=' data has been added to db')
log.info(
'DONE adding {0} items out of {1} for file -- {2} into the db '.format(data_count, total_count, filename))
except Exception as e:
log.error('Error in inserting data into the DB -- {0}'.format(e[0]))
db_session.rollback()
return usage
def insert_usage_data(data_list, filename, service):
usage = dict()
# data_list is a string in csv format
# read csv to db
reader = csv.DictReader(data_list.splitlines(), delimiter=',')
try:
data_count = 0
total_count = 0
for data in reader:
total_count += 1
usage_date = datetime.datetime.strptime(data['Report Date'],
'%Y-%m-%d')
if len(data['Quantity']) != 0:
usage_value = int(data['Quantity'])
measurement_unit = str(data['Unit'])
else:
usage_value = 0
measurement_unit = "none"
resource_uri = str(data['Resource URI'].replace(
"https://www.googleapis.com/compute/v1/projects", ""))
location = str(data['Location'])
resource_id = str(data['ResourceId'])
resource_type = str(data['MeasurementId']).replace(
"com.google.cloud/services", "")
insert_done = insert_data(usage_date=usage_date,
resource_type=resource_type,
resource_id=resource_id,
resource_uri=resource_uri,
location=location,
usage_value=usage_value,
measurement_unit=measurement_unit)
if not insert_done:
log.info(data)
log.debug('row not added!!!')
continue
else:
log.debug('row added')
data_count += 1
usage = dict(message=' data has been added to db')
log.info(
'DONE adding {0} items out of {1} for file -- {2} into the db '.
format(data_count, total_count, filename))
except Exception as e:
log.error('Error in inserting data into the DB -- {0}'.format(e[0]))
db_session.rollback()
return usage
def insert_usage_data(data_list, filename, service):
usage = dict()
# data_list is a string in csv format
# read csv to db
reader = csv.DictReader(data_list.splitlines(), delimiter=',')
try:
data_count = 0
total_count = 0
for data in reader:
total_count += 1
usage_date = datetime.datetime.strptime(data['Report Date'], '%Y-%m-%d')
if len(data['Quantity']) != 0:
usage_value = int(data['Quantity'])
measurement_unit = str(data['Unit'])
else:
usage_value = 0
measurement_unit = "none"
resource_uri = str(data['Resource URI'].replace("https://www.googleapis.com/compute/v1/projects", ""))
location = str(data['Location'])
resource_id = str(data['ResourceId'])
resource_type = str(data['MeasurementId']).replace("com.google.cloud/services", "")
insert_done = insert_data(usage_date=usage_date, resource_type=resource_type, resource_id=resource_id, resource_uri=resource_uri, location=location, usage_value=usage_value, measurement_unit=measurement_unit)
if not insert_done:
log.info(data)
log.debug('row not added!!!')
continue
else:
log.debug('row added')
data_count += 1
usage = dict(message=' data has been added to db')
log.info(
'DONE adding {0} items out of {1} for file -- {2} into the db '.format(data_count, total_count, filename))
except Exception as e:
log.error('Error in inserting data into the DB -- {0}'.format(e[0]))
db_session.rollback()
return usage
def insert_project_data(project_id, project_name):
done = False
log.info('{0}<---->{1}'.format(project_id, project_name))
try:
project = Project('other', project_id, project_name, 'other', '', '', '', 0)
db_session.add(project)
db_session.commit()
done = True
except IntegrityError as e:
# log.info('---- Project DATA ALREADY IN DB --- UPDATE ------')
db_session.rollback()
project = Project.query.filter_by(project_id=project_id).first()
project.project_name = project_name
db_session.commit()
done = True
except Exception as e:
log.error(' ------------- ERROR IN ADDING DATA TO THE DB ------------- {0}'.format(e[0]))
return done
def insert_data(usage_date, cost, project_id, resource_type, account_id, usage_value, measurement_unit):
done = False
try:
usage = Usage(usage_date, cost, project_id, resource_type, account_id, usage_value, measurement_unit)
db_session.add(usage)
db_session.commit()
done = True
except IntegrityError as e:
# log.info('---- DATA ALREADY IN DB --- UPDATE ------')
# log.info('{0}<---->{1}<----->{2}<------>{3}<------>{4}'.format(usage_date, cost, project_id, resource_type,usage_value))
db_session.rollback()
usage = Usage.query.filter_by(project_id=project_id, usage_date=usage_date, resource_type=resource_type).first()
usage.cost = cost
usage.usage_value = usage_value
usage.measurement_unit = measurement_unit
db_session.commit()
done = True
except Exception as e:
log.error(' ------------- ERROR IN ADDING DATA TO THE DB ------------- {0}'.format(e[0]))
return done
def insert_project__table_data(data_list, filename, service):
project = dict()
try:
data_count = 0
total_count = 0
log.info('---- Starting to Add/Update Project Name -----')
for data in data_list:
total_count += 1
'''
update the Project table with project_name with data['projectName']
if there else use data['projectId'] if there else add as support
'''
if 'projectNumber' in data:
project_id = 'ID-' + str(data['projectNumber'])
if 'projectName' in data:
insert_done = insert_project_data(project_id,
data['projectName'])
else:
insert_done = insert_project_data(project_id, project_id)
else:
project_id = 'Not Available'
insert_done = insert_project_data(project_id, 'support')
if not insert_done:
log.info(data)
continue
else:
data_count += 1
project = dict(message=' data has been added to db')
log.info(
'DONE adding {0} items out of {1} for file -- {2} into the db '.
format(data_count, total_count, filename))
except Exception as e:
log.error('Error in inserting data into the DB -- {0}'.format(e[0]))
db_session.rollback()
return project
def insert_project__table_data(data_list, filename, service):
project = dict()
try:
data_count = 0
total_count = 0
log.info('---- Starting to Add/Update Project Name -----')
for data in data_list:
total_count += 1
'''
update the Project table with project_name with data['projectName']
if there else use data['projectId'] if there else add as support
'''
if 'projectNumber' in data:
project_id = 'ID-' + str(data['projectNumber'])
if 'projectName' in data:
insert_done = insert_project_data(project_id, data['projectName'])
else:
insert_done = insert_project_data(project_id, project_id)
else:
project_id = 'Not Available'
insert_done = insert_project_data(project_id, 'support')
if not insert_done:
log.info(data)
continue
else:
data_count += 1
project = dict(message=' data has been added to db')
log.info(
'DONE adding {0} items out of {1} for file -- {2} into the db '.format(data_count, total_count, filename))
except Exception as e:
log.error('Error in inserting data into the DB -- {0}'.format(e[0]))
db_session.rollback()
return project
def insert_usage_data(data_list, filename, service):
usage = dict()
try:
data_count = 0
total_count = 0
for data in data_list:
total_count += 1
date = data['startTime']
resource_type = str(data['lineItemId']).replace(
"com.google.cloud/services",
"").replace("com.googleapis/services", "")
account_id = str(data['accountId'])
usage_date = datetime.datetime.strptime(
date.split("-")[0] + '-' + date.split("-")[1] + '-' +
date.split("-")[2], "%Y-%m-%dT%H:%M:%S")
# check if there is projectnumber else add it as Not available
if 'projectNumber' in data:
project_id = 'ID-' + str(data['projectNumber'])
else:
project_id = 'Not Available'
if len(data['measurements']) != 0:
usage_value = float(data['measurements'][0]['sum'])
measurement_unit = str(data['measurements'][0]['unit'])
else:
usage_value = float(0)
measurement_unit = str('none')
# check if credits is there if so then add it to cost
cost = float(data['cost']['amount'])
if 'credits' in data:
cost = float(data['cost']['amount'])
# log.info('CREDITS PRESENT FOR THIS DATA')
# log.info('cost before-- {0}'.format(cost))
for credit in data['credits']:
cost += float(credit['amount'])
# log.info('{0}<---->{1}<----->{2}<------>{3}'.format(usage_date, project_id, credit['amount'], cost))
# log.info('cost after -- {0}'.format(cost))
if cost == 0:
# log.info('--- COST is 0 --- NOT adding to DB')
continue
else:
# log.info('INSERTING DATA INTO DB -- {0}'.format(data))
insert_done = insert_data(usage_date, cost, project_id,
resource_type, account_id,
usage_value, measurement_unit)
if not insert_done:
log.info(data)
continue
else:
data_count += 1
usage = dict(message=' data has been added to db')
log.info(
'DONE adding {0} items out of {1} for file -- {2} into the db '.
format(data_count, total_count, filename))
except Exception as e:
log.error('Error in inserting data into the DB -- {0}'.format(e[0]))
db_session.rollback()
return usage
def insert_instance_data(instance_list, zone):
instance = dict()
# data_list is a string in csv format
# read csv to db
try:
for obj in instance_list:
log.info(
"--------- Processing metadata for instanceID: {0} -------------------------"
.format(obj['id']))
instance_id = obj['id']
project_name = obj['zone'].split('/')[6]
insert_data(instance_id, 'project', project_name)
insert_data(instance_id, 'zone', zone)
insert_data(instance_id, 'creationTimestamp',
obj['creationTimestamp'])
insert_data(instance_id, 'selfLink', obj['selfLink'])
insert_data(instance_id, 'status', obj['status'])
insert_data(instance_id, 'name', obj['name'])
insert_data(instance_id, 'machineType', obj['machineType'])
if 'tags' in obj and 'items' in obj['tags']:
for tag in obj['tags']['items']:
insert_data(instance_id, 'tags.items', tag)
for networkInterfaces in obj['networkInterfaces']:
insert_data(instance_id, 'networkInterfaces.network',
networkInterfaces['network'])
insert_data(instance_id, 'networkInterfaces.networkIP',
networkInterfaces['networkIP'])
for accessconfig in networkInterfaces['accessConfigs']:
if 'natIP' in accessconfig:
insert_data(instance_id,
'networkInterfaces.accessconfig.natIP',
accessconfig['natIP'])
for disk in obj['disks']:
insert_data(instance_id, 'disks.type', disk['type'])
insert_data(instance_id, 'disks.mode', disk['mode'])
insert_data(instance_id, 'disks.interface', disk['interface'])
insert_data(instance_id, 'disks.source', disk['source'])
for license in disk['licenses']:
insert_data(instance_id, 'disks.license', license)
if 'items' in obj['metadata']:
for metadata in obj['metadata']['items']:
if metadata['key'] != 'ssh-keys':
insert_data(instance_id, 'metadata.' + metadata['key'],
metadata['value'])
for serviceAccount in obj['serviceAccounts']:
insert_data(instance_id, 'serviceAccounts.email',
serviceAccount['email'])
for scope in serviceAccount['scopes']:
insert_data(instance_id, 'serviceAccounts.scope', scope)
insert_data(instance_id, 'scheduling.onHostMaintenance',
obj['scheduling']['onHostMaintenance'])
insert_data(instance_id, 'scheduling.automaticRestart',
obj['scheduling']['automaticRestart'])
insert_data(instance_id, 'scheduling.preemptible',
obj['scheduling']['preemptible'])
except Exception as e:
log.error('Error in inserting data into the DB -- {0}'.format(e[0]))
db_session.rollback()
return instance