Home› SQL Server

SCSM DW [DWDataMart] - Get all incidents with last known related users and enum display names

Bruno_SerafimBruno_Serafim Customer IT Monkey ✭
in SQL Server

Hi,

I'm not a SQL expert but I just finished a query who returns all the incidents from the DWDataMart with the :

  • Last known related users display names :
    • Affected user
    • Assigned To
    • Primary Owner
    • Created by
  • Enum display names in french (FRA) if exists otherwise in ENU
    • Status
    • Classification
    • Source
    • Impact
    • Urgency
    • Tier queue
    • Resolution category

/ ! \ This query return the last user related even if the relationship have been deleted. / ! \


Plus it also returns the :

  • ID
  • Title
  • Created date
  • Resolution date
  • Closed date
  • Priority
  • Description
  • Resolution Description

I hope this query may help those who are looking to make reports from the data warehouse.

-- GET ALL INCIDENTS
SELECT  	IR.Id AS 'ID',
		IR.Title AS 'Titre',
		
		-- GET STATUS
		(
			SELECT TOP 1 IREStatus.DisplayName
			FROM DisplayStringDimvw as IREStatus 
			WHERE IR.Status = IREStatus.ElementName AND (IREStatus.LanguageCode = 'FRA' OR IREStatus.LanguageCode = 'ENU') 
			ORDER BY IREStatus.DisplayStringDimKey DESC
		) AS 'Statut',
		
		IR.CreatedDate AS 'Date de création (UTC)',
		IR.ResolvedDate AS 'Date de résolution (UTC)',
		IR.ClosedDate as 'Date de fermeture (UTC)',
		
		-- GET CLASSIFICATION
		(
			SELECT TOP 1 IREClassification.DisplayName
			FROM DisplayStringDimvw as IREClassification 
			WHERE IR.Classification = IREClassification.ElementName AND (IREClassification.LanguageCode = 'FRA' OR IREClassification.LanguageCode = 'ENU') 
			ORDER BY IREClassification.DisplayStringDimKey DESC
		) AS 'Classification',
		
		-- GET SOURCE
		(
			SELECT TOP 1 IRESource.DisplayName
			FROM DisplayStringDimvw as IRESource
			WHERE IR.Source = IRESource.ElementName AND (IRESource.LanguageCode = 'FRA' OR IRESource.LanguageCode = 'ENU') 
			ORDER BY IRESource.DisplayStringDimKey DESC
		) AS 'Source',
		
		-- GET IMPACT
		(
			SELECT TOP 1 IREImpact.DisplayName
			FROM DisplayStringDimvw as IREImpact
			WHERE IR.Impact = IREImpact.ElementName AND (IREImpact.LanguageCode = 'FRA' OR IREImpact.LanguageCode = 'ENU') 
			ORDER BY IREImpact.DisplayStringDimKey DESC
		) AS 'Impact',
		
		-- GET URGENCY
		(
			SELECT TOP 1 IREUrgency.DisplayName
			FROM DisplayStringDimvw as IREUrgency
			WHERE IR.Urgency = IREUrgency.ElementName AND (IREUrgency.LanguageCode = 'FRA' OR IREUrgency.LanguageCode = 'ENU') 
			ORDER BY IREUrgency.DisplayStringDimKey DESC
		) AS 'Urgence',
		
		IR.Priority AS 'Priorité',
		
		-- GET TIER QUEUE
		(
			SELECT TOP 1 IRETierQueue.DisplayName
			FROM DisplayStringDimvw as IRETierQueue
			WHERE IR.TierQueue = IRETierQueue.ElementName AND (IRETierQueue.LanguageCode = 'FRA' OR IRETierQueue.LanguageCode = 'ENU') 
			ORDER BY IRETierQueue.DisplayStringDimKey DESC
		) AS 'Groupe de support',
		
		-- GET RESOLUTION CATEGORY
		(
			SELECT TOP 1 IREResolutionCategory.DisplayName
			FROM DisplayStringDimvw as IREResolutionCategory
			WHERE IR.ResolutionCategory = IREResolutionCategory.ElementName AND (IREResolutionCategory.LanguageCode = 'FRA' OR IREResolutionCategory.LanguageCode = 'ENU') 
			ORDER BY IREResolutionCategory.DisplayStringDimKey DESC
		) AS 'Catégorie de résolution',
		
		-- GET AFFECTED USER
		(
			SELECT TOP 1 IRAffectedUser.DisplayName
			FROM WorkItemAffectedUserFactvw
			LEFT JOIN UserDimvw AS IRAffectedUser ON WorkItemAffectedUserFactvw.WorkItemAffectedUser_UserDimKey = IRAffectedUser.UserDimKey
			WHERE WI.WorkItemDimKey = WorkItemAffectedUserFactvw.WorkItemDimKey
			Order By WorkItemAffectedUserFactvw.CreatedDate DESC
		) AS 'Utilisateur affecté',
		
		-- GET ASSIGNED TO USER
		(
			SELECT TOP 1 IRAssignedUser.DisplayName
			FROM WorkItemAssignedToUserFactvw
			LEFT JOIN UserDimvw AS IRAssignedUser ON WorkItemAssignedToUserFactvw.WorkItemAssignedToUser_UserDimKey = IRAssignedUser.UserDimKey
			WHERE WI.WorkItemDimKey = WorkItemAssignedToUserFactvw.WorkItemDimKey
			Order By WorkItemAssignedToUserFactvw.CreatedDate DESC
		) AS 'Attribué à',
		
		-- GET PRIMARY OWNER
		(
			SELECT TOP 1 IRPrimaryOwner.DisplayName
			FROM IncidentHasPrimaryOwnerFactvw
			LEFT JOIN UserDimvw AS IRPrimaryOwner ON IncidentHasPrimaryOwnerFactvw.IncidentPrimaryOwner_UserDimKey = IRPrimaryOwner.UserDimKey
			LEFT JOIN IncidentDimvw AS IR ON IncidentHasPrimaryOwnerFactvw.IncidentDimKey = IR.IncidentDimKey
			WHERE WI.EntityDimKey = IR.EntityDimKey
			Order By IncidentHasPrimaryOwnerFactvw.CreatedDate DESC
		) AS 'Propriétaire principal',
		
		-- GET CREATED BY
		(
			SELECT TOP 1 IRCreatedBY.DisplayName
			FROM WorkItemCreatedByUserFactvw
			LEFT JOIN UserDimvw AS IRCreatedBY ON WorkItemCreatedByUserFactvw.WorkItemCreatedByUser_UserDimKey = IRCreatedBY.UserDimKey
			WHERE WI.WorkItemDimKey = WorkItemCreatedByUserFactvw.WorkItemDimKey
			Order By WorkItemCreatedByUserFactvw.CreatedDate DESC
		) AS 'Créé par',
		
		IR.Description AS 'Description',
		IR.ResolutionDescription AS 'Description de résolution'


FROM IncidentDimvw AS IR


-- GET WORKITEMS
LEFT JOIN WorkItemDimvw as WI on ir.EntityDimKey = WI.EntityDimKey


ORDER BY IR.CreatedDate DESC

Comments

  • Gerhard_GoossensGerhard_Goossens Customer Advanced IT Monkey ✭✭✭
    edited November 2019

    Here is a view we created for IR and SR

    You cab just remove the selects for the campus, building and office.

    USE [DWDataMart]

    GO

    /****** Object: View [dbo].[WorkitemSummaryvw]  Script Date: 2019/11/07 4:26:19 PM ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE view [dbo].[WorkitemSummaryvw] 

    as

    Select c.ID, 

        c.Title,

      c.Description as[Description],

      c.[CreatedDate],

      c.[CreatedTime],

      c.CreatedDay,

      c.CreatedWeek,

      c.CreatedMonth,

      CreatedYear,

      c.[CompletedDate],   

      c.[CompletedTime],

      c.CompletedDay,

      c.CompletedWeek,

      c.CompletedMonth,

      c.CompletedYear,

      c.LastModified,

      c.Status as [Status],

      c.ParentClassification,

      c.Classification,

      c.[FullClassificationPath],

      c.[ParentSupportGroup],

      c.[SupportGroup],

      c.[AffectedUserDerpartment],

      c.[AffectedUser],

      c.[AssignedUserDepartment],

      c.[AssignedUser],

      c.Source,

      c.Priority as [Priority],

      c.Urgency,

      c.[ImplementationResults],

      c.Campus,

      c.Bullding,

      c.Location

     From (

    Select SR.Id as [ID],

        SR.Title as [Title],

      SR.Description as [Description],

      convert (date,SR.CreatedDate) as [CreatedDate],

      convert(varchar(5),(dateadd (hour,2,sr.CreatedDate)),108) as [CreatedTime],

      DATEPART(DAY,SR.CreatedDate) AS 'CreatedDay',

        DATEPART(MONTH,SR.CreatedDate) AS 'CreatedMonth',

        DATEPART(YEAR,SR.CreatedDate) AS 'CreatedYear',

        DATEPART(WEEK,SR.CreatedDate) AS 'CreatedWeek',

      convert(date, SR.CompletedDate) as[CompletedDate],

      convert(varchar(5),(dateadd (hour,2,sr.CompletedDate)),108) as [CompletedTime],

      DATEPART(DAY,SR.CompletedDate) AS 'CompletedDay',

        DATEPART(MONTH,SR.CompletedDate) AS 'CompletedMonth',

        DATEPART(YEAR,SR.CompletedDate) AS 'CompletedYear',

        DATEPART(WEEK,SR.CompletedDate) AS 'CompletedWeek',

      convert(varchar, SRlastMod.LastModified, 120) as [LastModified],   

      SRStatus.ServiceRequestStatusValue as [Status],

      (Select t.ServiceRequestAreaValue from ServiceRequestAreavw t

       where SRCatergory.ParentId=t.ServiceRequestAreaId) as [ParentClassification],

      class.DisplayName [Classification],

      ((Select SRS.ServiceRequestSupportGroupValue from ServiceRequestSupportGroup SRS

      where SRSupport.ParentId=SRS.ServiceRequestSupportGroupId)

      +'\'+

      (Select t.ServiceRequestAreaValue from ServiceRequestAreavw t

       where SRCatergory.ParentId=t.ServiceRequestAreaId)

    +'\'+ class.DisplayName) as [FullClassificationPath],

      AffectedRep.Department as [AffectedUserDerpartment],

      AffectedRep.FirstName+' '+AffectedRep.LastName as [AffectedUser],

      AssignedRep.Department as[AssignedUserDepartment],

      AssignedRep.FirstName+' '+AssignedRep.LastName as [AssignedUser],   

      (Select SRS.ServiceRequestSupportGroupValue from ServiceRequestSupportGroup SRS

      where SRSupport.ParentId=SRS.ServiceRequestSupportGroupId) [ParentSupportGroup],

      SRSupport.ServiceRequestSupportGroupValue as [SupportGroup],

      SRSource.ServiceRequestSourceValue as [Source],

      SRPriority.ServiceRequestPriorityValue as [Priority],

      SRUrgency.ServiceRequestUrgencyValue as [Urgency],

      SRImplementation.ServiceRequestImplementationResultsValue as[ImplementationResults],

      CampusDS.DisplayName [Campus],

      BuildingDS.DisplayName[Bullding],

      SR.CurrentLocation [Location]   

     from ServiceRequestDim SR with (nolock)

    left outer join WorkItemDim (nolock) WI on SR.EntityDimKey= WI.EntityDimKey and wi.IsDeleted =0

    left outer join EntityDimvw (nolock) SRlastMod on SR.EntityDimKey = SRlastMod.EntityDimKey 

    left outer join WorkItemAffectedUserFactvw (nolock) wiAffected on WI.WorkItemDimKey=wiAffected.WorkItemDimKey AND wiAffected.DeletedDate is null 

    left outer join UserDim(nolock) AffectedRep on wiAffected.WorkItemAffectedUser_UserDimKey= AffectedRep.UserDimKey

    left outer join WorkItemAssignedToUserFactvw(nolock) wiAssigned on WI.WorkItemDimKey=wiAssigned.WorkItemDimKey and wiAssigned.DeletedDate is null

    left outer join UserDim(nolock) AssignedRep on wiAssigned.WorkItemAssignedToUser_UserDimKey= AssignedRep.UserDimKey 

    left outer join ServiceRequestUrgency(nolock) SRUrgency on Sr.Urgency_ServiceRequestUrgencyId=SRUrgency.ServiceRequestUrgencyId

    left outer join ServiceRequestArea (nolock) SRCatergory on Sr.Area_ServiceRequestAreaId= SRCatergory.ServiceRequestAreaId

    left outer join ServiceRequestPriority(nolock) SRPriority on Sr.Priority_ServiceRequestPriorityId=SRPriority.ServiceRequestPriorityId

    left outer join ServiceRequestImplementationResults(nolock) SRImplementation on SR.ImplementationResults_ServiceRequestImplementationResultsId=SRImplementation.ServiceRequestImplementationResultsId

    left outer join ServiceRequestSource(nolock) SRSource on SR.Source_ServiceRequestSourceId=SRSource.ServiceRequestSourceId 

    left outer join ServiceRequestStatus(nolock) SRStatus on SR.Status_ServiceRequestStatusId=SRStatus.ServiceRequestStatusId

    left outer join ServiceRequestSupportGroup(nolock) SRSupport on SR.SupportGroup_ServiceRequestSupportGroupId=SRSupport.ServiceRequestSupportGroupId

    LEFT JOIN DisplayStringDimvw (nolock) CampusDS ON SR.NWUCampusList = CampusDS.BaseManagedEntityId and CampusDS.LanguageCode='ENU' 

    LEFT JOIN DisplayStringDimvw (nolock) BuildingDS ON SR.NWUBuildingList = BuildingDS.BaseManagedEntityId and BuildingDS.LanguageCode='ENU' 

    left join DisplayStringDim (nolock) class on SR.Area = class.ElementName and class.LanguageCode='ENU' 

    where SR.id is not null 

    union all

    select ir.id as [ID],

        ir.Title as [Title],

    ir.Description as [Discription],

        convert (date,IR.CreatedDate) as [CreatedDate],

      convert(varchar(5),(dateadd (hour,2,ir.CreatedDate)) ,108) as [CreatedTime],

      DATEPART(DAY,IR.CreatedDate) AS 'CreatedDay',

        DATEPART(MONTH,IR.CreatedDate) AS 'CreatedMonth',

        DATEPART(YEAR,IR.CreatedDate) AS 'CreatedYear',

        DATEPART(WEEK,IR.CreatedDate) AS 'CreatedWeek',

      convert(date,ir.ResolvedDate) as[CompletedDate],

      convert(varchar(5),(dateadd (hour,2,ir.ResolvedDate)),108) as [CompletedTime],

      DATEPART(DAY,ir.ResolvedDate) AS 'CompletedDay',

        DATEPART(MONTH,ir.ResolvedDate) AS 'CompletedMonth',

        DATEPART(YEAR,ir.ResolvedDate) AS 'CompletedYear',

        DATEPART(WEEK,ir.ResolvedDate) AS 'CompletedWeek',

    convert(varchar, IRlastMod.LastModified, 120) as [LastModified],  

    IRStatus.IncidentStatusValue as [Status],

    (Select t.IncidentClassificationValue from IncidentClassification t

        where IRCatergory.ParentId=t.IncidentClassificationId) as [ParentClassification],

       class.DisplayName as [Classification],

    ((Select IRS.IncidentTierQueuesValue from IncidentTierQueues IRS

      where IRSupport.ParentId=IRS.IncidentTierQueuesId)

      +'\'+

      (Select t.IncidentClassificationValue from IncidentClassification t

       where IRCatergory.ParentId=t.IncidentClassificationId)

    +'\'+ class.DisplayName) as [FullClassificationPath],

    AffectedRep.Department as [AffectedUserDerpartment],

      AffectedRep.DisplayName as [AffectedUser],

      AssignedRep.Department as[AssignedUserDepartment],

      AssignedRep.DisplayName as [AssignedUser],

      (Select IRS.IncidentTierQueuesValue from IncidentTierQueues IRS

      where IRSupport.ParentId=IRS.IncidentTierQueuesId) [ParentSupportGroup],

      IRSupport.IncidentTierQueuesValue as [SupportGroup],   

      IRSource.IncidentSourceValue as [Source],

      IRImpact.IncidentImpactValue as [Impact],

      IRUrgency.IncidentUrgencyValue as [Urgency],

      IRResolutionCategory.IncidentResolutionCategoryValue as [ResolutionCategory],

      CampusDS.DisplayName [Campus],

      BuildingDS.DisplayName[Bullding],

      IR.CurrentLocation [Location]

    from IncidentDim IR with (nolock)

    left outer join WorkItemDim WI on IR.EntityDimKey= WI.EntityDimKey and wi.IsDeleted =0

    left outer join EntityDimvw IRlastMod on IR.EntityDimKey = IRlastMod.EntityDimKey 

    left outer join WorkItemAffectedUserFactvw wiAffected on WI.WorkItemDimKey=wiAffected.WorkItemDimKey AND wiAffected.DeletedDate is null 

    left outer join UserDim AffectedRep on wiAffected.WorkItemAffectedUser_UserDimKey= AffectedRep.UserDimKey

    left outer join WorkItemAssignedToUserFactvw wiAssigned on WI.WorkItemDimKey=wiAssigned.WorkItemDimKey and wiAssigned.DeletedDate is null

    left outer join UserDim AssignedRep on wiAssigned.WorkItemAssignedToUser_UserDimKey= AssignedRep.UserDimKey 

    left outer join IncidentClassificationvw IRCatergory on IR.Classification_IncidentClassificationId= IRCatergory.IncidentClassificationId

    left outer join IncidentUrgencyvw IRUrgency on IR.Urgency_IncidentUrgencyId=IRUrgency.IncidentUrgencyId

    left outer join IncidentImpact IRImpact on IR.Impact_IncidentImpactId=IRImpact.IncidentImpactId

    left outer join IncidentResolutionCategory IRResolutionCategory on IR.ResolutionCategory_IncidentResolutionCategoryId=IRResolutionCategory.IncidentResolutionCategoryId

    left outer join IncidentSource IRSource on IR.Source_IncidentSourceId=IRSource.IncidentSourceId 

    left outer join IncidentStatus IRStatus on IR.Status_IncidentStatusId=IRStatus.IncidentStatusId

    left outer join IncidentTierQueues IRSupport on IR.TierQueue_IncidentTierQueuesId=IRSupport.IncidentTierQueuesId

    left join DisplayStringDimvw class on IR.Classification = class.ElementName and class.LanguageCode='ENU'

    LEFT JOIN DisplayStringDimvw (nolock) CampusDS ON IR.NWUCampusList = CampusDS.BaseManagedEntityId and CampusDS.LanguageCode='ENU' 

    LEFT JOIN DisplayStringDimvw (nolock) BuildingDS ON IR.NWUBuildingList = BuildingDS.BaseManagedEntityId and BuildingDS.LanguageCode='ENU'  

    where iR.id is not null 

    )C 

    GO

  • Bruno_SerafimBruno_Serafim Customer IT Monkey ✭

    Thank you 💪

Sign In or Register to comment.