Service Manager Service Catalog Query

Author by Christopher Mank

Anytime you setup a new Request Offering (RO), you are required to map the prompts to properties in the selected template.  When it comes to integrating Orchestrator Runbooks in ROs, we actually just map the prompts to some generic properties.  So for example on the Service Request class, we have some Ext properties to hold our values (ExtString1, ExtString2, ExtDate1, etc.).  This allows us to just pass the Runbook Automation Activity GUID to the Runbook and then using relationships, find the values of the prompts the user entered in. SCSM Request Offering Mapping Image This is really a great solution because if you later want to add some additional prompts to your RO, you don't need to re-sync the Runbook in SCSM to Orchestrator.  Once it's setup once, you are good to go. The one challenge that does come into play with this setup, as you start to add more and more published ROs to your Service Catalog, it become increasingly challenging to track what prompts are mapped to what properties.  ExtString1 in your New Hardware Request may map to a username while on your Add New User Request, ExtString1 may map to an Office location.  So how can we manage all of these requests? The first option is keeping track in a spreadsheet.  As you add new prompts, you can keep track of what properties they are mapped to.  This is a great place to start but just like   anything else, the data is only as good as those who maintain it. SCSM Request Offering Mapping Spreadsheet Image So the second solution...go to the source.  To solve the challenge of knowing how my Request Offerings are mapped, I wrote a SQL query that runs against the live operational CMDB that displays exactly how each RO is mapped.

USE ServiceManager;
SOCategory.DisplayName AS 'Category',
 SO.Title_7AE4D8A8_00C8_DF5A_347D_A336D5D875BF AS 'SO Title',
 SO.BriefDescription_D0A7DBFA_DB91_FF21_4133_DE93C42965D5 AS 'SO Description',
 SO.Overview_E32CC8CA_4982_11F8_54AD_6C137A89DEA1 AS 'SO Overview',
 RO.Title_7AE4D8A8_00C8_DF5A_347D_A336D5D875BF AS 'RO Title',
 RO.BriefDescription_D0A7DBFA_DB91_FF21_4133_DE93C42965D5 AS 'RO Description',
 RO.Overview_E32CC8CA_4982_11F8_54AD_6C137A89DEA1 AS 'RO Overview',
 CONVERT(XML, RO.PresentationMappingTemplate_FFA7A1B9_BD46_952D_FC8F_A9BC64E489F3).query('
 for $ed in Object/Data/PresentationMappingTemplate/Sources/Source return $ed') AS 'Mapping Template'
MTV_System$ServiceOffering SO
-- Service Offering Status/Category
 LEFT OUTER JOIN DisplayStringView SOStatus ON SO.Status_17348DC0_DB86_81C4_DC59_5EE105DAF50A = SOStatus.LTStringId AND SOStatus.LanguageCode = 'ENU'
 LEFT OUTER JOIN DisplayStringView SOCategory ON SO.Category_FA6F9436_5014_F7B7_CB61_38D3F8202828 = SOCategory.LTStringId AND SOCategory.LanguageCode = 'ENU'
-- Request Offering
 LEFT OUTER JOIN Relationship R ON SO.BaseManagedEntityId = R.SourceEntityId AND R.RelationshipTypeId = 'BE417A55-6622-0FC3-FCEA-90CD23E0FC23' AND R.IsDeleted = 0
 LEFT OUTER JOIN MTV_System$RequestOffering RO ON R.TargetEntityId = RO.BaseManagedEntityId
-- Request Offering Status
 LEFT OUTER JOIN DisplayStringView ROStatus ON RO.Status_17348DC0_DB86_81C4_DC59_5EE105DAF50A = ROStatus.LTStringId AND ROStatus.LanguageCode = 'ENU'
-- SO is Published
 SOStatus.DisplayName = 'Published'
-- RO is Published
 ROStatus.DisplayName = 'Published'
SOCategory.DisplayName ASC,
 SO.Title_7AE4D8A8_00C8_DF5A_347D_A336D5D875BF ASC,
 RO.Title_7AE4D8A8_00C8_DF5A_347D_A336D5D875BF ASC

Open SQL Server Management Studio and run this query against your live operational CMDB.  You should see results like the below (broken up into 2 screenshots). SCSM Request Offering Query Results 1 Image SCSM Request Offering Query Results 2 Image You will notice the last column, Mapping Template, is highlighted in Blue.  This is the XML that actually contains the configuration data we are interested in.  If you click on the Mapping of the RO you are interested in, it will open in a separate window. SCSM Request Offering Mapping XML Image From here, you can see all of the Prompts, Is ReadOnly, Is Optional, Prompt Type, List Items, Query Object Configuration and all of the Mapping.  Using this query, I hope this helps to quickly find how all of your ROs are mapped. Until the Whole World Hears, Christopher


Christopher Mank

Systems Architect