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 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 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_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 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 WhatInSiteWithStat(collType, params):
data = []
logger.info('Using access data source: %s' % params.source)
DBUSER = '******'
if params.source == 'xrootd':
DBUSER = '******'
logger.info('Using DBUSER: %s' % DBUSER)
### D.G. Comment [2014-03-18] request to cover also the last 12 months, moving to MV MV_block_stat0_aggr_12_months
### D.G. Comment: MV_block_stat0_aggr_5_weeks is included in MV_block_stat0_aggr_180_days. Going to use only this
#table_name = 'MV_block_stat0_aggr_5_weeks'
#delta = datetime.strptime(params.TStop,"%Y-%m-%d") - datetime.strptime(params.TStart,"%Y-%m-%d")
#if delta.days > 35:
table_name = 'MV_block_stat0_aggr_12_months'
#logger.info('Using MV %s' %table_name)
vars = "SITENAME, COLLNAME"
if collType == 'BlocksStat':
table = "%s.%s" % (DBUSER, table_name)
vars += ", sum(TOTCPU) as TOTCPU, sum(NUMACCESSES) as NACC"
else:
return {}
whereCondition = "TDay >= trunc(to_date('%s','YYYY-MM-DD'),'W') and TDay <= trunc(to_date('%s','YYYY-MM-DD'),'W') " % (
params.TStart, params.TStop)
whereCondition += " and SiteName like %s" % '%s'
groupBy = 'sitename, collname'
orderBy = 'sitename'
query = "select %s from %s where %s group by %s order by %s;" % (
vars, table, whereCondition, groupBy, orderBy)
logger.info("WhatInSiteWithStat query: %s" % query)
try:
cursor = connections[DBUSER].cursor()
cursor.execute(query, [params.SiteName])
data = victorinterfaceUtility.genericTranslateInListDictVict(
cursor, 'SITENAME', 'COLLNAME')
# data=_genericTranslateInListDict(cursor,'COLLNAME','SITENAME')
except Exception as e:
raise PopularityDBException(query, e)
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 AccessStatsByDirAtSite(params):
# Provides access statistics aggregated by directory
# Requires parameters SiteName and DirName (the top-level dir from which to provide the statistics)
# Currently only supported for xrootd popularity at T2_CH_CERN (EOS)
data = {}
logger.info('Using access data source: %s' % params.source)
if params.source == 'xrootd':
DBUSER = '******'
else:
raise Paramvalidationexception(
'source',
'param source=%s unsupported, please select source=xrootd' %
params.source)
logger.info('Using DBUSER: %s' % DBUSER)
vars = "'T2_CH_CERN' as SITENAME, regexp_replace(PATH,'^/eos/cms','') as PATH, (max_tday - to_date('1970-01-01','YYYY-MM-DD')) * 86400 as LASTDAY, (min_tday - to_date('1970-01-01','YYYY-MM-DD')) * 86400 as FIRSTDAY, READ_ACC as NACC, READ_BYTES as READMBYTES"
table = "%s.%s" % (DBUSER, 'V_XRD_STAT2_AGGR1')
whereCondition = " 'T2_CH_CERN' like %s and regexp_replace(PATH,'^/eos/cms','') like %s" % (
'%s', '%s')
query = "select %s from %s where %s" % (vars, table, whereCondition)
logger.info('AccessStatsByDirAtSite query: %s' % query)
# -----------------------------------
try:
cursor = connections[DBUSER].cursor()
# For wildcard queries, we need to add the % wildcard in the bind variable...
cursor.execute(query, [params.SiteName, params.TopDir + '%'])
data = victorinterfaceUtility.genericTranslateInListDictVict(
cursor, 'SITENAME', 'PATH')
except Exception as e:
raise PopularityDBException(query, e)
return data
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 WhatInSiteWithStatLastAcc(collType, params):
data = []
logger.info('Using access data source: %s' % params.source)
DBUSER = '******'
if params.source == 'xrootd':
DBUSER = '******'
logger.info('Using DBUSER: %s' % DBUSER)
applyPatch = False
if (params.SiteName == 'T2_CH_CERN'
or params.SiteName == 'T0_CH_CERN') and params.source != 'xrootd':
applyPatch = True
logger.info(
'applying patch to cope with the fact sitename in crab monitoring for CH_CERN moved from T0 to T2 in Apr 2013, affecting popularity statistics'
)
vars = "SITENAME, COLLNAME, (tday - to_date('1970-01-01','YYYY-MM-DD')) * 86400 as LASTDAY"
table = "%s.%s" % (DBUSER, 'MV_block_stat0_last_access')
whereCondition = " SiteName like %s" % '%s'
query = "select %s from %s where %s" % (vars, table, whereCondition)
if applyPatch:
query = "select 'T2_CH_CERN' as SITENAME, COLLNAME, max(tday - to_date('1970-01-01','YYYY-MM-DD')) * 86400 as LASTDAY from " + table + " where SITENAME = 'T0_CH_CERN' or SITENAME = 'T2_CH_CERN' group by COLLNAME"
logger.info('WhatInSiteWithStatLastAcc query: %s' % query)
# -----------------------------------
try:
cursor = connections[DBUSER].cursor()
if applyPatch:
cursor.execute(query)
else:
cursor.execute(query, [params.SiteName])
data = victorinterfaceUtility.genericTranslateInListDictVict(
cursor, 'SITENAME', 'COLLNAME')
except Exception as e:
raise PopularityDBException(query, e)
return data
def MostPopDSStat(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")
#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\'
and isUserCMS = %s
''' % (params.collName, params.isUserCMS)
orderBy = "TDay "
vtime = "trunc(TDay,'%s')" % (timeformatTrunc)
groupBy = "group by %s, collName" % vtime
vars = ''' %s as TDay, sum(numAccesses) as naccess,
round(sum(totCPU)/3600,0) as totCPU,
sum(numUsers) as nUsers
''' % (vtime)
#if params.SiteName != 'summary':
# whereCondition = "%s and siteName like '%s' " % (whereCondition, params.SiteName)
#else:
# table += "_SUMM"
query = "select %s from %s where %s %s " % (vars, table, whereCondition,
groupBy)
query = '''select round((
TDay-to_date(\'19700101\',\'YYYYMMDD\')
)*86400)*1000
as millisecondsSinceEpoch,
ta.%s
from ( %s ) ta order by %s''' % (params.orderVar, query,
orderBy)
logger.info(query)
try:
cursor = connections[DBUSER].cursor()
cursor.execute(query)
except Exception as e:
raise PopularityDBException(query, e)
data = {
'data': [(utility.assignValue(row[0]), utility.assignValue(row[1]))
for row in cursor.fetchall()],
'name':
params.collName
}
return data