Custom Reporting Views for RT

sparc-rt-reporting-views.sql

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `rt_user`@`localhost` 
    SQL SECURITY DEFINER
VIEW `TicketBaseProperties` AS
    SELECT 
        `t`.`id` AS `TicketId`,
        `q`.`id` AS `QueueId`,
        `q`.`Name` AS `Queue`,
        `t`.`Owner` AS `OwnerId`,
        `u1`.`RealName` AS `OwnerName`,
        `t`.`Subject` AS `Summary`,
        `t`.`Priority` AS `Priority`,
        `t`.`Status` AS `Status`,
        `t`.`Started` AS `Started`,
        `t`.`Resolved` AS `Resolved`,
        `t`.`Created` AS `Created`,
        `t`.`LastUpdatedBy` AS `LastUpdatedBy`,
        `u3`.`RealName` AS `UpdatedByName`,
        `t`.`LastUpdated` AS `LastUpdated`,
        `t`.`Creator` AS `Creator`,
        `u2`.`RealName` AS `CreatorName`
    FROM
        ((((`Tickets` `t`
        JOIN `Queues` `q` ON ((`q`.`id` = `t`.`Queue`)))
        JOIN `Users` `u1` ON ((`u1`.`id` = `t`.`Owner`)))
        JOIN `Users` `u2` ON ((`u2`.`id` = `t`.`Creator`)))
        JOIN `Users` `u3` ON ((`u3`.`id` = `t`.`LastUpdatedBy`)))
CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `rt_user`@`localhost` 
    SQL SECURITY DEFINER
VIEW `TicketCustomProperties` AS
    SELECT 
        `cv`.`ObjectId` AS `TicketId`,
        `cv`.`CustomField` AS `CustomFieldId`,
        `cf`.`Name` AS `CustomFieldName`,
        IFNULL(`u`.`RealName`, `cv`.`Content`) AS `CustomFieldValue`,
        `cv`.`id` AS `ObjectCustomFieldValueId`
    FROM
        ((`ObjectCustomFieldValues` `cv`
        JOIN `CustomFields` `cf` ON ((`cv`.`CustomField` = `cf`.`id`)))
        LEFT JOIN `Users` `u` ON (((`cv`.`Content` = `u`.`id`)
            AND (`cv`.`Disabled` = 0))))
GO
CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `rt_user`@`localhost` 
    SQL SECURITY DEFINER
VIEW `RequestTaskSummary` AS
    SELECT 
        `l`.`LocalTarget` AS `RequestTicketId`,
        `r`.`Status` AS `RequestStatus`,
        `r`.`Summary` AS `RequestTicketSummary`,
        `l`.`LocalBase` AS `TaskTicketId`,
        `t`.`Status` AS `TaskStatus`,
        `cp`.`CustomFieldValue` AS `TaskType`,
        `t`.`OwnerName` AS `TaskOwner`,
        `t`.`Summary` AS `TaskTicketSummary`
    FROM
        (((`Links` `l`
        JOIN `TicketBaseProperties` `r` ON ((`r`.`TicketId` = `l`.`LocalTarget`)))
        JOIN `TicketBaseProperties` `t` ON ((`t`.`TicketId` = `l`.`LocalBase`)))
        JOIN `TicketCustomProperties` `cp` ON ((`cp`.`TicketId` = `l`.`LocalBase`)));
GO

We have a custom version of RT called SPARC. We have a parent-child relationship between tickets. We call the parent tickets "Requests" and child tickets "Tasks". The properties we care about for request and task tickets differ.