Perl script to dump mysql db transactions?

RT Users
1

Howdy,

Under different circumstances I would not make such a naked plea for
assistance, however time is of the essence (when is it not?) and I’m hoping
that this is one of those trivial issues that someone with more experience
has already tackled.

I’m fairly competent with Perl coding, but not much experienced in mysql.
What I need to do is generate a list of all the transactions (i.e., activity
of any sort) that have occurred in a given RT queue during a specified time
period. Say for example, I might want to run a cron script at 12:01 am every
night that dumped the previous day’s transactions to a text file.

I can handle issues related to formatting, printing to file, etc., what I’m
hoping someone has handy in their tool box is the code to get mysql to “tell
me everything that’s changed in this table between time X and time Y.” Or,
if this is a trivial exercise if someone could point me to the Fine Manual I
should read, I’d be grateful for that kind of help as well.

Thanks,

Doug

 If you're never wrong, you're not trying hard enough
2

Try:

select * from Transactions where Created > ‘DATE’ AND Create < ‘DATE’;

Where DATE represents some timeperiod of transactions that you want
returned.

Plug that into your perl script and you can do whatever you want with the
data returned…

  • Jay
3

Howdy,

Under different circumstances I would not make such a naked plea for
assistance, however time is of the essence (when is it not?) and I’m hoping
that this is one of those trivial issues that someone with more experience
has already tackled.

I’m fairly competent with Perl coding, but not much experienced in mysql.
What I need to do is generate a list of all the transactions (i.e.,
activity of any sort) that have occurred in a given RT queue during a
specified time period. Say for example, I might want to run a cron script
at 12:01 am every night that dumped the previous day’s transactions to a
text file.

I can handle issues related to formatting, printing to file, etc., what I’m
hoping someone has handy in their tool box is the code to get mysql to
“tell me everything that’s changed in this table between time X and time
Y.” Or, if this is a trivial exercise if someone could point me to the Fine
Manual I should read, I’d be grateful for that kind of help as well.

Thanks,

Doug

If you are competent at Perl, why not use the Perl RT API to do this?

4

Todd Chapman wrote:

If you are competent at Perl, why not use the Perl RT API to do this?

Can you point me to some documentation for this? A search for “perl RT API”
on the wiki gives no results.

Thanks,

Doug

 If you're never wrong, you're not trying hard enough
5

Look in the bin dir of your RT home.On Thu, 2005-03-03 at 10:43 -0800, Doug Barton wrote:

Todd Chapman wrote:

If you are competent at Perl, why not use the Perl RT API to do this?

Can you point me to some documentation for this? A search for “perl RT API”
on the wiki gives no results.

Thanks,

Doug

Scott T. Hildreth shildret@scotth.emsphone.com

6

At Thursday 3/3/2005 01:43 PM, Doug Barton wrote:

Can you point me to some documentation for this? A search for “perl RT
API” on the wiki gives no results.

Doug,

The documentation is embedded in the code - for example, to see the doc for
the Ticket part of the API:

perldoc $RT_HOME/lib/Ticket.pm
perldoc $RT_HOME/lib/Ticket_Overlay.pm

Here’s an example of a perl script that might get you started. It takes a
RT username as an argument and displays some info for that user. The script
will need to know where your RT libraries are, so add $RT_HOME/lib to @INC.

Digging around the mason components and perl libraries to see how they use
the API should help you get further.

Good luck,
Steve

#!/var/local/bin/perl
use RT::Interface::CLI qw (CleanEnv GetCurrentUser);
use RT::User;

die “Usage: $0 username” if scalar(@ARGV) != 1;

CleanEnv();
RT::LoadConfig();
RT::Init();

my $UserObj = new RT::User(RT::SystemUser);
my $user = new RT::User($UserObj);
my ($v, $m) = $user->Load($ARGV[0]);

if ($v) {
print "User found: ", $user->Name, " ", $user->EmailAddress, " ",
$user->RealName, “\n”;
} else {
print “User NOT found: $m\n”;
}

exit;