How to view ticket CMDB Locations in a ServiceDesk report
Last Updated January 10, 2012
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: