apiResponse = requests.get(URL, params=payload)
if apiResponse.status_code == 200:
apiResponseJSON = apiResponse.json()
#print(apiResponse.json())
#print(apiResponseJSON['members'])
#print(apiResponseJSON['members'][0])
#print(apiResponseJSON['_metadata'])
#print(apiResponseJSON['_metadata'].get('totalRows'))
apiRowCount = apiResponseJSON['_metadata'].get('rowCount')
apiOffset = apiOffset + apiLimit
for member in apiResponseJSON['members']:
memberCount += 1
logger.debug(str(memberCount) + " " + member.get('email'))
# Check to see if we have seen this member
SQL = 'SELECT ID, User_Email, User_Last_Name, User_First_Name FROM Members WHERE ID = ?;'
SQLdata = (cleanStrLower(member.get('userName')), )
dbCursor.execute(SQL, SQLdata)
dbMemberRecord = dbCursor.fetchone()
if dbMemberRecord is None:
uniqueMemberCount += 1
SQL = 'INSERT INTO Members (ID, User_Email, User_Last_Name, User_First_Name, KMS_Valid) VALUES (?, ?, ?, ?, 1);'
SQLdata = (cleanStrLower(member.get('userName')), cleanStrLower(member.get('email')), cleanStr(member.get('lastName')), cleanStr(member.get('firstName')), )
try:
dbUpdateCursor.execute(SQL, SQLdata)
except sqlite3.Error as errorMessage:
logger.error('SQL error on update Members: ' + str(errorMessage) + ' on record: ' + member.get('email'))
else:
if args.update:
updateMemberCount += 1
logger.debug('Updating: ' + cleanStr(member.get('email')))
SQL = 'UPDATE Members SET User_Email = ?, User_Last_Name = ?, User_First_Name = ?, KMS_Valid = 1 WHERE ID = ?;'
apiResponse = requests.get(URL, params=payload)
if apiResponse.status_code == 200:
apiResponseJSON = apiResponse.json()
#print(apiResponse.json())
#print(apiResponseJSON['members'])
#print(apiResponseJSON['members'][0])
#print(apiResponseJSON['_metadata'])
#print(apiResponseJSON['_metadata'].get('totalRows'))
apiRowCount = apiResponseJSON['_metadata'].get('rowCount')
apiOffset = apiOffset + apiLimit
for key in apiResponseJSON['keys']:
keyCount += 1
logger.debug(str(keyCount) + " " + key.get('apiKey'))
# Check to see if we have seen this key
SQL = 'SELECT API_Key, fk_Site_ID, fk_Member_ID, Application_Name, Description, KMS_Valid FROM Keys WHERE API_key = ?;'
SQLdata = (cleanStrLower(key.get('apiKey')), )
dbCursor.execute(SQL, SQLdata)
dbKeyRecord = dbCursor.fetchone()
if dbKeyRecord is None:
uniqueKeyCount += 1
SQL = 'INSERT INTO Keys (API_Key, fk_Site_ID, fk_Member_ID, Application_Name, Description, KMS_Valid) VALUES (?, ?, ?, ?, ?, 1);'
SQLdata = (cleanStrLower(key.get('apiKey')),
cleanStrLower(key.get('customer')),
cleanStr(key['member'].get('userName')),
cleanStr(key['application'].get('name')),
cleanStr(key['application'].get('description')), )
try:
dbUpdateCursor.execute(SQL, SQLdata)
except sqlite3.Error as errorMessage:
logger.error('SQL error on insert into Keys: ' + str(errorMessage) + ' on record: ' + key.get('apiKey'))
else:
def processQuery (database, SQL):
matchCount = 0
emailCount = 0
nameCount = 0
descriptionCount = 0
SQLdata = ()
dbCursor.execute(SQL, SQLdata)
queryRecords = dbCursor.fetchall()
if queryRecords is None:
logger.info('No rows retrieved from query: ' + SQL)
else:
readRow = dict(
key = str(),
application_name = str(),
email = str())
for queryRecord in queryRecords:
matchCount += 1
readRow = dict(
key = queryRecord[0],
application_name = queryRecord[1],
email = queryRecord[2],
description = queryRecord[3])
writeRow = dict(
key = str(),
application_name = str(),
description = str(),
email =str(),
last_name = str(),
first_name = str())
URL = 'https://kmsapi.portal.bazaarvoice.com/api/v1/keys/' + readRow['key']
payload = {'passkey': args.apiKey}
keyDetails = requests.get(URL, params=payload)
if keyDetails.status_code == 200:
writeRow['key'] = cleanStrLower(readRow['key'])
keySet = keyDetails.json()
for keyDetailsJSON in keySet['keys']:
applicationJSON = keyDetailsJSON.get('application')
if applicationJSON:
logger.debug('Application JSON: ' + str(applicationJSON))
logger.debug('Application Name WriteRow: ' + str(writeRow))
writeRow['application_name'] = cleanStr(applicationJSON.get('name'))
if readRow['application_name'] is None and writeRow['application_name'] is not None:
nameCount += 1
SQL = 'UPDATE Keys SET Application_Name = ?, KMS_Valid = 1 WHERE API_Key = ?;'
SQLdata = (writeRow['application_name'], writeRow['key'], )
try:
dbUpdateCursor.execute(SQL, SQLdata)
except sqlite3.Error as errorMessage:
logger.error('SQL error on update application name: ' + str(errorMessage) + ' on record: ' + str(writeRow))
writeRow['description'] = cleanStr(applicationJSON.get('description'))
if readRow['description'] is None and writeRow['description'] is not None:
descriptionCount += 1
SQL = 'UPDATE Keys SET Description = ?, KMS_Valid = 1 WHERE API_Key = ?;'
SQLdata = (writeRow['description'], writeRow['key'], )
try:
dbUpdateCursor.execute(SQL, SQLdata)
except sqlite3.Error as errorMessage:
logger.error('SQL error on update description: ' + str(errorMessage) + ' on record: ' + str(writeRow))
memberJSON = keyDetailsJSON.get('member')
if memberJSON:
writeRow['email'] = cleanStrLower(memberJSON.get('email'))
writeRow['last_name'] = cleanStr(memberJSON.get('last_name'))
writeRow['first_name'] = cleanStr(memberJSON.get('first_name'))
if readRow['email'] is None and writeRow['email'] is not None:
emailCount += 1
logger.debug('Email JSON: ' + str(memberJSON))
logger.debug('Email WriteRow: ' + str(writeRow))
SQL = 'SELECT User_Email FROM Members WHERE User_Email = ?;'
SQLdata = (writeRow['email'], )
dbEmailLookupCursor.execute(SQL, SQLdata)
dbMemberRecord = dbEmailLookupCursor.fetchone()
if dbMemberRecord is None:
SQL = 'INSERT INTO Members (User_Email, User_Last_Name, User_First_Name) VALUES (?, ?, ?);'
SQLdata = (writeRow['email'], writeRow['last_name'], writeRow['first_name'], )
try:
dbUpdateCursor.execute(SQL, SQLdata)
except sqlite3.Error as errorMessage:
logger.error('SQL error on insert into Member: ' + str(errorMessage) + ' on record: ' + str(writeRow))
SQL = 'UPDATE Keys SET fk_User_Email = ?, KMS_Valid = 1 WHERE API_Key = ?;'
SQLdata = (writeRow['email'], writeRow['key'], )
try:
dbUpdateCursor.execute(SQL, SQLdata)
except sqlite3.Error as errorMessage:
logger.error('SQL error on update email in Keys: ' + str(errorMessage) + ' on record: ' + str(writeRow))
else:
if keyDetails.status_code == 404:
logger.debug('Key: ' + readRow['key'] + ', ' + 'keyDetails.status_code: ' + str(keyDetails.status_code))
SQL = 'UPDATE Keys SET KMS_Valid = 0 WHERE API_Key = ?;'
SQLdata = (writeRow['key'], )
try:
dbUpdateCursor.execute(SQL, SQLdata)
except sqlite3.Error as errorMessage:
logger.error('SQL error on update KMS_Valid = 0 in Keys: ' + str(errorMessage) + ' on record: ' + str(writeRow))
else:
logger.error('Key: ' + readRow['key'] + ', ' + 'keyDetails.status_code: ' + str(keyDetails.status_code))
logger.debug('dbrecord: ' + str(queryRecord))
dbConnection.commit()
logger.info('Matched Criteria: ' + str(matchCount))
logger.info('Application Name Found in KMS: ' + str(nameCount))
logger.info('Application Description Found in KMS: ' + str(descriptionCount))
logger.info('Emails Found in KMS: ' + str(emailCount))
return()
# Setup the database connection
dbConnection = sqlite3.connect(args.database)
dbCursor = dbConnection.cursor()
# Open the input csv file
inputFileHandle = open(args.inputFile, 'r')
inputDictReader = csv.DictReader(inputFileHandle, delimiter=',')
totalCount = 0
uniqueKeyCount = 0
updateKeyCount = 0
for inputFileRecord in inputDictReader:
totalCount += 1
tempClient = cleanStrLower(inputFileRecord['name'])
# Check to see if we have seen this key
SQL = 'SELECT Client FROM Clients WHERE Client = ?;'
SQLdata = (tempClient, )
dbCursor.execute(SQL, SQLdata)
curResult = dbCursor.fetchone()
if curResult is None:
uniqueKeyCount += 1
SQL = 'INSERT INTO Clients (Client, SF_Account_Name, SF_Account_ID, CS_Segment, SF_CSD, SF_CSP, Country_Name, Country_Code) VALUES (?, ?, ?, ?, ?, ?, ?, ?);'
SQLdata = (tempClient, inputFileRecord['sf_accountname'], inputFileRecord['account ID'], inputFileRecord['CS_Segment'], inputFileRecord['sf_csd'], inputFileRecord['sf_csp'], inputFileRecord['Country Name'], inputFileRecord['Country_Code'], )
dbCursor.execute(SQL, SQLdata)
else:
updateKeyCount += 1
SQL = '''UPDATE Clients SET Client = ?,
SF_Account_Name = ?,
SF_Account_ID = ?,
totalCount = 0
uniqueKeyCount = 0
uniqueKeyClientCount = 0
uniqueMemberCount = 0
keyClientErrorCount = 0
badRecordCount = 0
for inputMemberRecord in inputDictReader:
totalCount += 1
# Check for and ignore invalid rows
if inputMemberRecord['apikey'] is not None and len(inputMemberRecord['apikey']) > 0:
# Check to see if we have seen this key
SQL = 'SELECT API_Key, fk_Site_ID, fk_User_Email FROM Keys WHERE API_Key = ?;'
SQLdata = (cleanStrLower(inputMemberRecord['apikey']), )
dbCursor.execute(SQL, SQLdata)
dbKeyRecord = dbCursor.fetchone()
if dbKeyRecord is None:
uniqueKeyCount += 1
SQL = 'INSERT INTO Keys (API_key, fk_Site_ID, fk_User_Email) VALUES (?, ?, ?);'
SQLdata = (cleanStrLower(inputMemberRecord['apikey']), cleanStrLower(inputMemberRecord['client']), cleanStrLower(inputMemberRecord['email']), )
try:
dbUpdateCursor.execute(SQL, SQLdata)
except sqlite3.Error as errorMessage:
logger.error('SQL error on update Sites: ' + str(errorMessage) + ' on record: ' + str(inputMemberRecord))
else:
# Make sure site ID matches
if cleanStrLower(dbKeyRecord[1]) != cleanStrLower(inputMemberRecord['client']):
keyClientErrorCount += 1
logger.warning("Key " + cleanStrLower(inputMemberRecord['apikey']) + " has mismatched site ID data DB Site_ID = " + cleanStr(dbKeyRecord[1]) + " input file Client = " + cleanStr(inputMemberRecord['client']))
updatedKeyResponseFormatCount = 0
keyClientErrorCount = 0
badRecordCount = 0
badServerHost = ('server_host','NO_DATA','none','FIELD_NOT_FOUND','undefined','',' ')
badAPIVersion = ('apiversion','NO_DATA','none','FIELD_NOT_FOUND','undefined','',' ','''0129ttm,0130ttm,014cttm',0906ttm,095attm>,0982ttm",0bb8ttm,0bf2ttm<,0c00ttm",5.4''',"""1 and sleep(0),5.1,5.1 procedure+analyse((select+extractvalue(rand(),concat(0x3a,(if(mid(version(),1,1)+like+5,+benchmark(500'0000,sha1(1)),1))))),1),5.1'+(select+1+from+(select+sleep(0))a)+'""","""5.0,5.1,5.1 and 1=1,5.1',5.1' and '1'='1""",'5.4,5.4.filter=rating:lte:3')
try:
for inputReport2Record in inputDictReader:
totalCount += 1
# Check for and ignore invalid rowsdb
if inputReport2Record['server_host'] not in badServerHost and inputReport2Record['apiversion'] not in badAPIVersion:
# Check to see if we have seen this key
SQL = 'SELECT API_Key, fk_Site_ID FROM Keys WHERE API_Key = ?;'
SQLdata = (cleanStrLower(inputReport2Record['passkey']), )
dbCursor.execute(SQL, SQLdata)
dbKeyRecord = dbCursor.fetchone()
if dbKeyRecord is None:
uniqueKeyCount += 1
SQL = 'INSERT INTO Keys (API_Key, fk_Site_ID) VALUES (?, ?);'
SQLdata = (cleanStrLower(inputReport2Record['passkey']), cleanStrLower(inputReport2Record['client']), )
try:
dbUpdateCursor.execute(SQL, SQLdata)
except sqlite3.Error as errorMessage:
logger.error('SQL error on update Keys: ' + str(errorMessage) + ' on record: ' + str(inputReport2Record))
else:
# Make sure site ID matches
if cleanStrLower(dbKeyRecord[1]) != cleanStrLower(inputReport2Record['client']):
keyClientErrorCount += 1
logger.warning("Key " + cleanStrLower(inputReport2Record['passkey']) + " has mismatched site ID data DB Site_ID = " + dbKeyRecord[1] + " input file Client = " + cleanStrLower(inputReport2Record['client']))
if (inputSiteRecord['Account ID'] is not None
and len(inputSiteRecord['Account ID']) > 0
and inputSiteRecord['Account ID'] not in badAccountID
and "Copyright" not in inputSiteRecord['Account ID']
and "Generated" not in inputSiteRecord['Account ID']
and inputSiteRecord['BV Site: BV Site Name'] is not None):
# Check to see if we have seen this record
SQL = '''SELECT ID,
SF_ID,
SF_ID_18,
Name,
fk_Account_ID,
fk_Accounts_SF_ID_18
FROM Sites
WHERE ID = ?;'''
SQLdata = (cleanStrLower(inputSiteRecord['BV Site: BV Site Name']), )
dbReadCursor.execute(SQL, SQLdata)
dbSiteRecord = dbReadCursor.fetchone()
if dbSiteRecord is None:
#Record does not exist. Adding a new record.
uniqueKeyCount += 1
SQL = '''INSERT INTO Sites (ID,
SF_ID,
SF_ID_18,
Name,
fk_Account_ID,
fk_Accounts_SF_ID_18)
VALUES (?, ?, ?, ?, ?, ?);'''
SQLdata = (cleanStrLower(inputSiteRecord['BV Site: BV Site Name']),
cleanStr(inputSiteRecord['BV Site: ID']),
"",
