# % (hostname,hostname)
# Queue == 10 is the spam Queue in RT.
+# SELECT Tk.* FROM Tickets AS Tk, Attachments AS At JOIN Transactions AS Tr ON Tk.id=Tr.ObjectId WHERE Tk.Queue != 10 AND Tk.id > 10000 AND Tr.id=At.TransactionID AND Tk.Status = 'open' ;
+#
+
sql = """SELECT distinct Tk.id, Tk.Status, Tk.Subject, At.Content
FROM Tickets AS Tk, Attachments AS At
JOIN Transactions AS Tr ON Tk.id=Tr.ObjectId
#WHERE Tk.Queue != 10 AND Tk.id > 10000 AND
#Tr.id=At.TransactionID AND ( Tk.Status = 'open' OR
#Tk.Status = 'new') """
- sqlall = """SELECT distinct Tk.id, Tk.Status, Tk.Subject, At.Content, Us.EmailAddress FROM Tickets AS Tk, Attachments AS At, Users as Us JOIN Transactions AS Tr ON Tk.id=Tr.ObjectId WHERE (Tk.Queue=3 OR Tk.Queue=22) AND Tk.id > 10000 AND Tr.id=At.TransactionID AND ( Tk.Status = 'open' OR Tk.Status = 'new') AND Us.id=Tk.LastUpdatedBy """
+ sqlall = """SELECT distinct Tk.id, Tk.Status, Tk.Subject, At.Content, Us.EmailAddress, Tk.LastUpdated, Q.Name, Tk.Owner FROM Tickets AS Tk, Attachments AS At, Queues as Q, Users as Us JOIN Transactions AS Tr ON Tk.id=Tr.ObjectId WHERE (Tk.Queue=3 OR Tk.Queue=22) AND Tk.id > 10000 AND Tr.id=At.TransactionID AND ( Tk.Status = 'open' OR Tk.Status = 'new') AND Us.id=Tk.LastUpdatedBy AND Q.id=Tk.Queue """
raw = fetch_from_db(db, sql)
"status":x[1],
"subj":str(x[2]),
"content":str(x[3]),
- "email":str(x[4]) },
+ "email":str(x[4]),
+ "lastupdated":str(x[5]),
+ "queue":str(x[6]),
+ "owner":str(x[7]),
+ },
raw)
db.close()