Home› General Discussion
Options

Incident Reporting against CIs

GordonGordon Member Adept IT Monkey ✭✭
in General Discussion

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? 

Answers

  • Options
    Brett_MoffettBrett_Moffett Cireson PACE Super IT Monkey ✭✭✭✭✭
    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.
  • Options
    Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭
    edited December 2016
    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
  • Options
    GordonGordon Member Adept IT Monkey ✭✭
    Thanks bud!
Sign In or Register to comment.