def gettime(service, today, sep, nmonths, DEBUG):
import dbtools.datemapper as dm
import dbtools as dbt
import simpletemplate as st
import datetime
import sys
dbt.DEBUG = DEBUG
service_db = dbt.SERVICE_DB[service.lower()]
monthstart = datetime.date(today.year, today.month, 1)
lastmonthend = dm.subtractmonth(monthstart)
# Get the last 12 months.
months = dm.getlastnmonths(monthstart, nmonths)
total = 0
print("Month" + sep + "Time(h)" + sep)
for a in reversed(list(months.keys())):
period = dm.datetoperiod(months[a])
keys = {'%DB%': service_db, '%PERIOD%': period}
query = st.templatefile(filename="sql/usage.sql", keys=keys)
results = dbt.dbquery(db=keys['%DB%'], query=query)
t = dbt.undecimal(results[0]['sum((run_time*cost))']) / 3600
total += t
print(period + sep + str(t) + sep)
print("Total" + sep + str(total) + sep)
print("Average" + sep + str(total / nmonths) + sep)
def genfacstats(service, today, sep='|', nmonths=12, DEBUG=False):
import dbtools.facultymapper as fm
import dbtools.datemapper as dm
import dbtools as dbt
import simpletemplate as st
import datetime
import sys
dbt.DEBUG = DEBUG
# Timing.
starttime = datetime.datetime.now()
# Get the latest faculty map.
fmap = fm.getmap()
service_db = dbt.SERVICE_DB[service.lower()]
monthstart = datetime.date(today.year, today.month, 1)
lastmonthend = dm.subtractmonth(monthstart)
# Get the last 12 months.
months = dm.getlastnmonths(monthstart, nmonths)
print("Faculty", end=sep)
for i in range(1, nmonths + 1):
print(dm.datetoperiod(months[i]), end=sep)
print("")
# we need to loop over faculties, querying to get the sum for each period.
for f in sorted(fmap.keys()):
print(f, end=sep)
usage = {}
d = dbt.sqllist(fmap[f])
keys = {'%DB%': service_db, '%DEPARTMENT%': d}
query = st.templatefile(filename="sql/facultyusage.sql", keys=keys)
results = dbt.dbquery(db=keys['%DB%'], query=query)
for i in range(1, nmonths + 1):
txtdate = dm.datetoperiod(months[i])
usage[txtdate] = 0
for j in results:
if j["Period"] == txtdate:
usage[txtdate] = dbt.undecimal(j["Total CPU Time Usage"])
for i in range(1, nmonths + 1):
txtdate = dm.datetoperiod(months[i])
print(usage[txtdate], end=sep)
print("")
# Timing.
endtime = datetime.datetime.now()
if DEBUG:
dbt.log(str(endtime - starttime),
"Time taken to generate faculty usage report")
def gendeptstats(service, today, sep='|', nmonths=12, DEBUG=False):
import dbtools.datemapper as dm
import dbtools as dbt
import simpletemplate as st
import datetime
import sys
dbt.DEBUG = DEBUG
service_db = dbt.SERVICE_DB[service.lower()]
monthstart = datetime.date(today.year, today.month, 1)
lastmonthend = dm.subtractmonth(monthstart)
# Get the last 12 months.
months = dm.getlastnmonths(monthstart, nmonths)
print("Dept", end=sep)
for i in range(1, nmonths + 1):
print(dm.datetoperiod(months[i]), end=sep)
print("")
keys = {'%DB%': service_db}
query = st.templatefile(filename="sql/cputime-by-department.sql",
keys=keys)
results = dbt.dbquery(db=keys['%DB%'], query=query)
departments = []
for a in results:
if a["Department"] not in departments:
departments.append(a["Department"])
for a in departments:
print(a, end=sep)
for b in range(1, nmonths + 1):
d = False
for c in results:
if c["Department"] == a and c["Period"] == dm.datetoperiod(
months[b]):
print(c["Total CPU Time Usage"], end=sep)
d = True
if not d:
print("0", end=sep)
print("")
#!/usr/bin/env python3
# Print out a list of users in a given department.
# Useful for department-specific ACLs.
import dbtools as dbt
import simpletemplate as st
import sys
if len(sys.argv) < 2:
print("Usage: " + sys.argv[0] + " \"department\"")
sys.exit(1)
service_db = "myriad_sgelogs"
d = dbt.sqllist(sys.argv[1])
keys = {'%DB%': service_db, '%DEPARTMENT%': d}
query = st.templatefile(filename="sql/facultyusers.sql", keys=keys)
results = dbt.dbquery(db=keys['%DB%'], query=query)
for a in results:
print(a['username'])
# Print out the unique users over all services over all time.
import dbtools as dbt
import simpletemplate as st
keys = {}
query = st.templatefile(filename="sql/allusersalltime.sql", keys=keys)
results = dbt.dbquery(query=query)
print("Period|Users")
for a in results:
print(a['Period'] + "|" + str(a['Total Users']))