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