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   [ + ] Show Comments

Comments

Please log in to comment

Answers

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.
0

Works great thanks! [:)]

Answered 02/24/2012 by: stubox
Blue Belt

Please log in to comment
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
Answer this question or Comment on this question for clarity