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),
DECLARE @LoopNoSD INT, @TotalRowsSD INT, @LoopNoCMDB INT, @TotalRowsCMDB INT, @SDUser NVARCHAR(201), @SDFirstName NVARCHAR(100),
@SDLastName NVARCHAR(100), @CMDBUser NVARCHAR(201), @CMDBLocation NVARCHAR(255)
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,
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
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)
WHILE @LoopNoSD <= @TotalRowsSD
SELECT @SDUser = SDLoginName, @SDFirstName = SDFirstName, @SDLastName = SDLastName
WHERE SDRowId = @LoopNoSD
SELECT @LoopNoCMDB = 1, @TotalRowsCMDB = MAX(CMDBRowID)
WHILE @LoopNoCMDB <= @TotalRowsCMDB
SELECT @CMDBUser = CMDBLoginName, @CMDBLocation = CMDBLocation
WHERE CMDBRowId = @LoopNoCMDB
IF @SDUser = @CMDBUser
SET CMDBLocation = @CMDBLocation
WHERE SDRowID = @LoopNoSD
SET SDLocation = ''
WHERE SDLocation IS NULL
SET @LoopNoCMDB = @LoopNoCMDB + 1
SET @LoopNoSD = @LoopNoSD + 1
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'
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?