IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'ServiceManager_Report_ServiceManagement_SP_GetOMGroupGroupData')
BEGIN
DROP PROCEDURE dbo.ServiceManager_Report_ServiceManagement_SP_GetOMGroupGroupData
END
GO
CREATE PROCEDURE [dbo].[ServiceManager_Report_ServiceManagement_SP_GetOMGroupGroupData]
@StartDate datetime = NULL,
@EndDate datetime = NULL,
@LanguageCode nvarchar(max)= 'ENU',
@GroupByForReport nvarchar(max) = NULL,
@TimeAggregation nvarchar(max) = NULL,
@ServiceDimKeys nvarchar(max)= NULL,
@GroupDimKeys nvarchar(max)= NULL,
@CollectionDimKeys nvarchar(max)= NULL,
@CIDimKeys nvarchar(max) = NULL
AS
BEGIN
SET NOCOUNT ON
IF(@StartDate) is NULL SET @StartDate = '1900/01/01'
IF(@EndDate) is NULL SET @EndDate = '2100/01/01'
SET @EndDate = DATEADD(DAY, 1, @EndDate)
DECLARE @StartDateKey int
DECLARE @EndDateKey int
SET @StartDateKey = CONVERT(nvarchar(8), @StartDate, 112)
SET @EndDateKey = CONVERT(nvarchar(8), @EndDate, 112)
DECLARE @configItemDimKeys AS TABLE(ConfigItemEntityDimKey int, GroupByName nvarchar(max), DeletedDate datetime)
IF ( @ServiceDimKeys = '' AND @GroupDimKeys = '' AND @CollectionDimKeys = '' AND @GroupByForReport = 'AggregatedTime' )
BEGIN
INSERT INTO @configItemDimKeys
SELECT DISTINCT entityDim.EntityDimKey, configItem.DisplayName, '9999/01/01' AS DeletedDate
FROM dbo.EntityDimvw entityDim
INNER JOIN ConfigItemDimvw configItem on entityDim.BaseManagedEntityId = configItem.BaseManagedEntityId
WHERE configItem.IsDeleted = 0
END
ELSE
BEGIN
INSERT INTO @configItemDimKeys
SELECT DISTINCT dimGroupCfg.EntityDimKey, grp.GroupByName, '9999/01/01' AS DeletedDate
FROM dbo.ConfigItemDimvw dimGroupCfg
INNER JOIN @groupByComputers grp ON
dimGroupCfg.EntityDimKey = grp.GroupByEntityDimKey
UNION
SELECT DISTINCT entityDim.EntityDimKey, grp.GroupByName, ISNULL(grp.DeletedDate, '9999/01/01')
FROM dbo.ConfigItemDimvw dimGroupCfg
INNER JOIN @groupByComputers grp ON dimGroupCfg.BaseManagedEntityId = grp.ConfigItemBME
INNER JOIN dbo.EntityDimvw entityDim ON entityDim.BaseManagedEntityId = dimGroupCfg.BaseManagedEntityId
WHERE grp.CreatedDate <= @EndDate
AND ISNULL(grp.DeletedDate, @StartDate) >= @StartDate
END
IF @GroupByForReport != 'AggregatedTime'
BEGIN
DECLARE @summary AS TABLE(ReportSeries NVARCHAR(256),
Alerts int,
AlertsToIncidentsRatio FLOAT)
INSERT INTO @summary
SELECT targetEntities.GroupByName AS ReportSeries,
COUNT(DISTINCT AlertInstanceDimKey) AS Alerts,
null AS AlertsToIncidentsRatio
FROM dbo.AlertInstanceFactvw factAlerts
INNER JOIN dbo.AlertDimvw dimAlert on factAlerts.AlertDimKey = dimAlert.AlertDimKey
INNER JOIN @configItemDimKeys targetEntities ON dimAlert.TargetEntityDimKey = targetEntities.ConfigItemEntityDimKey AND targetEntities.DeletedDate >= factAlerts.TimeRaised
WHERE factAlerts.TimeRaised >= @StartDate AND factAlerts.TimeRaised < @EndDate
GROUP BY targetEntities.GroupByName
INSERT INTO @summary
SELECT targetEntities.GroupByName AS ReportSeries,
null as Alerts,
SUM(CASE WHEN dimInc.AlertId IS NULL THEN 0 ELSE 1 END) * 100.0/COUNT(DISTINCT AlertInstanceDimKey) AS AlertsToIncidentsRatio
FROM dbo.AlertInstanceFactvw factAlerts
INNER JOIN dbo.AlertDimvw dimAlert ON factAlerts.AlertDimKey = dimAlert.AlertDimKey
INNER JOIN @configItemDimKeys targetEntities ON dimAlert.TargetEntityDimKey = targetEntities.ConfigItemEntityDimKey AND targetEntities.DeletedDate >= factAlerts.TimeRaised
LEFT JOIN dbo.IncidentDimvw dimInc ON dimInc.AlertId = CONVERT(nvarchar(256),dimAlert.AlertProblemGuid)
WHERE factAlerts.TimeRaised >= @StartDate AND factAlerts.TimeRaised < @EndDate
GROUP BY targetEntities.GroupByName
SELECT
ReportSeries,
null As Incidents,
null As IncidentsResolvedWithinTarget,
null As AvgTimeToResolution,
SUM(Alerts),
SUM(AlertsToIncidentsRatio),
null As PhysicalComputers,
null As VirtualComputers,
null As SoftwareUpdateCompliance,
null As SecurityBulletinCompliance,
null As DCMIncidents,
null As TotalConsumption,
null As CostOfPower,
null As UnusedConsumption
from @summary
group by ReportSeries
END
ELSE IF @TimeAggregation IS NOT NULL
BEGIN
DECLARE @datesummary AS TABLE(ReportSeries DateTime,
Alerts int,
AlertsToIncidentsRatio INT)
INSERT INTO @summary
SELECT ReportSeries =
CASE
WHEN ( @TimeAggregation = 'Daily')
THEN selectdatedim.DailyAggregate
WHEN (@TimeAggregation = 'Weekly')
THEN selectdatedim.WeeklyAggregate
WHEN (@TimeAggregation = 'Monthly')
THEN selectdatedim.MonthlyAggregate
WHEN (@TimeAggregation = 'Quarterly')
THEN selectdatedim.QuarterlyAggregate
WHEN (@TimeAggregation = 'Yearly')
THEN selectdatedim.YearlyAggregate
ELSE 0
END,
COUNT(DISTINCT AlertInstanceDimKey) AS Alerts,
null AS AlertsToIncidentsRatio
FROM dbo.AlertInstanceFactvw factAlerts
INNER JOIN dbo.AlertDimvw dimAlert on factAlerts.AlertDimKey = dimAlert.AlertDimKey
INNER JOIN @configItemDimKeys targetEntities ON dimAlert.TargetEntityDimKey = targetEntities.ConfigItemEntityDimKey AND targetEntities.DeletedDate >= factAlerts.TimeRaised
INNER JOIN @selectdatedim selectdatedim ON selectdatedim.DateKey = factAlerts.DateKey
WHERE factAlerts.TimeRaised >= @StartDate AND factAlerts.TimeRaised < @EndDate
GROUP BY
CASE
WHEN ( @TimeAggregation = 'Daily')
THEN selectdatedim.DailyAggregate
WHEN (@TimeAggregation = 'Weekly')
THEN selectdatedim.WeeklyAggregate
WHEN (@TimeAggregation = 'Monthly')
THEN selectdatedim.MonthlyAggregate
WHEN (@TimeAggregation = 'Quarterly')
THEN selectdatedim.QuarterlyAggregate
WHEN (@TimeAggregation = 'Yearly')
THEN selectdatedim.YearlyAggregate
ELSE 0
END
INSERT INTO @summary
SELECT ReportSeries =
CASE
WHEN ( @TimeAggregation = 'Daily')
THEN selectdatedim.DailyAggregate
WHEN (@TimeAggregation = 'Weekly')
THEN selectdatedim.WeeklyAggregate
WHEN (@TimeAggregation = 'Monthly')
THEN selectdatedim.MonthlyAggregate
WHEN (@TimeAggregation = 'Quarterly')
THEN selectdatedim.QuarterlyAggregate
WHEN (@TimeAggregation = 'Yearly')
THEN selectdatedim.YearlyAggregate
ELSE 0
END,
null as Alerts,
SUM(CASE WHEN dimInc.AlertId IS NULL THEN 0 ELSE 1 END) * 100.0/COUNT(DISTINCT AlertInstanceDimKey) AS AlertsToIncidentsRatio
FROM dbo.AlertInstanceFactvw factAlerts
INNER JOIN dbo.AlertDimvw dimAlert ON factAlerts.AlertDimKey = dimAlert.AlertDimKey
INNER JOIN @configItemDimKeys targetEntities ON dimAlert.TargetEntityDimKey = targetEntities.ConfigItemEntityDimKey AND targetEntities.DeletedDate >= factAlerts.TimeRaised
LEFT JOIN dbo.IncidentDimvw dimInc ON dimInc.AlertId = CONVERT(nvarchar(256),dimAlert.AlertProblemGuid)
INNER JOIN @selectdatedim selectdatedim ON selectdatedim.DateKey = factAlerts.DateKey
WHERE factAlerts.TimeRaised >= @StartDate AND factAlerts.TimeRaised < @EndDate
GROUP BY
CASE
WHEN ( @TimeAggregation = 'Daily')
THEN selectdatedim.DailyAggregate
WHEN (@TimeAggregation = 'Weekly')
THEN selectdatedim.WeeklyAggregate
WHEN (@TimeAggregation = 'Monthly')
THEN selectdatedim.MonthlyAggregate
WHEN (@TimeAggregation = 'Quarterly')
THEN selectdatedim.QuarterlyAggregate
WHEN (@TimeAggregation = 'Yearly')
THEN selectdatedim.YearlyAggregate
ELSE 0
END
SELECT
ReportSeries,
null As Incidents,
null As IncidentsResolvedWithinTarget,
null As AvgTimeToResolution,
SUM(Alerts) AS Alerts ,
SUM(AlertsToIncidentsRatio) AS AlertsToIncidentsRatio,
null As PhysicalComputers,
null As VirtualComputers,
null As SoftwareUpdateCompliance,
null As SecurityBulletinCompliance,
null As DCMIncidents,
null As TotalConsumption,
null As CostOfPower,
null As UnusedConsumption
from @datesummary
group by ReportSeries
END
SET @Error = @@ERROR
QuitError:
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.ServiceManager_Report_ServiceManagement_SP_GetOMGroupGroupData TO reportuser
GO
Home