Custom View for all tickets associated with a Manual User Label and Custom Rule
I have created and populated a Manual Label called "ServiceDesk VIP Users".
I am trying to create a Custom View for all tickets associated with a Manual User Label as well as also trying to create a Custom Rule that when a ticket comes in from users in this label, that it automatically assigns a "High" status and sends an email to an interested party email address.
Can someone please advise on the best way to accomplish this? Am i on the right track with the Manual Label?
0 Comments
[ + ] Show comments
Answers (3)
Please log in to answer
Posted by:
chucksteel
10 years ago
Create a new rule and use this SQL:
select HD_TICKET.ID,
HD_TICKET.ID as TICKNUM,
HD_TICKET.TITLE,
U1.USER_NAME as OWNER_NAME,
U3.USER_NAME as LASTINPUTNAME,
DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED,
DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED,
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.ORDINAL as STATUS_ORDINAL,
STATE,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
U2.USER_NAME as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
U3.EMAIL as UPDATEREMAIL,
UNIX_TIMESTAMP(HD_TICKET_CHANGE.TIMESTAMP),
HD_TICKET_CHANGE.COMMENT,
HD_CATEGORY.CC_LIST AS NEWTICKETEMAIL,
HD_CATEGORY.NAME AS CATEGORY_NAME,
U2.LOCATION AS SUBMITTER_LOCATION,
U2.WORK_PHONE AS SUBMITTER_WORK_PHONE,
HD_PRIORITY.NAME AS TICKET_PRIORITY,
HD_QUEUE.NAME AS QUEUE_NAME,
"email@company.com" as NOTIFY
from ( HD_TICKET,
HD_PRIORITY,
HD_STATUS,
HD_IMPACT,
HD_CATEGORY)
JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
and HD_TICKET_CHANGE.ID=<CHANGE_ID>
left join USER U1 on U1.ID = HD_TICKET.OWNER_ID
left join USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
left join USER U3 on U3.ID = HD_TICKET_CHANGE.USER_ID
JOIN USER SUBMITTER on SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
LEFT JOIN USER_LABEL_JT on USER_LABEL_JT.USER_ID = SUBMITTER.ID
JOIN LABEL SUBMITTER_LABEL on SUBMITTER_LABEL.ID = USER_LABEL_JT.LABEL_ID
left join HD_QUEUE on HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID
where HD_PRIORITY.ID = HD_PRIORITY_ID and
HD_STATUS.ID = HD_STATUS_ID and
HD_IMPACT.ID = HD_IMPACT_ID and
HD_CATEGORY.ID = HD_CATEGORY_ID and
HD_TICKET_CHANGE.DESCRIPTION LIKE 'TICKET CREATED%' and
HD_TICKET.OWNER_ID != HD_TICKET_CHANGE.USER_ID and
and SUBMITTER_LABEL.NAME = "VIP Users"
Make sure you change the email@company.com value to the address that you want notified and change the "VIP Users" to match your label. This rule should be set to run on ticket save. Check the box to email each recipient in query results and enter NOTIFY for the Column containing email addresses field. Fill out the rest of the form as needed.
Comments:
-
chucksteel,
Thank you for the query above. I have tried to implement this and have an issue. Below is the "Last Run Log".
10/11/2014 15:29:16> Starting: 10/11/2014 15:29:16 10/11/2014 15:29:16> Executing Select Query... 10/11/2014 15:29:16> mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'and SUBMITTER_LABEL.NAME = SD_ServiceDesk_VIP_Users and (HD_TICKET.ID = 2423)' at line 46] in EXECUTE("select HD_TICKET.ID, HD_TICKET.ID as TICKNUM, HD_TICKET.TITLE, U1.USER_NAME as OWNER_NAME, U3.USER_NAME as LASTINPUTNAME, DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED, DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED, HD_STATUS.NAME AS STATUS_NAME, HD_STATUS.ORDINAL as STATUS_ORDINAL, STATE, U1.FULL_NAME as OWNER_FULLNAME, U1.EMAIL as OWNER_EMAIL, U2.USER_NAME as SUBMITTER_NAME, U2.FULL_NAME as SUBMITTER_FULLNAME, U2.EMAIL as SUBMITTER_EMAIL, U3.EMAIL as UPDATEREMAIL, UNIX_TIMESTAMP(HD_TICKET_CHANGE.TIMESTAMP), HD_TICKET_CHANGE.COMMENT, HD_CATEGORY.CC_LIST AS NEWTICKETEMAIL, HD_CATEGORY.NAME AS CATEGORY_NAME, U2.LOCATION AS SUBMITTER_LOCATION, U2.WORK_PHONE AS SUBMITTER_WORK_PHONE, HD_PRIORITY.NAME AS TICKET_PRIORITY, HD_QUEUE.NAME AS QUEUE_NAME, 'sduser@myco.com' as NOTIFY from ( HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY) JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID and HD_TICKET_CHANGE.ID=13628 left join USER U1 on U1.ID = HD_TICKET.OWNER_ID left join USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID left join USER U3 on U3.ID = HD_TICKET_CHANGE.USER_ID JOIN USER SUBMITTER on SUBMITTER.ID = HD_TICKET.SUBMITTER_ID LEFT JOIN USER_LABEL_JT on USER_LABEL_JT.USER_ID = SUBMITTER.ID JOIN LABEL SUBMITTER_LABEL on SUBMITTER_LABEL.ID = USER_LABEL_JT.LABEL_ID left join HD_QUEUE on HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID where HD_PRIORITY.ID = HD_PRIORITY_ID and HD_STATUS.ID = HD_STATUS_ID and HD_IMPACT.ID = HD_IMPACT_ID and HD_CATEGORY.ID = HD_CATEGORY_ID and HD_TICKET_CHANGE.DESCRIPTION LIKE 'TICKET CREATED%' and HD_TICKET.OWNER_ID != HD_TICKET_CHANGE.USER_ID and and SUBMITTER_LABEL.NAME = SD_ServiceDesk_VIP_Users and (HD_TICKET.ID = 2423) ")
Your query is below with my mods:
select HD_TICKET.ID,
HD_TICKET.ID as TICKNUM,
HD_TICKET.TITLE,
U1.USER_NAME as OWNER_NAME,
U3.USER_NAME as LASTINPUTNAME,
DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED,
DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED,
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.ORDINAL as STATUS_ORDINAL,
STATE,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
U2.USER_NAME as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
U3.EMAIL as UPDATEREMAIL,
UNIX_TIMESTAMP(HD_TICKET_CHANGE.TIMESTAMP),
HD_TICKET_CHANGE.COMMENT,
HD_CATEGORY.CC_LIST AS NEWTICKETEMAIL,
HD_CATEGORY.NAME AS CATEGORY_NAME,
U2.LOCATION AS SUBMITTER_LOCATION,
U2.WORK_PHONE AS SUBMITTER_WORK_PHONE,
HD_PRIORITY.NAME AS TICKET_PRIORITY,
HD_QUEUE.NAME AS QUEUE_NAME,
'sduser@myco.com' as NOTIFY
from ( HD_TICKET,
HD_PRIORITY,
HD_STATUS,
HD_IMPACT,
HD_CATEGORY)
JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
and HD_TICKET_CHANGE.ID=<CHANGE_ID>
left join USER U1 on U1.ID = HD_TICKET.OWNER_ID
left join USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
left join USER U3 on U3.ID = HD_TICKET_CHANGE.USER_ID
JOIN USER SUBMITTER on SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
LEFT JOIN USER_LABEL_JT on USER_LABEL_JT.USER_ID = SUBMITTER.ID
JOIN LABEL SUBMITTER_LABEL on SUBMITTER_LABEL.ID = USER_LABEL_JT.LABEL_ID
left join HD_QUEUE on HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID
where HD_PRIORITY.ID = HD_PRIORITY_ID and
HD_STATUS.ID = HD_STATUS_ID and
HD_IMPACT.ID = HD_IMPACT_ID and
HD_CATEGORY.ID = HD_CATEGORY_ID and
HD_TICKET_CHANGE.DESCRIPTION LIKE 'TICKET CREATED%' and
HD_TICKET.OWNER_ID != HD_TICKET_CHANGE.USER_ID and
and SUBMITTER_LABEL.NAME = SD_ServiceDesk_VIP_Users
Any insight into what the issue is on line 46? - camachof9 10 years ago-
There's an extra "and" in the statement, remove the and before SUBMITTER_LABEL.NAME. I'm not sure how I got that in there. - chucksteel 10 years ago
-
It now works but my email shows:
$submitter_fname has opened a ticket.
The submission was:
Ticket: 2455
Subject: test tick vip
From: $submitter_fname
Category: $category
Priority: $priority
Status: $status
Severity: $impact
Opening Comment: Test3
I tried to stick in a few variables to no avail. - camachof9 10 years ago -
The variables that are available come from the columns that are selected in the query. These are the items that you can see in the part of the query from the SELECT until the FROM lines. For the variables you are looking for use $submitter_fullname, $category_name, $status_name. Impact is not being selected to it would have to be added to the query. - chucksteel 10 years ago
-
chucksteel,
I just want to thank you for all of your help on the above. It was exactly what I needed.
Now I am trying to come up with a report that someone can run ad-hoc to display all open tickets from this label.
Thanks Again,
Camachof9 - camachof9 10 years ago
Posted by:
chucksteel
10 years ago
This query will work as a report:
select HD_TICKET.ID,
HD_TICKET.ID as TICKNUM,
HD_TICKET.TITLE,
OWNER.USER_NAME as OWNER_NAME,
DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED,
DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED,
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.ORDINAL as STATUS_ORDINAL,
STATE,
OWNER.FULL_NAME as OWNER_FULLNAME,
OWNER.EMAIL as OWNER_EMAIL,
SUBMITTER.USER_NAME as SUBMITTER_NAME,
SUBMITTER.FULL_NAME as SUBMITTER_FULLNAME,
SUBMITTER.EMAIL as SUBMITTER_EMAIL,
HD_CATEGORY.CC_LIST AS NEWTICKETEMAIL,
HD_CATEGORY.NAME AS CATEGORY_NAME,
HD_PRIORITY.NAME AS TICKET_PRIORITY,
HD_QUEUE.NAME AS QUEUE_NAME
from ( HD_TICKET,
HD_PRIORITY,
HD_STATUS,
HD_IMPACT,
HD_CATEGORY)
JOIN USER OWNER on OWNER.ID = HD_TICKET.OWNER_ID
JOIN USER SUBMITTER on SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
LEFT JOIN USER_LABEL_JT on USER_LABEL_JT.USER_ID = SUBMITTER.ID
JOIN LABEL SUBMITTER_LABEL on SUBMITTER_LABEL.ID = USER_LABEL_JT.LABEL_ID
left join HD_QUEUE on HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID
where HD_PRIORITY.ID = HD_PRIORITY_ID and
HD_STATUS.ID = HD_STATUS_ID and
HD_IMPACT.ID = HD_IMPACT_ID and
HD_CATEGORY.ID = HD_CATEGORY_ID and
HD_TICKET.TIME_CLOSED = "0000-00-00 00:00:00" and
SUBMITTER_LABEL.NAME = "SD_ServiceDesk_VIP_Users"
This will pull tickets from all queues.
Posted by:
chucksteel
10 years ago
The rule should be possible but I don't think a custom view will be. Have you searched for similar questions? I seem to recall someone asking for a similar rule before but I'm not positive.
You won't be able to use the rules wizard to create the rule since it doesn't include the ability to check user labels. Do you have experience with MySQL?
Comments:
-
ChuckSteel,
Thank you for your reply to my question. Unfortunately, I have no experience with MySQL although i do have it installed :)
I noticed that i did not have any options for labels in custom views either.
Is it possible to do a report for all open tickets by users in this "ServiceDesk VIP Users" Label? I can do an open tickets report but do not know how to select users in this label only.
I no longer need to change the status with a rule but would still like an email to kick off to a specific recipient when a ticket like this comes in.
Any help would be greatly appreciated. - camachof9 10 years ago
