This third part in this series will show how the “queries” are defined in SCSM by using stored procedures.
After this part you should be able to “translate” or adjust already existing stored procedures or at least “read” and understand how a stored procedure is set up in SCSM!.
I know this part will look like real hard core but again this part is only to understand a stored procedure!
YOU Don’t have to fully write them from scratch!
End goal
Remember our end goal is delivering a need report to present to the managers crying for reports!
The list of the end goal report:
- What work items are open?
- Date/time when the work items where logged?
- Duration of the work items being in one state?
- Which people are responsible?
- When was the last time the work item was modified?
- Who did the last modification?
With part 1 “ Basic Queries” we are able to deliver 2 out of the 6 fields to present in our report.
- What work items are open?
- Date/time when the work items where logged
Part 2 “Getting those Relationships!” delivers you all related objects to and incident like:
Which people are responsible?
Part 3 will show you how to write a stored procedure to deliver the data for the report.
Stored Procedure
In SCSM reporting stored procedures are used to deliver data for reports. The reason for following this procedure is because the queries are performance wise a pain because of the many joins.
So delivering the query in a Stored Procedure (SP) will be far more efficient.
Explaining the Stored procedure and how it is been setup in SCSM.
Translate the SP “Servicemanager_Report_IncidentManagement_SP_GetListOfIncidents”
For the example I am going to use the default SP for a list of incidents which already has allot of information in them on the open incidents.
First you need to edit the already existing SP simply right click the SP and select modify to show the full SP:
We are going to edit the SP.
Below is the standard stored procedure
Servicemanager_Report_IncidentManagement_SP_GetListOfIncidents
I have added extra comments to clarify the SP, for explanation on how the SP is setup.
This is the standard SP which exists in SCSM DWDatamart.
USE [DWDataMart]
GO
/****** Object: StoredProcedure [dbo].[ServiceManager_Report_IncidentManagement_SP_GetListOfIncidents] Script Date: 7/31/2013 8:28:54 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ServiceManager_Report_IncidentManagement_SP_GetListOfIncidents]
/***** BEGIN Section Defining Parameters *****/
@DateFilter nvarchar(256) = null,
@StartDate datetime,
@EndDate datetime,
@Source nvarchar(max) = ‘-1’,
@Status nvarchar(max) = ‘-1’,
@Priority nvarchar(max) = ‘-1’,
@Urgency nvarchar(max) = ‘-1’,
@Impact nvarchar(max) = ‘-1’,
@Classification nvarchar(max) = ‘-1’,
@SupportGroup nvarchar(max) = ‘-1’,
@ResolutionCategory nvarchar(max) = ‘-1’,
@AssignedTo int = null,
@ContactMethod nvarchar(max) = null,
@Description nvarchar(max) = null,
@ResolutionDescription nvarchar(max) = null,
@RelatedCIs nvarchar(max) = null,
@ID nvarchar(max) = null,
@IncludeDeleted bit = 0,
@LanguageCode nvarchar(max)= ‘ENU’
/***** END Section Defining Parameters *****/
AS
BEGIN
SET NOCOUNT ON
DECLARE @Error int
DECLARE @ExecError int
SET @EndDate = DateAdd(Day, 1, @EndDate)
/***** BEGIN Section Creating Temp Tables *****/
DECLARE @tableID TABLE(value nvarchar(256))
INSERT @tableID (value)
Select * FROM dbo.fn_CSVToTableString(ISNULL(@ID, ”))
DECLARE @tableSource TABLE (value nvarchar(256))
INSERT @tableSource (value)
SELECT * FROM dbo.fn_CSVToTableInt(@Source)
DECLARE @tableStatus TABLE (value nvarchar(256))
INSERT @tableStatus (value)
SELECT * FROM dbo.fn_CSVToTableInt(@Status)
DECLARE @tablePriority TABLE (value nvarchar(256))
INSERT @tablePriority (value)
SELECT * FROM dbo.fn_CSVToTableInt(@Priority)
DECLARE @tableUrgency TABLE (value nvarchar(256))
INSERT @tableUrgency (value)
SELECT * FROM dbo.fn_CSVToTableInt(@Urgency)
DECLARE @tableImpact TABLE (value nvarchar(256))
INSERT @tableImpact (value)
SELECT * FROM dbo.fn_CSVToTableInt(@Impact)
DECLARE @tableClassification TABLE (value nvarchar(256))
INSERT @tableClassification (value)
SELECT * FROM dbo.fn_CSVToTableInt(@Classification)
DECLARE @tableSupportGroup TABLE (value nvarchar(256))
INSERT @tableSupportGroup (value)
SELECT * FROM dbo.fn_CSVToTableInt(@SupportGroup)
DECLARE @tableResCategory TABLE (value nvarchar(256))
INSERT @tableResCategory (value)
SELECT * FROM dbo.fn_CSVToTableInt(@ResolutionCategory)
DECLARE @tableRelatedCIs TABLE (value nvarchar(256))
INSERT @tableRelatedCIs (value)
SELECT * FROM dbo.fn_CSVToTableInt(@RelatedCIs)
/***** END Section Creating Temp Tables *****/
/**** BEGIN Actual SQL Query *****/
/***** BEGIN Select Statement *****/
SELECT DISTINCT
I.IncidentDimKey,
I.CreatedDate,
Description=NULL,
I.ResolvedDate,
I.Priority,
I.Id,
I.Title,
Source = ISNULL(SourceDS.DisplayName, SourceEnum.IncidentSourceValue) ,
SourceEnum.IncidentSourceId AS SourceId,
Status = ISNULL(StatusDS.DisplayName, StatusEnum.IncidentStatusValue) ,
StatusEnum.IncidentStatusId AS StatusId,
Impact = ISNULL(ImpactDS.DisplayName, ImpactEnum.IncidentImpactValue),
ImpactEnum.IncidentImpactId AS ImpactId,
AssignedTo.UserDimKey AssignedToUserId,
AssignedTo.DisplayName AssignedToUserName
/***** END Select Statement *****/
FROM
/***** Incident View ******/
dbo.IncidentDimvw I
/***** Incident View ******/
/***** JOIN INCIDENTDIMVW ON WORKITEMDIMVW in order to retrieve WorkItem relations
– WorkItem Assigned to User
– About Config Item
*****/
INNER JOIN dbo.WorkItemDimvw WI
ON I.EntityDimKey = WI.EntityDimKey
/***** JOIN INCIDENTDIMVW ON WORKITEMDIMVW ******/
/***** RETRIEVE ASSIGNED TO USER *****/
/***** join WorkItemDimvw on WorkItemAssignedUserFactVw to Retrieve Assigned User Dimkey *****/
LEFT OUTER JOIN
dbo.WorkItemAssignedToUserFactvw
ON dbo.WorkItemAssignedToUserFactvw.WorkItemDimKey = WI.WorkItemDimKey
AND (@IncludeDeleted = 1 OR dbo.WorkItemAssignedToUserFactvw.DeletedDate IS NULL)
/***** Join WorkItemAssignedToUserFactvw on UserDimvw to retrieve Assigned to User DisplayName *****/
LEFT OUTER JOIN
dbo.UserDimvw AS AssignedTo
ON dbo.WorkItemAssignedToUserFactvw.WorkItemAssignedToUser_UserDimKey = AssignedTo.UserDimKey
/***** RETRIEVE ASSIGNED TO USER *****/
/***** RETRIEVE ABOUT CONFIG ITEMS *****/
LEFT OUTER JOIN
dbo.WorkItemAboutConfigItemFactvw ON
dbo.WorkItemAboutConfigItemFactvw.WorkItemDimKey = WI.WorkItemDimKey
AND (@IncludeDeleted = 1 OR dbo.WorkItemAboutConfigItemFactvw.DeletedDate IS NULL)
LEFT OUTER JOIN
dbo.WorkItemAboutConfigItemFactvw CIFctForFilter ON
CIFctForFilter.WorkItemDimKey = WI.WorkItemDimKey
AND (@IncludeDeleted = 1 OR CIFctForFilter.DeletedDate IS NULL)
/***** RETRIEVE ABOUT CONFIG ITEMS *****/
/***** RETRIEVE INCIDENT SOURCE *****/
LEFT OUTER JOIN
dbo.IncidentSourcevw AS SourceEnum
ON SourceEnum.IncidentSourceId = I.Source_IncidentSourceId
/**** join on displaystringvw to get the displayname of the source enum *****/
LEFT OUTER JOIN
dbo.DisplayStringDimvw SourceDS
ON SourceEnum.EnumTypeId=SourceDS.BaseManagedEntityId
AND SourceDS.LanguageCode = @LanguageCode
/***** RETRIEVE INCIDENT SOURCE *****/
/***** RETRIEVE INCIDENT STATUS *****/
LEFT OUTER JOIN
dbo.IncidentStatusvw AS StatusEnum
ON StatusEnum.IncidentStatusId = I.Status_IncidentStatusId
/**** join on displaystringvw to get the displayname of the Status enum *****/
LEFT OUTER JOIN
dbo.DisplayStringDimvw StatusDS
ON StatusEnum.EnumTypeId=StatusDS.BaseManagedEntityId
AND StatusDS.LanguageCode = @LanguageCode
/***** RETRIEVE INCIDENT STATUS *****/
/*****RETRIEVE INCIDENT IMPACT *****/
LEFT OUTER JOIN
dbo.IncidentImpactvw AS ImpactEnum
ON ImpactEnum.IncidentImpactId = I.Impact_IncidentImpactId
/**** join on displaystringvw to get the displayname of the Impact enum *****/
LEFT OUTER JOIN
dbo.DisplayStringDimvw ImpactDS
ON ImpactEnum.EnumTypeId=ImpactDS.BaseManagedEntityId
AND ImpactDS.LanguageCode = @LanguageCode
/*****RETRIEVE INCIDENT IMPACT *****/
WHERE
/**** FILTERS FOR THE SQL QUERY all @….. values are the parameters of the report *****/
(
(@DateFilter = ‘ResolvedOn’ AND ((I.ResolvedDate >= @StartDate) AND (I.ResolvedDate < @EndDate))) OR
(@DateFilter = ‘ClosedOn’ AND ((I.ClosedDate >= @StartDate) AND (I.ClosedDate < @EndDate))) OR
(@DateFilter = ‘CreatedOn’ AND ((I.CreatedDate >= @StartDate) AND (I.CreatedDate < @EndDate))) OR
(@DateFilter = ‘All’)
) AND
(@StartDate <= @EndDate) AND
((-1 IN (Select value from @tableSource)) OR (I.Source_IncidentSourceId IN (Select value from @tableSource))) AND
((-1 IN (Select value from @tableStatus)) OR (I.Status_IncidentStatusId IN (Select value from @tableStatus))) AND
((-1 IN (Select value from @tableImpact)) OR (I.Impact_IncidentImpactId IN (Select value from @tableImpact))) AND
((-1 IN (Select value from @tableUrgency)) OR (I.Urgency_IncidentUrgencyId IN (Select value from @tableUrgency))) AND
((-1 IN (Select value from @tableClassification)) OR (I.Classification_IncidentClassificationId IN (Select value from @tableClassification))) AND
((-1 IN (Select value from @tableSupportGroup)) OR (I.TierQueue_IncidentTierQueuesId IN (Select value from @tableSupportGroup))) AND
((-1 IN (Select value from @tablePriority)) OR (I.Priority IN (Select value from @tablePriority))) AND
(
(-1 in (Select value from @tableResCategory))
OR
(
I.ResolutionCategory_IncidentResolutionCategoryId IN (Select value from @tableResCategory)
OR
(
(I.ResolutionCategory_IncidentResolutionCategoryId IS NULL)
AND
(” IN (Select value from @tableResCategory))
)
)
) AND
((@AssignedTo = 0) OR AssignedTo.UserDimKey = @AssignedTo) AND
((@ID IS NULL) OR (I.Id IN (Select value from @tableID)))
AND ((0 IN (select value from @tableRelatedCIs)) OR (CIFctForFilter.WorkItemAboutConfigItem_ConfigItemDimKey in (select value from @tableRelatedCIs)))
/**** END Actual SQL Query *****/
SET @Error = @@ERROR
QuitError:
RETURN @Error
END
Just a little background on the comments:
/***** BEGIN Section Defining Parameters *****/
This section defines the parameters in the report:
/**** BEGIN Actual SQL Query *****/
This section defines the SQL query to retrieve the data from the database.
/**** FILTERS FOR THE SQL QUERY all @….. values are the parameters of the report *****/
This section covers the “Filters” of the SQL query.
which is described in the next section.
Filters
Up until now I have shown how to query for data from the Data Warehouse. The query just pulled ALL data from the database. Although this works you would normally just get a subset of the data like data from a particular time frame or only data with a specific priority etc. in other words filter the data.
The filters are defined after the WHERE clause let’s take a simple query to explain:
Select I.Id, I.Title, I.CreatedDate, StatusEnum.IncidentStatusValue
From IncidentDimvw I
LEFT OUTER JOIN
dbo.IncidentStatusvw AS StatusEnum
ON StatusEnum.IncidentStatusId = I.Status_IncidentStatusId
LEFT OUTER JOIN
dbo.DisplayStringDimvw StatusDS
ON StatusEnum.EnumTypeId=StatusDS.BaseManagedEntityId
This will retrieve ALL data from the IncidentDimvw.
Now let’s say we ONLY want the incidents with the status Closed?
We need to add the following line:
Where StatusEnum.IncidentStatusValue = ‘Active’
This will retrieve all Incidents from IncidentDimvw WHERE status is Active!
You can use this same approach to get any filtered results you want.
Another option is to get only data after a specifc create date:
Where StatusEnum.IncidentStatusValue = ‘Active’
AND I.CreatedDate > ‘2013-07-25’
This means you can filter the results any way you want.
Going the Extra Mile on filters / Parameters and Temp Tables (OPTIONAL!)
Now in the default query we are also filtering the results but the results are filtered based on the parameters given when you run the report. (These are all parameters with @ in front of them).
So basically you would say :
where status = @IncidentStatus.
Now there is a little catch here. In the stored procedures provided by SCSM the results of the parameters are stored in temp tables (which are tables used temporarily when you run the report they are store in memory on the SQL server when you run the report).
You don’t need to know the details but let’s quickly look at how the temp tables are defined:
DECLARE @tableSource TABLE (value nvarchar(256))
INSERT @tableSource (value)
SELECT * FROM dbo.fn_CSVToTableInt(@Source)
This tells SQL to create a temp table @TableSource and place the results from the selected parameter(s) @Source into this table.
Now if you look at the filter in place when you want to filter the results on Source:
((-1 IN (Select value from @tableSource)) OR (I.Source_IncidentSourceId IN (Select value from @tableSource)))
Which the first statement is when you select nothing and leave the parameters default. (before the OR)
The second statement is used when you select one or more Sources in the @Source parameter.
I know this all looks really complex but in reality it isn’t just try to read these. This is only required if you want to create a report with extra parameters instead of the only provided ones.
Since we will edit the already installed stored procedure this isn’t required so you are safe for now
recap
Now you understand how to read the stored procedures presented in SCSM DWDatamart.
For our requirement as stated in the beginning of this series you need to be able to “read” the SP no need to fully write them!
The next part 4 of this series I will explain how to get the other required properties and how to combine them in the SP!
Cheers,
Oskar Landman
