Modify Most due incidents

RT Developers
1

I need to modify “Most due incidents” list, by default it takes tickets ordered by id, SQL sentence to get only ids would be:

SELECT id
FROM Tickets
WHERE
Queue = (SELECT id
FROM Queues
WHERE Name = ‘Incidents’)
AND Status in (‘new’,‘open’,‘stalled’);

I need limit show tickets in the following way:

SELECT id
FROM Tickets
WHERE
Queue = (SELECT id
FROM Queues
WHERE Name = ‘Incidents’)
AND Status in (‘new’,‘open’,‘stalled’)
AND Due < “2005-04-05 00:00:00”;

I can order by Due, but I need show only tickets that match above criteria. How can I do that?

Juan Ramón Alfageme Mata. Soporte ICM +34 91 787 23 00

2

Finally I get it to work on my own, here is diff -u from original and modified DueIncidents element:

server:/opt/rt3/local/html/RTIR/Elements# diff -u …/…/…/…/share/html/RTIR/Elements/DueIncidents DueIncidents
— …/…/…/…/share/html/RTIR/Elements/DueIncidents 2005-04-11 12:14:51.000000000 +0200
+++ DueIncidents 2005-04-11 15:54:20.000000000 +0200
@@ -28,21 +28,26 @@
Query => $Query,
QueryString => $QueryString,
Format => $Format,

  • Rows => 10,
  • Rows => 10000,
  • OrderBy => $OrderBy,
    &>

<& /Elements/TitleBoxEnd &>

<%INIT>
$Format = $RT::RTIRSearchResultFormats->{‘DueIncidents’};
+my $todaydate = new RT::Date($session{CurrentUser});
+$todaydate ->Set( Format => ‘unix’, Value => time );

my $Query = “Queue = ‘Incidents’”;

if ($owner) {
$Query .= " AND (Owner = " . $owner->id . " OR Owner = ‘Nobody’)";
+} else {

  • $Query .= " AND Due < ‘" . $todaydate->ISO() . "’";
    }

-$Query .= " AND " .
+$Query .= " AND " .
$m->comp(‘/RTIR/Elements/NewQuery’,
Queue => ‘Incidents’,
states => [ ‘new’, ‘open’, ‘stalled’ ]);
@@ -59,9 +64,9 @@
</%INIT>

<%ARGS>
-$rows => 10
+$rows => 10000
$owner => undef
-$OrderBy => undef
+$OrderBy => ‘Due’
$Format => undef
$QueryString => undef
</%ARGS>
server:/opt/rt3/local/html/RTIR/Elements#

It does three things, first order by Due, second get infinite number of tickets (10.000) and third in Most Due Incidents list get only tickets with Due date lower than current date.

Juan Ramón Alfageme Mata
+34 91 787 23 00 alhambra-eidos.com

3

Hi,

this request was the last in the mysql-slow.log when I had to nothalt
the db today:

SELECT COUNT (DISTINCT main.id) FROM
(
(Tickets main LEFT JOIN ObjectCustomFieldV alues
ObjectCustomFieldValues_1
ON (
(ObjectCustomFieldValues_1.ObjectType = ‘RT ::Ticket’)
)
AND (
(ObjectCustomFieldValues_1.Disabled = ‘0’)
)
AND (
main.id = Obj ectCustomFieldValues_1.ObjectId
)
)
JOIN CustomFields CustomFields_2 ON
( Object CustomFieldValues_1.CustomField = CustomFields_2.id)
AND( (CustomFields_2.Name = ‘NAS-ID’)))
WHERE ((main.EffectiveId = main.id)) AND ((main.Status !=
‘delete d’))
AND ((main.Type = ‘ticket’))
AND ((main.Status = ‘open’)OR(main.Queue = '25 ')
OR(ObjectCustomFieldValues_1.Content LIKE ‘%HIT387721%’)
);

Any hints for optimization?

thanks
Alex

4

This looks like you were doing

(Status = ‘open’ OR Queue=‘something’ OR CF.NAS-ID LIKE “HIT387721”)

That’s going to be expensive no matter what, because of the OR and
returning all the open tickets and the broad LIKE on all tickets.

-R