Hello,

I am trying to create a report which displays the ticket ID and all related rules which are created with the ticket.
I want this report so I can create an overview when a ticket has been approved and by who but also when the message is sent to the approver requesting the approval.

I like the new reports wau from Kace itself but it looks like you can't use it yourself because then it generates errors. Below is the SQL statement I am using:

SELECT
HD_TICKET.ID,
HD_TICKET.TITLE,
S.FULL_NAME AS SUBMITTER_NAME,
HD_TICKET.CREATED,
HD_TICKET.TIME_STALLED,
HD_TICKET.TIME_CLOSED,
HD_IMPACT.NAME AS IMPACT,
HD_PRIORITY.NAME AS PRIORITY,
HD_TICKET.APPROVAL,
HD_TICKET.APPROVAL_NOTE,
AP.FULL_NAME,
HD_CATEGORY.NAME AS CATEGORY,
HD_STATUS.NAME AS STATUS,
HD_TICKET.ID as TOPIC_ID FROM HD_TICKET 
LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
JOIN HD_IMPACT ON (HD_IMPACT.ID = HD_TICKET.HD_IMPACT_ID)
JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID)
LEFT JOIN USER AP ON (AP.ID = HD_TICKET.APPROVER_ID)
JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
WHERE (HD_TICKET.HD_QUEUE_ID = 3) AND ((HD_IMPACT.NAME = 'Rfc') AND (HD_CATEGORY.NAME = 'Personeel')) GROUP BY HD_TICKET.ID;
SELECT
HD_TICKET_CHANGE.ID,
HD_TICKET_CHANGE.TIMESTAMP,
HD_TICKET_CHANGE.USER_ID,
HD_TICKET_CHANGE.COMMENT,
HD_TICKET_CHANGE.DESCRIPTION,
GROUP_CONCAT(DISTINCT REPORT_TEMP.TT1.ROW ORDER BY REPORT_TEMP.TT1.ROW) AS PARENT_ROW FROM HD_TICKET
LEFT JOIN HD_TICKET_CHANGE ON (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)
JOIN REPORT_TEMP.TT1 ON (REPORT_TEMP.TT1.ID = HD_TICKET.ID)

Can anybody help me to fix this issue?

Johan

2 Comments   [ + ] Show Comments

Comments

  • You haven't set a variable for the two reports that you're joining in the second query. I'm not totally sure how to do that, but that's the source of the error.

    You also have Category name in the first query misspelled as "Personeel" instead of "Personnel"
  • You might want to also try creating your mysql query in FlySpeed first.
    http://www.activedbsoft.com/download.html
    Then bring it into the reporting module.
Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity

Share