Reporting

2009-01-22 by Patrick S. Kyne

I have been using RT3 for a while now as my trouble-ticketing system. For most things, it just works and does so well.

However, I must generate reports on those tickets and have not found an easy way to get the information I needed from RT3 quickly and neatly. (Printing of tickets from Firefox on Linux in my environment does not look very good.)

At first, I was just viewing the ticket(s) in RT3 and then copying/pasting the information I wanted into my reports. This was rather awkward for me and very time consuming. I Googled, read the docs/FAQs/wiki, but didn't find anything that would readily do what I wanted/needed. I figured that I had one of four possibilities: Pulling the data directly from the database --MySQL here-- (I didn't know the database schema, but couldn't find all the information I wanted easily.); Possibly installing/configuring RTFM-the RT Faq Manager (after searching the docs/FAQs/wiki I wasn't confident I'd get what I wanted.); Write a perl application using the RT-api (I didn't find very much information about the RT-api on-line so I decided this would be my last choice.); or Use the RT-Tool mentioned in the FAQs to script my solution (I opted for this last route because it looked as if it would consume the least amount of time.)

For my purposes, I need the following information from RT: Ticket#, Queue, Owner, Subject, Status, Created Date, Resolved Date, Time Worked, some custom fields, AND the dates & content of the comments/replies fields. These last bits of data I found were considered attachments to the ticket by RT. Of course, I only wanted to get the attachments that were of type text.

Below is my solution. It is not elegant, but rather a functional bash script. I hope it can be of use to others.

---

#!/bin/bash
 
 ###########################
 ### rt3ticket-report.sh ###
 ###
 ### by Patrick S. Kyne
 ###
 ### Quick? and dirty little script to print out RT3 ticket details
 ###  including ALL text/plain & text/html attachments
 ###  for use outside of RT3.
 ###
 ### Requires: RT3, sed, & grep.
 ###
 ### Created 2009-01-22 by Patrick S. Kyne
 ###
 ### Note: I often use this script to report on a group of tickets
 ###  by running from bash:
 ###    for TICKET in 37 45 46 47 48 51 52 53 55 56 57 ; do
 ###        ./rt3ticket-report.sh ${TICKET} ; done | tee tickets.txt
 ###
 ###########################
 
 RTPATH="/opt/rt3"
 
 if [[ $# -lt 1 ]]; then
         echo -e "\nERROR: Missing RT Ticket Number.\n"
         echo "Usage: $0 {ticket number} [ > {filename} ]"
         echo -e "\tPrint RT Ticket details including comments & replys."
         echo -e "\t(Attachments of type text/plain and text/html.)"
         exit
 fi
 
 # show ticket header
 echo "###############################################"
 echo "###  RT3 Ticket Details for Ticket# ${1}  ###"
 echo "###############################################"
 ${RTPATH}/bin/rt show ticket/${1}
 
 # get ticket attachment number(s), if any
 ATTCH="`${RTPATH}/bin/rt show ticket/${1}/attachments | grep 'text/' | sed 's/(.*$//g;s/Attachments: //g;s/[ :]//g;s/$/,/g;/id.*/d;/^,/d;s/,$//g'`"
 
 # print attachement creation date & contents, removing consecutive blank lines.
 for ATTMT in $ATTCH; do
         echo -e "\n======> BEG Attachment# ${1}/${ATTMT} <======"
         ${RTPATH}/bin/rt show ticket/${1}/attachments/${ATTMT} | \
                 sed '/^Transaction:/,/^Content:/{ /^Content:/,/^Transaction:/!d; };/^id:/,/^Creator:/d;/./,/^&/!d'
 ### Another method of retrieving Attachments (slower) ###
 ##      ${RTPATH}/bin/rt show ticket/${1}/attachments/${ATTMT}/content
 ##      ${RTPATH}/bin/rt show ticket/${1}/attachments/${ATTMT} | \
         ##      sed -n '/^Created:.*$/,$p' | \
         ##      sed '/^Transaction:/,/^Content:/{ /^Content:/,/^Transaction:/!d; }'
         echo -e "\n======> END Attachment# ${1}/${ATTMT} <======"
 done
 echo -e "###  END TICKET ${1} DETAIL  ###\n"
 
 ## END ##
 

---

Produces output like the following. Note: the redundancy at the bottom came as a result of my playing with this early ticket.

---

 ###############################################
 ###  RT3 Ticket Details for Ticket# 37  ###
 ###############################################
 id: ticket/37
 Queue: MyQueue
 Owner: Patrick
 Creator: Patrick
 Subject: Beverly's msword not printing.
 Status: open
 Priority: 5
 InitialPriority: 5
 FinalPriority: 10
 Requestors: Patrick@myemail.address
 Cc:
 AdminCc:
 Created: Wed Oct 08 13:32:45 2008
 Starts: Wed Oct 08 00:00:00 2008
 Started: Thu Oct 09 01:05:28 2008
 Due: Wed Oct 08 00:00:00 2008
 Resolved: Not set
 Told: Not set
 LastUpdated: Thu Oct 09 01:05:28 2008
 TimeEstimated: 0
 TimeWorked: 90 minutes
 TimeLeft: 0
 CF.{Cost of Materials}: 0.00

 ======> BEG Attachment# 37/244 <======
 Created: 2008-10-08 18:32:45
 Content: Beverly's msword will only quickprint.  It crashes upon trying to print
          via any other method.
          -- Patrick

 ======> END Attachment# 37/244 <======

 ======> BEG Attachment# 37/245 <======
 Created: 2008-10-08 18:32:46
 Content:
          Greetings,

          This message has been automatically generated in response to the
          creation of a trouble ticket regarding:
          	"Beverly's msword not printing.",
          a summary of which appears below.

          There is no need to reply to this message right now.  Your ticket has been
          assigned an ID of [DTHH #37].

          Please include the string:

                   [DTHH #37]

          in the subject line of all future correspondence about this issue. To do so,
          you may reply to this message.

                                  Thank you,
                                  THHSupport@myrtserver

          -------------------------------------------------------------------------
          Beverly's msword will only quickprint.  It crashes upon trying to print
          via any other method.
          -- Patrick

 ======> END Attachment# 37/245 <======

 ======> BEG Attachment# 37/246 <======
 Created: 2008-10-08 18:32:46
 Content:
          Wed Oct 08 13:32:45 2008: Request 37 was acted upon.
          Transaction: Ticket created by Patrick
                 Queue: MyQueue
               Subject: Beverly's msword not printing.
                 Owner: Patrick
            Requestors: Patrick@myemail.address
                Status: new
           Ticket <URL: https://myrtserver:8443/rt/Ticket/Display.html?id=37 >


          Beverly's msword will only quickprint.  It crashes upon trying to print
          via any other method.
          -- Patrick

 ======> END Attachment# 37/246 <======

 ======> BEG Attachment# 37/251 <======
 Created: 2008-10-09 06:05:24
 Content: <p>Followed recommendations of microsoft. Problem only appears on Beverly's user.</p>
          <p>Re-installed msoffice. Applied mso97sp2b. Removed registry settings for user. Cleared temp directory.
Problem still persists. Problem only appears in msword. msexcel and mspowerpoint both do not exhibit the problem.
Will pursue with more research. Apparent option is to delete the user and create new user to resolve the problem.
Will look for other options.<br />
          -- Patrick</p>

 ======> END Attachment# 37/251 <======

 ======> BEG Attachment# 37/252 <======
 Created: 2008-10-09 06:05:26
 Content:
          <URL: https://myrtserver:8443/rt/Ticket/Display.html?id=37 >

          Followed recommendations of microsoft. Problem only appears on Beverly's
           user.

          Re-installed msoffice. Applied mso97sp2b. Removed registry settings for user.
          Cleared temp directory. Problem still persists. Problem only appears in msword.
          msexcel and mspowerpoint both do not exhibit the problem. Will pursue with more
          research. Apparent option is to delete the user and create new user to
          resolve the problem. Will look for other options.
          -- Patrick


 ======> END Attachment# 37/252 <======

 ======> BEG Attachment# 37/253 <======
 Created: 2008-10-09 06:05:27
 Content: Followed recommendations of microsoft. Problem only appears on Beverly's
           user.

          Re-installed msoffice. Applied mso97sp2b. Removed registry settings for user.
          Cleared temp directory. Problem still persists. Problem only appears in msword.
          msexcel and mspowerpoint both do not exhibit the problem. Will pursue with more
          research. Apparent option is to delete the user and create new user to
          resolve the problem. Will look for other options.
          -- Patrick


 ======> END Attachment# 37/253 <======

 ======> BEG Attachment# 37/254 <======
 Created: 2008-10-09 06:05:27
 Content: Followed recommendations of microsoft. Problem only appears on Beverly's
           user.

          Re-installed msoffice. Applied mso97sp2b. Removed registry settings for user.
          Cleared temp directory. Problem still persists. Problem only appears in msword.
          msexcel and mspowerpoint both do not exhibit the problem. Will pursue with more
          research. Apparent option is to delete the user and create new user to
          resolve the problem. Will look for other options.
          -- Patrick


 ======> END Attachment# 37/254 <======
 ###  END TICKET 37 DETAIL  ###