This query is the basis for a SRS report that accepts the AD Office and
Department locations so that our various site managers can quickly pull a
report of Active incidents for users at their site. This relies upon these
attributes being accurate for the User objects in SCSM. In addition, we use
the Cireson Web Console and allow these managers to access it. So the report
gives them a quick summary and if they want details they just click the URL
link and can see the 'live' incident. Big plug for Cireson - great app!
SELECT
inc.IncidentDimKey,
wi.ID AS 'Incident ID',
usr2.DisplayName AS 'Assigned To User'
,wi.Title
,'http://srvhojisv312:85/ViewIncident.aspx?id=' + wi.Id AS 'Link to
Incident'
,usr.DisplayName AS 'Affected User'
,usr.Department
,inc.CreatedDate
,inc.Status
FROM [DWDataMart].[dbo].[WorkItemDimvw] wi
JOIN [DWDataMart].[dbo].[IncidentDimvw] inc on wi.EntityDimKey =
inc.EntityDimKey
JOIN dwdatamart.dbo.IncidentTierQueuesvw tqvw on
inc.TierQueue_IncidentTierQueuesId = tqvw.IncidentTierQueuesId
JOIN [DWDataMart].[dbo].[DisplayStringDim] disp on tqvw.EnumTypeId =
disp.MPElementId
JOIn [DWDataMart].[dbo].[WorkItemAffectedUserFactvw] aff on inc.IncidentDimKey
= aff.WorkItemDimKey
JOIN [DWDataMart].[dbo].[UserDimvw] usr on aff.WorkItemAffectedUser_UserDimKey
= usr.UserDimKey
JOIN DWDataMart.dbo.[WorkItemAssignedToUserFactvw] assusr on inc.IncidentDimKey
= assusr.WorkItemDimKey
JOIN DWDataMart.dbo.UserDimvw usr2 on assusr.WorkItemAssignedToUser_UserDimKey
= usr2.UserDimKey
where
--wi.ID = 'IR2488'
Disp.LanguageCode = 'ENU'
--AND usr.Department IN (@Dept) AND usr.Office in (@Office)
AND inc.Status LIKE '%Active%'
ORDER BY inc.CreatedDate DESC
