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)