Database Reference
In-Depth Information
There's more...
How about if you want to create your own custom reports for valuable data introduced by
the new Service Level Management feature in Service Manager 2012?
Creating Service Level reports
Service Manager 2012 introduced Service Level Management that allows you to monitor
key metrics, such as Incident Response Time and Service Request Completion Time. Un-
fortunately, there are no out-of-the-box reports available to report on Service Level
breaches.
The instructions earlier in this recipe should have given you a basic understanding of how
to use tables and charts to display Service Manager Data in reports. You can use the follow-
ing query as an example to create a custom Service Level report:
SELECT
m.DisplayName AS Metric,
CONVERT(varchar(7), StartDate, 126) AS StartDate,
SUM(CONVERT(tinyint, s.IsBreached)) AS Breached,
COUNT(*) - SUM(CONVERT(tinyint, s.IsBreached)) AS
NotBreached,
COUNT(*) AS TotalFROM
SLAInstanceInformationFactvw s
INNER JOIN SLAConfigurationDimvw c ON
s.SLAConfigurationDimKey = c.SLAConfigurationDimKey
INNER JOIN SLAConfigurationHasMetricFactvw cm ON
c.SLAConfigurationDimKey = cm.SLAConfigurationDimKey
INNER JOIN SLAMetricDimvw m ON
cm.ConfigurationRefersToMetric_SLAMetricDimKey =
m.SLAMetricDimKey
WHERE
s.IsCancelled = 0
AND c.IsDeleted = 0
AND m.IsDeleted = 0
GROUP BY
m.DisplayName,
CONVERT(varchar(7), StartDate, 126)
