def build_soft(inservserial, oraconn):
sqlstmt = 'select distinct licensedesc,nvl(licensestatus,\'Valid\') from legacy.config_inserv_soft where to_char(inservserial)=\'' + str(
inservserial) + '\''
softcur = oracon.execSql(oraconn, sqlstmt)
newrow = []
ht = ''
for sftrow in softcur:
newrow.append(str(sftrow[0]) + ';' + str(sftrow[1]))
if len(newrow) > 0:
datString = string.join(newrow, '|')
headStr = 'Software,License'
ht += html.generateHtmlTableHeader(headStr, 0)
ht += html.generateHtmlDataTable(datString, 0)
else:
ht += ''
return ht
def getAlert(inservserial, oraconn):
sqlstmt = 'select alert_message from legacy.inservalert where to_char(inservserial)=\'' + str(
inservserial) + '\''
datString = ''
ht = ''
headStr = 'Alert'
ht += html.generateHtmlTableHeader(headStr, 1)
alertcur = oracon.execSql(oraconn, sqlstmt)
newrow = []
for arow in alertcur:
newrow.append(str(arow[0]))
if len(newrow) > 0:
datString = string.join(newrow, '|')
ht += html.generateHtmlDataTable(datString, 1)
else:
ht = ''
return ht
def getNumSystemsNearCapacity(oraconn):
sqlstmt = 'select count(distinct inservserial) from customer_inserv_space_proj where numdays<=30'
datString = ''
ht = ''
headStr = 'Alert'
ht += html.generateHtmlTableHeader(headStr, 1)
projcur = oracon.execSql(oraconn, sqlstmt)
newrow = []
for proj in projcur:
newrow.append(str(proj[0]))
if len(newrow) > 0:
datString = string.join(newrow, '|')
ht += html.generateHtmlDataTable(datString, 1)
else:
ht = ''
return ht
def build_pd(inservserial, oraconn):
ht = '<strong>Physical Disk Installed by Cage</strong>'
sqlstmt = 'select substr(cagepos,1,instr(cagepos,\':\',1,1)-1) cageid,pdtype,drivespeed,'
sqlstmt += 'capacity,count(1) numdrives from legacy.pdtype_info where to_char(inservserial)=\'' + str(
inservserial) + '\' group by pdtype,'
sqlstmt += 'substr(cagepos,1,instr(cagepos,\':\',1,1)-1),drivespeed,capacity order by 1'
pdtypecur = oracon.execSql(oraconn, sqlstmt)
newrow = []
for pdrow in pdtypecur:
newrow.append(
str(pdrow[0]) + ';' + pdrow[1] + ';' + str(pdrow[2]) + ';' +
str(pdrow[3]) + ';' + str(pdrow[4]))
if len(newrow) > 0:
datString = string.join(newrow, '|')
headStr = 'Cage Id,Drive Type,Drive Speed (in K rpm),Capacity in GB,# of Drives'
ht += html.generateHtmlTableHeader(headStr, 0)
ht += html.generateHtmlDataTable(datString, 0)
else:
ht += ''
return ht
def getLargestInstalledSys(oraconn):
sqlstmt = 'select company,inservserial,installdate,Round((total_space/1024)/1024,2),round((used_space/1024)/1024,2),rnk from ('
sqlstmt += 'select company,inservserial,installdate,total_space,(total_space-total_free_space) used_space,dense_rank() over(order by total_space desc) rnk from '
sqlstmt += '(select * from legacy.capacity_report where upper(company) not like \'%HEWLETT-PACKARD%\')) where rnk <=5'
lrgCur = oracon.execSql(oraconn, sqlstmt)
newrow = []
ht = ''
for lrgrow in lrgCur:
newrow.append(
str(lrgrow[0]) + ';' + str(lrgrow[1]) + ';' + str(lrgrow[2]) +
';' + str(lrgrow[3]) + ';' + str(lrgrow[4]) + ';' + str(lrgrow[5]))
if len(newrow) > 0:
datString = string.join(newrow, '|')
headStr = 'Company,Serial Number,Installation Date,Capacity (in Tb),Used Capacity (in Tb),Rank by size'
ht += html.generateHtmlTableHeader(headStr, 0)
ht += html.generateHtmlDataTable(datString, 0)
else:
ht += ''
return ht
def getMajorAlert(inservserial, oraconn):
sqlstmt = 'select a.alerttime, a.alerttype,trim(A.ALERTMSG),a.alertrepeatcount from legacy.alertnew a,'
sqlstmt += '(select inservserial,max(datadate) mxdt from legacy.alertnew group by inservserial) '
sqlstmt += 'b where upper(a.alertsvrty)=\'MAJOR\' and a.datadate=b.mxdt and a.inservserial=b.inservserial and to_char(a.inservserial)=\'' + str(
inservserial) + '\''
datString = ''
alertcur = oracon.execSql(oraconn, sqlstmt)
newrow = []
for arow in alertcur:
newrow.append(
str(arow[0]) + ';' + str(arow[1]) + ';' + str(arow[2]) + ';' +
str(arow[3]))
if len(newrow) > 0:
ht = '<strong>Latest Major Alerts</strong>'
headStr = 'Alert Time,Alert Type,Message,Repeats'
ht += html.generateHtmlTableHeader(headStr, 0)
datString = string.join(newrow, '|')
ht += html.generateHtmlDataTable(datString, 0)
else:
ht = ''
return ht
def getinfo(inservserial, oraconn):
sqlstmt = 'select * from (select distinct model,osversion,nvl(company,\'Unknown\') company,'
sqlstmt += 'min(nvl(installdate,(sysdate-(365*4)))) over (partition by inservserial),'
sqlstmt += 'rank() over (order by company) rnk '
sqlstmt += 'from legacy.capacity_report where to_char(inservserial)=\'' + str(
inservserial) + '\') where rnk=1'
headStr = ','
datString = ''
ht = ''
ht += html.generateHtmlTableHeader(headStr, 1)
infocur = oracon.execSql(oraconn, sqlstmt)
for inrec in infocur:
datString = 'Company :<strong>' + str(
inrec[2]) + '</strong>;Model :<strong>' + str(
inrec[0]) + '</strong>;Inform OS Version :<strong>' + str(
inrec[1]) + '</strong>'
if len(datString) > 0:
ht += html.generateHtmlDataTable(datString, 1)
else:
ht = ''
return ht
for vvrow in vvcur:
ht+='</td></tr><tr><td>';
ht+='# of VVs :'+str(vvrow[0]);
ht+='</td></tr></table><td><table><tr><td>';
sqlstmt='select pdtype,drivespeed,capacity,numdrives,round((total-free)/total * 100,2) percent_free from ';
sqlstmt+='(select pdtype,drivespeed,capacity,count(1) numdrives,sum(total) total,sum(free) free ';
sqlstmt+='from legacy.pdtype_info where inservserial=\''+str(row[0])+'\' group by pdtype,drivespeed,capacity)';
pdtypecur =oracon.execSql(oraconn,sqlstmt);
newrow=[];
for pdrow in pdtypecur:
newrow.append(pdrow[0]+';'+str(pdrow[1])+';'+str(pdrow[2])+';'+str(pdrow[3])+';'+str(pdrow[4]));
if len(newrow) > 0:
datString=string.join(newrow,'|')
headStr='Drive Type,Drive Speed (in K rpm),Capacity in GB,# of Drives,% Used';
ht+=html.generateHtmlTableHeader(headStr,0);
ht+=html.generateHtmlDataTable(datString);
else:
ht+='</tr></table>';
ht+='</td></tr><tr>';
sqlstmt='select substr(cagepos,1,instr(cagepos,\':\',1,1)-1) cageid,pdtype,drivespeed,';
sqlstmt+='capacity,count(1) numdrives from legacy.pdtype_info where inservserial=\''+str(row[0])+'\' group by pdtype,';
sqlstmt+='substr(cagepos,1,instr(cagepos,\':\',1,1)-1),drivespeed,capacity order by 1';
pdtypecur =oracon.execSql(oraconn,sqlstmt);
newrow=[];
for pdrow in pdtypecur:
newrow.append(str(pdrow[0])+';'+pdrow[1]+';'+str(pdrow[2])+';'+str(pdrow[3])+';'+str(pdrow[4]));
if len(newrow) > 0:
datString=string.join(newrow,'|')
headStr='Cage Id,Drive Type,Drive Speed (in K rpm),Capacity in GB,# of Drives';
# newrow.append('Hardware Contract: '+string.join(str(row[2]).split(';'),'<br/>'));
# newrow.append('Software Contract: '+string.join(str(row[3]).split(';'),'<br/>'));
newrow.append('Model: ' + str(row[6]))
newrow.append('OS: ' + string.join(str(row[7]).split(';'), ' '))
newrow.append('Number of Nodes: ' + str(row[8]))
newrow.append('Site Name: ' + str(row[9]))
newrow.append('System Status: ' + str(row[10]))
newrow.append('Country Code: ' + str(row[11]))
newrow.append('Location: ' + str(row[12]))
newrow.append('Postal Code: ' + str(row[13]))
newrow.append('City: ' + str(row[15]))
newrow.append('State: ' + str(row[16]))
newrow.append('Inserv Name: ' + str(row[17]))
dataString = string.join(newrow, '|')
ht += html.generateHtmlTableHeader('', 0)
ht += html.generateHtmlDataTable(dataString, 0)
ht += '</td></tr>\n</table>\n</td><td>\n<table>\n<tr><td>\n<table><td>'
ht += report.getInservSpaceUtil(oraconn, row[0])
ht += '</td><td>'
ht += report.build_disktype_free_trend(row[0], oraconn)
ht += '</td></table></td></tr>\n<tr><td>'
ht += report.getutilByDiskType(oraconn, row[0])
ht += '</td></tr></table>\n<td>'
ht += report.build_soft(row[0], oraconn)
ht += '</td>\n</table>\n'
ht += '<hr color=red>'
tabList = 'Layout;Alerts;Drives;Performance'
ht += html.buildtab(tabList)
#addtabContent(id,tabval);