def CorruptedFilesInTimeWindow(params):
if params.SiteName == 'summary':
sitename = 'T2_%'
else:
sitename = params.SiteName
#cursor = connection.cursor()
table = "%s.%s" % (DBUSER, params.dbview)
query = '''select * from %s
where sitename like %%s ;''' % (table)
try:
cursor = connection.cursor()
cursor.execute(query, [sitename])
except Exception as e:
raise PopularityDBException(query, e)
data = {}
data['DATA'] = utility.genericTranslateInList(cursor)
return data
def xrdStatInTimeWindow(params):
table = "%s.%s" % (DBUSER, params.table)
vars = 'xTime, yValue, yValue/3600. as rate, round((xTime-to_date(\'19700101\',\'YYYYMMDD\'))*86400)*1000 as millisecondsSinceEpoch'
whereCondition = '''
xTime >= to_date(\'%s\',\'YYYY-MM-DD HH24:MI:SS\')
and xTime <= to_date(\'%s\',\'YYYY-MM-DD HH24:MI:SS\')''' % (params.TStart,
params.TStop)
orderby = ''
if hasattr(params, 'orderby'):
orderby = params.orderby
query = 'select %s from %s where %s %s' % (vars, table, whereCondition,
orderby)
logger.info(query)
try:
cursor = connections[DBUSER].cursor()
cursor.execute(query)
except Exception as e:
raise PopularityDBException(query, e)
data = {}
data['DATA'] = utility.genericTranslateInList(cursor)
return data
def xrdStatInTimeWindow(params):
table = "%s.%s" % (DBUSER, params.table)
vars = 'xTime, yValue, yValue/3600. as rate, round((xTime-to_date(\'19700101\',\'YYYYMMDD\'))*86400)*1000 as millisecondsSinceEpoch'
whereCondition = '''
xTime >= to_date(\'%s\',\'YYYY-MM-DD HH24:MI:SS\')
and xTime <= to_date(\'%s\',\'YYYY-MM-DD HH24:MI:SS\')''' % (params.TStart, params.TStop)
orderby = ''
if hasattr(params, 'orderby'):
orderby = params.orderby
query = 'select %s from %s where %s %s' % (vars, table, whereCondition, orderby)
logger.info(query)
try:
cursor = connections[DBUSER].cursor()
cursor.execute(query)
except Exception as e:
raise PopularityDBException(query, e)
data = {}
data['DATA'] = utility.genericTranslateInList(cursor)
return data
def MostPopDSStat(params, MView, collName):
#cursor = connection.cursor()
if params.includeWMAgent == 'y':
baseMV = "STAT0"
elif params.includeWMAgent == 'n':
baseMV = "STAT1"
if MView == 'DataTier':
aggrMV = "AGGR2"
elif MView == 'DS':
aggrMV = "AGGR1"
elif MView == 'DSName':
aggrMV = 'AGGR4'
table = "%s.MV_DS_%s_%s" % (DBUSER, baseMV, aggrMV)
#TimeFormats: timeformat acts to the displayed date, timeformatTrunc acts to the truncation of the input dates, and should be keept with the format of the aggregation
timeformat = 'YYYY/MM/DD'
if params.AggrFlag == 'day':
timeformatTrunc = 'DDD'
elif params.AggrFlag == 'week':
timeformatTrunc = 'WW'
elif params.AggrFlag == 'month':
timeformatTrunc = 'MONTH'
elif params.AggrFlag == 'quarter':
timeformatTrunc = 'Q'
elif params.AggrFlag == 'year':
timeformatTrunc = 'YEAR'
whereCondition = "collName = '%s'" % collName
orderBy = "order by TDay "
vtime = "trunc(TDay,'%s')" % (timeformatTrunc)
groupBy = "group by %s, collName" % vtime
vars = '''to_char(%s,'%s') as TDay, sum(numAccesses) as nAcc,
round(sum(totCPU)/3600,0) as totCPU, sum(numUsers) as nUsers
''' % (vtime, timeformat)
if params.SiteName != 'summary':
whereCondition = "%s and siteName like '%s' " % (whereCondition, params.SiteName)
else:
table += "_SUMM"
query = "select %s from %s where %s %s %s " % (vars, table, whereCondition, groupBy, orderBy)
try:
cursor = connection.cursor()
cursor.execute(query)
except Exception as e:
raise PopularityDBException(query, e)
data = {}
data = {'DATA': utility.genericTranslateInList(cursor), 'COLLNAME': collName}
return data
def DSStatInTimeWindow(params, MView):
#cursor = connection.cursor()
if params.includeWMAgent == 'y':
baseMV = "STAT0"
elif params.includeWMAgent == 'n':
baseMV = "STAT1"
if MView == 'DataTier':
aggrMV = "AGGR2"
elif MView == 'DS':
aggrMV = "AGGR1"
elif MView == 'DSName':
aggrMV = 'AGGR4'
table = "%s.MV_DS_%s_%s" % (DBUSER, baseMV, aggrMV)
vars = '''collName , sum(numAccesses) as nAcc, round(sum(totCPU)/3600,0)
as totCPU, sum(numUsers) as nUsers'''
whereCondition = '''TDay >= to_date('%s','YYYY-MM-DD')
and TDay <= to_date('%s','YYYY-MM-DD')
''' % (params.TStart, params.TStop)
groupBy = "collName"
if params.orderVar == 'naccess' :
orderBy = "nAcc desc"
elif params.orderVar == 'nusers' :
orderBy = "nUsers desc"
else:
orderBy = "totCPU desc"
if hasattr(params, "collname"):
whereCondition += " and collName = '%s'" % (params.collname)
if params.SiteName != 'summary':
groupBy = "%s, siteName" % groupBy
whereCondition = "%s and siteName like '%s' " % (whereCondition, params.SiteName)
else:
table += "_SUMM"
query = '''select collName, nAcc, totcpu, nUsers, 100* ratio_to_report(nAcc)
over() as rnAcc , 100* ratio_to_report(totcpu)
over() as rtotcpu , 100* ratio_to_report(nUsers)
over() as rnUsers
from (select %s from %s where %s group by %s order by %s)
''' % (vars, table, whereCondition, groupBy, orderBy)
logger.info(query)
try:
cursor = connection.cursor()
cursor.execute(query)
except Exception as e:
raise PopularityDBException(query, e)
data = {}
data['DATA'] = utility.genericTranslateInList(cursor)
return data
def UserStatInTimeWindow(params):
#cursor = connection.cursor()
table = "%s.%s" % (DBUSER, "MV_XRD_DS_STAT0_AGGR3")
orderBy = "%s " % params.orderVar
if hasattr(params, 'LocalVsGlobal') and params.LocalVsGlobal == True :
whereCondition = '''TDay >= to_date('%s','YYYY-MM-DD')
and TDay <= to_date('%s','YYYY-MM-DD')
''' % (params.TStart, params.TStop)
groupBy = "REGEXP_INSTR(server_username,'^cms'), collName"
query = '''select replace(replace(username,\'0\',\'Local\'),\'1\',\'Grid\') as username, collName, nAcc, totcpu, readMB,
100* ratio_to_report(nAcc) over() as rnAcc ,
100* ratio_to_report(totcpu) over() as rtotcpu,
100* ratio_to_report(readMB) over() as rreadMB
from (select REGEXP_INSTR(server_username,\'^cms\') as username, collName, sum(numAccesses) as nAcc, round(sum(totcpu)/3600.,3) as totcpu, sum(readBytes)/1024/1024 / sum(numAccesses) as readMB
from %s where %s group by %s order by %s)
''' % (table, whereCondition, groupBy, orderBy)
else:
whereCondition = '''TDay >= to_date('%s','YYYY-MM-DD')
and TDay <= to_date('%s','YYYY-MM-DD')''' % (params.TStart, params.TStop)
if params.collName.lower() != 'all' :
whereCondition += '''and collName like '%s' ''' % params.collName
groupBy = "server_username"
query = '''select username, nAcc, totcpu, readMB,
100* ratio_to_report(nAcc) over() as rnAcc ,
100* ratio_to_report(totcpu) over() as rtotcpu,
100* ratio_to_report(readMB) over() as rreadMB
from (select server_username as username, sum(numAccesses) as nAcc, round(sum(totcpu)/3600.,3) as totcpu, sum(readBytes)/1024/1024 / sum(numAccesses) as readMB
from %s where %s group by %s order by %s) ''' % (table, whereCondition, groupBy, orderBy)
logger.info(query)
try:
cursor = connections[DBUSER].cursor()
cursor.execute(query)#
except Exception as e:
raise PopularityDBException(query, e)
data = {}
data['DATA'] = utility.genericTranslateInList(cursor)
return data
def UserStatInTimeWindow(params):
#cursor = connection.cursor()
table = "%s.%s" % (DBUSER, "MV_DS_STAT0_AGGR3")
vars = '''userid, sum(numAccesses) as nAcc,
round(sum(totCPU)/3600,0) as totCPU,
sum(numSites)/((to_date('%s','YYYY-MM-DD')-to_date('%s','YYYY-MM-DD'))+1) as nSites
''' % (params.TStop, params.TStart)
whereCondition = '''TDay >= to_date('%s','YYYY-MM-DD')
and TDay <= to_date('%s','YYYY-MM-DD')
''' % (params.TStart, params.TStop)
if params.CollName.lower() != 'all' :
whereCondition += '''and collName like '%s' ''' % params.CollName
groupBy = "userid"
if params.orderVar == 'nacc' :
orderBy = "nAcc desc"
elif params.orderVar == 'nsites' :
orderBy = "nSites desc"
else:
orderBy = "totCPU desc"
useridtable = "%s.%s" % (DBUSER, "MV_USER_USERID")
#NB: the additional group by username at the end of this query is needed because I discovered
## that the unicity userid <-> username is not guarantee: the same username can have more than one userid in dashboard
## the usage of userid was decided to help in making a query faster: being based on a integer search instead of a string search
query = '''select username, nAcc, nsites, totcpu, 100* ratio_to_report(nAcc)
over() as rnAcc , 100* ratio_to_report(nsites)
over() as rnsites , 100* ratio_to_report(totcpu)
over() as rtotcpu
from (select username, sum(nacc) as nAcc, sum(nsites) as nsites, sum(totcpu) as totcpu
from %s, (select %%s from %%s where %%s group by %%s ) res
where %s.userid=res.userid group by username order by %%s )
''' % (useridtable, useridtable)
query = query % (vars, table, whereCondition, groupBy, orderBy)
try:
cursor = connection.cursor()
cursor.execute(query)
except Exception as e:
raise PopularityDBException(query, e)
data = {}
data['DATA'] = utility.genericTranslateInList(cursor)
return data
def DSStatInTimeWindow(params):
#cursor = connection.cursor()
if params.table == 'DataTier':
table = "%s.%s" % (DBUSER, "MV_XRD_DS_STAT0_AGGR2")
elif params.table == 'DS':
table = "%s.%s" % (DBUSER, "MV_XRD_DS_STAT0_AGGR1")
elif params.table == 'DSName':
table = "%s.%s" % (DBUSER, "MV_XRD_DS_STAT0_AGGR4")
elif params.table == 'UserDS':
table = "%s.%s" % (DBUSER, "MV_XRD_DS_STAT1_AGGR1")
vars = '''collName , sum(numAccesses) as nAcc, round(sum(totCPU)/3600,0)
as totCPU, sum(numUsers)/( to_date('%s','YYYY-MM-DD') - to_date('%s','YYYY-MM-DD') +1 ) as nUsers,
sum(readBytes)/1024/1024 / sum(numAccesses) as readMB
''' % (params.TStop, params.TStart)
whereCondition = '''TDay >= to_date('%s','YYYY-MM-DD')
and TDay <= to_date('%s','YYYY-MM-DD')
and isUserCMS = %s
''' % (params.TStart, params.TStop, params.isUserCMS)
groupBy = "collName"
orderBy = "%s desc" % params.orderVar
if hasattr(params, "collname"):
whereCondition += " and collName = '%s'" % (params.collname)
# if params.SiteName != 'summary':
# groupBy = "%s, siteName" % groupBy
# whereCondition = "%s and siteName like '%s' " % (whereCondition, params.SiteName)
# else:
# table += "_SUMM"
query = '''select collName, nAcc, totcpu, nUsers, readMB,
100* ratio_to_report(nAcc) over() as rnAcc ,
100* ratio_to_report(totcpu) over() as rtotcpu ,
100* ratio_to_report(nUsers) over() as rnUsers ,
100* ratio_to_report(readMB) over() as rreadMB
from (select %s from %s where %s group by %s order by %s)
''' % (vars, table, whereCondition, groupBy, orderBy)
logger.info(query)
try:
cursor = connections[DBUSER].cursor()
cursor.execute(query)
except Exception as e:
raise PopularityDBException(query, e)
data = {}
data['DATA'] = utility.genericTranslateInList(cursor)
return data
def DSStatInTimeWindow(params):
#cursor = connection.cursor()
if params.table == 'DataTier':
table = "%s.%s" % (DBUSER, "MV_XRD_DS_STAT0_AGGR2")
elif params.table == 'DS':
table = "%s.%s" % (DBUSER, "MV_XRD_DS_STAT0_AGGR1")
elif params.table == 'DSName':
table = "%s.%s" % (DBUSER, "MV_XRD_DS_STAT0_AGGR4")
elif params.table == 'UserDS':
table = "%s.%s" % (DBUSER, "MV_XRD_DS_STAT1_AGGR1")
vars = '''collName , sum(numAccesses) as nAcc, round(sum(totCPU)/3600,0)
as totCPU, sum(numUsers)/( to_date('%s','YYYY-MM-DD') - to_date('%s','YYYY-MM-DD') +1 ) as nUsers,
sum(readBytes)/1024/1024 / sum(numAccesses) as readMB
''' % (params.TStop, params.TStart)
whereCondition = '''TDay >= to_date('%s','YYYY-MM-DD')
and TDay <= to_date('%s','YYYY-MM-DD')
and isUserCMS = %s
''' % (params.TStart, params.TStop, params.isUserCMS)
groupBy = "collName"
orderBy = "%s desc" % params.orderVar
if hasattr(params, "collname"):
whereCondition += " and collName = '%s'" % (params.collname)
# if params.SiteName != 'summary':
# groupBy = "%s, siteName" % groupBy
# whereCondition = "%s and siteName like '%s' " % (whereCondition, params.SiteName)
# else:
# table += "_SUMM"
query = '''select collName, nAcc, totcpu, nUsers, readMB,
100* ratio_to_report(nAcc) over() as rnAcc ,
100* ratio_to_report(totcpu) over() as rtotcpu ,
100* ratio_to_report(nUsers) over() as rnUsers ,
100* ratio_to_report(readMB) over() as rreadMB
from (select %s from %s where %s group by %s order by %s)
''' % (vars, table, whereCondition, groupBy, orderBy)
logger.info(query)
try:
cursor = connections[DBUSER].cursor()
cursor.execute(query)
except Exception as e:
raise PopularityDBException(query, e)
data = {}
data['DATA'] = utility.genericTranslateInList(cursor)
return data
def UserStatInTimeWindow(params):
#cursor = connection.cursor()
table = "%s.%s" % (DBUSER, "MV_XRD_DS_STAT0_AGGR3")
orderBy = "%s " % params.orderVar
if hasattr(params, 'LocalVsGlobal') and params.LocalVsGlobal == True:
whereCondition = '''TDay >= to_date('%s','YYYY-MM-DD')
and TDay <= to_date('%s','YYYY-MM-DD')
''' % (params.TStart, params.TStop)
groupBy = "REGEXP_INSTR(server_username,'^cms'), collName"
query = '''select replace(replace(username,\'0\',\'Local\'),\'1\',\'Grid\') as username, collName, nAcc, totcpu, readMB,
100* ratio_to_report(nAcc) over() as rnAcc ,
100* ratio_to_report(totcpu) over() as rtotcpu,
100* ratio_to_report(readMB) over() as rreadMB
from (select REGEXP_INSTR(server_username,\'^cms\') as username, collName, sum(numAccesses) as nAcc, round(sum(totcpu)/3600.,3) as totcpu, sum(readBytes)/1024/1024 / sum(numAccesses) as readMB
from %s where %s group by %s order by %s)
''' % (table, whereCondition, groupBy, orderBy)
else:
whereCondition = '''TDay >= to_date('%s','YYYY-MM-DD')
and TDay <= to_date('%s','YYYY-MM-DD')''' % (
params.TStart, params.TStop)
if params.collName.lower() != 'all':
whereCondition += '''and collName like '%s' ''' % params.collName
groupBy = "server_username"
query = '''select username, nAcc, totcpu, readMB,
100* ratio_to_report(nAcc) over() as rnAcc ,
100* ratio_to_report(totcpu) over() as rtotcpu,
100* ratio_to_report(readMB) over() as rreadMB
from (select server_username as username, sum(numAccesses) as nAcc, round(sum(totcpu)/3600.,3) as totcpu, sum(readBytes)/1024/1024 / sum(numAccesses) as readMB
from %s where %s group by %s order by %s) ''' % (table, whereCondition,
groupBy, orderBy)
logger.info(query)
try:
cursor = connections[DBUSER].cursor()
cursor.execute(query) #
except Exception as e:
raise PopularityDBException(query, e)
data = {}
data['DATA'] = utility.genericTranslateInList(cursor)
return data
def getSitesList(DB):
""" Query the memorized view MV_Site to get a list of
the sites present on the database"""
table = "%s.%s" % (DB, "MV_Site")
query = 'select SITENAME from %s order by SITENAME' % (table)
try:
cursor = connections[DB].cursor()
cursor.execute(query)
data = {}
data = utility.genericTranslateInList(cursor)
sitelist = map(lambda x: x["SITENAME"], data)
return sitelist
except Exception as e:
raise PopularityDBException(query, e)
def getSitesList(DB):
""" Query the memorized view MV_Site to get a list of
the sites present on the database"""
table = "%s.%s" % (DB, "MV_Site")
query = 'select SITENAME from %s order by SITENAME' % (table)
try:
cursor = connections[DB].cursor()
cursor.execute(query)
data = {}
data = utility.genericTranslateInList(cursor)
sitelist = map(lambda x: x["SITENAME"], data)
return sitelist
except Exception as e:
raise PopularityDBException(query, e)
def getProcessedDataSetList(DB):
""" Query the memorized view MV_DSNAME to get a list of
the Processed DataSets present on the database"""
table = "%s.%s" % (DB, "MV_DSNAME")
query = 'select COLLNAME from %s' % (table)
try:
cursor = connections[DB].cursor()
cursor.execute(query)
data = {}
data = utility.genericTranslateInList(cursor)
"""
dslist = map(lambda x: x["COLLNAME"], data)
"""
return data
except Exception as e:
raise PopularityDBException(query, e)
def getProcessedDataSetList(DB):
""" Query the memorized view MV_DSNAME to get a list of
the Processed DataSets present on the database"""
table = "%s.%s" % (DB, "MV_DSNAME")
query = 'select COLLNAME from %s' % (table)
try:
cursor = connections[DB].cursor()
cursor.execute(query)
data = {}
data = utility.genericTranslateInList(cursor)
"""
dslist = map(lambda x: x["COLLNAME"], data)
"""
return data
except Exception as e:
raise PopularityDBException(query, e)
