Incident Reporting against CIs

Gordon

We recently made the shift to attach CIs to all of our IR tickets. The idea was that we would make CIs the center of the universe, eventually attaching them to PR, MA, SR, CR, etc. That way, if I want to know everything about a system, simply create a report around CIs and merge the data to the Work Item table.

I have the aforementioned report and it has multiple work item (duplicates if you will) one for each CI attached. Makes sense right. I should be able to pivot that data either way to say what work items are attached to what CIs and what CIs are attached to which work items...that is where the trouble comes in.

I have a number of IRs that appear over 1600 times in the power query extract. I checked..they one of them and it only has three affected CIs and two related/affected users, max total would be five. Cannot find a way to figure out why these examples appear so often. There are multiple examples of this and then multiple examples of items in the 800 range and then some in 5-10 range, and then a bunch that appear correct

Anyone seen anything like this before? 

Brett_Moffett

Where are you getting your data from? Data Warehouse? Cubes? Service Manager DB?
It sounds like there is an issue with either the Type Projection you are using or the query that is being executed here.
I would start by creating a query for just one CI and see what it reports as related WI's, then change this to a CI that is incorrectly assigned to a known WI (the one with 1600 CI's associated) and see what it reports. This should narrow down the issue.

Brian_Wiest

That is how we do all our work items and reporting
Example SQL statement I use to pull IR's with specific or type CI's

Data warehouse DWdataMart

DECLARE @StartDate datetime, @EndDate datetime

SET @StartDate = '01/01/2016'

SET @EndDate = '12/31/2016'

SELECT DISTINCT WIM.Id, IStatus.IncidentStatusValue, WIM.Title, DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), WIM.CreatedDate) AS CreateDate, AffectedUser.Department, REPLACE(REPLACE(IDM.Description, CHAR(13), ' '), CHAR(10), ' ') as 'Description', ITQ.IncidentTierQueuesValue

  FROM [SC2012_DWDataMart].[dbo].[WorkItemDimvw] WIM

  left outer join [SC2012_DWDataMart].[dbo].[IncidentDimvw] IDM on IDM.Id = WIM.Id

  left join [SC2012_DWDataMart].[dbo].[WorkItemCreatedByUserFactvw] WICB on WICB.WorkItemDimKey = WIM.WorkItemDimKey

  left join [SC2012_DWDataMart].[dbo].[UserDimvw] CreatedBy on CreatedBy.UserDimKey = WICB.WorkItemCreatedByUser_UserDimKey

  left join [SC2012_DWDataMart].[dbo].[WorkItemAboutConfigItemFactvw] WCI on WIM.WorkItemDimKey = WCI.WorkItemDimKey

  left join [SC2012_DWDataMart].[dbo].[ConfigItemDimvw] CID on CID.ConfigItemDimKey = WCI.WorkItemAboutConfigItem_ConfigItemDimKey

  left join [SC2012_DWDataMart].[dbo].[IncidentStatusDurationFactvw] TimeWorked on WIM.[WorkItemDimKey] = Timeworked.[IncidentDimKey] 

  left join [SC2012_DWDataMart].[dbo].[WorkItemAssignedToUserFactvw] WIAA on WIM.WorkItemDimKey = WIAA.WorkItemDimKey

  left join [SC2012_DWDataMart].[dbo].[UserDimvw] AssignedTo on AssignedTo.UserDimKey = WIAA.WorkItemAssignedToUser_UserDimKey

  left join [SC2012_DWDataMart].[dbo].[WorkItemAffectedUserFactvw] WIAU on WIM.WorkItemDimKey = WIAU.WorkItemDimKey

  left join [SC2012_DWDataMart].[dbo].[UserDimvw] AffectedUser on AffectedUser.UserDimKey = WIAU.[WorkItemAffectedUser_UserDimKey]

  left join [SC2012_DWDataMart].[dbo].[IncidentTierQueues] ITQ on IDM.Tierqueue = ITQ.[ID]

  left join [SC2012_DWDataMart].[dbo].[IncidentStatus] IStatus on IDM.[Status] = IStatus.[ID]

  WHERE WIM.CreatedDate >= @StartDate AND WIM.CreatedDate <= @EndDate
And CID.DisplayName like '%My CI Name%'

Update the last option to meet the needs of the CI lookup

HTH

Gordon

Thanks bud!