RT4-SQLite-Weekly Stats

RT4 - SQLite - Weekly Stats

Quickie perl script to report number of opened and closed tickets by queue.

I didn't want to depend on DBI just to avoid the whole CPAN install for this crummy little script.

A sample report that will be emailed out:

RT Weekly Ticket Stats - Opened Tickets:
Support had 34 tickets opened this last week.
Info had 31 tickets opened this last week.
IT had 1 tickets opened this last week.
Requests had 1 tickets opened this last week.
Content had 2 tickets opened this last week.

RT Weekly Ticket Stats - Closed Tickets:
Support had 19 tickets closed this last week.
Info had 30 tickets closed this last week.
IT had 1 tickets closed this last week.

Code:

#!/usr/bin/perl
# The SQLite db to use...
$db = "/var/lib/dbconfig-common/sqlite3/request-tracker4/rtdb";
# Who to send the report to...
$emailaddress = "admin@domain.com";
$mailbody = "/tmp/RTStats_report";

open(MAILBODY,">$mailbody");
print MAILBODY "RT Weekly Ticket Stats - Opened Tickets:\n";
# Tickets created...
open(LIST,"sqlite3 $db \"SELECT Queue from Tickets Where Created > datetime('now', '-7 days');\" | sort -n  | uniq -c |");
while(<LIST>) {
        s/^\s+//;
        ($number,$queuenum) = split(/ /);
        $queuename = `sqlite3 $db \"select Name from Queues where id = $queuenum;\"`;
        chomp $queuename;
        print MAILBODY "$queuename had $number tickets opened this last week.\n";
}
close(LIST);

print MAILBODY "\nRT Weekly Ticket Stats - Closed Tickets:\n";
# Tickets closed...
open(LIST,"sqlite3 $db \"SELECT Queue from Tickets Where Resolved > datetime('now', '-7 days');\" | sort -n  | uniq -c |");
while(<LIST>) {
        s/^\s+//;
        ($number,$queuenum) = split(/ /);
        $queuename = `sqlite3 $db \"select Name from Queues where id = $queuenum;\"`;
        chomp $queuename;
        print MAILBODY "$queuename had $number tickets closed this last week.\n";
}
close(LIST);

`/bin/cat $mailbody | /usr/bin/mail -s \"RT Ticket Stats by Queue\" $emailaddress`;

unlink $mailbody;