def getNumDisks(oraconn):
sqlstmt = ''
sqlstmt += 'select * from (select \'FC\' DiskType, round((fc_disks/total_disks*100),2) percent from '
sqlstmt += ' (select sum(total_disks) total_disks,sum(fc_num) fc_disks from legacy.capacity_report where total_disks is not null) union '
sqlstmt += ' select \'NL\' DiskType,round((nl_disks/total_disks*100),2) percent from '
sqlstmt += ' (select sum(total_disks) total_disks,sum(nl_num) nl_disks from legacy.capacity_report where total_disks is not null) '
sqlstmt += ' union select \'SSD\' DiskType,round((ssd_disks/total_disks*100),2) percent from '
sqlstmt += ' (select sum(total_disks) total_disks,sum(ssd_num) ssd_disks from legacy.capacity_report where total_disks is not null))'
datString = ''
ht = ''
newrow = []
headrow = []
diskcur = oracon.execSql(oraconn, sqlstmt)
for diskrec in diskcur:
headrow.append(str(diskrec[0]))
newrow.append(str(diskrec[1]))
headStr = string.join(headrow, ' , ')
datStr = string.join(newrow, ',')
xmdat = html.generateXmlPie(datStr, headStr, '', 1, 1, 30)
ht += '<div id="disknumpiediv" align="center">Number of Disks by Disk Type</div>\n'
ht += '<script type="text/javascript">\n'
ht += '\tvar chart = new FusionCharts("../FusionChartsFree/Charts/FCF_Bar2D.swf", "ChartId", "550", "300");\n'
ht += '\tchart.setDataXML("' + xmdat + '");\n'
ht += '\tchart.render("disknumpiediv");\n'
ht += '\t</script>\n'
return ht
def getPenetrationByModel(oraconn):
sqlstmt = ''
sqlstmt += 'select m,round(ct/tct*100,2) from (select substr(model,1,1) m,count(1) ct from legacy.capacity_report '
sqlstmt += ' where model!=\'Unknown\' group by substr(model,1,1)) a,(select count(1) tct from legacy.capacity_report where model!=\'Unknown\') b'
mcur = oracon.execSql(oraconn, sqlstmt)
newrow = []
headrow = []
for rrec in mcur:
headrow.append(str(rrec[0]) + ' - Class')
newrow.append(str(rrec[1]))
if len(newrow) > 0:
headStr = string.join(headrow, ' , ')
datStr = string.join(newrow, ',')
xmdat = html.generateXmlPie(
datStr, headStr, '', 1, 1, 30,
'FF0000,33FF00,AFD8F8,F6BD0F,8BBA00,FF8E46,008E8E,D64646,8E468E,588526,B3AA00,008ED6,9D080D'
)
ht = ''
ht += '<div id="mdiv" align="center">Space by Raid Type</div>\n'
ht += '<script type="text/javascript">\n'
ht += '\tvar chart = new FusionCharts("../FusionChartsFree/Charts/FCF_Pie2D.swf", "ChartId", "400", "300");\n'
ht += '\tchart.setDataXML("' + xmdat + '");\n'
ht += '\tchart.render("mdiv");\n'
ht += '\t</script>\n'
return ht
else:
return ''
def getSystemsByCountry(oraconn):
sqlstmt = 'select country,sum(num_inserv) num_inserv from '
sqlstmt += ' (select country,num_inserv from ('
sqlstmt += ' select nvl(country,\'Unknown\') country,count(distinct inservserial) num_inserv'
sqlstmt += ' from legacy.capacity_report group by nvl(country,\'Unknown\')'
sqlstmt += ' )) group by country order by 2 desc'
datString = ''
ht = ''
newrow = []
headrow = []
diskcur = oracon.execSql(oraconn, sqlstmt)
for diskrec in diskcur:
headrow.append(str(diskrec[0]))
newrow.append(str(diskrec[1]))
headStr = string.join(headrow, ' , ')
datStr = string.join(newrow, ',')
xmdat = html.generateXmlPie(datStr, headStr, '', 1, 1, 30)
ht += '<div id="Countrypiediv" align="center">Systems by Country</div>\n'
ht += '<script type="text/javascript">\n'
ht += '\tvar chart = new FusionCharts("../FusionChartsFree/Charts/FCF_Column2D.swf", "ChartId", "3500", "300");\n'
ht += '\tchart.setDataXML("' + xmdat + '");\n'
ht += '\tchart.render("Countrypiediv");\n'
ht += '\t</script>\n'
return ht
def getInservSpaceByRaidType(oraconn):
sqlstmt = ''
sqlstmt += 'SELECT \'Raid \'||RAIDTYPE,PERCENTALLOC FROM LEGACY.PERCENTSPACEBYRAIDTYPE'
raidcur = oracon.execSql(oraconn, sqlstmt)
newrow = []
headrow = []
for rrec in raidcur:
headrow.append(str(rrec[0]))
newrow.append(str(rrec[1]))
if len(newrow) > 0:
headStr = string.join(headrow, ' , ')
datStr = string.join(newrow, ',')
xmdat = html.generateXmlPie(
datStr, headStr, '', 1, 1, 30,
'FF0000,33FF00,AFD8F8,F6BD0F,8BBA00,FF8E46,008E8E,D64646,8E468E,588526,B3AA00,008ED6,9D080D'
)
ht = ''
ht += '<div id="raiddiv" align="center">Space by Raid Type</div>\n'
ht += '<script type="text/javascript">\n'
ht += '\tvar chart = new FusionCharts("../FusionChartsFree/Charts/FCF_Pie2D.swf", "ChartId", "400", "300");\n'
ht += '\tchart.setDataXML("' + xmdat + '");\n'
ht += '\tchart.render("raiddiv");\n'
ht += '\t</script>\n'
return ht
else:
return ''
def getCapacityInstalledByDiskType(oraconn):
sqlstmt = 'SELECT \'FC\',round(sum(nvl(FC_TOTAL_SIZE_MB,0))/sum(total_space) * 100,2) FROM LEGACY.CAPACITY_REPORT UNION'
sqlstmt += ' SELECT \'NL\',round(sum(nvl(NL_TOTAL_SIZE_MB,0))/sum(total_space) * 100,2) FROM LEGACY.CAPACITY_REPORT UNION'
sqlstmt += ' SELECT \'SSD\',round(sum(nvl(SSD_TOTAL_SIZE_MB,0))/sum(total_space) * 100,2) FROM LEGACY.CAPACITY_REPORT'
datString = ''
ht = ''
newrow = []
headrow = []
diskcur = oracon.execSql(oraconn, sqlstmt)
for diskrec in diskcur:
headrow.append(str(diskrec[0]))
newrow.append(str(diskrec[1]))
headStr = string.join(headrow, ' , ')
datStr = string.join(newrow, ',')
xmdat = html.generateXmlPie(datStr, headStr, '', 1, 1, 30)
ht += '<div id="diskpiediv" align="center">Installed Capacity By Disk Type</div>\n'
ht += '<script type="text/javascript">\n'
ht += '\tvar chart = new FusionCharts("../FusionChartsFree/Charts/FCF_Bar2D.swf", "ChartId", "400", "300");\n'
ht += '\tchart.setDataXML("' + xmdat + '");\n'
ht += '\tchart.render("diskpiediv");\n'
ht += '\t</script>\n'
return ht
def getSysByPecentUsed(oraconn):
sqlstmt = 'select capacity_used_tier,round((num_inserv/total_ins)*100,2) percent_ins from'
sqlstmt += '(select capacity_used_tier,count(distinct inservserial) Num_inserv from ('
sqlstmt += 'select inservserial, case when percent_utilized > 90 then \' 90% - 100%\''
sqlstmt += ' when percent_utilized between 80 and 90 then \' 80% - 90%\' '
sqlstmt += ' when percent_utilized between 70 and 80 then \' 70% - 80%\''
sqlstmt += ' when percent_utilized between 60 and 70 then \' 60% - 70%\''
sqlstmt += ' when percent_utilized between 50 and 60 then \' 50% - 60%\''
sqlstmt += ' when percent_utilized between 40 and 50 then \' 40% - 50%\''
sqlstmt += ' when percent_utilized between 30 and 40 then \' 30% - 40%\''
sqlstmt += ' when percent_utilized between 20 and 30 then \' 20% - 30%\''
sqlstmt += ' else \' 0% - 20%\' end capacity_used_tier'
sqlstmt += ' from legacy.capacity_report where total_space > 0) group by capacity_used_tier) a,'
sqlstmt += '(select count(distinct inservserial) total_ins from legacy.capacity_report where total_space > 0) b '
datString = ''
ht = ''
newrow = []
headrow = []
capuscur = oracon.execSql(oraconn, sqlstmt)
for caprec in capuscur:
headrow.append(str(caprec[0]))
newrow.append(str(caprec[1]))
headStr = string.join(headrow, ' , ')
datStr = string.join(newrow, ',')
xmdat = html.generateXmlPie(datStr, headStr, '', 1, 1, 30)
ht += '<div id="newpiediv" align="center">Inform Os Across Install Base</div>\n'
ht += '<script type="text/javascript">\n'
ht += '\tvar chart = new FusionCharts("../FusionChartsFree/Charts/FCF_Bar2D.swf", "ChartId", "500", "300");\n'
ht += '\tchart.setDataXML("' + xmdat + '");\n'
ht += '\tchart.render("newpiediv");\n'
ht += '\t</script>\n'
return ht
def getExecReportOsRatio(oraconn):
sqlstmt = 'select os,round((count_by_os/total_count)*100,2) os_pers from '
sqlstmt += ' (select nvl(os,\'Unknown\') os,count(distinct inservserial) count_by_os from '
sqlstmt += ' (select case when os like \'2.1%\' or os =\'2.2.1\' or os =\'2.2.2\' or os=\'2.2.3\' then \'less than 2.2.4\' else os end os,inservserial from '
sqlstmt += ' (select a.inservserial,substr(trim(nvl(substr(os_rel,1,5),substr(osver,1,5))),1,5) os from legacy.all_inserv_master a left join '
sqlstmt += ' (select distinct inservserial,case when component like \'%2%\' then trim(substr(component,length(\'kernel\')+1,length(component)))||\' \'||componentver '
sqlstmt += ' when component like \'%3%\' then trim(substr(component,length(\'kernel\')+1,length(component)))||\' \'||componentver '
sqlstmt += ' else componentver end osver '
sqlstmt += ' from legacy.ver_max where upper(ver_max.component) like \'KERNEL%\') b '
sqlstmt += ' on to_char(a.inservserial)=to_char(b.inservserial))) group by nvl(os,\'Unknown\')) sub, '
sqlstmt += ' (select count(distinct inservserial) total_count from legacy.all_inserv_master) tot '
datString = ''
ht = ''
oscur = oracon.execSql(oraconn, sqlstmt)
newrow = []
headrow = []
for orec in oscur:
headrow.append(str(orec[0]))
newrow.append(str(orec[1]))
headStr = string.join(headrow, ' , ')
datStr = string.join(newrow, ',')
xmdat = html.generateXmlPie(datStr, headStr, '', 0)
ht += '<div id="piediv" align="center">Inform Os Across Install Base</div>\n'
ht += '<script type="text/javascript">\n'
ht += '\tvar chart = new FusionCharts("../FusionChartsFree/Charts/FCF_Pie2D.swf", "ChartId", "400", "300");\n'
ht += '\tchart.setDataXML("' + xmdat + '");\n'
ht += '\tchart.render("piediv");\n'
ht += '\t</script>\n'
oscur.close()
return ht
def getInservSpaceUtil(oraconn, inservserial):
ht = ''
sqlstmt = 'select count(1) from legacy.capacity_report where inservserial=\'' + str(
inservserial) + '\''
checkcur = oracon.execSql(oraconn, sqlstmt)
for ckcur in checkcur:
if ckcur[0] == 0:
return ''
sqlstmt = 'select nvl(total_space,0) from legacy.capacity_report where inservserial=\'' + str(
inservserial) + '\''
checkcur = oracon.execSql(oraconn, sqlstmt)
for ckcur in checkcur:
if ckcur[0] == 0:
return ''
sqlstmt = 'select round(((total_space-total_free_space)/total_space)*100,2) used_pers,round((total_free_space/total_space)*100,2) free_pers from'
sqlstmt += '(Select distinct total_space,total_free_space from legacy.capacity_report where inservserial=\'' + inservserial + '\')'
capcur = oracon.execSql(oraconn, sqlstmt)
for caprec in capcur:
datStr = str(caprec[0]) + ',' + str(caprec[1])
colStr = 'FF0000,33FF00'
headStr = '%Used Space,%Free Space'
xmdat = html.generateXmlPie(datStr, headStr, '', 1, 0, 50, colStr)
ht += '<fieldset><legend>Capacity Utilization</legend>'
ht += '<div id="piediv" align="center"></div>\n'
ht += '<script type="text/javascript">\n'
ht += '\tvar chart = new FusionCharts("../FusionChartsFree/Charts/FCF_Pie2D.swf", "ChartId", "300", "150");\n'
ht += '\tchart.setDataXML("' + xmdat + '");\n'
ht += '\tchart.render("piediv");\n'
ht += '\t</script>\n</fieldset>\n'
ht += '</td>\n<td>\n'
return ht
def getOsByQtr(oraconn):
sqlstmt = 'select \'Q\'||\' \'||to_char(config_date,\'YYYYQ\') qtr,substr(b.model,1,1)||\'-CLASS\' model,a.attr_value,'
sqlstmt += ' count(distinct a.inservserial) num_inserv,to_char(config_date,\'YYYYQ\') from legacy.INSERV_CONFIG_FIX a, legacy.all_inserv_master b '
sqlstmt += ' where trim(a.inservserial)=trim(b.inservserial) '
sqlstmt += ' group by \'Q\'||\' \'||to_char(config_date,\'YYYYQ\'),substr(b.model,1,1)||\'-CLASS\',a.attr_value,to_char(config_date,\'YYYYQ\') order by 1,3,2'
oscatcur = oracon.execSql(oraconn, sqlstmt)
headrow = []
newrow = []
datstr = ''
headstr = ''
ht = ''
for osrec in oscatcur:
osverfl = osrec[4] + osrec[2] + '.html'
osfl = open(osverfl, 'w')
isqlstmt = 'select \'Q\'||\' \'||to_char(config_date,\'YYYYQ\') qtr,substr(b.model,1,1)||\'-CLASS\' model,a.attr_value,'
isqlstmt += ' count(distinct a.inservserial) num_inserv from legacy.INSERV_CONFIG_FIX a, legacy.all_inserv_master b '
isqlstmt += 'where trim(a.inservserial)=trim(b.inservserial) and to_char(config_date,\'YYYYQ\')=\'' + osrec[
4] + '\' and ATTR_VALUE=\'' + osrec[2] + '\''
isqlstmt += ' group by \'Q\'||\' \'||to_char(config_date,\'YYYYQ\'),substr(b.model,1,1)||\'-CLASS\',a.attr_value order by 1,3'
suboscur = oracon.execSql(oraconn, isqlstmt)
for subrec in suboscur:
headrow.append(str(subrec[1]))
newrow.append(str(subrec[3]))
headStr = string.join(headrow, ' , ')
datStr = string.join(newrow, ',')
caption = 'Q ' + osrec[4] + ' for Inform OS ' + osrec[2]
xmdat = html.generateXmlPie(datStr=datStr,
tagStr=headStr,
capt=caption,
shownames=1,
isbar=1,
yaxismaxvalue=2000)
ht += '<head>'
ht += '<script language="JavaScript" src="../FusionChartsFree/JSClass/FusionCharts.js"></script>'
ht += '</head>'
ht += '<body>'
ht += '<div id="osmodel" align="center">Quarter ' + osrec[
0] + ' OS by Model</div>\n'
ht += '<script type="text/javascript">\n'
ht += '\tvar chart = new FusionCharts("../FusionChartsFree/Charts/FCF_Bar2D.swf", "ChartId", "700", "300");\n'
ht += '\tchart.setDataXML("' + xmdat + '");\n'
ht += '\tchart.render("osmodel");\n'
ht += '\t</script>\n'
ht += '</body>'
ht += '</html>'
osfl.write(ht)
osfl.close()
ht = ''
shutil.move(osverfl, '/var/www/html/exec/' + osverfl)
headrow = []
newrow = []
datstr = ''
headstr = ''
ht += report.getinfo(row[0], oraconn)
ht += '</tr></td>'
ht += '<tr><td>'
ht += '<img src="3par_arrays.jpg" alt="Inserv" height="400" width="350" />'
ht += '</td><td>'
sqlstmt = 'select round(((total_space-total_free_space)/total_space)*100,2) used_pers,round((total_free_space/total_space)*100,2) free_pers from'
sqlstmt += '(Select distinct total_space,total_free_space from legacy.capacity_report where inservserial=\'' + str(
row[0]) + '\')'
capcur = oracon.execSql(oraconn, sqlstmt)
for caprec in capcur:
datStr = str(caprec[0]) + ',' + str(caprec[1])
colStr = 'FF0000,33FF00'
headStr = '%Used Space,%Free Space'
xmdat = html.generateXmlPie(datStr, headStr, '', 1, 0, 50, colStr)
ht += '<fieldset><legend>Capacity Utilization</legend>'
ht += '<div id="piediv" align="center"></div>\n'
ht += '<script type="text/javascript">\n'
ht += '\tvar chart = new FusionCharts("../FusionChartsFree/Charts/FCF_Pie2D.swf", "ChartId", "400", "150");\n'
ht += '\tchart.setDataXML("' + xmdat + '");\n'
ht += '\tchart.render("piediv");\n'
ht += '\t</script>\n</fieldset>\n'
ht += '</td>\n<td>\n'
sqlstmt = 'Select distinct round(fc_free_size_mb/1024,2) fc_free_gb,round(nl_free_size_mb/1024,2) nl_free_gb,'
sqlstmt += 'round(ssd_free_size_mb/1024,2) ssd_tot_gb,round((fc_total_size_mb-fc_free_size_mb)/1024,2) fc_used_gb,'
sqlstmt += ' round((nl_total_size_mb-nl_free_size_mb)/1024,2) nl_use_gb,round((ssd_total_size_mb-ssd_free_size_mb)/1024,2) ssd_used_gb '
sqlstmt += ' from legacy.capacity_report where inservserial=\'' + str(
row[0]) + '\''
dskcur = oracon.execSql(oraconn, sqlstmt)