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));
foldersinarray+=1;
if foldersinarray > 100:
flnmlist.write(string.join(filenameList));
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();
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 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 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 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 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 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 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 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 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 connectDb():
try:
print '1. Connecting to mysql ....'
db = mconnect.connectMysql('', 'root', '', 'localhost')
print '2. Creating db...'
mconnect.checkDb('ods', db)
print '3. Creating procuser...'
mconnect.checkUser('procuser', db)
print '4. Connecting to Mysql as procuser'
db = mconnect.connectMysql('ods', 'procuser', 'c@llhome', 'localhost')
cur = db.cursor()
print '5. Show tables'
cur.execute('SHOW TABLES')
datList = cur.fetchall()
tablenameList = {}
if not datList:
for dat in datList:
tablenameList.append(dat[0])
print '6. Connecting to oracle.......'
constr = 'ods/ods@callhomeods:1521/callhomeods'
oraconn = oracon.openconnect(constr)
print '7. Creating table........'
checkCreatetable(cur, tablenameList, oraconn, oracon)
db.close()
oraconn.close()
return
except:
print "Error reported: " + str(sys.exc_info()[1])
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 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 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 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 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 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 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 dirListing(datatype,dirNum):
try:
dirOfInterest = "/prod/data/files/3PAR.INSERV";
fileList=[];
filepath="/ods30/vm1/filename/";
orafilepath="/ods30/vm1/filename/";
rowcounter=1;
dirList=[];
filename="filenamelist_"+datatype+"_"+str(dirNum)+"_"+ time.strftime('%Y%m%d%H%M%S')+".lst";
flnmlist = open (filepath+filename,"w");
dirname ="/share/st"+str(dirNum)+dirOfInterest;
try:
dirList=os.listdir(dirname);
log.write(dirList);
except:
pass;
for dirn in dirList:
goodfiles=0;
try:
fileList=os.listdir(dirname+"/"+dirn+"/"+datatype);
except:
pass;
for filenm in fileList:
if string.find(filenm, ".bz2") == -1 and string.find(filenm, ".bad") == -1:
flnmlist.write(str(dirn)+"\t"+dirname+"/"+dirn+"/"+datatype+"\t"+datatype+"\t"+filenm+"\n");
goodfiles=goodfiles+1;
rowcounter=rowcounter+1;
if rowcounter >= maxrowctr:
rowcounter=0;
flnmlist.close();
oraconn=oracon.openconnect(constr);
sqlstmt="begin insert into statfilenamelist(filenamepath,statfilename) values (\'"+orafilepath+"\',\'"+filename+"\'); commit; end; ";
oracon.execSql(oraconn,sqlstmt);
oraconn.close();
filename="filenamelist_"+datatype+"_"+str(dirNum)+"_"+ time.strftime('%Y%m%d%H%M%S')+".lst";
flnmlist = open (filepath+filename,"w");
fileList=[];
dirList=[];
flnmlist.close();
oraconn=oracon.openconnect(constr);
sqlstmt="begin insert into statfilenamelist(filenamepath,statfilename) values (\'"+orafilepath+"\',\'"+filename+"\'); commit; end; ";
oracon.execSql(oraconn,sqlstmt);
oraconn.close();
except:
log = open("log/"+datatype+'_'+str(dirNum)+'_'+"filenameloader.log","w");
log.write("Error reported: "+str(sys.exc_info()[1])+" in filenameloader");
log.close();
def loadprocess(thread):
constr = 'monitor/[email protected]:1521/monitordb'
oraconn = oracon.openconnect(constr)
sqlstmt = "select fileid,filepath,filename from monitordatasource where loaded=2 and threadnumber=" + str(
thread)
eventcur = oracon.execSql(oraconn, sqlstmt)
for evt in eventcur:
loadfile(evt[0], evt[1], evt[2], thread)
oraconn.close()
def get_process():
constr = 'ods/[email protected]:1521/callhomeods'
oraconn = oracon.openconnect(constr)
hstip = hst.retHostIP()
sqlstmt = 'select NUMBEROFTHREADS from STATSPROCESSINGMACHINE where IPADDRESS=\'' + hstip + '\''
result = oracon.execSql(oraconn, sqlstmt)
p_count = ''
for count in result:
p_count = count[0]
oraconn.close()
return p_count
def GetCurrentStructure(STATSSTRUCTUREID):
sql = 'SELECT STATS_COLUMN_ID,STATS_COLUMN_NAME FROM STATSSTRUCTUREDETAIL WHERE STATS_STRUCTURE_ID=' + str(
STATSSTRUCTUREID) + ' ORDER BY STATS_COLUMN_ID'
constr = 'ods/[email protected]:1521/callhomeods'
oraconn = oracon.openconnect(constr)
structureRec = oracon.execSql(oraconn, sql)
structList = []
for strec in structureRec:
structList.append(strec[1])
structureRec.close()
oraconn.close()
return structList
def sales_detail():
constr = 'produser/[email protected]:1521/callhomedw'
sqlstmt = 'select UNBALANCED, nvl(UNBALANCE_TYPE,\'N/A\'), DIRECTOR, MANAGER, SALES_REP, COMPANY, INSERVSERIAL, nvl(HP_SERIAL_NUMBER,\'UNKNOWN\'),'
sqlstmt += 'MODEL, OSVERSION, TOTAL_SPACE, TOTAL_FREE_SPACE, FREE_PERCENT, nvl(CAMPAIGN,\'None\'), nvl(REVENUE,0), REGION, CASE WHEN TRIM(COUNTRY)=\'.\' THEN \'UNKNOWN\' ELSE COUNTRY END COUNTRY'
sqlstmt += ', CASE WHEN TRIM(STATE)=\'.\' THEN \'UNKNOWN\' ELSE STATE END STATE, CASE WHEN TRIM(CITY)=\'.\' THEN \'UNKNOWN\' ELSE CITY END CITY '
sqlstmt += 'from CAPACITY_FOR_SALES_CAMPAIGN where total_space>0 and inservserial >0 and inservserial < 9900000 order by inservserial'
oraconn = oracon.openconnect(constr)
resultrec = oracon.execSql(oraconn, sqlstmt)
datarray = []
datstr = '{"aaData": [\n'
for rec in resultrec:
unbalanced = rec[0]
unbalance_type = rec[1]
director = rec[2]
manager = rec[3]
sales_rep = rec[4]
company = rec[5]
inservserial = rec[6]
hpserial = rec[7]
model = rec[8]
osver = rec[9]
totspace = rec[10]
totfreespace = rec[11]
freepers = rec[12]
campaign = rec[13]
rev = rec[14]
region = rec[15]
ctry = rec[16]
state = rec[17]
city = string.replace(
string.replace(
string.replace(
string.replace(
string.replace(string.replace(rec[18], '/', ''), '&',
''), '?', ''), '\'', ''), '#', ' '),
'.', ' ')
datarray.append('["","' + unbalanced + '","' + unbalance_type + '","' +
director + '","' + manager + '","' + sales_rep +
'","' + company + '","' + str(inservserial) + '","' +
hpserial + '","' + model + '","' + osver + '",' +
str(totspace) + ',' + str(totfreespace) + ',' +
str(freepers) + ',"' + campaign + '",' + str(rev) +
',"' + region + '","' + ctry + '","' + state + '","' +
city + '"]\n')
datstr += string.join(datarray, ',') + '\n]\n}'
outfl = open('/report/salesCampaignDetails.txt', 'w')
outfl.write(datstr)
outfl.close()
resultrec.close()
oraconn.close()
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 check_purpose():
global machine_id
constr = 'ods/[email protected]:1521/callhomeods'
oraconn = oracon.openconnect(constr)
hstip = hst.retHostIP()
sqlstmt = 'select purpose,machineid from STATSPROCESSINGMACHINE where IPADDRESS=\'' + hstip + '\''
purrec = oracon.execSql(oraconn, sqlstmt)
purpose = ''
for prec in purrec:
purpose = prec[0]
machine_id = prec[1]
oraconn.close()
return purpose
def GetAllStructure(STATSID):
sql = 'SELECT DISTINCT STATSID,STATSNAME,VERTICA_COLUMN_POSITION,STATS_COLUMN_NAME FROM STATSSTRUCTURE A, STATSSTRUCTUREDETAIL B WHERE A.STATSID=' + str(
STATSID
) + ' AND A.STATS_STRUCTURE_ID = B.STATS_STRUCTURE_ID AND VERTICA_ENABLED = 1 ORDER BY STATSID, VERTICA_COLUMN_POSITION'
constr = 'ods/[email protected]:1521/callhomeods'
oraconn = oracon.openconnect(constr)
structureRec = oracon.execSql(oraconn, sql)
structList = []
for strec in structureRec:
structList.append(strec[3] + ':' + str(strec[2]))
structureRec.close()
oraconn.close()
return structList
def setconnection(dbhost, dbtype, dbsource, dbuser, sqlstmt):
try:
userList = [["produser", "pr0duser"], ["datamart", "datamart"],
["dbadmin", "c@llhome"], ["datapulluser", "cAllhome"]]
retArr = []
usr = []
for usr in userList:
if usr[0] == dbuser:
passwd = usr[1]
if dbtype == 'oracle':
connstr = dbuser + '/' + passwd + '@' + dbhost + '/' + dbsource + ':1521'
oraconn = oracon.openconnect(connstr)
dbresulSet = oracon.execSql(oraconn, sqlstmt)
if dbtype == 'mysql':
myconn = mysql.connectMysql(dbsource, dbuser, passwd, dbhost)
cur_mysql = myconn.cursor()
cur_mysql.execute(sqlstmt)
dbresulSet = cur_mysql.fetchall()
if dbtype == 'vertica':
conn = vconn.vertica_connect(dbhost, 5433, dbuser, passwd,
dbsource)
vdat = vconn.vertica_sql_execute(conn, sqlstmt)
dbresulSet = vdat.fetchall()
retArr = redata(dbresulSet)
if dbtype == 'oracle':
dbresulSet.close()
oraconn.close()
if dbtype == 'mysql':
cur_mysql.close()
if dbtype == 'vertica':
vdat.close()
return retArr
except:
errlog = open('log/datamart_error.log', 'a')
function = 'set connection'
timestr = time.strftime('%m/%d/%Y %H:%M:%S') + ' ' + function
errlog.write(timestr + '\t Error reported: ' + str(sys.exc_info()[1]) +
'\n')
errlog.write(timestr + '\t SQL STMT: ' + sqlstmt + '\n')
errlog.close()
def datamart_process(machine_id):
connstr = 'ods/[email protected]/callhomeods:1521'
oraconn = oracon.openconnect(connstr)
sqlstmt = 'select NAME, STATUS_POST_TIME, PURPOSE, DISKSPACE_UTIL,DATAMART_STATUS FROM omi_machine_status_v where MACHINEID=\'' + str(
machine_id) + '\''
OracleResultset = oracon.execSql(oraconn, sqlstmt)
for data in OracleResultset:
name = data[0]
status_time = data[1]
purpose = data[2]
diskspace = data[3]
datamart = data[4]
oraconn.close()
return datamart
def filenameloadT2below(datatype,startfolder,endfolder):
log = open("log/"+datatype+"filenameloader_tiertwobelow.log","w");
dirOfInterest = "/prod/data/files/3PAR.INSERV/TierTwo";
fileList=[];
subdirList=[];
dirList=[];
filepath="/ods30/vm1/filename/";
orafilepath="/ods30/vm1/filename/";
filename="filenamelist_tiertwobelow_"+datatype+"_"+ time.strftime('%Y%m%d%H%M%S')+".lst";
flnmlist = open (filepath+filename,"w");
i=startfolder;
while(i<=endfolder):
dirname ="/share/st"+str(i)+dirOfInterest;
try:
dirList=os.listdir(dirname);
except:
log.write(str(time.ctime())+" Error :"+ str(sys.exc_info()[1])+"\n");
for dirn in dirList:
goodfiles=0;
try:
subdirList=os.listdir(dirname+"/"+dirn);
except:
log.write(str(time.ctime())+" Error :"+ str(sys.exc_info()[1])+"\n");
for subdirn in subdirList:
try:
fileList=os.listdir(dirname+"/"+dirn+"/"+subdirn+"/"+datatype);
except:
log.write(str(time.ctime())+" Error :"+ str(sys.exc_info()[1])+"\n");
for filenm in fileList:
if string.find(filenm, ".bz2") == -1 and string.find(filenm, ".bad") == -1:
flnmlist.write(str(dirn)+"\t"+dirname+"/"+dirn+"/"+subdirn+"/"+datatype+"\t"+datatype+"\t"+filenm+"\n");
goodfiles=goodfiles+1;
sqlstmt="";
fileList=[];
subdirList=[];
dirList=[];
i=i+1;
flnmlist.close();
oraconn=oracon.openconnect(constr);
sqlstmt="begin insert into statfilenamelist(filenamepath,statfilename) values (\'"+orafilepath+"\',\'"+filename+"\'); commit; end; ";
oracon.execSql(oraconn,sqlstmt);
oraconn.close();
log.close();
return;
def GetEnabledData():
sql = 'SELECT STATS_STRUCTURE_ID,STATSID,STATSNAME FROM STATSSTRUCTURE WHERE VERTICA_ENABLED=1'
constr = 'ods/[email protected]:1521/callhomeods'
oraconn = oracon.openconnect(constr)
enableFileListrec = oracon.execSql(oraconn, sql)
dataEnabledList = []
for enrec in enableFileListrec:
dataEnabledList.append(
str(enrec[0]) + ':' + str(enrec[1]) + ':' + enrec[2])
enableFileListrec.close()
oraconn.close()
return string.join(dataEnabledList, ',')