Aug-14-2017, 04:32 PM
Hello All,
My company is using request tracker for its ticketing solutions.
There is also a reporting tool written in python that is really simple and has the following functions:
1. It makes a query based on a specific queue, user and time frame;
2. Produces a csv report for the processed tickets with the time spent on handling those.
The source of the in formation are sql databases. The request tracker is web based and runs on Ubuntu 14.
The matter is that for some reason it fails to detect tickets with failed SLAs and marks all of them with SLA passed.
Perhaps you could take a look and share your opinion what might be touched here.
The whole code is pasted below.
Thanks for sharing any knowledge.
My company is using request tracker for its ticketing solutions.
There is also a reporting tool written in python that is really simple and has the following functions:
1. It makes a query based on a specific queue, user and time frame;
2. Produces a csv report for the processed tickets with the time spent on handling those.
The source of the in formation are sql databases. The request tracker is web based and runs on Ubuntu 14.
The matter is that for some reason it fails to detect tickets with failed SLAs and marks all of them with SLA passed.
Perhaps you could take a look and share your opinion what might be touched here.
The whole code is pasted below.
Thanks for sharing any knowledge.
#! /usr/bin/python
from flask import Flask, request, Response, render_template
import subprocess, datetime
from datetime import date, timedelta
import time
app = Flask(__name__)
from config import *
from pprint import pprint
@app.route('/', methods=['GET'])
def agent1():
import rt
out="""<!Doctype>
<html>
<head>
<title>Agent Report v2.4</title>
<link rel="stylesheet" href=\""""+css+"""">
</head>
<body>
<form method="post" action="">
User: <select name="user"><option />"""
users=rt.getusers()
for id,user,name in users:
out+="<option value='"+str(user)+"'>"+str(name)+"</option>"
out+='</select> or Queue: <select name="queue" multiple="multiple"><option />'
for queue in rt.getqueues():
out+="<option value='"+str(queue[0])+"'>"+str(queue[0])+"</option>"
out+="""</select>
<br />
<select name="dir">
<option value="Created">Created</option>
<option value="Resolved">Resolved</option>
<option value="Open">Open</option>
</select> between:<br />
<label>Start Date: </label><input type="date" name="start" value=\""""+(date.today()-timedelta(days=1)).isoformat()[:10]+"""" /><br />
<label>End Date: </label><input type="date" name="end" value=\""""+datetime.datetime.now().isoformat()[:10]+"""" /><br />
<label>SLA?</label> None:<input type="radio" name="sla" value='0' checked="checked" /> """+sla1['name']+""":<input type="radio" name="sla" value='1'/> """+sla2['name']+""":<input type="radio" name="sla" value='2'/><br />
<input type="submit" value="Query" /> (This may take a long time.)
</form>
</body>
</html>
"""
return out
#@app.route('/agent1/',methods=['POST'])
@app.route('/',methods=['POST'])
def agent1post():
import rt
if request.form['sla']=='1':
responsesla=sla1['responsetime']
resolvesla=sla1['resolvetime']
if request.form['sla']=='2':
responsesla=sla2['responsetime']
resolvesla=sla2['resolvetime']
out=('{},{},{},{},{},{},{},{},{},{},{},{},{},{}\r\n'.format("id","Subject","Queue","Priority","Category","Owner","Created","","Started","","Resolved","","SLA","Response (hours)"))
resolvetimes=[]
search="Owner='{}'".format(request.form['user']) if request.form['user']!="" else "( Queue='{}' )".format("' or Queue='".join(request.form.getlist('queue')))
if request.form['dir']=='Open':
q="Status='open' and {}".format(search)
else:
q="{dir} > '{}' and {dir} < '{}' and {}".format(request.form['start'],request.form['end'],search,dir=request.form['dir'])
tickets=rt.gettickets(q)
if tickets==[{'': '', 'RT/4.0.19 200 Ok': '', 'No matching results.': ''}]: return "No tickets. <a href=''>Go back</a>"
resolvetimes,responsetimes,opennew,stalled=[],[],[],[]
for ticket in tickets:
try:
retd=time.strftime("%d/%m/%y",rt.parsetime(ticket["Resolved"]))
rett=time.strftime("%H:%M",rt.parsetime(ticket["Resolved"]))
except:
retd = "Not set"
rett = "Not set"
try:
ctd=time.strftime("%d/%m/%y",rt.parsetime(ticket["Created"]))
ctt=time.strftime("%H:%M",rt.parsetime(ticket["Created"]))
except:
ctd = "Not set"
ctt = "Not set"
try:
std=time.strftime("%d/%m/%y",rt.parsetime(ticket["Started"]))
stt=time.strftime("%H:%M",rt.parsetime(ticket["Started"]))
except:
std = "Not set"
stt = "Not set"
category = ticket["CF.{Category}"] if 'CF.{Category}' in ticket.keys() and ticket["CF.{Category}"]!="" else "Not Set"
if request.form['sla']!='0':
try:
sla,rtime=None,0
status=str(ticket["Status"])
if status=="new" or status=="open":
opennew.append(ticket)
elif status=="stalled":
stalled.append(ticket)
else:
resolved=rt.parsedate(ticket["Resolved"]) if ticket["Resolved"]!="Not set" else None
created=rt.parsedate(ticket["Created"]) if ticket["Created"]!="Not set" else None
started=rt.parsedate(ticket["Started"]) if ticket["Started"]!="Not set" else None
resolvetime=resolved-created
starttime=started-created
if request.form['sla']=='1':
resolvetimes.append((resolvetime,str(ticket["id"][7:]),ticket["Priority"]))
responsetimes.append((starttime,str(ticket["id"][7:]),ticket["Priority"]))
try:
rsla = responsesla[ticket["Priority"]]
except:
rsla = 24*60*60
sla = "Pass" if ticket["Priority"]=='0' or starttime.seconds < rsla else "Fail"
else:
resolvetimes.append((resolvetime,str(ticket["id"][7:]),category))
responsetimes.append((starttime,str(ticket["id"][7:]),category))
try:
rsla = responsesla[category]
except:
rsla = 24*60*60
sla = "Pass" if resolvetime.seconds < rsla else "Fail"
rtime = resolvetime.seconds/3600.0
out+=('{},"{}","{}",{},"{}","{}",{},{},{},{},{},{},{},{}\r\n'.format(ticket["id"][7:],ticket["Subject"],ticket["Queue"],ticket["Priority"],category,ticket["Owner"],ctd,ctt,std,stt,retd,rett,sla,rtime))
except Exception as inst:
try:
out+=('{},"Error parsing ticket"\r\n'.format(ticket["id"][7:]))
print("1. Error parsing "+str(ticket["id"]))
print(inst.args)
except:
pass
else:
try:
out+=('{},"{}","{}",{},"{}","{}",{},{},{},{},{},{}\r\n'.format(ticket["id"][7:],ticket["Subject"],ticket["Queue"],ticket["Priority"],category,ticket["Owner"],ctd,ctt,std,stt,retd,rett))
except Exception as inst:
try:
out+=('{},"Error parsing ticket"\r\n'.format(ticket["id"][7:]))
print("2. Error parsing "+str(ticket["id"]))
print(inst.args)
except:
pass
out+="\r\n\r\nTotal number of tickets:,{}".format(len(tickets))
if request.form['user']=="":
ticks={}
for ticket in tickets:
try:
if ticks.has_key(ticket["Owner"]):
ticks[ticket["Owner"]]+=1
else:
ticks[ticket["Owner"]]=1
except:
try:
print("3. Error parsing "+str(ticket["id"]))
except:
pass
out+="\r\n\r\nTickets by owner:"
for tick in ticks:
out+="\r\n{}:,{}".format(tick,ticks[tick])
if request.form['sla']!='0':
failedresponse=[]
failedresolve=[]
seconddiffs=[]
resolvesuccess=0
resolvefail=0
for diff,id,priority in resolvetimes:
if priority=='0': continue
seconddiffs.append(diff.seconds)
try:
rsla = resolvesla[priority]
except:
rsla = 24*60*60
if diff.seconds < rsla:
resolvesuccess+=1
else:
resolvefail+=1
failedresolve.append(id)
avgsecs=sum(seconddiffs)/(len(seconddiffs) or 1)
out+="\r\n\r\nAverage number of hours from created to resolved:,"+str(avgsecs/3600.0)
seconddiffs=[]
responsesuccess=0
responsefail=0
for diff,id,priority in responsetimes:
if priority=='0': continue
seconddiffs.append(diff.seconds)
try:
rsla = responsesla[priority]
except:
rsla = 24*60*60
if diff.seconds < rsla:
responsesuccess+=1
else:
responsefail+=1
failedresponse.append(id)
avgsecs=sum(seconddiffs)/(len(seconddiffs) or 1)
out+="\r\nAverage number of hours from created to started:,"+str(avgsecs/3600.0)
try:
out+="\r\nResponses:,{},with,{},missed =,{}".format(responsesuccess+responsefail, responsefail, (responsefail+0.0)/(responsesuccess+responsefail+0.0))
except ZeroDivisionError: pass
try:
out+="\r\nResolves:,{},with,{},missed =,{}".format(resolvesuccess+resolvefail, resolvefail, (resolvefail+0.0)/(resolvesuccess+resolvefail+0.0))
except ZeroDivisionError: pass
out+="\r\nFailed responses:,"
outl=""
for id in failedresponse:
outl+="id="+id+" or "
out+=outl[:-4]
out+="\r\nFailed resolves:,"
outl=""
for id in failedresolve:
outl+="id="+id+" or "
out+=outl[:-4]
out+="\r\nNew and open:,"
outl=""
for ticket in opennew:
outl+="id="+str(ticket["id"][7:])+" or "
out+=outl[:-4]
out+="\r\nStalled:,"
outl=""
for ticket in stalled:
outl+="id="+str(ticket["id"][7:])+" or "
out+=outl[:-4]
r=Response(response=out,mimetype="text/csv")
r.headers.add('Content-Disposition', 'attachment; filename="report.csv"')
return r
if __name__ == '__main__':
app.run(host='0.0.0.0', port=int("9090"),debug=debug_on)