def report():
constr = 'produser/[email protected]:1521/callhomedw'
sqlstmt = 'select to_char(rap_date,\'Mon YYYY\') RAP_Month,rap_desc,count(distinct prod_id) number_of_unique_systems '
sqlstmt += 'from rap_extract where to_char(rap_date,\'YYYYMM\') in (select max(to_char(rap_date,\'YYYYMM\')) from rap_extract) and rap_desc is not null '
sqlstmt += 'group by to_char(rap_date,\'Mon YYYY\'),rap_category,rap_desc order by 3 desc'
oraconn = oracon.openconnect(constr)
resultrec = oracon.execSql(oraconn, sqlstmt)
fl = open('report/rap_trend.txt', 'w')
datstr = '{"aaData": [\n'
datarray = []
for rec in resultrec:
rapmonth = rec[0]
rapdesc = rec[1]
systems = rec[2]
sqlstmt = 'SELECT to_char(rap_date,\'YYYYMM\'),count(distinct prod_id) FROM rap_extract WHERE rap_desc=\'' + rapdesc + '\' GROUP BY to_char(rap_date,\'YYYYMM\') ORDER BY to_char(rap_date,\'YYYYMM\')'
dat = oracon.execSql(oraconn, sqlstmt)
monthArr = []
for datrec in dat:
monthArr.append(str(datrec[1]))
monthStr = '[' + string.join(monthArr, ',') + ']'
datarray.append('[\"' + string.strip(rapdesc) + '\",\"' +
string.strip(str(rapmonth)) + '\",' + str(systems) +
',' + monthStr + ']')
datstr += string.join(datarray, ',\n') + '\n]\n}'
fl.write(datstr)
fl.close()
oraconn.close()
def getAPJByRapCategory(oraconn):
sqlstmt = 'select distinct rap_days||\' days \',rap_ord from legacy.supply_chain_region_rap where region=\'APJ\' order by rap_ord'
datString = ''
datrow = []
category = []
series = []
oscatcur = oracon.execSql(oraconn, sqlstmt)
for catrec in oscatcur:
series.append('\'' + catrec[0] + '\'')
isqlstmt = 'select rap_category,rap_count from legacy.supply_chain_region_rap where region=\'APJ\' and rap_ord=' + str(
catrec[1]) + ' order by rap_category'
ostrendcur = oracon.execSql(oraconn, isqlstmt)
for ostrec in ostrendcur:
if searcharray(category, ostrec[0]) == 0:
category.append(ostrec[0])
datrow.append(catrec[0] + ':' + str(ostrec[1]))
ht = ''
xmdat = html.generateMSLine(category, series, datrow, 100, '', '1')
ht += '<div id="msapdiv" align="center">WW RAP by RAP category</div>\n'
ht += '<script type="text/javascript">\n'
ht += '\tvar chart = new FusionCharts("../FusionChartsFree/Charts/FCF_MSColumn2D.swf", "ChartId", "700", "500");\n'
ht += '\tchart.setDataXML("' + xmdat + '");\n'
ht += '\tchart.render("msapdiv");\n'
ht += '\t</script>\n'
return ht
def LoadData():
try:
threadcount = 20
constr = 'monitor/[email protected]:1521/monitordb'
oraconn = oracon.openconnect(constr)
sqlstmt = 'select count(1) from monitordatasource where loaded=0'
reccount = oracon.execSql(oraconn, sqlstmt)
for rec in reccount:
reccountcurrent = rec[0]
if (reccountcurrent > threadcount):
recproc = round(reccountcurrent / threadcount, 0)
else:
recproc = 1
print 'Number of records:' + str(reccountcurrent)
ctr = 1
while ctr <= threadcount:
sqlstmt = "begin update monitordatasource set loaded=2,threadnumber=" + str(
ctr) + " where loaded=0 and rownum <=" + str(
recproc) + "; commit; end;"
oracon.execSql(oraconn, sqlstmt)
p = Process(target=loadprocess, args=(ctr, ))
p.start()
time.sleep(60)
ctr = ctr + 1
oraconn.close()
except:
log = open("log/loader.log", "w")
log.write(
str(time.ctime()) + " Error :" + str(sys.exc_info()[1]) + "\n")
log.close()
def loadelasticsearch():
try:
sqlstmt='SELECT REPORTID, REPORT_SQL, INDEXNAME, JDBC_STRING, USERNAME, PASSWD,A.TABLE_ID FROM OCULUSELASTICSEARCH A,DATAMART_TABLE_EXTRACT B WHERE A.TABLE_ID=B.TABLE_ID AND NVL(SYNCDATE,TRUNC(SYSDATE)) <= AGG_UPDATE_DATE ORDER BY REPORTID';
constr='ods/[email protected]:1521/callhomeods';
oraconn=oracon.openconnect(constr);
datrec=oracon.execSql(oraconn,sqlstmt);
for dt in datrec:
reportid=dt[0];
reportsql=dt[1];
reportindex=dt[2];
reportjdbc=dt[3];
reportuser=dt[4];
reportpasswd=dt[5];
reporttableid=dt[6];
indexcheck=commands.getoutput('curl -XGET \'localhost:9200/'+reportindex+'/_search?q=*\'');
if string.find(indexcheck,'"status":404')<0:
print 'dropping index '+reportindex;
os.system('curl -XDELETE \'localhost:9200/'+reportindex+'\'');
creatstatus=commands.getoutput('curl -XPUT \'localhost:9200/_river/'+reportindex+'/_meta\' -d \'{"type" : "jdbc","jdbc" : {"strategy": "simple","driver" : "com.mysql.jdbc.Driver", "url" : "'+reportjdbc+'","user" : "'+reportuser+'","password" : "'+reportpasswd+'","index" : "'+reportindex+'","autocommit" : true,"sql" : "'+reportsql+'"}}\'');
print creatstatus;
if string.find(creatstatus,'"created":true'):
oracon.execSql(oraconn,' begin update OCULUSELASTICSEARCH set syncdate=sysdate where table_id='+str(reporttableid)+'; commit; end;');
datrec.close();
oraconn.close();
except:
print 'Error reported: '+str(sys.exc_info()[1]);
def loadfile(fileid, filepath, filename, thread):
constr = 'monitor/[email protected]:1521/monitordb'
oraconn = oracon.openconnect(constr)
fl = open(filepath + '/' + filename)
allStatements = fl.readlines()
ctr = 1
for st in allStatements:
try:
if len(st) > 4000:
if string.find(st, 'creating deployment') >= 0:
st = st[:string.find(st, 'dataSourceName')]
st = st + ' Failed to deploy package\');'
st = string.replace(st, ') ', ',fileid) ')
st = string.replace(st, ');', ',' + str(fileid) + ');')
oracon.execSql(oraconn, 'begin ' + st + ' commit; end;')
ctr = ctr + 1
except:
log = open("log/loader_" + str(thread) + ".log", "a")
log.write(
str(time.ctime()) + " Error :" + str(sys.exc_info()[1]) + "\n")
log.close()
fl.close()
#print 'Done file '+filename+' added '+str(ctr)+' rows...';
oracon.execSql(
oraconn,
'Begin update monitordatasource set loaded=1 where filepath=\'' +
filepath + '\' and filename=\'' + filename + '\'; commit; end;')
oraconn.close()
def extract_check(machineid):
ex_tablearray = []
connstr = 'ods/[email protected]/callhomeods:1521'
oraconn = oracon.openconnect(connstr)
sqlstmt = "select count(1) from omi_extract_v where table_id in (select table_id from datamart_source where machineid=" + machineid + ")"
CountResultset = oracon.execSql(oraconn, sqlstmt)
for rec in CountResultset:
count = rec[0]
if (count == 0):
tablename_e = 1
agg_e = 1
ext_e = 1
ext_flag = 1
ex_tablearray.append([ext_flag, tablename_e, agg_e, ext_e])
if (count > 0):
sqlstmt = "select AGG_UPDATE_DATE, EXTRACTED from omi_extract_v where table_id in (select table_id from datamart_source where machineid=" + machineid + ")"
ExtractResultset = oracon.execSql(oraconn, sqlstmt)
for rec in ExtractResultset:
ext_flag = 0
tablename_e = rec[0]
agg_e = rec[1]
ext_e = rec[2]
ex_tablearray.append([ext_flag, tablename_e, agg_e, ext_e])
oraconn.close()
return ex_tablearray
def sync_check():
sync_tablearray = []
connstr = 'ods/[email protected]/callhomeods:1521'
oraconn = oracon.openconnect(connstr)
sqlstmt = "select count(1) from omi_sync_v"
CountResultset = oracon.execSql(oraconn, sqlstmt)
for rec in CountResultset:
synccount = rec[0]
if (synccount == 0):
tablename_s = 1
agg_s = 1
ext_s = 1
rep_s = 1
s_flag = 1
sync_tablearray.append([s_flag, tablename_s, agg_s, ext_s, rep_s])
if (synccount > 0):
sqlstmt = "select TABLE_NAME,AGG_UPDATE_DATE,EXTRACTED,REPLICATION_DATE from omi_sync_v"
SyncResultset = oracon.execSql(oraconn, sqlstmt)
for rec in SyncResultset:
s_flag = 0
tablename_s = rec[0]
agg_s = rec[1]
ext_s = rec[2]
rep_s = rec[3]
sync_tablearray.append([s_flag, tablename_s, agg_s, ext_s, rep_s])
oraconn.close()
return sync_tablearray
def getOsQtrlyTrend(oraconn):
datString = ''
datrow = []
category = []
series = []
sqlstmt = ''
sqlstmt = 'select distinct os_version from legacy.display_trend_table where sortq >= 20091 order by os_version'
oscatcur = oracon.execSql(oraconn, sqlstmt)
for catrec in oscatcur:
series.append('\'' + catrec[0] + '\'')
isqlstmt = 'select distinct qtr,os_version,inserv_num,sortq from legacy.display_trend_table where os_version=\'' + catrec[
0] + '\' and sortq >= 20091 order by sortq'
ostrendcur = oracon.execSql(oraconn, isqlstmt)
for ostrec in ostrendcur:
if searcharray(category, 'Q ' + ostrec[3]) == 0:
category.append('Q ' + ostrec[3])
datrow.append(ostrec[1] + ':' + str(ostrec[2]) + ':' +
str(ostrec[3]) + ostrec[1] + '.html')
ht = ''
xmdat = html.generateMSLine(category, series, datrow, 3000, '', '1')
ht += '<div id="msdiv" align="center">Inform Os Across Install Base</div>\n'
ht += '<script type="text/javascript">\n'
ht += '\tvar chart = new FusionCharts("../FusionChartsFree/Charts/FCF_MSLine.swf", "ChartId", "800", "300");\n'
ht += '\tchart.setDataXML("' + xmdat + '");\n'
ht += '\tchart.render("msdiv");\n'
ht += '\t</script>\n'
return ht
def startjob():
constr = 'produser/[email protected]:1521/callhomedw'
oraconn = oracon.openconnect(constr)
sqlstmt = 'select count(distinct inservserial) from capacity_web_report'
numrec = oracon.execSql(oraconn, sqlstmt)
ct = 0
for nrec in numrec:
numInserv = nrec[0]
print "########################Total Inservs : " + str(numInserv)
countInserv = 1
sqlstmt = 'select distinct inservserial from capacity_web_report where inservserial like \'164%\' order by inservserial'
insrec = oracon.execSql(oraconn, sqlstmt)
inservrec = []
for inrec in insrec:
inservrec.append(inrec[0])
insrec.close()
oraconn.close()
inservs = 1
ctr = 1
for inrec in inservrec:
p = Process(target=runbysys, args=(inrec, ))
p.start()
print "Number of systems done:" + str(inservs)
print "Number of jobs:" + checkjobs()
ctr += 1
inservs += 1
if ctr > 1000:
time.sleep(15)
ctr = 1
print "Last inserv :" + str(inrec)
while int(checkjobs()) > 5:
time.sleep(15)
def getSoftwareQtrAttachRate(oraconn):
datString = ''
datrow = []
category = []
series = []
sqlstmt = ''
sqlstmt = 'select distinct qtr_year from legacy.Qtrly_soft_attach_trend '
sqlstmt += ' order by to_number(trim(substr(qtr_year,length(qtr_year)-4,5))||\'0\'||trim(substr(qtr_year,2,2)))'
oscatcur = oracon.execSql(oraconn, sqlstmt)
for catrec in oscatcur:
series.append('\'' + catrec[0] + '\'')
isqlstmt = 'select qtr_year,software,install_pers,to_number(trim(substr(qtr_year,length(qtr_year)-4,5))||\'0\'||trim(substr(qtr_year,2,2))) qtr '
isqlstmt += ' from legacy.Qtrly_soft_attach_trend where upper(software) NOT IN (\'GOLDEN LICENSE\',\'RECOVERY MANAGER FOR EXCHANGE \'||\'&\'||\' ORACLE\')'
isqlstmt += ' and qtr_year=\'' + catrec[0] + '\' order by '
isqlstmt += ' to_number(trim(substr(qtr_year,length(qtr_year)-4,5))||\'0\'||trim(substr(qtr_year,2,2)))'
ostrendcur = oracon.execSql(oraconn, isqlstmt)
for ostrec in ostrendcur:
if searcharray(category, ostrec[1]) == 0:
category.append(ostrec[1])
datrow.append(catrec[0] + ':' + str(ostrec[2]))
ht = ''
xmdat = html.generateMSLine(category, series, datrow, 100, '', '1')
ht += '<div id="mscoldiv" align="center">Inform Os Across Install Base</div>\n'
ht += '<script type="text/javascript">\n'
ht += '\tvar chart = new FusionCharts("../FusionChartsFree/Charts/FCF_MSColumn2D.swf", "ChartId", "1500", "600");\n'
ht += '\tchart.setDataXML("' + xmdat + '");\n'
ht += '\tchart.render("mscoldiv");\n'
ht += '\t</script>\n'
return ht
def license():
constr = 'produser/[email protected]:1521/callhomedw'
sqlstmt = 'select INSERVSERIAL, CUSTOMER_NAME, LOCATION, COUNTRY, REGION, THINPROVISIONIG, THINCONVERSION, THINPERSISTENCE, VIRTUALCOPY, REMOTECOPY, PEERPERSISTENCE, DYNAMICOPTIMIZATION, ADAPTIVEOPTIMIZATION, PRIORITYOPTIMIZATION, PEERMOTION, DATAENCRYPTION, VIRTUALDOMAINS, VIRTUALLOCK, SYSTEMREPORTER, RMEXCHANGE, RMHYPERV, RMO, RMS, RMVV, SYSTEMTUNER from LICENSE_FILTER ORDER BY INSERVSERIAL'
oraconn = oracon.openconnect(constr)
resultrec = oracon.execSql(oraconn, sqlstmt)
fl = open('report/LicenseGlobal.txt', 'w')
datstr = '{"aaData": [\n'
datarray = []
for rec in resultrec:
datarray.append('[' + str(rec[0]) + ',\"' + string.strip(str(rec[1])) +
'\",\"' + str(rec[2]) + '\",\"' + str(rec[3]) +
'\",\"' + str(rec[4]) + '\",\"' + str(rec[5]) +
'\",\"' + str(rec[6]) + '\",\"' + str(rec[7]) +
'\",\"' + str(rec[8]) + '\",\"' + str(rec[9]) +
'\",\"' + str(rec[10]) + '\",\"' + str(rec[11]) +
'\",\"' + str(rec[12]) + '\",\"' + str(rec[13]) +
'\",\"' + str(rec[14]) + '\",\"' + str(rec[15]) +
'\",\"' + str(rec[16]) + '\",\"' + str(rec[17]) +
'\",\"' + str(rec[18]) + '\",\"' + str(rec[19]) +
'\",\"' + str(rec[20]) + '\",\"' + str(rec[21]) +
'\",\"' + str(rec[22]) + '\",\"' + str(rec[23]) +
'\",\"' + str(rec[24]) + '\"]')
datstr += string.join(datarray, ',\n') + '\n]\n}'
fl.write(datstr)
fl.close()
region = ['Americas', 'APJ', 'EMEA']
for reg in region:
sqlstmt = 'select INSERVSERIAL, CUSTOMER_NAME, LOCATION, COUNTRY, REGION, THINPROVISIONIG, THINCONVERSION, THINPERSISTENCE, VIRTUALCOPY, REMOTECOPY, PEERPERSISTENCE, DYNAMICOPTIMIZATION, ADAPTIVEOPTIMIZATION, PRIORITYOPTIMIZATION, PEERMOTION, DATAENCRYPTION, VIRTUALDOMAINS, VIRTUALLOCK, SYSTEMREPORTER, RMEXCHANGE, RMHYPERV, RMO, RMS, RMVV, SYSTEMTUNER from LICENSE_FILTER WHERE REGION=upper(\'' + reg + '\') ORDER BY INSERVSERIAL'
oraconn = oracon.openconnect(constr)
resultrec = oracon.execSql(oraconn, sqlstmt)
fl = open('report/License' + reg + '.txt', 'w')
datstr = '{"aaData": [\n'
datarray = []
for rec in resultrec:
datarray.append('[' + str(rec[0]) + ',\"' +
string.strip(str(rec[1])) + '\",\"' + str(rec[2]) +
'\",\"' + str(rec[3]) + '\",\"' + str(rec[4]) +
'\",\"' + str(rec[5]) + '\",\"' + str(rec[6]) +
'\",\"' + str(rec[7]) + '\",\"' + str(rec[8]) +
'\",\"' + str(rec[9]) + '\",\"' + str(rec[10]) +
'\",\"' + str(rec[11]) + '\",\"' + str(rec[12]) +
'\",\"' + str(rec[13]) + '\",\"' + str(rec[14]) +
'\",\"' + str(rec[15]) + '\",\"' + str(rec[16]) +
'\",\"' + str(rec[17]) + '\",\"' + str(rec[18]) +
'\",\"' + str(rec[19]) + '\",\"' + str(rec[20]) +
'\",\"' + str(rec[21]) + '\",\"' + str(rec[22]) +
'\",\"' + str(rec[23]) + '\",\"' + str(rec[24]) +
'\"]')
datstr += string.join(datarray, ',\n') + '\n]\n}'
fl.write(datstr)
fl.close()
oraconn.close()
def subdirlistcurr(dirname, dirn, datatype, dirNum):
try:
maxrowctr = 100000
subdirList = []
filelist = []
filepath = "/ods145/goldenvm/filename/"
orafilepath = "/ods145/goldenvm/filename/"
try:
subdirList = os.listdir(dirname + "/" + dirn)
except:
pass
subn = string.replace(dirn, '-', '')
filename = "filename_" + datatype + "_" + str(
dirNum) + "_" + subn + "_" + time.strftime('%Y%m%d%H%M%S') + ".lst"
flnmlist = open(filepath + filename, "w")
numDir = 1
numLines = 0
foldersinarray = 0
filenameList = []
subdirList = sorted(subdirList)
for subdirn in subdirList:
fileList = subDirList(subdirn, subn, dirname, datatype, dirn,
dirNum)
filenameList.append(string.join(fileList, '\n'))
foldersinarray += 1
if foldersinarray > 1000:
flnmlist.write(string.join(filenameList) + '\n')
flnmlist.flush()
flnmlist.close()
sqlstmt = "begin insert into statfilenamelist_start(filenamepath,statfilename,created_date) values (\'" + orafilepath + "\',\'" + filename + "\',sysdate); commit; end; "
oracon.execSql(oraconn, sqlstmt)
oraconn.close()
filename = "filename_" + datatype + "_" + str(
dirNum) + "_" + subn + "_" + time.strftime(
'%Y%m%d%H%M%S') + ".lst"
flnmlist = open(filepath + filename, "w")
foldersinarray = 0
filenameList = []
flnmlist.write(string.join(filenameList) + '\n')
flnmlist.flush()
flnmlist.close()
oraconn = oracon.openconnect(constr)
sqlstmt = "begin insert into statfilenamelist_start(filenamepath,statfilename,created_date) values (\'" + orafilepath + "\',\'" + filename + "\',sysdate); commit; end; "
oracon.execSql(oraconn, sqlstmt)
oraconn.close()
except:
log = open(
"log/filename_" + datatype + "_" + str(dirNum) + "_" + subn + "_" +
time.strftime('%Y%m%d%H%M%S') + ".log", "w")
log.write(
str(time.ctime()) + " Error :" + str(sys.exc_info()[1]) + "\n")
log.close()
def InsertMetaData(oraconn, oracon, cur):
dataList = oracon.execSql(
oraconn,
'select FUNCTIONID,FUNCTIONNAME,FUNCTIONPARAMETERS,FUNCTIONDESC from PROCESSFUNCTION'
)
cur.execute('truncate table PROCESSFUNCTION')
for dat in dataList:
metasql = 'Insert into PROCESSFUNCTION(FUNCTIONID,FUNCTIONNAME,FUNCTIONPARAMETERS,FUNCTIONDESC) values (' + str(
dat[0]) + ',\'' + str(dat[1]) + '\',\'' + str(
dat[2]) + '\',\'' + str(dat[3]) + '\')'
cur.execute(metasql)
cur.execute('commit;')
dataList = oracon.execSql(
oraconn,
'select STATS_STRUCTURE_ID ,PROCESS_SEQUENCE ,FUNCTIONID from PROCESSLOGIC'
)
cur.execute('truncate table PROCESSLOGIC')
for dat in dataList:
metasql = 'Insert into PROCESSLOGIC(STATS_STRUCTURE_ID ,PROCESS_SEQUENCE ,FUNCTIONID ) values (' + str(
dat[0]) + ',' + str(dat[1]) + ',' + str(dat[2]) + ')'
cur.execute(metasql)
cur.execute('commit;')
dataList = oracon.execSql(
oraconn,
'select DATATYPE,SPLIT_FILE_TYPE,SPLIT_FILE_SEARCH_TAG,SPLIT_FILE_SKIP_LINES,SPLIT_FILE_END_TAG,SPLIT_FILE_LINE_SEPERATOR,STATSID from STAT_SPLIT_FILE_LOOKUP'
)
cur.execute('truncate table STAT_SPLIT_FILE_LOOKUP')
for dat in dataList:
metasql = 'Insert into STAT_SPLIT_FILE_LOOKUP(DATATYPE,SPLIT_FILE_TYPE,SPLIT_FILE_SEARCH_TAG,SPLIT_FILE_SKIP_LINES,SPLIT_FILE_END_TAG,SPLIT_FILE_LINE_SEPERATOR,STATSID) '
metasql += 'values (\'' + str(dat[0]) + '\',\'' + str(
dat[1]) + '\',\'' + str(dat[2]) + '\',\'' + str(
dat[3]) + '\',\'' + str(dat[4]) + '\',\'' + str(
dat[5]) + '\',' + str(dat[6]) + ')'
cur.execute(metasql)
cur.execute('commit;')
dataList = oracon.execSql(
oraconn,
'select STATSID,STATS_STRUCTURE_ID,STATSNAME,STATS_VERSION,STATS_FIRST_ROW_VERSION,STATS_SINGLE_ROW,STATS_END_OF_ROW from STATSSTRUCTURE'
)
cur.execute('truncate table STATSSTRUCTURE')
for dat in dataList:
metasql = 'Insert into STATSSTRUCTURE(STATSID,STATS_STRUCTURE_ID,STATSNAME,STATS_VERSION,STATS_FIRST_ROW_VERSION,STATS_SINGLE_ROW,STATS_END_OF_ROW) '
metasql += 'values (' + str(dat[0]) + ',' + str(dat[1]) + ',\'' + str(
dat[2]) + '\',\'' + str(dat[3]) + '\',\'' + str(
dat[4]) + '\',\'' + str(dat[5]) + '\',\'' + str(dat[6]) + '\')'
cur.execute(metasql)
cur.execute('commit;')
return
def copyRemoveFile():
try:
oraconn=oracon.openconnect(constr);
sqlstmt='SELECT DISTINCT STATSNAME FROM STATSSTRUCTURE';
stat=oracon.execSql(oraconn,sqlstmt);
for statrec in stat:
sqlstmt='SELECT STATS_OUTPUTFILE_ID,STATS_OUTPUTFILE_PATH,STATS_OUTPUTFILE_NAME FROM STATSOUTPUT a, STATSSTRUCTURE b';
sqlstmt+=' WHERE STATS_FILE_LOAD_STATUS = 0 AND ROWNUM <= 500 AND b.STATSNAME = '+STATSNAME+' AND A.STATS_STRUCTURE_TYPE_ID = B.STATS_STRUCTURE_ID';
sqlstmt+=' ORDER BY STATS_OUTPUTFILE_ID DESC';
files=oracon.execSql(oraconn,sqlstmt);
for flrec in files:
p=Process(target=copyFile,args=(flrec[2],flrec[1],'/odstmp/'));
p.daemon = True;
def build_disktype_free_trend(inservserial, oraconn):
# number of vvs over time
datString = ''
datrow = []
category = []
series = []
sqlstmt = ''
sqlstmt = 'select count(1) from legacy.pd_type_daily_freetrend where to_char(inservserial)=\'' + str(
inservserial) + '\''
checkcur = oracon.execSql(oraconn, sqlstmt)
for ckcur in checkcur:
if ckcur[0] == 0:
return ''
sqlstmt = 'select max(round((free/1024)/1024,0))+10 from legacy.pd_type_daily_freetrend where to_char(inservserial)=\'' + str(
inservserial) + '\''
maxcur = oracon.execSql(oraconn, sqlstmt)
for mcur in maxcur:
maxval = mcur[0]
sqlstmt = 'select distinct pdtype from legacy.pd_type_daily_freetrend where to_char(inservserial)=\'' + str(
inservserial) + '\' order by 1'
oscatcur = oracon.execSql(oraconn, sqlstmt)
for catrec in oscatcur:
series.append('\'' + catrec[0] + '\'')
isqlstmt = 'select to_char(datadate,\'mm/dd/YYYY\') dt,pdtype,round((free/1024)/1024,2) from legacy.pd_type_daily_freetrend '
isqlstmt += ' where to_char(inservserial)=\'' + str(
inservserial) + '\' AND pdtype=\'' + catrec[0] + '\' and '
isqlstmt += ' datadate in (select datadate from (select datadate from legacy.pd_type_daily_freetrend where to_char(inservserial)= \'' + str(
inservserial) + '\''
isqlstmt += ' order by datadate desc) where rownum < 31 ) order by 1'
ostrendcur = oracon.execSql(oraconn, isqlstmt)
for ostrec in ostrendcur:
if searcharray(category, ostrec[0]) == 0:
category.append(ostrec[0])
datrow.append(ostrec[1] + ':' + str(ostrec[2]))
ht = ''
xmdat = html.generateMSLine(category, series, datrow, maxval, '', '1')
ht += '<fieldset><legend>Historical Free Capacity in TB</legend>'
ht += '<div id="msdiv" align="center">Historical Free Capacity</div>\n'
ht += '<script type="text/javascript">\n'
ht += '\tvar chart = new FusionCharts("../FusionChartsFree/Charts/FCF_MSLine.swf", "ChartId", "500", "200");\n'
ht += '\tchart.setDataXML("' + xmdat + '");\n'
ht += '\tchart.render("msdiv");\n'
ht += '\t</script>\n</fieldset>'
return ht
def setconnection(dbhost, dbtype, dbsource, dbuser, sqlstmt):
try:
#userList=[["produser","pr0duser"],["dbadmin","c@llhome"],["datapulluser","cAllhome"]];
#retArr=[];
#usr=[];
#for usr in userList:
# if usr[0]==dbuser:
# passwd=usr[1];
# if dbtype =='oracle':
# print "dbuser"+dbuser;
# print "sql in set\t"+sqlstmt;
# connstr=dbuser+'/'+passwd+'@'+dbhost+'/'+dbsource+':1521'
# oraconn = oracon.openconnect(connstr);
connstr = 'produser/pr0duser@callhomedw/callhomedw:1521'
oraconn = oracon.openconnect(connstr)
dbresulSet = oracon.execSql(oraconn, sqlstmt)
retArr = redata(dbresulSet)
dbresulSet.close()
if dbtype == 'oracle':
oraconn.close()
return retArr
except:
errlog = open('log/json_error.log', 'a')
function = 'set connection'
#optimuslogger.write('---------------------------------------------------------------------------------------------------------------------------------------------\n');
timestr = time.strftime('%m/%d/%Y %H:%M:%S') + ' ' + function
errlog.write(timestr + '\t Error reported: ' + str(sys.exc_info()[1]) +
'\n')
errlog.close()
def get_data():
try:
connstr = 'ods/[email protected]/callhomeods:1521'
oraconn = oracon.openconnect(connstr)
sqlstmt = 'SELECT OUTPUT_FILE_NAME,FILE_LOCATION,HTML_TEMPLATE,HTML_FILE_NAME,JSON_URL_HTML FROM GRAPH_REPORT_DATA where enabled=1 and AGG_UPDATE> nvl(OUTPUT_FILE_UPDATE,trunc(sysdate)) order by QUERY_ID'
htmlResultset = oracon.execSql(oraconn, sqlstmt)
for rec in htmlResultset:
json_file = rec[0]
json_location = rec[1]
html_template = rec[2]
html_name = rec[3]
json_url = rec[4]
print json_file
print json_location
print html_template
print html_name
print json_url
htmlResultset.close()
oraconn.close()
read_data(json_file, json_location, html_template, html_name,
json_url)
except:
errlog = open('log/SSDhtml_error.log', 'a')
function = 'get data'
#optimuslogger.write('---------------------------------------------------------------------------------------------------------------------------------------------\n');
timestr = time.strftime('%m/%d/%Y %H:%M:%S') + ' ' + function
errlog.write(timestr + '\t Error reported: ' + str(sys.exc_info()[1]) +
'\n')
errlog.close()
def processhwdata(datatype):
dat = time.strftime('%Y%m%d')
#hwlog.close();
#createHdfsDir(datatype,'log/'+datatype+'_processing'+dat+'.log');
#hwlog = open('log/'+datatype+'_processing'+dat+'.log','a');
sqlstmt = 'select distinct YYMMDD from vw_' + datatype + '_current_ecc where yymmdd between to_char(sysdate-5,\'YYMMDD\') and to_char(sysdate,\'YYMMDD\') order by yymmdd desc'
constr = 'ods/[email protected]:1521/callhomeods'
oraconn = oracon.openconnect(constr)
yyres = oracon.execSql(oraconn, sqlstmt)
numdays = 0
maxthread = 10
for yrec in yyres:
p = Process(target=processdate, args=(
datatype,
yrec[0],
))
#print datatype;
#print yrec[0];
#processdate(datatype,yrec[0]);
p.start()
#numdays+=1;
#if numdays > 2:
# time.sleep(1200);
# numdays=0;
numproc = commands.getoutput(
"ps -ef | grep [p]rocess_eventlog_ecc |wc -l")
while int(numproc) > maxthread:
time.sleep(600)
numproc = commands.getoutput(
"ps -ef | grep [p]rocess_eventlog_ecc |wc -l")
yyres.close()
oraconn.close()
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 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 filenameloadT2belowCurr(startfolder, endfolder):
try:
constr = 'ods/ods@callhomeods:1521/callhomeods'
oraconn = oracon.openconnect(constr)
log = open("log/filenameloader_tiertwoCurrent.log", "w")
sqlstmt = "select trim(foldername) from statsfolders where enabled=1"
eventcur = oracon.execSql(oraconn, sqlstmt)
for evnt in eventcur:
log.write("Starting event :" + evnt[0] + "\n")
log.flush()
i = startfolder
while (i <= endfolder):
log.write(" Folder :" + str(i) + "\n")
log.flush()
waitTime()
p = Process(target=dirListingCurrT2, args=(
evnt[0],
i,
))
p.start()
i = i + 1
log.close()
except:
# log = open("log/filenameloader_tiertwobelow.log","w");
log.write(
str(time.ctime()) + " Error :" + str(sys.exc_info()[1]) + "\n")
log.close()
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 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 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 get_data():
try:
connstr='ods/[email protected]/callhomeods:1521';
oraconn = oracon.openconnect(connstr);
sqlstmt="SELECT OUTPUT_FILE_NAME,FILE_LOCATION,HTML_TEMPLATE,HTML_FILE_NAME,JSON_URL_HTML,REPORT_TITLE FROM GRAPH_REPORT_DATA where HTML_TEMPLATE='fchtml.py'";
htmlResultset = oracon.execSql(oraconn,sqlstmt);
for rec in htmlResultset:
json_file= rec[0];
json_location=rec[1];
html_template=rec[2];
html_name=rec[3];
json_url=rec[4];
title= rec[5];
print "FC HTML";
read_data(json_file,json_location,html_template,html_name,json_url,title);
htmlResultset.close();
oraconn.close();
except:
errlog=open('log/html_error.log','a');
function='get data-FC';
#optimuslogger.write('---------------------------------------------------------------------------------------------------------------------------------------------\n');
timestr=time.strftime('%m/%d/%Y %H:%M:%S')+' '+function;
errlog.write(timestr+'\t Error reported: '+str(sys.exc_info()[1])+ '\n');
errlog.close();
def processhwdata(datatype):
dat = time.strftime('%Y%m%d')
#hwlog.close();
#createHdfsDir(datatype,'log/'+datatype+'_processing'+dat+'.log');
#hwlog = open('log/'+datatype+'_processing'+dat+'.log','a');
sqlstmt = 'select distinct YYMMDD from vw_' + datatype + '_current order by yymmdd desc'
constr = 'ods/[email protected]:1521/callhomeods'
oraconn = oracon.openconnect(constr)
yyres = oracon.execSql(oraconn, sqlstmt)
numdays = 0
maxthread = 10
for yrec in yyres:
p = Process(target=processdate, args=(
datatype,
yrec[0],
))
#print datatype;
#print yrec[0];
#processdate(datatype,yrec[0]);
p.start()
#numdays+=1;
#if numdays > 2:
# time.sleep(1200);
# numdays=0;
yyres.close()
oraconn.close()
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 getcolStruct(stats_structureid, stats_single_row, stats_end_row, logfile):
oraconn = oracon.openconnect(constr)
colstartpos = list()
colendpos = list()
lastcol = list()
colsearchstr = list()
colsize = list()
coltype = list()
statslog.logwrite(logfile, 'Generating columns...', 'getcolStruct')
sqlstmt = 'select stats_column_name,stats_column_start_pos,stats_column_end_pos,'
sqlstmt += ' stats_column_searchstr,stats_column_size,stats_column_type from statsstructuredetail where stats_structure_id=' + str(
stats_structureid) + ' order by stats_column_id'
structrec = oracon.execSql(oraconn, sqlstmt)
for struct in structrec:
colstartpos.append(struct[1])
colendpos.append(struct[2])
colsearchstr.append(struct[3])
colsize.append(struct[4])
coltype.append(struct[5])
if stats_single_row == 'False':
if stats_end_row == struct[0]:
lastcol.append(1)
else:
lastcol.append(0)
return colstartpos, colendpos, colsearchstr, colsize, coltype, lastcol
def get_data():
try:
connstr = 'ods/[email protected]/callhomeods:1521'
oraconn = oracon.openconnect(connstr)
sqlstmt = "SELECT OUTPUT_FILE_NAME,FILE_LOCATION,HTML_TEMPLATE,HTML_FILE_NAME,JSON_URL_HTML,REPORT_TITLE FROM GRAPH_REPORT_DATA where HTML_TEMPLATE='fsbhtmlread.py'"
htmlResultset = oracon.execSql(oraconn, sqlstmt)
for rec in htmlResultset:
json_file = rec[0]
json_location = rec[1]
html_template = rec[2]
html_name = rec[3]
json_url = rec[4]
title = rec[5]
print "FSB HTML"
read_data(json_file, json_location, html_template, html_name,
json_url, title)
htmlResultset.close()
oraconn.close()
#os.system('mv /var/www/html/ssd/'+mysqlorgdump+' /root/proc/mysqldump/'+mysqldump_name);
except:
errlog = open('log/html_error.log', 'a')
function = 'get data-FSB'
#optimuslogger.write('---------------------------------------------------------------------------------------------------------------------------------------------\n');
timestr = time.strftime('%m/%d/%Y %H:%M:%S') + ' ' + function
errlog.write(timestr + '\t Error reported: ' + str(sys.exc_info()[1]) +
'\n')
errlog.close()
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 ''
