Need to add more than 4 support technicians to a custom view under Service Desk--> Tickets
We have about 100 employees in IT and they are divided up by divisions and locations. I need to create views of the queue that easily separates the teams so we can see the activity of each. In Custom Views, I am limited to only 4 search criteria. I am searching on 'Support Tech User Name = XYZab'. How do you overcome the limitation of only 4 search criteria in KACE or in our case a limit of only 4 support techs per custom view? How can you add more search fields?
Answers (2)
I got it to work using the wizard just like you said going back an editing the User.FUL_NAME to USER.EMAIL Then updated on condition: ---------------------------------------- update HD_TICKET set HD_TICKET.CUSTOM_FIELD_VALUE5 = 'My_TEAM' where (HD_TICKET.ID in ()) ---------------------------------------
I would consider the following course of action:
Add a custom field for team to the service desk. This could be a dropdown where the team can be selected. It doesn't need to be user visible but can be owners only.
Create a custom rule that will automatically assign the team field based on the ticket owner. You can create this rule using the rules wizard which will allow you to select four fields. However, once the rule is created, you can modify the SQL to add all of the team members.
An even slicker way to handle it would be to assign labels to the team members indicating which team they are in. Your custom rule would then look at the labels on the team members and use that to determine the team on the ticket. This would all have to be custom SQL for the ticket rule. If you don't have someone with SQL experience on your team we can probably help get that setup.
Once the team custom field is setup and working in the service desk you can create your views based on that field. If you use a system that uses labels for the ticket owners then you won't have to update your custom views when your team changes, you just need to make sure your team members have the correct label assigned.
Comments:
-
Chucksteel - We are going to try the 'slicker' option. Let you know how it works. Thanks! - Jalvey74 10 years ago
-
I am trying to make a ticket rule that parses the domain name (abzxxxx.com) from the sender’s email. Each division in our company has their own unique email domain. Then based on the domain name I need the rule to auto select a custom ticket field called ‘IT TEAM’. That way we can relate where the tickets are coming from and create custom ticket view for each division, to see what their users are submitting.
I created the custom IT TEAM ticket field with no problem, but when I try to create a ticket rule I do not have the customer’s email (the from field) as an option in the ticket rule wizard. I guess I will have to code that manually. - Jalvey74 10 years ago-
Yeah, the rules wizard only includes a limited set of fields. I would create it using Submitter Fullname and then modifying it afterwards to change the fields to match email instead. If you get stuck, post your SQL query here. - chucksteel 10 years ago
-
I roughly made the same thing: My custom_field_value2 (read 3) populates with the submitters label, and then we can go to our custom view and have it view all with a custom_field3 of "this" criteria. - Wildwolfay 10 years ago
Thanks! That was very helpful. I got it to work using the wizard just like you said going back an editing the User.FUL_NAME to USER.EMAIL
Then updated on condtion:
----------------------------------------
update HD_TICKET
set HD_TICKET.CUSTOM_FIELD_VALUE5 = 'My_TEAM'
where
(HD_TICKET.ID in (<TICKET_IDS>))
---------------------------------------
select HD_TICKET.*,
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.ORDINAL as STATUS_ORDINAL,
HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,
STATE,
if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS,
if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED,
if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED,
if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED,
if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED,
if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED,
if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED,
if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE,
case upper(STATE)
when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)
when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)
else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE,
if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME,
if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME,
if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME,
if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(APPROVER_ID>0, 'Pending', '')))) as APPROVAL_STATUS,
Q.NAME as QUEUE_NAME
from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
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.APPROVER_ID
LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_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 (( (1 in (select 1 from USER where HD_TICKET.SUBMITTER_ID = USER.ID and USER.EMAIL like '%XZY.com')) ) and HD_TICKET.HD_QUEUE_ID = 2 )
Comments:
-
Cool. I don't really like the way the wizard creates the where statements. I would probably rewrite after HD_CATEGORY_ID as the following:
and U2.EMAIL like '%xzy.com'
and HD_TICKET.HD_QUEUE_ID = 2
I find having the additional search parameters in the parenthesis to be obfuscating so I normally break them out, but that might just be personal preference.
Another interesting approach here would be to have the rule set the custom field to the domain of the submitting user. If you're only dividing by domain then you would just need one rule that would automatically assign the field based on the user, whereas with the current system you'll need a rule per domain/team. - chucksteel 10 years ago-
I am not a SQL expert, but it would make sense to have all the logic in one rule. How would I do that? Would a case statement like this be the best route?
CASE When USER.EMAIL like '%DivsionA.com' then ‘DivisionA’
When USER.EMAIL like '%DivsionB.com' then ‘DivisionB’
When USER.EMAIL like '%DivsionC.com' then ‘DivisionC’
Else ‘Other’
End AS IT_TEAM --$it_team
---update HD_TICKET
set HD_TICKET.CUSTOM_FIELD_VALUE5 = $it_team
where
(HD_TICKET.ID in (<TICKET_IDS>)) - Jalvey74 10 years ago-
The update statement doesn't have access to the variables like the email does, unfortunately. Instead you would do something like this:
update HD_TICKET
set HD_TICKET.CUSTOM_FIELD_VALUE5 =
( select substring_index(EMAIL, '@', -1) from USERS where USER.ID = HD_TICKET.SUBMITTER_ID)
WHERE HD_TICKET.ID in <TICKET_IDS>
Your select statement can have the final where statement reduced to
AND HD_TICKET.CUSTOM_FIELD_VALUE5 = 'Default Value' instead of looking for a specific value in the user's email address.
For fields where a rule will set the value I normally have the default value as "Set on Save" or something similar. I then have my rule check to see if the field is set to that value, this way the rule only runs once. So the above 'Default Value' would be set to whatever the default value for custom field six is. - chucksteel 10 years ago