Ticket rule to assign to submitters manager (As per category)
I'm having trouble tackling the logic for this possible ticket rule I'd love to have. Pre-note: My LDAP Import DOES pull in the manager information from AD when the user is imported, if that is referencial.
My tickets have CATEGORY as a required field. One group of categories is SOFTWARE::MICROSOFT OFFICE::WORD, and the rest of them. When this category is selected, I would like for the ticket to be assigned to the submitters manager. Although the Select logic would make sense (WHERE hd_ticket.Category = 'Soft....) but how would I pull the submitter information and assign it to THEIR manager.
If it would make any difference: I already have a rule that populated the custom_field_value2 with the submitters label, and maybe dependant on that label I would have it assigned to a person? But then I would have to write a rule for each label....
Any ideas??
Answers (1)
Your update query will be something like this:
UPDATE HD_TICKET
SET HD_TICKET.OWNER_ID = (SELECT CUSTOM_1 FROM USER WHERE USER.ID = HD_TICKET.SUBMITTER_ID)
where
(HD_TICKET.ID in (<TICKET_IDS>))
That's assuming that the CUSTOM_1 field contains the KACE user ID of the person's manager, but I realize now that's probably not what it is set to. So we need to find a way to relate what is in CUSTOM_1 to the correct KACE user id. If it's username then the query should be something like this:
UPDATE HD_TICKET
SET HD_TICKET.OWNER_ID = (SELECT USER.ID FROM USER WHERE USER.CUSTOM_1 = (SELECT CUSTOM_1 FROM USER WHERE USER.ID = HD_TICKET.SUBMITTER_ID))
where
(HD_TICKET.ID in (<TICKET_IDS>))
That seems really convoluted, however, so I don't know if it will work. There might be a way to do this with a join instead, but I'm not sure.
Comments:
-
Yeah but what you mentioned about custom_1 is where I'm stuck. The rest of the logic seems right there (tickets, where this category is this, and this is the managers ID, then assign ticket to the ID in custom_field_value0) buttttt getting that manager ID is the problem
My biggest pitfall with KACE, and speedbump, is trying to get that active relationship to open up. I pulled the manager information from LDAP so that is within the USER information, but trying to sap that information is where I'm having trouble. Especially without hardcoding names. - Wildwolfay 11 years ago-
What are you pulling in for the manager? Is it username, fullname or something else? - chucksteel 11 years ago
-
It literally pulls the Manager field from their AD account info (Organization Tab)
This is their custom field 3 post LDAP import:
CN=Manager Name,OU=DP CSR,OU=DP Customer Service,OU=COMPANY,DC=COMPANY,DC=com - Wildwolfay 11 years ago-
If the manager name matches how their name is displayed in KACE then there should be a way to perform the matching. You would have to incorporate some MySQL functions grab just the name portion from the string. We don't make use of the Manager field in our AD so I'm not sure what format the name is stored. - chucksteel 11 years ago
-
It's their display name, and yeah it would just take some join statements in order to get that if it wasn't some long string, in which I hit my SQL brickwall of how to filter out such things. - Wildwolfay 11 years ago
-
I reference this page frequently:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
You'll need to use something like
mid(CUSTOM_3, 3, locate(",", CUSTOM_3)
Of course using a regular expression would probably be neater but I've always been lousy at those. - chucksteel 11 years ago
