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