How to view ticket CMDB Locations in a ServiceDesk report

book

Article ID: 180862

calendar_today

Updated On:

Products

ServiceDesk

Issue/Introduction

 

Resolution

ServiceDesk includes several out of box reports that display tickets by location, such as "List Open Incidents by Location". The way that this works requires a worker to have specified the Location in the drop down field when saving the advanced ticket. A primary contact that is referenced by the advanced ticket that has a location does not automatically set this drop down field, nor is it a required field. Therefore, advanced tickets can be created that do not have the Location set. The best recommendation is to provide training to the workers to ensure they always fill in the Location drop down field. Alternatively, a customization (which is not discussed or part of the scope of this article) could be implemented to force the Location drop down to have a value before the ticket can be saved.

If there are many tickets already in the database that have no ServiceDesk Location set but do have primary contacts who do have locations set (which is an associated data class from CMDB), the following example SQL can be used to add to a custom report to cross-reference the CMDB Locations as well as the specified (if any) ServiceDesk Locations.

Please note: This SQL query is provided AS-IS as an example on how to cross-reference CMDB data from ServiceDesk. This assumes that the ProcessManager and Symantec_CMDB databases are on the same SQL Server.

DECLARE @SDLocations TABLE (SDRowID INT IDENTITY(1,1), CMDBLocation NVARCHAR(255) DEFAULT '', SDLocation NVARCHAR(255), SDProcessID NVARCHAR(20),
SDLoginName NVARCHAR(255), SDFirstName NVARCHAR(100), SDLastName NVARCHAR(100), SDEmail NVARCHAR(255), SDTitle NVARCHAR(1000), SDCreatedOn DATETIME,
SDPriority NVARCHAR(10), SDCatagory1 NVARCHAR(1000))
DECLARE @CMDBUsers TABLE (CMDBRowID INT IDENTITY(1,1), CMDBLoginName NVARCHAR(255), CMDBFirstName NVARCHAR(100), CMDBLastName NVARCHAR(100),
CMDBLocation NVARCHAR(255))
DECLARE @LoopNoSD INT, @TotalRowsSD INT, @LoopNoCMDB INT, @TotalRowsCMDB INT, @SDUser NVARCHAR(201), @SDFirstName NVARCHAR(100),
@SDLastName NVARCHAR(100), @CMDBUser NVARCHAR(201), @CMDBLocation NVARCHAR(255)
USE ProcessManager
INSERT INTO @SDLocations(SDLocation, SDProcessID, SDLoginName, SDFirstName, SDLastName, SDEmail, SDTitle, SDCreatedOn, SDPriority, SDCatagory1)
SELECT sdim.Location, sdim.process_id, u.ADLoginName, u.FirstName, u.LastName, u.PrimaryEmail, sdim.incident_name, sdim.date_created,
sdim.Priority, sdim.classification_category_01
FROM ServiceDeskIncidentManagement sdim
JOIN ReportProcessContact rpc
ON rpc.SessionID = sdim.session_id
JOIN [User] u
ON u.UserID = rpc.ReferenceID
WHERE rpc.ContactType = 'Affected User'
AND resolutionText IS NULL
ORDER BY sdim.process_id
USE Symantec_CMDB
INSERT INTO @CMDBUsers(CMDBLoginName, CMDBFirstName, CMDBLastName, CMDBLocation)
SELECT Domain + '\' + vu.Name, [Given Name], Surname, i.Name
FROM vUser vu
JOIN ResourceAssociation ra
ON ra.ParentResourceGuid = vu.Guid
JOIN Item i
ON ra.ChildResourceGuid = i.Guid
WHERE ra.ResourceAssociationTypeGuid = '2030C6CD-C049-4C81-957D-34E4DFB23BCF'
AND Domain + '\' + vu.Name <> '\'
ORDER BY Domain, vu.Name
SELECT @LoopNoSD = 1, @TotalRowsSD = MAX(SDRowID)
FROM @SDLocations
WHILE @LoopNoSD <= @TotalRowsSD
BEGIN
  SELECT @SDUser = SDLoginName, @SDFirstName = SDFirstName, @SDLastName = SDLastName
  FROM @SDLocations
  WHERE SDRowId = @LoopNoSD
  SELECT @LoopNoCMDB = 1, @TotalRowsCMDB = MAX(CMDBRowID)
  FROM @CMDBUsers
  WHILE @LoopNoCMDB <= @TotalRowsCMDB
  BEGIN
    SELECT @CMDBUser = CMDBLoginName, @CMDBLocation = CMDBLocation
    FROM @CMDBUsers
    WHERE CMDBRowId = @LoopNoCMDB
    IF @SDUser = @CMDBUser
    BEGIN
      UPDATE @SDLocations
      SET CMDBLocation = @CMDBLocation
      WHERE SDRowID = @LoopNoSD
      UPDATE @SDLocations     
      SET SDLocation = ''
      WHERE SDLocation IS NULL     
    END
    SET @LoopNoCMDB = @LoopNoCMDB + 1 
  END
  SET @LoopNoSD = @LoopNoSD + 1
END
SELECT CMDBLocation 'CMDB Location', SDLocation 'ServiceDesk Location', SDProcessID 'ID', SDFirstName 'First Name', SDLastName 'Last Name',
SDEmail 'Primary Email', SDTitle 'Title', SDCreatedOn 'Date Opened', SDPriority 'Priority', SDCatagory1 'Catagory1'
FROM @SDLocations
ORDER BY CMDBLocation, SDProcessID

For more information on how CMDB Locations work, please refer to the following article:

How are Locations, Departments, Equipment and Services populated in ServiceDesk?
http://www.symantec.com/business/support/index?page=content&id=HOWTO30360