Helpdesk - Help creating custom Queue to pull Department out of AD user account
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
Answers (1)
Please log in to answer
Posted by:
chucksteel
8 years ago
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:
select HD_TICKET.*, 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,
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,
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.
Comments:
-
How would I pull AD account name alone in ticketing field, before or after ticket creation? - rahimpal 8 years ago
-
Where is the AD account name stored? - chucksteel 8 years ago
-
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. - williemac99 8 years ago
-
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 - rahimpal 8 years ago -
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. - chucksteel 8 years ago
-
Hi Chuck,thanks...im getting error , I couldnt post the screen shot here..pls check the screen shot below
http://www.itninja.com/question/error-populating-ad-name-in-help-desk - rahimpal 8 years ago
http://www.itninja.com/question/error-populating-ad-name-in-help-desk - rahimpal 8 years ago