Workflow and ServiceDesk Community

In this Article I'm going to explain a way of getting the Classifications from the Process Manager db for use within your Workflows.

In an Incdient in ServiceDesk you can set classification.

These are created and administered at Admin | Data | Hierarchy Data Service

• http://localhost/ProcessManager/Hierarchy/HierarchyItemListing.aspx

When they are stored against the ticket they are saved as a "." separated string

• e.g. Hardware.Desktop

These are split and joined within the various Workflows.

I had the need in a WF to check if given string was an available Classification before setting it.

I can use the below CTE that takes in a @flag parameter of 'Hardware', being the Second Level item, below 'Incident Management' to check it exists and then show all it's siblings.

DECLARE @ParentChildLocationRAT UNIQUEIDENTIFIER
SET @ParentChildLocationRAT = '3B4F3C0E-31DF-4AB3-A05F-C6F0550A9305'; --'Incident Management' FROM [HierarchyCategory]
DECLARE @flag nvarchar(255) = 'hardware'

BEGIN
WITH CTE_HierarchyAssociatedPairs
    AS (
        SELECT 
    hi.parentHierarchyItemid,
    name 'name',
    HierarchyItemid,
    0 as Depth,
    name as flag
        FROM [HierarchyItem] hi
        where HierarchyCategoryID = @ParentChildLocationRAT--'3B4F3C0E-31DF-4AB3-A05F-C6F0550A9305'
        
        UNION ALL
        
  SELECT 
   hi1.parentHierarchyItemid,
   hi1.name,
   hi1.HierarchyItemid,
            ha.Depth + 1 AS Depth,
            ha.flag
        FROM [HierarchyItem] hi1
        JOIN CTE_HierarchyAssociatedPairs ha ON hi1.parentHierarchyItemid = ha.HierarchyItemid
        )
    SELECT DISTINCT *
    FROM CTE_HierarchyAssociatedPairs
    WHERE flag = @flag
    ORDER BY Depth
END

Result

One use for this is with the Email Monitor.

Say you put a Classification in the body of an email and wish to map it to a ticket you could split it, pass the first value to this SP, return the results then filter on the subsequent depth fields to get at least a parital match classification set. This could be if it was incorrect in the email or classifications had been removed from Process Manager.

So Hardware is found, then Desktop is found but Computer isn't so only the first two are mapped in.

In a previous version of ServiceDesk the Classification was split into separate Fields, this could made it easier for reporting and other requirements.

If you have migrated it and it is stored as a "." separated string in the new version and want it back into the separate Fields you could use the following.

This does assume there are only 10 levels.

(Create by our resident SQL Guru Kev)

You could create a Stored Procedure that gets Incident Details with the Classification split

• sp_IncidentWithSplitClassification

WITH e1(n) 
AS 
(
	SELECT 1 UNION ALL 
	SELECT 1 UNION ALL
	SELECT 1 UNION ALL 
	SELECT 1 UNION ALL 
	SELECT 1 UNION ALL 
	SELECT 1 UNION ALL 
	SELECT 1 UNION ALL 
	SELECT 1 UNION ALL 
	SELECT 1 UNION ALL 
	SELECT 1
)
,e2(n) 
AS 
(
	SELECT 0 UNION ALL
	SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) 
	FROM 
			(SELECT 1 as 'n'
				FROM 
					e1 a,
					e1 b, 
					e1 c)  cj
)
,IMwithSplit AS 
(
	SELECT [ProcessId]
		  ,[SessionId]
		  ,[ReportProcessId]
		  ,[CurrentTask]
		  ,[IncidentName]
		  ,[IncidentDescription]
		  ,[IncidentType]
		  ,[Classification]
		  ,[SubmittedBy]
		  ,[AffectedUser]
		  ,[Owner]
		  ,[TicketStatus]
		  ,[CurrentlyAssignedQueueId]
		  ,SUBSTRING(Classification,n+1,ISNULL(NULLIF(CHARINDEX('.',classification,N+1),0)-(N+1),8000)) 'csplit'
		  ,ROW_NUMBER() OVER (PARTITION BY processid ORDER BY (e.n)) rno
	FROM 
		ImIncidentTicket it
	LEFT JOIN 
		e2 e
		ON e.n <= 1000
	WHERE 
		SUBSTRING(Classification, n, 1) = '.' 
	OR	n = 0
)SELECT [ProcessId]
    ,[SessionId]
	,ISNULL([1],'') AS  'Classification1'
	,ISNULL([2],'') AS 'Classification2'
	,ISNULL([3],'') AS 'Classification3'
	,ISNULL([4],'') AS 'Classification4'
	,ISNULL([5],'') AS 'Classification5'
	,ISNULL([6],'') AS 'Classification6'
	,ISNULL([7],'') AS 'Classification7'
	,ISNULL([8],'') AS 'Classification8'
	,ISNULL([9],'') AS 'Classification9'
	,ISNULL([10],'') AS 'Classification10'
FROM IMwithSplit
PIVOT
(
	max(csplit) for rno in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])
) pvt

Classification = "Software.Altiris.CMDB"

Result

Forum

Export the Hierarchy Classifications to a Spreadsheet

https://www-secure.symantec.com/connect/forums/export-hierarchy-classifications-spreadsheet

Category, Parent, Item

SELECT 
    p.Name AS Category, 
    i.Name AS Parent, 
    o.Name AS Item
FROM 
    [HierarchyCategory] AS p 
    INNER JOIN [HierarchyItem] AS i ON p.HierarchyCategoryID = i.HierarchyCategoryID 
    LEFT OUTER JOIN [HierarchyItem] AS o ON i.HierarchyItemID = o.ParentHierarchyItemID
ORDER BY 
    Category, Parent

Get a Simple List

SELECT 
    i.name as Parent, 
    o.name as Item
FROM 
    [HierarchyItem] o
    INNER JOIN [HierarchyItem] i ON i.HierarchyItemID = o.ParentHierarchyItemID

Email Routing and Classification

https://www-secure.symantec.com/connect/forums/email-routing-and-classification