SMA SQL: Subcategory question
Scenario: Rule changes the title of a service desk field if the character limit exceeds 50. I want the new field to read the category name and/or the category name plus something else. Now I have gotten that working, but it is not able to read subcategories. For example, if the category is "Microsoft:: Windows::Office" then the SQL will only change it to "Microsoft::" So how can I get it to show all of the subcategories? My SQL here: https://pastebin.com/Mmqx7hd5
Answers (2)
I am still stumped on how to get KACE to set/concat a category name.
If category is "Parent Ticket" and I want to update to "New Employee::", how do I word this? (new employee has blank subcategory)
What I have does not work. It changes the category to "Initial Setup" instead, and I have no idea why. Its not even close to the beginning of the alphabetized list.
SELECT:
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(M1.ID is null, 'z', concat('a', M1.NAME)) as sort_MACHINE_NAME,
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() - unix_timestamp(HD_TICKET.TIME_OPENED)
else unix_timestamp() - 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(HD_TICKET.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_CATEGORY.NAME = 'Parent Ticket') and ( HD_TICKET.ID between '15829' and '20000') and HD_TICKET.HD_QUEUE_ID = 5 )
UPDATE:
UPDATE HD_TICKET PARENT, HD_TICKET, HD_CATEGORY
set HD_TICKET.HD_CATEGORY_ID = ' : New Employee', HD_TICKET.TITLE = concat( 'New Employee - ',HD_TICKET.CUSTOM_FIELD_VALUE18,' - ',HD_TICKET.CUSTOM_FIELD_VALUE8,' - ', HD_TICKET.CUSTOM_FIELD_VALUE21,' - ',HD_TICKET.CUSTOM_FIELD_VALUE20,' - ',HD_TICKET.CUSTOM_FIELD_VALUE19 )
where
(HD_TICKET.ID in (<TICKET_IDS>))
Comments:
-
set HD_TICKET.HD_CATEGORY_ID in your update statement won't work because it needs to be a number. I think that is why you are getting a strange result for this. You need to lookup what this number is for the category you are after and set it to that. The HD_CATEGORY table is the one with the name in it, not HD_TICKET. - Mashby1 1 year ago
-
That makes some sense, but I do not know how to find those ID numbers. - walkerl 1 year ago
-
The easiest way is in a report: reporting -> reports -> new (SQL) -> put this SQL in (and name and such) and save it:
SELECT ID, NAME
FROM HD_CATEGORY
WHERE NAME LIKE '%New Employee%'
And then run as HTML (using the button beside it) to see what the results are. Should return the IDs and Names for any queues that have that wording in it so you can figure out the ID you need for your original query. - Mashby1 1 year ago
Because of what you are saying it is doing, it is possible that SQL is reading the commas as part of the set statement? As you can pass comma separated values to SET, so maybe try:
SET (HD_TICKET.TITLE = CONCAT(HD_CATEGORY.NAME, ' : Test'))
if not an alternative syntax for that line could be:
SET HD_TICKET.TITLE = HD_CATEGORY.NAME + ' : Test'
Not sure what else to try.
Comments:
-
Well I have learned how to use concat to pass CSV, but the HD.CATEGORY.NAME only shows the top level category. What's more, it is showing our default category (Account::) rather than whatever the true category is. So another problem. - walkerl 1 year ago
-
If you query your DB (I do this in a table query "report" and run as HTML - just make sure to use LIMIT if you select *) does it also only show top level for HD_CATEGORY.NAME? If it shows the whole thing it shows there must be something incorrect with the query. Mine certainly shows the whole thing meaning I'd drill down on the UPDATE part as to the source of the problem. - Michelle.ashby 1 year ago
-
I think my SQL is calling the name of the available categories rather than the name of category chosen on the actual ticket I am working on. I am not sure how to format that to work correctly. I also have a goal of having a ticket rule append the excessively long ticket title into the ticket summary or new comments automatically and then change the title. I have not found a way to make that work. - walkerl 1 year ago