Admin: 2 revisions imported

2016-04-06T20:23:34Z

2 revisions imported

New page

= 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;

RT4-SQLite-Weekly Stats - Revision history

Admin: 2 revisions imported