/bundles/itninjaweb/img/Breadcrumb_cap_w.png
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

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 DESC


Source: http://www.kace.com/support/kb/index.php?action=artikel&id=954
0 Comments   [ - ] Hide Comments

Comments

Please log in to comment

Community Chosen Answer


Answers

2
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
Answered 02/23/2012 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.
Answer this question or Comment on this question for clarity

Answers

0
Works great thanks! [:)]
Answered 02/24/2012 by: stubox
Blue Belt

Please log in to comment