Restrict report by submitter email address?
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.com
Source: http://www.kace.com/support/kb/index.php?action=artikel&id=954
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.com
SELECT 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 DESCSource: http://www.kace.com/support/kb/index.php?action=artikel&id=954
0 Comments
[ + ] Show comments
Answers (2)
Please log in to answer
Posted by:
GillySpy
12 years ago
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 cohort
SELECT 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
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.