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.
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
