laura.hamersly

Finding KT Categories and Request Areas that Don't Match

Blog Post created by laura.hamersly on May 8, 2015

As part of the functionality for the request area to populate properly into a request/incident/problem after choosing a corresponding KT document, the KT Category that the document resides in and its corresponding request area must match perfectly. If it is even just one character off (but not case-sensitive), the request area will fail to populate properly into the ticket.

 

KTcategory1.png= TicketRequestArea1.png

 

I have put together a SQL query that can help us proactively find KT categories with missing request areas. A SQL file containing the query is also attached to this document.

SELECT DISTINCT
CASE WHEN oi2.caption is not null THEN oi2.caption ELSE '' END +
CASE WHEN oi3.caption is not null THEN '.' +oi3.caption ELSE '' END +
CASE WHEN oi4.caption is not null THEN '.' +oi4.caption ELSE '' END +
CASE WHEN oi5.caption is not null THEN '.' +oi5.caption ELSE '' END +
CASE WHEN oi6.caption is not null THEN '.' +oi6.caption ELSE '' END +
CASE WHEN oi7.caption is not null THEN '.' +oi7.caption ELSE '' END +
CASE WHEN oi8.caption is not null THEN '.' +oi8.caption ELSE '' END +
/*If your KT Categories are more than 8 folders deep, insert more "CASE WHEN oi##" statements such as the one above,
starting with oi10 as the name of your table alias
*/
CASE WHEN oi9.caption is not null THEN '.' +oi9.caption ELSE '' END AS KTC
, oi1.caption AS caption
, CAST('' AS VARCHAR(500)) AS title
, CAST('' AS VARCHAR(6)) AS KTID
INTO ##KTC
FROM o_indexes oi1
LEFT OUTER JOIN o_indexes oi2 ON SUBSTRING(oi1.relational_id, 3, 6) = oi2.id
LEFT OUTER JOIN o_indexes oi3 ON SUBSTRING(oi1.relational_id, 10, 6) = oi3.id
LEFT OUTER JOIN o_indexes oi4 ON SUBSTRING(oi1.relational_id, 17, 6) = oi4.id
LEFT OUTER JOIN o_indexes oi5 ON SUBSTRING(oi1.relational_id, 24, 6) = oi5.id
LEFT OUTER JOIN o_indexes oi6 ON SUBSTRING(oi1.relational_id, 31, 6) = oi6.id
LEFT OUTER JOIN o_indexes oi7 ON SUBSTRING(oi1.relational_id, 38, 6) = oi7.id
LEFT OUTER JOIN o_indexes oi8 ON SUBSTRING(oi1.relational_id, 45, 6) = oi8.id
LEFT OUTER JOIN o_indexes oi9 ON SUBSTRING(oi1.relational_id, 52, 6) = oi9.id
/*If your KT Categories are more than 8 folders deep, insert more oi## left outer joins such as the line above,
starting with oi10 as the name of your table alias. The start of the substring will increase by 7. The oi## will need
to correspond with the newly added oi## in the select statement.
*/
WHERE oi4.caption is not null
--AND oi2.caption not like '%ARCHIVE%' --If you have any "Archive" folders containing only retired documents, uncomment the beginning of this statement and include them here
AND oi1.has_children=0
ORDER BY KTC

UPDATE ##KTC
SET ##KTC.title = skeletons.title,
KTID = skeletons.id
FROM skeletons
LEFT OUTER JOIN ##KTC ON ##KTC.KTC = skeletons.title

SELECT ##KTC.KTC AS [Knowledge Category]
FROM ##KTC
LEFT OUTER JOIN prob_ctg ON ##KTC.KTC = prob_ctg.sym
WHERE prob_ctg.sym is null
--AND ##KTC.KTC not like '[Category you want to be excluded]%' --If there are any Knowledge Categories that you don't want to be included in the search, uncomment and include them here
ORDER BY KTC
DROP TABLE ##KTC









 

So far, I have only tested this script with CA Service Desk R12.6 and SQL Server 2008 R2.

Attachments

Outcomes