IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'ServiceManager_Report_ServiceManagement_SP_GetCMCollectionSummaryData')
BEGIN
DROP PROCEDURE dbo.ServiceManager_Report_ServiceManagement_SP_GetCMCollectionSummaryData
END
GO
CREATE PROCEDURE [dbo].[ServiceManager_Report_ServiceManagement_SP_GetCMCollectionSummaryData]
@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
DECLARE @Error int
DECLARE @ExecError int
DECLARE @WattPerComputer INT = 150
DECLARE @CostOfPower FLOAT = 0.15
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)
SET @ServiceDimKeys = ISNULL(@ServiceDimKeys, '')
SET @GroupDimKeys = ISNULL(@GroupDimKeys, '')
SET @CollectionDimKeys = ISNULL(@CollectionDimKeys, '')
INSERT INTO @computerDimKeys
SELECT configItems.GroupById ,
configItems.GroupByDimKey ,
configItems.GroupByBME ,
configItems.GroupByEntityDimKey ,
configItems.GroupByName ,
compDim.ComputerDimKey,
compDim.EntityDimKey,
compDim.BaseManagedEntityId,
configItems.CreatedDate,
configItems.DeletedDate
FROM @groupByConfigItems configItems
INNER JOIN ComputerDimvw compDim ON configItems.ConfigItemEntityDimKey = compDim.EntityDimKey
IF ( @ServiceDimKeys = '' AND @GroupDimKeys = '' AND @CollectionDimKeys = '' )
BEGIN
INSERT INTO @computerDimKeys
SELECT DISTINCT NULL, NULL, NULL, NULL, NULL, CompDim.ComputerDimKey, CompDim.EntityDimKey, CompDim.BaseManagedEntityId, '1/1/1900', NULL
FROM dbo.ComputerDimvw CompDim
WHERE CompDim.IsDeleted = 0
END
DECLARE @selectdatedim AS TABLE(DateKey int,
CalendarDate DateTime,
WeeklyAggregate int
)
INSERT INTO @selectdatedim
Select
DateKey,
CalendarDate,
WeeklyAggregate = CAST(CONVERT(varchar,DATEADD(DAY, (DD.WeekNumberInYear - 1)*7, CONVERT(nvarchar(4),DD.YearNumber) + '-1-1 00:00:00'),112) AS INT)
from DateDim DD
DECLARE @summary AS TABLE(Area NVARCHAR(256), Measure NVARCHAR(256), [DateTime] INT, Value FLOAT)
INSERT INTO @summary
SELECT 'Compliance Management' AS Area, 'Software Compliance Management(%)' AS Measure, selectedDateDim.WeeklyAggregate AS [DateTime],
ROUND(SUM(CAST(factSWUpd.IsInstalled AS INT)) * 100.0 / ISNULL(NULLIF(SUM(CAST(factSWUpd.IsInstalled AS INT) + CAST(factSWUpd.IsMissing AS INT)), 0), 1), 2) AS Value
FROM dbo.ComputerDimvw dimComp
INNER JOIN @computerDimKeys comp ON dimComp.ComputerDimKey = comp.ComputerDimKey
INNER JOIN dbo.ComputerHasSoftwareUpdateInstalledFactvw factSWUpd on dimComp.ComputerDimKey = factSWUpd.ComputerDimKey
INNER JOIN @selectdatedim selectedDateDim ON factSWUpd.DateKey = selectedDateDim.DateKey
WHERE factSWUpd.DateKey >= @StartDateKey AND factSWUpd.DateKey < @EndDateKey
GROUP BY selectedDateDim.WeeklyAggregate
INSERT INTO @summary
SELECT 'Compliance Management' AS Area, 'Security Bulletin Compliance(%)' AS Measure, selectedDateDim.WeeklyAggregate AS [DateTime],
ROUND(SUM(CAST(factSWUpd.IsInstalled AS INT)) * 100.0 / ISNULL(NULLIF(SUM(CAST(factSWUpd.IsInstalled AS INT) + CAST(factSWUpd.IsMissing AS INT)), 0), 1), 2) AS Value
FROM dbo.ComputerDimvw dimComp
INNER JOIN @computerDimKeys comp ON dimComp.ComputerDimKey = comp.ComputerDimKey
INNER JOIN dbo.ComputerHasSoftwareUpdateInstalledFactvw factSWUpd on dimComp.ComputerDimKey = factSWUpd.ComputerDimKey
INNER JOIN @selectdatedim selectedDateDim ON factSWUpd.DateKey = selectedDateDim.DateKey
INNER JOIN dbo.SoftwareUpdateDimvw dimSU ON factSWUpd.SoftwareUpdateDimKey = dimSU.SoftwareUpdateDimKey
WHERE factSWUpd.DateKey >= @StartDateKey AND factSWUpd.DateKey < @EndDateKey
AND ( dimSU.BulletinID <> '' AND dimSU.BulletinID IS NOT NULL) -- security bulletin
GROUP BY selectedDateDim.WeeklyAggregate
IF EXISTS (SELECT 'x' FROM sys.tables WHERE name = '#ComputerOnSummary')
BEGIN
DROP TABLE #ComputerOnSummary
END
IF EXISTS (SELECT 'x' FROM sys.tables WHERE name = '#ComputerActiveSummary')
BEGIN
DROP TABLE #ComputerActiveSummary
END
SELECT WeeklyAggregate AS [DateTime], SUM(Hour0 + Hour1 + Hour2 + Hour3 + Hour4 + Hour5 + Hour6 + Hour7 + Hour8 + Hour9 +
Hour10 + Hour11 + Hour12 + Hour13 + Hour14 + Hour15 + Hour16 + Hour17 + Hour18 + Hour19 +
Hour20 + Hour21 + Hour22 + Hour23) AS Value
INTO dbo.#ComputerOnSummary
FROM dbo.PowerActivityDayFactvw factPwr
INNER JOIN dbo.ComputerDimvw dimComp ON factPwr.ComputerDimKey = dimComp.ComputerDimKey
INNER JOIN @computerDimKeys comp ON dimComp.ComputerDimKey = comp.ComputerDimKey
INNER JOIN PowerActivityRecordEventType outrigger ON factPwr.PowerActivityRecordEventTypeId = outrigger.PowerActivityRecordEventTypeId
INNER JOIN @selectdatedim selectedDateDim ON selectedDateDim.DateKey = factPwr.DateKey
WHERE CAST(CAST(factPwr.DateKey AS CHAR(8)) AS DATETIME) >= @StartDate AND CAST(CAST(factPwr.DateKey AS CHAR(8)) AS DATETIME) < @EndDate
AND outrigger.ID = 'System.Power.ActivityRecordEnum.ComputerOn'
GROUP BY selectedDateDim.WeeklyAggregate
SELECT WeeklyAggregate AS [DateTime], SUM(Hour0 + Hour1 + Hour2 + Hour3 + Hour4 + Hour5 + Hour6 + Hour7 + Hour8 + Hour9 +
Hour10 + Hour11 + Hour12 + Hour13 + Hour14 + Hour15 + Hour16 + Hour17 + Hour18 + Hour19 +
Hour20 + Hour21 + Hour22 + Hour23) AS Value
INTO dbo.#ComputerActiveSummary
FROM dbo.PowerActivityDayFactvw factPwr
INNER JOIN dbo.ComputerDimvw dimComp ON factPwr.ComputerDimKey = dimComp.ComputerDimKey
INNER JOIN @computerDimKeys comp ON dimComp.ComputerDimKey = comp.ComputerDimKey
INNER JOIN PowerActivityRecordEventType outrigger ON factPwr.PowerActivityRecordEventTypeId = outrigger.PowerActivityRecordEventTypeId
INNER JOIN @selectdatedim selectedDateDim ON selectedDateDim.DateKey = factPwr.DateKey
WHERE CAST(CAST(factPwr.DateKey AS CHAR(8)) AS DATETIME) >= @StartDate AND CAST(CAST(factPwr.DateKey AS CHAR(8)) AS DATETIME) < @EndDate
AND outrigger.ID = 'System.Power.ActivityRecordEnum.ComputerActive'
GROUP BY selectedDateDim.WeeklyAggregate
INSERT INTO @summary
SELECT 'Power' AS Area, 'Total Consumption (KWH)' AS Measure, [DateTime], ROUND((compOnSummary.Value * @WattPerComputer)/(60 * 1000), 2) AS Value
FROM dbo.#ComputerOnSummary compOnSummary
INSERT INTO @summary
SELECT 'Power' AS Area, 'Cost of Power' AS Measure, [DateTime], ROUND((((compOnSummary.Value * @WattPerComputer)/(60 * 1000)) * @CostOfPower), 2) AS Value
FROM dbo.#ComputerOnSummary compOnSummary
INSERT INTO @summary
SELECT 'Power' AS Area, 'Unused Consumption (%)' AS Measure, compActiveSummary.[DateTime], CASE WHEN compOnSummary.Value > 0 THEN ROUND(((100. * compActiveSummary.Value)/compOnSummary.Value), 2) ELSE 0 END AS [Value]
FROM dbo.#ComputerOnSummary compOnSummary
INNER JOIN dbo.#ComputerActiveSummary compActiveSummary ON compOnSummary.DateTime = compActiveSummary.DateTime
SELECT * from @summary
SET @Error = @@ERROR
QuitError:
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.ServiceManager_Report_ServiceManagement_SP_GetCMCollectionSummaryData TO reportuser
GO
Home