Hi
I'm using the code below to output all tickets and their comments. How can it be restricted to show only tickets from specific users? Ideally by a specific domain name, e.g. @mycompanyname.comSELECT HD_TICKET.ID, O.FULL_NAME AS OWNER_NAME, S.FULL_NAME AS SUBMITTER_NAME,
GROUP_CONCAT( CONCAT('\n-- Change by ',U.USER_NAME,' on ',
CAST(DATE(HD_TICKET_CHANGE.TIMESTAMP) AS CHAR),' ---\n',
IF(DESCRIPTION=' OR ISNULL(DESCRIPTION),',
CONCAT('-->',TRIM(TRAILING '-->' FROM REPLACE(DESCRIPTION,'\n','\n-->')),')),
if(HD_TICKET_CHANGE.COMMENT=',',CONCAT(HD_TICKET_CHANGE.COMMENT,'\n'))
)
ORDER BY CAST( HD_TICKET_CHANGE.ID AS CHAR) DESC SEPARATOR '\n' ) AS
HD_TICKET_CHANGE_COMMENT
FROM HD_TICKET JOIN HD_CATEGORY ON (HD_CATEGORY.ID =
HD_TICKET.HD_CATEGORY_ID)
JOIN HD_TICKET_CHANGE ON (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)
LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
LEFT JOIN USER U ON USER_ID=U.ID
LEFT JOIN USER S ON S.ID=HD_TICKET.SUBMITTER_ID
WHERE HD_TICKET_CHANGE.OWNERS_ONLY=0
GROUP BY HD_TICKET_ID
ORDER BY HD_TICKET.ID DESC
Source: http://www.kace.com/support/kb/index.php?action=artikel&id=954
Restrict report by submitter email address?
Answers
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.
I didn't test it but this should do it. Note that i provided alternatives depending upon which user you are referring to. I assumed the submitter here. If you want to use an alternative then just uncomment the line (remove the -- ) and comment it's cohortSELECT HD_TICKET.ID, O.FULL_NAME AS OWNER_NAME, S.FULL_NAME AS SUBMITTER_NAME,
GROUP_CONCAT( CONCAT('\n-- Change by ',U.USER_NAME,' on ',
CAST(DATE(HD_TICKET_CHANGE.TIMESTAMP) AS CHAR),' ---\n',
IF(DESCRIPTION=' OR ISNULL(DESCRIPTION),',
CONCAT('-->',TRIM(TRAILING '-->' FROM REPLACE(DESCRIPTION,'\n','\n-->')),')),
if(HD_TICKET_CHANGE.COMMENT=',',CONCAT(HD_TICKET_CHANGE.COMMENT,'\n'))
)
ORDER BY CAST( HD_TICKET_CHANGE.ID AS CHAR) DESC SEPARATOR '\n' ) AS
HD_TICKET_CHANGE_COMMENT
FROM HD_TICKET JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
JOIN HD_TICKET_CHANGE ON (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)
LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
LEFT JOIN USER U ON USER_ID=U.ID
LEFT JOIN USER S ON S.ID=HD_TICKET.SUBMITTER_ID
WHERE HD_TICKET_CHANGE.OWNERS_ONLY=0
and S.EMAIL like '%@mycompanyname.com'
-- and O.EMAIL like '%@mycompanyname.com'
-- and U.EMAIL like '%@mycompanyname.com'
GROUP BY HD_TICKET_ID
ORDER BY HD_TICKET.ID DESC
Please log in to comment