Home CMDB Portal

Might anyone have a query that can pull SR tickets from a DWDataMart with all action and comment log

alex_kokinalex_kokin Member IT Monkey ✭

Might anyone have a query that can pull SR tickets from a DWDataMart with all associated action and comment logs from the CMDWDataMart ?

Answers

  • Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭

    As a matter of fact I do.

    --Define the Service Request whose Action Log you want to grab
    DECLARE @serviceRequestID nvarchar(20)
    SET @serviceRequestID = 'SR1234'
    
    
    --Get the Action Log as defined by SCSM
    SELECT  sr.id as 'ID', sr.description as 'Description',
        wial.EnteredDate, wial.EnteredBy, wial.Description as 'Entry',
        ds.DisplayName as 'Action Type',
        null as 'Private'
    FROM servicerequestdim as sr
        LEFT JOIN ServiceRequestRelatesToActionLogFactvw as srRelAL on sr.ServiceRequestDimKey = srRelAL.ServiceRequestDimKey
            INNER JOIN WorkItemActionLogDimvw as wial on srRelAL.WorkItemHasActionLog_WorkItemActionLogDimKey = wial.WorkItemActionLogDimKey
            INNER JOIN ActionType as actiontype on wial.ActionType_ActionTypeId = actiontype.ActionTypeId
            INNER JOIN DisplayStringDim as ds on actiontype.EnumTypeId = ds.BaseManagedEntityId
    WHERE sr.id = @serviceRequestID
        AND ds.LanguageCode = 'ENU'
    ORDER BY 'EnteredDate'
    --
    
    
    -- Get the Action Log as defined by the Analyst
    SELECT sr.id, sr.Description,
        wicl.EnteredDate, wicl.EnteredBy, wicl.Comment as 'Entry',
        wiacl.Comment as 'Analyst Comment',
        wiucl.Comment as 'User Comment',
        wiacl.IsPrivate as 'Private'
    FROM servicerequestdim as sr
        LEFT JOIN ServiceRequestRelatesToCommentLogFactvw as srRelACL on sr.ServiceRequestDimKey = srRelACL.ServiceRequestDimKey
            INNER JOIN WorkItemCommentLogDimvw as wicl on srRelACl.WorkItemHasCommentLog_WorkItemCommentLogDimKey = wicl.WorkItemCommentLogDimKey
                LEFT JOIN WorkItemAnalystCommentLogDim as wiacl on wicl.EntityDimKey = wiacl.EntityDimKey
                LEFT JOIN WorkItemUserCommentLogDim as wiucl on wicl.EntityDimKey = wiucl.EntityDimKey
    WHERE sr.id = @serviceRequestID
    ORDER BY 'EnteredDate'
    


    Now I should add that this only works if you've extended the SCSM DW with a management pack that syncs the Action Log for Work Items. As this does not occur out of the box with SCSM nor its DW component. Probably the most well known solution in this area is the following management pack on Technet. As a heads up though, before you go and import it

    • This doesn't sync Problem Action Logs
    • It's a sealed MP

    Which means if you want Problem Action Logs - the MP will require some edits and then your own re-seal. But otherwise you should be good to go.

Sign In or Register to comment.