def purge_jobhistory():
with db.trans() as c:
c.execute('''
SELECT id
FROM jobs
''')
for job in c:
job_id = job['id']
with db.trans() as c2:
c2.execute(
'''
SELECT daystokeep
FROM jobconfig
WHERE job_id = %(job_id)s
''', locals())
daystokeep = 30
r = c2.fetchone()
if r:
daystokeep = r['daystokeep']
with db.trans() as c3:
c3.execute(
'''
DELETE FROM jobhistory
WHERE date(now()) - date(datestarted) > %(daystokeep)s
AND job_id = %(job_id)s
''', locals())
if c3.rowcount > 0:
log.debug("purged %s entries for jobhistory",
c3.rowcount)
def test_trans(self):
"Test transaction with commit"
tbl = rand()
with db.trans() as c:
c.execute("create table " + tbl + " (test text)")
c.execute("insert into " + tbl + " values ('test')")
with db.trans() as c:
c.execute("select test from " + tbl + " limit 1")
r = c.fetchone()
self.assertEqual(r[0], "test")
def test_trans(self):
"Test transaction with commit"
tbl = rand()
with db.trans() as c:
c.execute("create table " + tbl + " (test text)")
c.execute("insert into " + tbl + " values ('test')")
with db.trans() as c:
c.execute("select test from " + tbl + " limit 1")
r = c.fetchone()
self.assertEqual(r[0], 'test')
def test_trans_rollback(self):
"Test transaction with rollback"
tbl = rand()
with db.trans() as c:
c.execute("create table " + tbl + " (test text)")
with self.assertRaises(psycopg2.DataError):
with db.trans() as c:
c.execute("insert into " + tbl + " values ('test')")
c.execute("select 1/0")
with db.trans() as c:
c.execute("select test from " + tbl + " limit 1")
r = c.fetchone()
self.assertIs(r, None)
def test_trans_rollback(self):
"Test transaction with rollback"
tbl = rand()
with db.trans() as c:
c.execute("create table " + tbl + " (test text)")
with self.assertRaises(psycopg2.DataError):
with db.trans() as c:
c.execute("insert into " + tbl + " values ('test')")
c.execute("select 1/0")
with db.trans() as c:
c.execute("select test from " + tbl + " limit 1")
r = c.fetchone()
self.assertIs(r, None)
def jobhistory(computername, computeruser, script):
ctx = getctx()
ctx['computername'] = computername
ctx['computeruser'] = computeruser
ctx['script'] = script
with db.trans() as c:
c.execute(
'''
SELECT h.id
, j.computername
, j.computeruser
, j.script
, h.datestarted
, h.datefinished
, h.status
, h.duration
FROM jobhistory AS h
INNER JOIN jobs AS j
ON j.id = h.job_id
WHERE j.computername = %(computername)s
AND j.computeruser = %(computeruser)s
AND j.script = %(script)s
ORDER BY j.computername
, j.computeruser
, j.script
, h.datestarted DESC
''', locals())
history = []
for hist in c:
h = dict(hist)
h['duration'] = duration_to_human(h['duration'])
history.append(h)
return dict(history=history, ctx=ctx)
def savedaystokeep(computername, computeruser, script):
daystokeep = request.forms.daystokeep
if not re.match(r'^\d+$', daystokeep):
abort(400, "invalid days to keep")
daystokeep = int(daystokeep)
if daystokeep < 0:
return 'days to keep must be >= 0'
job_id = get_job_id(computername, computeruser, script)
with db.trans() as c:
c.execute(
'''
UPDATE jobconfig
SET daystokeep = %(daystokeep)s
WHERE job_id = %(job_id)s
''', locals())
if c.rowcount == 0:
c.execute(
'''
INSERT INTO jobconfig
(job_id, daystokeep)
VALUES
(%(job_id)s, %(daystokeep)s)
''', locals())
return redirect('/config-job/' + computername + '/' + computeruser + '/' +
script + '/')
def joboutput(computername, computeruser, script, id):
if not re.match(r'^[a-f0-9-]{36}$', id):
raise ValueError('invalid id')
output = ''
with db.trans() as c:
c.execute(
'''
SELECT o.output
FROM jobhistory AS h
INNER JOIN jobs AS j
ON h.job_id = j.id
INNER JOIN outputs AS o
ON o.sha1 = h.output_sha1
WHERE j.computername = %(computername)s
AND j.computeruser = %(computeruser)s
AND j.script = %(script)s
AND h.id = %(id)s
''', locals())
r = c.fetchone()
if not r:
response.status = 404
return 'not found'
else:
response.content_type = 'text/plain; charset=utf-8'
return zlib.decompress(r['output'])
def configjob(computername, computeruser, script):
ctx = getctx()
ctx['computername'] = computername
ctx['computeruser'] = computeruser
ctx['script'] = script
daystokeep = 30
with db.trans() as c:
c.execute(
'''
SELECT c.daystokeep
FROM jobconfig AS c
INNER JOIN jobs AS j
ON j.id = c.job_id
WHERE j.computername = %(computername)s
AND j.computeruser = %(computeruser)s
AND j.script = %(script)s
''', locals())
r = c.fetchone()
if r:
daystokeep = r['daystokeep']
c.execute(
'''
SELECT a.email
FROM jobconfigalert AS a
INNER JOIN jobs AS j
ON j.id = a.job_id
WHERE j.computername = %(computername)s
AND j.computeruser = %(computeruser)s
AND j.script = %(script)s
''', locals())
emails = []
for r in c:
emails.append(r['email'])
return dict(ctx=ctx, daystokeep=daystokeep, emails="\n".join(emails))
def allhistory():
offset = 0
if 'offset' in request.query:
if re.match(r'^\d+$', request.query.offset):
offset = int(request.query.offset) * 25
with db.trans() as c:
c.execute(
'''
SELECT h.id
, j.computername
, j.computeruser
, j.script
, h.datestarted
, h.datefinished
, h.status
, h.duration
FROM jobhistory AS h
INNER JOIN jobs AS j
ON j.id = h.job_id
ORDER BY h.datestarted DESC LIMIT 25 OFFSET %(offset)s
''', locals())
history = []
for hist in c:
h = dict(hist)
h['duration'] = duration_to_human(h['duration'])
history.append(h)
return dict(history=history, offset=offset)
def removesession(session_id):
with db.trans() as c:
c.execute(
'''
DELETE FROM sessions
WHERE session_id = %(session_id)s
''', locals())
def purge_sessions():
with db.trans() as c:
c.execute('''
DELETE FROM sessions
WHERE date(now()) - date(date_login) > 7
''')
if c.rowcount > 0:
log.info('purged %s login sessions', c.rowcount)
def userisadmin(username):
with db.trans() as c:
c.execute(
'''
SELECT is_admin
FROM users
WHERE username = %(username)s
''', locals())
return c.fetchone()['is_admin']
def purge_outputs():
with db.trans() as c:
c.execute('''
DELETE FROM outputs
WHERE sha1 NOT IN (
SELECT DISTINCT output_sha1 FROM jobhistory
)
''')
if c.rowcount > 0:
log.debug("purged %s entries for outputs", c.rowcount)
def validatesession(session_id):
with db.trans() as c:
c.execute(
'''
SELECT session_id
FROM sessions
WHERE session_id = %(session_id)s
''', locals())
r = c.fetchone()
return bool(r)
def currentuser():
session_id = request.get_cookie('clog')
with db.trans() as c:
c.execute(
'''
SELECT username
FROM sessions
WHERE session_id = %(session_id)s
''', locals())
return c.fetchone()['username']
def makesession(user):
with db.trans() as c:
session_id = str(uuid4())
c.execute(
'''
INSERT INTO sessions
(session_id, username)
VALUES
(%(session_id)s, %(user)s)
''', locals())
return session_id
def admin():
users = []
with db.trans() as c:
c.execute('''
SELECT username, is_admin
FROM users
ORDER BY username
''')
for user in c:
user = dict(user)
users.append(user)
return dict(ctx=getctx(), users=users)
def validateuserdb(user, passwd):
passwdsha1 = sha1(passwd).hexdigest()
with db.trans() as c:
c.execute(
'''
SELECT username
FROM users
WHERE username = %(user)s
AND password = %(passwdsha1)s
''', locals())
r = c.fetchone()
return bool(r)
def forceuserpassword(username):
password = str(int(random.random() * 999999))
sha1password = sha1(password).hexdigest()
if username == currentuser():
return 'cant change password for current user!'
with db.trans() as c:
c.execute(
'''
UPDATE users
SET password = %(sha1password)s
WHERE username = %(username)s
''', locals())
return u'user %s had password changed to: %s' % (username, password)
def getalertemails(computername, computeruser, script):
job_id = get_job_id(computername, computeruser, script)
with db.trans() as c:
c.execute(
'''
SELECT email
FROM jobconfigalert
WHERE job_id = %(job_id)s
''', locals())
emails = []
for row in c:
emails.append(row['email'])
return emails
def changeuseradminstatus(username, status):
if username == currentuser():
return 'cant change admin status for current user!'
if status not in ('0', '1'):
abort(400, "invalid status")
status = bool(int(status))
with db.trans() as c:
c.execute(
'''
UPDATE users
SET is_admin = %(status)s
WHERE username = %(username)s
''', locals())
return redirect('/admin')
def get_job_id(computername, computeruser, script):
with db.trans() as c:
c.execute(
'''
SELECT id
FROM jobs
WHERE computername = %(computername)s
AND computeruser = %(computeruser)s
AND script = %(script)s
''', locals())
r = c.fetchone()
if not r:
return None
else:
return r[0]
def removeuser(username):
if username == currentuser():
return 'cant remove current user!'
with db.trans() as c:
c.execute(
'''
DELETE FROM sessions
WHERE username = %(username)s
''', locals())
c.execute(
'''
DELETE FROM users
WHERE username = %(username)s
''', locals())
return redirect('/admin')
def newuser():
username = request.forms.username
if username.strip() == '':
return 'invalid user!'
password = str(int(random.random() * 999999))
sha1password = sha1(password).hexdigest()
with db.trans() as c:
c.execute(
'''
INSERT INTO users
(username, password, is_admin)
VALUES
(%(username)s, %(sha1password)s, 'f')
''', locals())
return u'user %s created with password %s' % (username, password)
def savealertemails(computername, computeruser, script):
job_id = get_job_id(computername, computeruser, script)
with db.trans() as c:
c.execute(
'''
DELETE FROM jobconfigalert
WHERE job_id = %(job_id)s
''', locals())
for email in request.forms.emails.split():
c.execute(
'''
INSERT INTO jobconfigalert
VALUES
(%(job_id)s, %(email)s)
''', locals())
return redirect('/config-job/' + computername + '/' + computeruser + '/' +
script + '/')
def changepasswordsave():
oldpasswd = request.forms.oldpasswd
newpasswd = request.forms.newpasswd
newpasswd2 = request.forms.newpasswd2
username = currentuser()
if not validateuserdb(username, oldpasswd):
return 'invalid current password!'
if newpasswd.strip() == '' or newpasswd2.strip() == '':
return 'invalid new password!'
if newpasswd != newpasswd2:
return 'new passwords do not match!'
passwdsha1 = sha1(newpasswd).hexdigest()
with db.trans() as c:
c.execute(
'''
UPDATE users
SET password = %(passwdsha1)s
WHERE username = %(username)s
''', locals())
return redirect('/')
def jobs():
with db.trans() as c:
c.execute('''
SELECT computername
, computeruser
, script
, date_last_success
, date_last_failure
, last_status
, last_duration
FROM jobs
ORDER BY computername
, computeruser
, script
''')
jobs = []
for job in c:
j = dict(job)
j['date_last_success'] = date_to_human(j['date_last_success'])
j['date_last_failure'] = date_to_human(j['date_last_failure'])
j['last_duration'] = duration_to_human(j['last_duration'])
jobs.append(j)
return dict(jobs=jobs)
def purgejob(computername, computeruser, script):
job_id = get_job_id(computername, computeruser, script)
with db.trans() as c:
c.execute(
'''
DELETE FROM jobhistory
WHERE job_id = %(job_id)s
''', locals())
c.execute(
'''
DELETE FROM jobconfig
WHERE job_id = %(job_id)s
''', locals())
c.execute(
'''
DELETE FROM jobconfigalert
WHERE job_id = %(job_id)s
''', locals())
c.execute(
'''
DELETE FROM jobs
WHERE id = %(job_id)s
''', locals())
return redirect('/')
def newjob():
rid_regexp = r'^[a-f0-9]{8}-[a-f0-9]{4}-[a-f0-9]{4}-[a-f0-9]{4}-[a-f0-9]{12}$'
rid = request.forms.id
if not re.match(rid_regexp, rid):
abort(400, "invalid job id")
start_time = request.forms.start_time
if not re.match(r'^[\d+\.]+$', start_time):
abort(400, "invalid start time")
start_time = datetime.datetime.fromtimestamp(int(float(start_time)))
end_time = request.forms.end_time
if not re.match(r'^[\d+\.]+$', end_time):
abort(400, "invalid end time")
end_time = datetime.datetime.fromtimestamp(int(float(end_time)))
duration = request.forms.duration
if not re.match(r'^[0-9\.]+$', duration):
abort(400, "invalid duration")
status = request.forms.status
if status not in ('fail', 'ok'):
abort(400, "invalid status")
script = request.forms.script
if not re.match(r'^[a-zA-Z0-9\-_\.]+$', script):
abort(400, "invalid script name")
output = request.forms.output or ''
output = b64decode(output)
outputz = buffer(zlib.compress(output))
computername = request.forms.computername
computeruser = request.forms.username
# Windows
if '\\' in computeruser:
computeruser = computeruser.split('\\')[-1]
ip = request.remote_addr
log.info('new job status from %s@%s/%s (%s)', computeruser, computername,
script, ip)
log.info(' id: %s', rid)
output_sha1 = sha1(output).hexdigest()
with db.trans() as c:
try:
c.execute(
'''
INSERT INTO outputs
(sha1, output)
VALUES
(%(output_sha1)s, %(outputz)s)
''', locals())
except db.psycopg2.IntegrityError:
pass
job_id = get_job_id(computername, computeruser, script)
if not job_id:
with db.trans() as c:
c.execute(
'''
INSERT INTO jobs (
computername, computeruser, script,
last_status, last_duration
)
VALUES (
%(computername)s, %(computeruser)s, %(script)s,
%(status)s, %(duration)s
)
RETURNING id
''', locals())
job_id = c.fetchone()[0]
try:
with db.trans() as c:
c.execute(
'''
INSERT INTO jobhistory (
id, job_id, ip, datestarted, datefinished,
status, duration, output_sha1
)
VALUES (
%(rid)s, %(job_id)s, %(ip)s, %(start_time)s,
%(end_time)s, %(status)s, %(duration)s, %(output_sha1)s
)
''', locals())
if status == 'ok':
c.execute(
'''
UPDATE jobs
SET date_last_success = %(start_time)s
, last_status = 'ok'
, last_duration = %(duration)s
WHERE id = %(job_id)s
''', locals())
else:
c.execute(
'''
UPDATE jobs
SET date_last_failure = %(start_time)s
, last_status = 'fail'
, last_duration = %(duration)s
WHERE id = %(job_id)s
''', locals())
except db.psycopg2.IntegrityError:
# Ignoring duplicate insertion.
return 'ok'
else:
emails = getalertemails(computername, computeruser, script)
if emails:
if status == 'fail':
for email in emails:
log.info(" job failed, sending alert to %s", email)
send_alert(email, computername, computeruser, script,
status, output)
elif status == 'ok':
with db.trans() as c:
c.execute(
'''
SELECT status
FROM jobhistory
WHERE job_id = %(job_id)s
ORDER BY datestarted DESC LIMIT 1 OFFSET 1
''', locals())
r = c.fetchone()
if r and r['status'] == 'fail':
for email in emails:
log.info(" job ok, sending alert to %s", email)
send_alert(email, computername, computeruser,
script, status, output)
return 'ok'
