SCSM Reports creation. SCSM warehouse database. SCSM incident details reports

       Working in an environment where Microsoft system center Service manager (SCSM) is used. Need to work with the management to work on various reports .SCSM product is used across the whole environment for

·         Incident creation.

·         Problem management

·         Change management

·         Service Request.

So ticket are raised via email, portal and web.

So need to work as which tables are to be targeted and the relation with other tables to get related incident information.

So to start with Incident. Below are the tables and views can be used for. Microsoft provide lots of table and views and store procedure to get details information’s.

select

      I.Id,i.Title ,i.Description,i.DisplayName ,i.FirstAssignedDate,          i.CreatedDate,i.ClosedDate,i.EntityDimKey,i.Escalated,i.FirstResponseDate,

i.DisplayName,i.Priority ,i.Source , 

i.Status,i.TierQueue, i.TierQueue_IncidentTierQueuesId,i.Impact, 

i.ResolutionCategory,i.ResolvedDate,i.ClosedDate

 from IncidentDim I

 where i.CreatedDate >'2015-12-04'

 order by i.CreatedDate desc

The above query will be give all related information for all the incident came after 4th dec 2015. though this table has lot of many more columns with many more details which you can always check for your self. I have used most used once. 

Ok let me also mention to people who are relatively new to SCSM. About SCSM architecture. When you install SCSM application from the front end portal many activities can be done. 

Like Incident creation or Services request creation and these activities will not impact SCSM database warehouse directly. Initially all these information will got to ServiceManager database and this database can be hosted in a different instance. But writing query directly to this database is not always advisable depends on your requirement. 

Like if want to generate a query which will give a run time view of incident flow and its priorities.
For that below query can help.

SELECT I.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C AS [ID], I.DisplayName AS [Name], L.LTValue AS [Support Group] ,

i.CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688 as create_date ,e.enumtypename,

i.Priority_B930B964_A1C4_0B5A_B2D1_BFBE9ECDC794 into  new_incident

FROM MT_System$WorkItem$Incident I (nolock)

INNER JOIN EnumType E (nolock) ON I.TierQueue_1E9615C2_3386_2452_BA83_05B2169DF38C = E.EnumTypeId

INNER JOIN LocalizedText L (nolock) ON L.ElementName = E.EnumTypeName

where i.CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688 >='2015-12-25'

and l.LTValue like '%_SSCIT_%'

and I .CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688 > dateadd(MI ,-80,CURRENT_TIMESTAMP)

order by i.CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688 desc

This above query can be run on the ServiceManager database and it will give number of incident came in last 80 minutes.

But from the above query you can understand that it not very user friendly to work with. The data getting generated in ServiceManager database can be also called as SCSM internal database. Are moved to SCSM datawarehouse databases.  The warehouse database is called as DataMart.

Ok let me give you an approach which I followed when I first worked into it. Put a filter on table tab with incident and it will which give you all tables name related to incident same approach can be used in view. 

Now, let me give you a case study and lets move one step ahead now for the above query you got all basic information related to incident. Now your manager ask to get a report on how much time the incident was kept in pending status and what are the reasons for the incident to be kept in pending status.

Below is the query which will give you all related informations.

select i.id,i.CreatedDate ,i.displayname,i.Description ,i.firstAssignedDate,i.ResolvedDate ,i.Priority

      ,DATEDIFF([hour],i.CreatedDate, i.FirstAssignedDate) as TtRespond

      ,DATEDIFF([hour],i.CreatedDate, i.ResolvedDate) as TtResolve,

                  ActiveDuration = SUM(

                              CASE

                                    WHEN statusEnum.ID = 'IncidentStatusEnum.Active'

                                    THEN dFact.TotalTimeMeasure

                                    ELSE 0

                              END  

                              ),

                  PendingDuration = SUM(

                              CASE

                                    WHEN statusEnum.ID = 'IncidentStatusEnum.Active.Pending'

                                    THEN dFact.TotalTimeMeasure

                                    ELSE 0

                              END  

                              ),

                  PendingThirdParty=SUM(

                              CASE

                                    when statusenum.IncidentStatusId='12'

                                    then dfact.TotalTimeMeasure

                                    ELSE 0

                              END

                              ),

                  AssignedStatus=SUM(

                              CASE

                                    when statusenum.IncidentStatusId='10'

                                    then dfact.TotalTimeMeasure

                                    ELSE 0

                              END

                              ),

                  InProgress=SUM(

                              CASE

                                    when statusenum.IncidentStatusId='7'

                                    then dfact.TotalTimeMeasure

                                    ELSE 0

                              END

                              ),

                  Resolution_Verification=SUM(

                              CASE

                                    when statusenum.IncidentStatusId='9'

                                    then dfact.TotalTimeMeasure

                                    ELSE 0

                              END

                              ),

                  PendingChangeRequestImplemnetation=SUM(

                              CASE

                                    when statusenum.IncidentStatusId='8'

                                    then dfact.TotalTimeMeasure

                                    ELSE 0

                              END

                              ),

                  dFact.IncidentDimKey into pending_sla

            from

            dbo.IncidentStatusDurationFactvw dFact,

            dbo.IncidentStatusvw statusEnum,

            dbo.IncidentDimvw i

           

            where statusEnum.IncidentStatusId = dFact.IncidentStatusId

            and dfact.incidentdimkey =i.incidentdimkey

            and i.CreatedDate >='2015-12-01'

            Group by i.Priority,dfact.IncidentDimKey,i.id,i.CreatedDate ,i.displayname,i.Description ,

            i.firstAssignedDate,i.ResolvedDate--,ttrespond,ttresolve   

            order by i.CreatedDate desc

As Now you have the query don’t just copy paste it try to understand the login on this. Please notice where in the above query views are used.  Please remember View ending with VW,  these views are IncidentDimVW, IncidentstatusVW, IncidentStatusDurationFactVW. As already earlier informed that we are writing the queries into data warehouse database and hope that you have some idea on datawarehousing as from the view name it can be understood that incidentDimVW is a Dimensional and IncidentstatusDurationFactVW refers to a fact table IncidentstatusDurationFact.

      

Ok hopefully you are still here now lets move one more step ahead and the below query will use multiple table to get most of the information for incident.

SELECT DISTINCT

     incident.Id

            ,incident.Title

            ,incident.Description

            ,incident.ResolutionDescription

            ,stringsSupportGroup.DisplayName as SupportGroup         

            ,incident.CreatedDate

            ,MONTH(incident.CreatedDate) as CreatedDateMonth       

            ,YEAR(incident.CreatedDate) as CreatedDateYear

,DATEPART(q,incident.CreatedDate) as CreatedDateQuarter                   

,incident.FirstAssignedDate

,assignedUser.DisplayName as AssignedToUserName

,incident.ResolvedDate         

            ,DATEDIFF([hour],incident.CreatedDate, incident.FirstAssignedDate) as TtRespond

            ,DATEDIFF([hour],incident.CreatedDate, incident.ResolvedDate) as TtResolve

            ,resolvedByUser.DisplayName as ResolvedByUserName

            ,stringsStatus.DisplayName as [Status]

           ,stringsSource.DisplayName as [Source]  

            ,incident.Priority                    

            ,classi.IncidentClassificationValue as Classification

           ,incident.Escalated     

           ,affectedUser.DisplayName as AffectedUserName

            ,affectedUser.Country as AffectedUserCountry

            ,affectedUser.City as AffectedUserCity

            ,affectedUser.Department as AffectedUserDepartment

            ,affectedUser.Company as AffectedUserCompany

    ,stringsResoltuionCategory.DisplayName as ResolutionCategory                      

    --,tw.TimeWorked  

   into tabl1      

FROM IncidentDim incident

LEFT JOIN WorkItemDim workitem on incident.EntityDimKey = workItem.EntityDimKey

LEFT JOIN IncidentTierQueues as tq ON incident.TierQueue_IncidentTierQueuesId = tq.IncidentTierQueuesId

LEFT JOIN DisplayStringDimvw as stringsSupportGroup ON tq.EnumTypeId=stringsSupportGroup.BaseManagedEntityId and stringsSupportGroup.LanguageCode = 'ENU'

LEFT JOIN IncidentStatusvw stat on incident.Status = stat.ID

LEFT JOIN DisplayStringDimvw as stringsStatus ON stat.EnumTypeId = stringsStatus.BaseManagedEntityId and stringsStatus.LanguageCode = 'ENU'

LEFT JOIN IncidentClassification as classi ON incident.Classification_IncidentClassificationId = classi.IncidentClassificationId

LEFT JOIN IncidentSourcevw source on incident.Source = source.ID

LEFT JOIN DisplayStringDimvw as stringsSource ON source.EnumTypeId = stringsSource.BaseManagedEntityId and stringsSource.LanguageCode = 'ENU'

LEFT JOIN WorkItemAffectedUserFactvw as affectUserFact ON affectUserFact.WorkItemDimKey = workitem.WorkItemDimKey --and affectUserFact.DeletedDate is null

AND affectUserFact.CreatedDate = (SELECT MAX(CreatedDate) FROM WorkItemAffectedUserFactvw as tmp WHERE tmp.WorkItemDimKey=workitem.WorkItemDimKey)

LEFT JOIN UserDimvw as affectedUser ON affectUserFact.WorkItemAffectedUser_UserDimKey = affectedUser.UserDimKey

LEFT JOIN WorkItemAssignedToUserFactvw as assignedUserFact ON assignedUserFact.WorkItemDimKey = workitem.WorkItemDimKey

AND assignedUserFact.CreatedDate = (SELECT MAX(CreatedDate) FROM dbo.WorkItemAssignedToUserFactvw as tmp WHERE tmp.WorkItemDimKey=workitem.WorkItemDimKey)

LEFT JOIN UserDimvw as assignedUser ON assignedUserFact.WorkItemAssignedToUser_UserDimKey = assignedUser.UserDimKey

LEFT JOIN IncidentResolutionCategoryvw as resultionCat ON resultionCat.ID = incident.ResolutionCategory

LEFT JOIN DisplayStringDimvw as stringsResoltuionCategory ON resultionCat.EnumTypeId = stringsResoltuionCategory.BaseManagedEntityId and stringsResoltuionCategory.LanguageCode = 'ENU'

LEFT JOIN IncidentResolvedByUserFactvw as resolvedByUserFact ON incident.IncidentDimKey = resolvedByUserFact.IncidentDimKey

AND resolvedByUserFact.CreatedDate = (SELECT MAX(CreatedDate) FROM IncidentResolvedByUserFactvw as tmp WHERE tmp.IncidentDimKey=incident.IncidentDimKey)

LEFT JOIN UserDimvw as resolvedByUser ON resolvedByUserFact.TroubleTicketResolvedByUser_UserDimKey = resolvedByUser.UserDimKey

Best of luck hope above query are helpfull.


http://sqlsignature.blog