Home› Analyst Portal

In what table will I find the Service Request "Implementation Notes"

Alan_FosterAlan_Foster Customer Adept IT Monkey ✭✭
in Analyst Portal
I need a query for a report on the Service Request results.  I have the "implementation Results" from the workitem table but don't know what table to join to get the "Implementation Notes".
 

Best Answers

  • Alan_FosterAlan_Foster Customer Adept IT Monkey ✭✭
    edited June 2018 Answer ✓
    In case anyone else is interested, here is my SQL query to build a report displaying the SR, Implementation Result, and Implementation Notes.

    /********* Security Firewall Request Report ***********/
    SELECT [Id_9A505725_E2F2_447F_271B_9B9F4F0D190C] AS 'ID'
          ,[Title_9691DD10_7211_C835_E3E7_6B38AF8B8104] AS 'Title'
          ,[CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688] AS 'Created Date'
          ,[CompletedDate_3116AB82_745B_654E_6B48_74E8AAF48065] AS 'Completed Date'
          ,[Description_59B77FD5_FE0E_D2B5_D541_0EBBD1EC9A2B] AS 'Description'
       ,[ResultDisplay].LTValue AS 'Implementation Result'
          ,[Notes_F647D4C9_9543_73C4_79D0_FCB06C288B5D] AS 'Implementation Notes'
       
    FROM [ServiceManager].[dbo].[MTV_System$WorkItem$ServiceRequest] AS Workitem
     LEFT JOIN [ServiceManager].[dbo].[LocalizedText] AS ResultDisplay ON ResultDisplay.LTStringId = Workitem.[ImplementationResults_AF8FC947_2D63_F965_8CAD_6BA329903711]
    WHERE (LanguageCode = 'ENU' AND LTStringType='1' AND Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 LIKE '%Firewall%')
     Or (LanguageCode IS NULL AND LTStringType IS NULL AND Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 LIKE 'Firewall Request')
     AND CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688 >= @StartDate AND CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688 < @EndDate


    The NULL value references where needed to display several "Test" request that where cancelled and therefore did not have language code and string type set. 

Answers

  • Tom_HendricksTom_Hendricks Customer Super IT Monkey ✭✭✭✭✭
    The name of this field is actually just "Notes" in the background.  This can be a bit confusing, especially if you are comparing it to the CR ticket which has a Notes field and an Implementation Results field, which are distinct.
  • Alan_FosterAlan_Foster Customer Adept IT Monkey ✭✭
    edited June 2018
    @Tom_Hendricks
    I don't see a "Notes" field in the Workitem table in the ServiceManagement DB.  In what table will I find the "Implementation Notes"?
  • Alan_FosterAlan_Foster Customer Adept IT Monkey ✭✭
    edited June 2018
    @Roland_Kind
    Would you know what table to join to MTV_System$WorkItem$ServiceRequest to get the "Implementation Results" wording instead of just the ID.  like "Implemented successfully" = D84DF223-CC9F-B503-D1D4-824D225D2722 ?
    In the servicemanagement DB I would join the displaystring table with the workitem table to get the ID's wording.
  • Alan_FosterAlan_Foster Customer Adept IT Monkey ✭✭
    edited June 2018 Answer ✓
    In case anyone else is interested, here is my SQL query to build a report displaying the SR, Implementation Result, and Implementation Notes.

    /********* Security Firewall Request Report ***********/
    SELECT [Id_9A505725_E2F2_447F_271B_9B9F4F0D190C] AS 'ID'
          ,[Title_9691DD10_7211_C835_E3E7_6B38AF8B8104] AS 'Title'
          ,[CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688] AS 'Created Date'
          ,[CompletedDate_3116AB82_745B_654E_6B48_74E8AAF48065] AS 'Completed Date'
          ,[Description_59B77FD5_FE0E_D2B5_D541_0EBBD1EC9A2B] AS 'Description'
       ,[ResultDisplay].LTValue AS 'Implementation Result'
          ,[Notes_F647D4C9_9543_73C4_79D0_FCB06C288B5D] AS 'Implementation Notes'
       
    FROM [ServiceManager].[dbo].[MTV_System$WorkItem$ServiceRequest] AS Workitem
     LEFT JOIN [ServiceManager].[dbo].[LocalizedText] AS ResultDisplay ON ResultDisplay.LTStringId = Workitem.[ImplementationResults_AF8FC947_2D63_F965_8CAD_6BA329903711]
    WHERE (LanguageCode = 'ENU' AND LTStringType='1' AND Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 LIKE '%Firewall%')
     Or (LanguageCode IS NULL AND LTStringType IS NULL AND Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 LIKE 'Firewall Request')
     AND CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688 >= @StartDate AND CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688 < @EndDate


    The NULL value references where needed to display several "Test" request that where cancelled and therefore did not have language code and string type set. 
Sign In or Register to comment.