def delAWSTable(db, aws):
cur = db.cursor()
tableName = pwdCaller(aws)['data']['table']
sql = 'DELETE FROM ' + tableName + ' WHERE Location = "%s"' % (aws)
cur.execute(sql)
db.commit()
cur.close()
return
def getdata(db, aws):
cur = db.cursor()
tableName = pwdCaller(aws)['data']['table']
sql = 'SELECT userID,userName, company, created, lastLogin, ticket, vpnUser from ' + tableName + ' WHERE Location="%s"' % (aws)
cur.execute(sql)
rows = list(cur)
db.commit()
cur.close()
return rows
def confluenceConnector(conflConnector):
options = {
'server': conflConnector['url'],
'verify': conflConnector['verify']
}
keyValue = pwdCaller('officeLdap')['data']
confl = Confluence(profile='confluence',
username=keyValue['user'],
password=keyValue['password'])
return confl
def insdata(aList,db,aws):
tableName = pwdCaller(aws)['data']['table']
cur = db.cursor()
for elem in aList:
sql = 'SELECT * FROM ' + tableName + ' WHERE userID = "%s" AND Location = "%s"' % (elem['userID'],aws)
if (cur.execute(sql)) > 0:
sql1 = 'UPDATE ' + tableName + ' SET lastLogin="******" WHERE userID="%s" and Location = "%s"' % (elem['lastLogin'], elem['userID'],aws)
cur.execute(sql1)
else:
sql1 = 'INSERT INTO ' + tableName + ' (uuid,Location, userID, userName, company, created, lastLogin, ticket, vpnUser,memberOf) VALUES ("uuid","%s","%s","%s","%s","%s","%s","%s", "%d","%s")' % (elem['location'], elem ['userID'], elem['userName'],elem['company'], elem['Ctime'],elem['lastLogin'],elem['ticket'], elem['VPN'],elem['memberOf'])
cur.execute(sql1)
db.commit()
cur.close()
return
def createJIRATicket(desc, jiraConnector):
options = {
'server': jiraConnector['url'],
'verify': jiraConnector['verify']
}
keyValue = pwdCaller('officeLdap')['data']
jira = JIRA(options, basic_auth=(keyValue['user'], keyValue['password']))
qType = 'Daily Audit'
new_issue = jira.create_issue(project=jiraConnector['project'],
summary=qType,
description=desc,
issuetype={'name': 'Task'})
jira.add_watcher(new_issue.id, jiraConnector['watcher'])
return
def searchJIRATicket(aws):
AWSIDPattern = re.compile('[0-9]{12}')
jiraConnector = pwdCaller('jira')['data']
AWSList = []
options = {
'server':jiraConnector['url'],
'verify':jiraConnector['verify']
}
tableName = pwdCaller(aws)['data']['table']
keyValue = pwdCaller('officeLdap')['data']
jira = JIRA(options, basic_auth=(keyValue['user'],keyValue['password']))
exam = jira.search_issues('project='+jiraConnector['project']+' AND issuetype = '+jiraConnector['issuetype']+' AND "'+jiraConnector['subissuetype']+'"="'jiraConnector['subissuetypecontent']+'" AND status=Resolved',maxResults=100)
for issue in exam:
tDict = {}
tDict['ticket'] = str(issue)
tDict['Owner'] = issue.fields.customfield_11811
tDict['Category'] = str(issue.fields.customfield_11807)
tDict['AccountID'] = issue.fields.customfield_11819.replace(' ','')
if AWSIDPattern.match(tDict['AccountID']):
AWSList.append(tDict)
#Call DB handler
db = DBConnector()
cur = db.cursor()
if len(AWSList) == 0:
print AWSList
else:
sql = 'DELETE from '+tableName+';'
cur.execute(sql)
for eAWS in AWSList:
sql = 'INSERT INTO '+tableName+' (AWSAccountName, JiraTicket, AccountID, AWSCategory) values ("%s", "%s", "%s", "%s");' % (eAWS['Owner'], eAWS['ticket'],eAWS['AccountID'],eAWS['Category'])
cur.execute(sql)
cur.close()
db.commit()
db.close()
return
def OfficeQuerymain(db):
keyValue = pwdCaller('officeLdapBind')['data']
try:
ldap.set_option(ldap.OPT_X_TLS_REQUIRE_CERT, ldap.OPT_X_TLS_NEVER)
l = ldap.initialize(keyValue['url'])
l.set_option(ldap.OPT_REFERRALS, 0)
l.set_option(ldap.OPT_PROTOCOL_VERSION, 3)
l.set_option(ldap.OPT_X_TLS, ldap.OPT_X_TLS_DEMAND)
l.set_option(ldap.OPT_X_TLS_DEMAND, True)
l.set_option(ldap.OPT_DEBUG_LEVEL, 255)
l.simple_bind
l.set_option
l.protocol_version = ldap.VERSION3
username = keyValue['username']
password = keyValue['password']
l.simple_bind(username, password)
l.result()
except ldap.LDAPError, e:
print e
def IAMQuerymain(db, aws):
newSet = []
mfaSet={}
print aws
keyValue = pwdCaller(aws)['data']
TZ = keyValue['timezone']
client = AWSClient(aws, 'iam', keyValue['region'])
uList = client.list_users()['Users']
for user in uList:
aList = {}
userID = user['UserName']
cn = ''
Ctime= user['CreateDate'].astimezone(TZ).strftime("%Y%m%d")
Company = ''
ticket=''
VPN = 0
lastLogon = 'Never Logged in'
if user.has_key('PasswordLastUsed'):
lastLogon = user['PasswordLastUsed'].astimezone(TZ).strftime("%Y%m%d%H%M")
try:
VPN = mfaChecker(client, userID)
except ClientError :
VPN = 0
aList['location'] = aws
aList['userID'] = userID
aList['userName'] = cn
aList['company'] = Company
aList['ticket'] = ticket
aList['Ctime'] = Ctime
aList['lastLogin'] = lastLogon
aList['VPN'] = VPN
aList['memberOf'] = ''
newSet.append(aList)
if VPN == 0:
mfaSet.update({aList['userID']:aList})
final = compareUsers(getdata(db,aws),newSet,mfaSet)
delAWSTable(db,aws)
insdata(newSet,db,aws)
return final
def writeAuditReport(fileJSON):
db = DBConnector()
awsAccountIDList = getAWSAccountID(db)
db.close()
conflConnector = pwdCaller(Confluence)['data']
jiraConnector = pwdCaller(JIRA)['data']
fhM = open(fileJSON, 'r+')
oData = json.load(fhM)
summary = {}
raw = """h2.Summary
This page contains daily audit.
- AD/AWS account
"""
for key0, value0 in oData.iteritems():
for key1, value1 in value0.iteritems():
for key2, value2 in value1.iteritems():
if len(value2) > 0:
if key0 in summary.keys():
if key1 in summary[key0].keys():
summary[key0][key1][key2] = len(value2)
else:
summary[key0][key1] = {}
summary[key0][key1][key2] = len(value2)
else:
summary[key0] = {}
summary[key0][key1] = {}
summary[key0][key1][key2] = len(value2)
summary = {}
summary['OfficeADAudit'] = {}
summary['OfficeADAudit']['created'] = len(
oData['OfficeADAudit']['created'])
summary['OfficeADAudit']['removed'] = len(
oData['OfficeADAudit']['removed'])
summary['OfficeADAudit']['updated'] = len(
oData['OfficeADAudit']['updated'])
for AWSAccount in awsAccountIDList:
AWSAccountID = AWSAccount['AccountID']
AWSAccountName = AWSAccount['AWSAccountName']
summary[AWSAccountName] = {'mfaSet': 0, 'removed': 0, 'created': 0}
summary[AWSAccountName]['mfaSet'] = len(
oData[AWSAccountName]['mfaSet'])
summary[AWSAccountName]['removed'] = len(
oData[AWSAccountName]['removed'])
summary[AWSAccountName]['created'] = len(
oData[AWSAccountName]['created'])
jiraRaw = """
AD Accounts
|| Location || Created || removed ||Updated ||
| Office | """ + str(summary['OfficeADAudit']['created']) + " | " + str(
summary['OfficeADAudit']['removed']) + " | " + str(
summary['OfficeADAudit']['updated']) + " |" + """
AWS Account
|| AWS || created || removed || mfaSet ||
"""
for AWSAccount in awsAccountIDList:
AWSAccountID = AWSAccount['AccountID']
AWSAccountName = AWSAccount['AWSAccountName']
AWSCategory = AWSAccount['AWSCategory']
try:
jiraRaw = jiraRaw + """ | """ + AWSAccountName + "_" + AWSCategory + " | " + str(
summary[AWSAccountName]['created']) + " | " + str(
summary[AWSAccountName]['removed']) + " | " + str(
summary[AWSAccountName]['mfaSet']) + " |" + """
"""
except KeyError:
continue
jiraRaw = """Confluence page: """ + conflConnector[
'url'] + """/display/""" + conflConnector[
'space'] + """/Daily+audit+report
""" + jiraRaw
createJIRATicket(jiraRaw, jiraConnector)
teamEvents = {}
fhM.close()
############################
# Confluence #
############################
confl = confluenceConnector(conflConnector)
token = confl._token
server = confl._server
conflConnector = pwdCaller(Confluence)['data']
parent_id = conflConnector['pID']
space = conflConnector['space']
title = conflConnector['title']
try:
existing_page = confl.storePageContent(title, space, raw)
except:
write_page(server, token, space, title, raw, parent_id)
return
