For lack of a better term, I’m looking for a way to create a “bulk template”.
We regularly acquire new offices. When we do, there are about 50,000 things that need to be done. Those things should be tickets and they should be related to each other. A small example would be “purchase server” and that should block “provision server” and that should block “install server”. “install server” should also be blocked by “get bill of sale”, etc… “purchase server” should be in the “purchasing” queue, “provision server” should be in the engineering queue, “install server” should be in the on-site queue, etc…
What I’m trying to accomplish is this:
Have a way to indicate to RT that we are about to acquire a new office named “foo”, and have the tickets automatically created with dependencies in the correct queues.
Is anyone aware of an extension that can handle this and my Google-fu is just not strong enough, or is this something I’m going to have to write myself?
Look into the “Create Tickets” action for your scrip.
You can build a multi-ticket template based on the contents of the current ticket.
1 Like
Not an extension but I wrote some code to allow a CSV file to be ingested to create tickets in bulk. In our case it is for tickets for repetitive tasks (re-imaging Windows lab machines for example) where each item needs its own ticket. I think some folk who use it generate their CSV files from other systems.
If that’s of any interest I can tidy it up and post it here.
1 Like
I’d definitely be interested in seeing it. I haven’t had to write a lick of Perl for about 20 years (and even then, I wasn’t very good at it)…so I’m a bit rusty. 
OK, here it is, although I just realised it doesn’t ingest CSV files but XLSX spreadsheets (hey, I only wrote it!). It lives in our /opt/rt4/local/html/Ticket/ directory and we’ve linked it into the Tools menu for privileged users. It makes use of a couple of extra Perl modules - Spreadsheet::ParseXLSX and DateTime::Format::Excel - which you’ll need to install.
The first row in the spreadsheet is taken to be column titles. You can indicate a couple of these to be extracted for each row as the ticket subject and requestor if you want. For each subsequent row it creates a new ticket with the column title and cell value on separated lines in the ticket body. You could do some extra fancy stuff here with custom fields, but this was all our folk wanted, and it means it isn’t tied to a particular queue or lifecycle (and we have lots of queues). You can specify which queue to put the tickets in, defaulting to a “Bulk Planned Work” queue we have here. There’s also a “Dry Run” button that you can use to see what tickets would be created, without actually creating them. Worth using that first to see what it will try to do and sanity check your settings against the spreadsheet.
Anyway, I hope this is of some use!
<& /Elements/Header, Title => $title &>
<& /Elements/Tabs &>
<& /Elements/ListActions, actions => \@Actions &>
<%perl>
if($sheet) {
my $column = 0;
my @columnTitles;
while(1) {
my $title = $sheet->{Cells}[$sheet->{MinRow}][$column]->{Val};
last if(!$title);
push @columnTitles, $title;
$column++;
}
foreach my $row ($sheet->{MinRow}+1 ... $sheet->{MaxRow}) {
$processed = 1;
my $requestor = '';
my $ok = 1;
my $ticketId = 0;
my $initialContents = '';
my $subject = $ARGS{'SubjectPrefix'} || 'Bulk importer';
my $ownerObj = RT::User->new($session{'CurrentUser'});
if($owner =~ /\@/) {
$ownerObj->LoadByEmail($owner);
} elsif($owner ne '') {
$ownerObj->Load($owner);
} else {
$ownerObj->Load('Nobody');
}
$initialContents = '';
$column = 0;
foreach my $title (@columnTitles) {
if($ARGS{'SubjectTitle'} eq $title) {
$subject .= ' - ' . $sheet->{Cells}[$row][$column]->{Val};
}
if($ARGS{'RequestorTitle'} eq $title) {
$requestor = $sheet->{Cells}[$row][$column]->{Val};
}
$initialContents .= "$title:\t\t " .
$sheet->{Cells}[$row][$column]->{Val} . "\n\n";
$column++;
}
my $MIMEObj = MIME::Entity->build(Type => "text/plain",
Data => $initialContents,
);
if(!$requestor || $requestor eq '') {
$requestor = 'Nobody';
}
if($ok) {
my $ticketObj = RT::Ticket->new($session{'CurrentUser'});
if($dryrun eq 'N') {
my($ticketId, $transObj, $errMsg) =
$ticketObj->Create(Queue => $queueObj,
Requestor => $requestor,
Owner => $ownerObj,
Subject => $subject,
MIMEObj => $MIMEObj,
ActivateScrips => 0,
);
if(!$ticketId) {
$ok = 0;
$m->print("<div>Failed to create ticket: $errMsg</div>");
} else {
$m->print("<div>Created ticket for <i>\"$subject\"</i>, owned by " . $ownerObj->Name . ", for user $requestor: <a href='/Ticket/Display.html?id=$ticketId'>$ticketId</a>.</div>");
warn "Created Generic Bulk Importer ticket ID $ticketId\n";
$numberCases++;
}
} else {
# sleep(1);
$m->print("<div><strong>DRY RUN:</strong> Would have created a ticket for <i>\"$subject\"</i>, owned by " . $ownerObj->Name . ", for user $requestor with contents of:<br><pre>$initialContents</pre><br></div>\n");
}
}
$m->flush_buffer;
}
if($dryrun eq 'N') {
$m->print("<div>".$ARGS{'spreadsheet'}." has been processed to create $numberCases " . ($numberCases == 1 ? "case" : "cases") . "</div>");
} else {
$m->print("<div>".$ARGS{'spreadsheet'}." has been processed but no tickets have been generated as this was a DRY RUN.</div>");
}
}
</%perl>
% if($processed) {
<hr>
% }
<form action="UploadGenericBulkTickets.html" method="post" enctype="multipart/form-data"><input type="hidden" id="dryrun" name="dryrun" value="Y">
Spreadsheet to upload: <input type="file" name="spreadsheet"><br><br>
<table>
<tr><th style="text-align: left;">Owner:</th><td><& /Elements/EmailInput, Name => 'owner', id => 'owner', Size => '10', Default => $ARGS{'owner'} &> <em>(defaut: you)</em></td></tr>
<tr><th style="text-align: left;">Ticket subject prefix:</th><td><input name="SubjectPrefix"> <em>(default: 'Bulk importer')</em></td></tr>
<tr><th style="text-align: left;">Ticket subject field:</th><td><input name="SubjectTitle"> <em>(default: none)</em></td></tr>
<tr><th style="text-align: left;">Ticket requestor field:</th><td><input name="RequestorTitle"> <em>(default: 'Nobody')</em></td></tr>
</table>
<br><br><br><input type="submit" value="Dry Run"><input type="submit" value="Do it for real!" onClick="jQuery('#dryrun').val('N');1;">
</form>
<%attr>
AutoFlush => 1
</%attr>
<%ARGS>
$spreadsheet => undef
$dryrun => 'Y'
$owner => undef
$SubjectPrefix => undef
$SubjectTitle => undef
$RequestorTitle => undef
</%ARGS>
<%INIT>
my @Actions = undef;
my $processed = '';
my $title = "Bulk Generic Ticket Upload";
my $sheet;
my $numberCases = 0;
my $queueObj = RT::Queue->new($session{'CurrentUser'});
if($ARGS{'spreadsheet'}) {
$title = "Loading " . $ARGS{'spreadsheet'};
my $query = $m->cgi_object;
my $fh = $query->upload('spreadsheet');
my $queueName = $ARGS{'QueueName'} || 'Planned Bulk Work';
$queueObj->Load($queueName);
my $xlsx = '';
my $tmpfile="/tmp/$$.xlsx";
open(TMPFILE, ">$tmpfile");
if($fh) {
while(<$fh>) {
$xlsx .= $_;
print TMPFILE $_;
}
close TMPFILE;
use Spreadsheet::ParseXLSX;
use DateTime::Format::Excel;
use Data::Dumper;
my $book = Spreadsheet::ParseXLSX->parse($tmpfile);
$sheet = pop @{$book->{Worksheet}};
}
}
</%INIT>