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 ''