K1000 Service Desk: Custom Ticket Rule
I have a ticket rule to email the supervisor when a ticket is in a state for more than a certain period of time. I am trying to add a variable based on ticket owner so i can have separate rules based on the ticket owner's supervisor. Here is the statement I have that works getting all tickets in status 'New' for 7 days or more:
Select 'cfrey@wacoisd.org' AS CHARLIE,
S.NAME AS STATUS, T.ID AS ID, T.TITLE AS ISSUE,
U.FULL_NAME AS SUBMITTER, O.FULL_NAME AS OWNER,
T.CREATED AS CREATED, T.MODIFIED AS STALLED
FROM HD_TICKET T
JOIN HD_STATUS S ON (S.ID = T.HD_STATUS_ID)
JOIN USER U ON (U.ID = T.SUBMITTER_ID)
JOIN USER O ON (O.ID = T.OWNER_ID)
WHERE (S.NAME = 'New' AND DATEDIFF(NOW(), T.MODIFIED) >= 7
AND T.HD_QUEUE_ID = 1
I want to add: AND O.FULL_NAME = 'Arturo Jimenez'
When I add that after '>=7' I get 0 results even though without that statement it pulls tickets by that owner. Any ideas?
Answers (2)
I changed the variable to AND O.USER_NAME = 'ajimenez' and it works. Didn't like the full name for some reason, but user name worked.
Comments:
If you change the statement slightly to this, does it return anything?
AND O.FULL_NAME rlike 'Arturo'
John