I've read through so many blogs that my head is spinning. I do recall that I can create a custom queue in the helpdesk so that when a user creates a ticket, it will automatically pull the Department field out of the AD user's account and populate the ticket with this info.   I got to believe this has been done many times. Can anyone point me to a document or  syntax to add this to the ticket queue custom queue? 
1 Comment   [ + ] Show Comment


  • Hi Chuck,thanks...im getting error , I couldnt post the screen shot here..pls check the screen shot here

Please log in to comment


Depending on the field in the user table that is holding the user's department you would create a custom rule like this.
SQL Select code:
                        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,
                        U2.WORK_PHONE as SUBMITTER_PHONE,
                        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 ((  HD_TICKET.CUSTOM_FIELD_VALUE10 = 'Set on Save') and HD_TICKET.HD_QUEUE_ID = 2 )
This is for Queue #2 and it checks to see if custom field 11 (VALUE10 in the database) is current "Set on Save", which is the default value that I always use for custom fields that will be set by a rule.

The update statement:
update HD_TICKET as T
    set T.CUSTOM_FIELD_VALUE10 = (select CUSTOM_1 FROM USER  where ID = SUBMITTER_ID) where 
        T.ID = <TICKET_IDS>;
This assumes that you LDAP import is pulling your AD Department field into CUSTOM_1. If it is in a different field then change that part of the query accordingly.

Answered 03/07/2016 by: chucksteel
Red Belt

  • How would I pull AD account name alone in ticketing field, before or after ticket creation?
    • Where is the AD account name stored?
      • That information is in the AD users and computers. I'm actually going about this wrong. After reviewing some of our initial training on the K1000, I remembered that this part of the training didn't work and the Kace Training technician was supposed to get back with us, but he didn't. I'm working with Dell because that info should be getting pulled from the user accounts.. The Filter was getting errors when pulling the user info.. working with Dell now. I'll update when/if fixed.
      • Thanks for reply Chuck,Windows login name is stored in USER table in ORG1...under column USER_NAME.

        I would like to populate this USER_NAME in ticketing page..after or when creating ticket if it populates that would be grt
      • You can use the same rule as above but instead of select CUSTOM_1 FROM USER in the update statement use select USER_NAME from USER.
      • Hi Chuck,thanks...im getting error , I couldnt post the screen shot here..pls check the screen shot below

Please log in to comment
Answer this question or Comment on this question for clarity