def GetCSUploadInterval(self):
print __name__ + "Start"
sqlQuery = "select COUNT(*) AS 'TotalCSCount' from cf_SurveyResultFunc(2,0,1,NULL,NULL)"
TotalCSCount = 0
countTable = Table()
if self._bOpenConnectionSurvey == True and self._dbSurvey.Execute(
sqlQuery, countTable) == True:
countTable.MoveFirst()
if countTable.GetErrorErrStr() != "Success":
print 'Failed to Get TotalCSCount'
else:
GetSucces, TotalCSCount = countTable.Get('TotalCSCount')
if GetSucces == False:
print __name__ + "Failed to get Total Commcell Count"
sqlQuery = "select COUNT(*) AS 'InSevenDayCSCount' from cf_SurveyResultFunc(2,0,1,NULL,NULL)WHERE DATEDIFF(day,Logdate,getutcdate()) <= 7"
InSevenDayCSCount = 0
countTable = Table()
if self._bOpenConnectionSurvey == True and self._dbSurvey.Execute(
sqlQuery, countTable) == True:
countTable.MoveFirst()
if countTable.GetErrorErrStr() != "Success":
print 'Failed to Get InSevenDayCSCount'
else:
GetSucces, InSevenDayCSCount = countTable.Get(
'InSevenDayCSCount')
if GetSucces == False:
print __name__ + "Failed to get Commcell count which beamed data in last seven day Count"
sqlQuery = "select COUNT(*) AS 'WithinMonthCSCount' from cf_SurveyResultFunc(2,0,1,NULL,NULL)WHERE DATEDIFF(day,Logdate,getutcdate()) BETWEEN 8 AND 30"
WithinMonthCSCount = 0
countTable = Table()
if self._bOpenConnectionSurvey == True and self._dbSurvey.Execute(
sqlQuery, countTable) == True:
countTable.MoveFirst()
if countTable.GetErrorErrStr() != "Success":
print 'Failed to Get WithinMonthCSCount'
else:
GetSucces, WithinMonthCSCount = countTable.Get(
'WithinMonthCSCount')
if GetSucces == False:
print __name__ + "Failed to get Commcell count which beamed data in within the last month but greater than seven days"
sqlQuery = "select COUNT(*) AS 'GreaterThanMonthCSCount' from cf_SurveyResultFunc(2,0,1,NULL,NULL)WHERE DATEDIFF(day,Logdate,getutcdate()) BETWEEN 30 AND 90"
GreaterThanMonthCSCount = 0
countTable = Table()
if self._bOpenConnectionSurvey == True and self._dbSurvey.Execute(
sqlQuery, countTable) == True:
countTable.MoveFirst()
if countTable.GetErrorErrStr() != "Success":
print 'Failed to Get GreaterThanMonthCSCount'
else:
GetSucces, GreaterThanMonthCSCount = countTable.Get(
'GreaterThanMonthCSCount')
if GetSucces == False:
print __name__ + "Failed to get Commcell count which beamed data before a month"
sqlQuery = "select COUNT(*) AS 'GreaterThanThreeMonthCSCount' from cf_SurveyResultFunc(2,0,1,NULL,NULL)WHERE DATEDIFF(day,Logdate,getutcdate()) > 90"
GreaterThanThreeMonthCSCount = 0
countTable = Table()
if self._bOpenConnectionSurvey == True and self._dbSurvey.Execute(
sqlQuery, countTable) == True:
countTable.MoveFirst()
if countTable.GetErrorErrStr() != "Success":
print 'Failed to Get GreaterThanThreeMonthCSCount'
else:
GetSucces, GreaterThanThreeMonthCSCount = countTable.Get(
'GreaterThanThreeMonthCSCount')
if GetSucces == False:
print __name__ + "Failed to get Commcell count which beamed data before a month"
strComcellOverview = '<p>Below is the overview of the number of Commcells that Uploaded Data.</p>'
strComcellOverview += '<p>Total Commcell Count is <strong>%d</h4></strong>' % TotalCSCount
strComcellOverview += '<table border="1"><tr><th>Days Since Last Upload</th><th>Count</th></tr>'
strComcellOverview += '<tr><td>Within 7 days</td><td><font color="green"><strong>%d</strong></font></td></tr>' % InSevenDayCSCount
strComcellOverview += '<tr><td>Within 30 days but over 7 days</td><td><font color="orange"></strong>%d<strong></font></td></tr>' % WithinMonthCSCount
strComcellOverview += '<tr><td>Within 90 days but over 30 days</td><td><font color="red"><strong>%d</strong></font></td></tr>' % GreaterThanMonthCSCount
strComcellOverview += '<tr><td>Over 90 days</td><td><font color="red"><strong>%d</strong></font></td></tr>' % GreaterThanThreeMonthCSCount
strComcellOverview += '</table>'
sqlQuery = "SELECT BaseTable.CommServUniqueId, \
BaseTable.CommCellID, BaseTable.CustomerName, BaseTable.CommServVersion, CONVERT(varchar(50),BaseTable.logdate, 106) AS 'Last Upload Time', \
DATEDIFF(day,BaseTable.Logdate,getutcdate()) AS 'Days Since No Upload', \
case when HealthTable.CommServUniqueId IS NULL then 'No' else 'Yes' end as 'Health Check' \
FROM cf_SurveyResultFunc(2,0,1,NULL,NULL) as BaseTable \
LEFT OUTER JOIN ( select * from cf_SurveyResultFunc(16,0,1,NULL,NULL) )HealthTable ON \
BaseTable.CommServUniqueId = HealthTable.CommServUniqueId \
WHERE DATEDIFF(day,BaseTable.Logdate,getutcdate()) BETWEEN 8 AND 90 ORDER BY 'Days Since No Upload'"
strComcellUploadInfo = None
resultTable = Table()
if self._bOpenConnectionSurvey == True and self._dbSurvey.Execute(
sqlQuery, resultTable) == True:
count = 1
while True:
if count == 1:
resultTable.MoveFirst()
if resultTable.GetErrorErrStr() != "Success":
print 'No Commcell that did not upload data Before 7 days'
break
else:
strComcellUploadInfo = '<p>Below is the list of Commcells that beamed data before a week.</p><table border="1"><tr><th>CommCell UniqueID</th><th>CommCell ID</th><th>Customer Name</th><th>Version</th><th>Last Upload Time</th><th>Days Since No Upload</th></tr>'
else:
resultTable.MoveNext()
if resultTable.GetErrorErrStr() != "Success":
print 'Done with all Installed CS'
break
strComcellUploadInfo += '<tr>'
cs = CSInfo()
GetSuccess, cs.CCId = resultTable.Get('CommCellID')
if GetSuccess == False:
print __name__ + "Failed to get CS CommCell ID"
continue
else:
strComcellUploadInfo += '<td>'
strComcellUploadInfo += str(cs.CCId)
strComcellUploadInfo += '</td>'
GetSuccess, cs.CustomerName = resultTable.Get('CustomerName')
GetSuccess, cs.CCUId = resultTable.Get('CommServUniqueId')
if GetSuccess == False:
print __name__ + "Failed to get CommCell Customer Name"
continue
else:
strComcellUploadInfo += '<td><a href="http://clouddriver/webconsole/survey/reports/commcellmonitoring.jsp?commUniId='
strComcellUploadInfo += str(cs.CCUId)
strComcellUploadInfo += '"><span style="color:#008ACD;text-decoration:none">'
strComcellUploadInfo += cs.CustomerName
strComcellUploadInfo += '</span></a></td>'
GetSucces, cs.Version = resultTable.Get('CommServVersion')
if GetSucces == False:
print __name__ + "Failed to get Version"
continue
else:
strComcellUploadInfo += '<td>'
strComcellUploadInfo += cs.Version
strComcellUploadInfo += '</td>'
GetSucces, cs.LastUploadDate = resultTable.Get(
'Last Upload Time')
if GetSucces == False:
print __name__ + "Failed to get Last Upload date"
continue
else:
strComcellUploadInfo += '<td>'
strComcellUploadInfo += cs.LastUploadDate
strComcellUploadInfo += '</td>'
GetSucces, cs.NoOfDaysSinceUpload = resultTable.Get(
'Days Since No Upload')
if GetSucces == False:
print __name__ + "Failed to get Number of days of upload"
continue
else:
strComcellUploadInfo += '<td>'
strComcellUploadInfo += str(cs.NoOfDaysSinceUpload)
strComcellUploadInfo += '</td>'
GetSuccess, cs.HealthCheck = resultTable.Get('Health Check')
if GetSuccess == False:
print __name__ + "Failed to get Health Check"
continue
else:
strComcellUploadInfo += '<td>'
strComcellUploadInfo += str(cs.HealthCheck)
strComcellUploadInfo += '</td>'
strComcellUploadInfo += '</tr>'
count = count + 1
cs = None
if count > 1:
strComcellUploadInfo += '</table>'
else:
strComcellUploadInfo = 'There are No Commcells that have not beamed data in the last 7 days'
sendEmailTo = []
try:
sendEmailTo = os.path.dirname(
os.path.realpath(__file__)) + '\EmailList.txt'
with open(sendEmailTo) as IPs:
sendEmailTo = IPs.read().splitlines()
except IOError:
sendEmailTo = ['']
print 'EmailList.txt... Procceeding with email ...'
text = emailFormat.replace('#OverviewCommCell#',
str(strComcellOverview))
subject = 'Information about the Commcells Last Data Upload'
text = text.replace('#subject#', subject)
attachText = attachFormat.replace('#IndiviualCommCellInformation#',
str(strComcellUploadInfo))
with open("listOfComcells.html", "w") as fs:
fs.write(attachText)
files = ['listOfComcells.html']
send_mail(self._smtpServer, '', sendEmailTo, subject,
text.encode('utf-8'), files)
print __name__ + 'Email Sent'
print 'End'
return "Success"
