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?

Answer Summary:
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 ()) ---------------------------------------
0 Comments   [ - ] Hide Comments


Please log in to comment

Answer this question or Comment on this question for clarity



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.


Answered 05/23/2013 by: chucksteel
Red Belt

  • Chucksteel - We are going to try the 'slicker' option. Let you know how it works. Thanks!
  • 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.
    • 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.
  • 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.
Please log in to comment

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




        (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,


                        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 )

Answered 05/23/2013 by: Jalvey74
Third Degree Green Belt

  • 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'

    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.
    • 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

      (HD_TICKET.ID in (<TICKET_IDS>))
      • 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
        ( select substring_index(EMAIL, '@', -1) from USERS where USER.ID = HD_TICKET.SUBMITTER_ID)

        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.
Please log in to comment