-- Drop stored procedure if it already exists
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo'
AND SPECIFIC_NAME = N'ServiceManager_Report_ConfigurationManager_SP_GetPowerActivityData'
)
DROP PROCEDURE dbo.ServiceManager_Report_ConfigurationManager_SP_GetPowerActivityData
GO
CREATE PROCEDURE [dbo].[ServiceManager_Report_ConfigurationManager_SP_GetPowerActivityData]
@StartDate datetime = NULL,
@EndDate datetime = NULL,
@Collection nvarchar(max) = null,
@TimeGrain nvarchar(64) = 'Hourly',
@LanguageCode nvarchar(max)= 'ENU'
AS
BEGIN
SET NOCOUNT ON
DECLARE @Error int
DECLARE @ExecError int
IF(@StartDate) is NULL SET @StartDate = '1/1/1900'
IF(@EndDate) is NULL SET @EndDate = '1/1/2100'
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)
IF @TimeGrain = 'Hourly'
BEGIN
SELECT
CONVERT(INT, SUBSTRING(HourKey,5,2)) AS GRAIN,
COUNT(DISTINCT(ComputerDimKey)) AS NumberOfComputers,
ROUND(AVG(Event1 * 1.0), 2) AS ComputerActive,
ROUND(AVG(Event2 * 1.0), 2) AS MonitorOn,
ROUND(AVG(Event3 * 1.0), 2) AS ComputerOn
FROM
(
SELECT ComputerDimKey, HourKey, DateKey, [4] as [Event1], [2] as [Event2], [3] as [Event3]
FROM
(
SELECT ComputerDimKey, Value, HourKey, PowerActivityRecordEventTypeId, DateKey from dbo.PowerActivityDayFactvw
UnPivot
(Value For HourKey in
(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 unpvt
)as u
Pivot
(MIN(Value) For PowerActivityRecordEventTypeId in ([2], [3], [4])) as pvt
WHERE DateKey > @StartDateKey AND DateKey < @EndDateKey
AND ((@Collection IS NULL OR @Collection = '') OR (ComputerDimKey IN (SELECT CollectionHasConfigItem_ComputerDimKey
FROM ConfigurationManagerCollectionHasComputervw
WHERE ConfigurationManagerCollectionDimKey in (SELECT value from @tableCollection))))
)as A
GROUP BY CONVERT(INT, SUBSTRING(HourKey,5,2))
ORDER BY GRAIN
END
ELSE
BEGIN
SELECT
CONVERT(DATE,CONVERT(NVARCHAR, DateKey)) AS GRAIN,
COUNT(DISTINCT(ComputerDimKey)) AS NumberOfComputers,
ROUND(AVG(Event1 * 1.0), 2) AS ComputerActive,
ROUND(AVG(Event2 * 1.0), 2) AS MonitorOn,
ROUND(AVG(Event3 * 1.0), 2) AS ComputerOn
FROM
(
SELECT ComputerDimKey, HourKey, DateKey, [4] as [Event1], [2] as [Event2], [3] as [Event3]
FROM
(
SELECT ComputerDimKey, Value, HourKey, PowerActivityRecordEventTypeId, DateKey from dbo.PowerActivityDayFactvw
UnPivot
(Value For HourKey in
(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 unpvt
)as u
Pivot
(MIN(Value) For PowerActivityRecordEventTypeId in ([2], [3], [4])) as pvt
WHERE DateKey > @StartDateKey AND DateKey < @EndDateKey
AND ((@Collection IS NULL OR @Collection = '') OR (ComputerDimKey IN (SELECT CollectionHasConfigItem_ComputerDimKey
FROM ConfigurationManagerCollectionHasComputervw
WHERE ConfigurationManagerCollectionDimKey in (SELECT value from @tableCollection))))
)as A
GROUP BY CONVERT(DATE,CONVERT(NVARCHAR, DateKey))
ORDER BY GRAIN
END
SET @Error = @@ERROR
QuitError:
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.ServiceManager_Report_ConfigurationManager_SP_GetPowerActivityData TO reportuser
GO
Home