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

alex_kokin

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

Adam_Dzyacky

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.