/****** Object: StoredProcedure [dbo].[SCUtils_Printer_DataWarehouse_Report_PrintedPagesSP_Install] Script Date: 30.04.2019 16:48:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Drop stored procedure if it already exists
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo'
AND SPECIFIC_NAME = N'SCUtils_Printer_DataWarehouse_Report_PrintedPagesSP_Install'
)
DROP PROCEDURE dbo.SCUtils_Printer_DataWarehouse_Report_PrintedPagesSP_Install
GO
CREATE PROCEDURE [dbo].[SCUtils_Printer_DataWarehouse_Report_PrintedPagesSP_Install]
@dtStart DateTime
,@dtEnd DateTime
,@DataAgg TINYINT
,@ObjectList xml
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @Error int
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
DECLARE @GuidPrinterIPAddress UNIQUEIDENTIFIER
SET @GuidPrinterIPAddress = '9092ABA4-8034-4AE6-0075-7C617CF7A528'
DECLARE @GuidPrinterLocation UNIQUEIDENTIFIER
SET @GuidPrinterLocation = 'B7CBB7E6-38AC-34E7-F948-B558BBE6CAB8'
DECLARE @GuidPrinterVendor UNIQUEIDENTIFIER
SET @GuidPrinterVendor = '136C423A-6C55-38EE-A172-C672887702B8'
--Get Object list for the Selected Object Groups
Insert into #ObjectList
Select distinct
Rel.TargetManagedEntityRowId,
Men.ManagedEntityGuid,
Men.ManagementGroupRowId,
Men.ManagedEntityDefaultName,
Men.Name,
Men.DisplayName
From Relationship Rel
Inner Join @ObjectList.nodes('/Data/Objects/Object') As ObjList(ManagedEntityRowId)
On Rel.SourceManagedEntityRowId = ObjList.ManagedEntityRowId.value('.','int')
And ObjList.ManagedEntityRowId.value('@Use','nvarchar(255)') = 'Containment'
Inner Join vManagedEntity Men
On Men.ManagedEntityRowId = Rel.TargetManagedEntityRowId
And Men.ManagementGroupRowId = Rel.ManagementGroupRowId
Inner Join vRelationshipType Rtp
On Rel.RelationshipTypeRowId = Rtp.RelationshipTypeRowId
And Rtp.RelationshipTypeSystemName in ('SCUtils.PrinterGeneralRelationship.DevicesGroupContainsGeneralPrinterDevice')
--Get Object list for the Selected Objects
Insert into #ObjectList
Select distinct
Men.ManagedEntityRowId,
Men.ManagedEntityGuid,
Men.ManagementGroupRowId,
Men.ManagedEntityDefaultName,
Men.Name,
Men.DisplayName
FROM @ObjectList.nodes('/Data/Objects/Object') AS ObjectList(ManagedEntity)
INNER JOIN
vManagedEntity AS Men ON Men.ManagedEntityRowId = ObjectList.ManagedEntity.value('.', 'int')
INNER JOIN
vManagedEntityType ON Men.ManagedEntityTypeRowId = vManagedEntityType.ManagedEntityTypeRowId
INNER JOIN
vManagementGroup ON Men.ManagementGroupRowId = vManagementGroup.ManagementGroupRowId
LEFT OUTER JOIN
vDisplayString ON vManagedEntityType.ManagedEntityTypeGuid = vDisplayString.ElementGuid
AND
vDisplayString.LanguageCode = 'ENU'
IF @DataAgg = 1
BEGIN
-- daily aggregation
SELECT p.DateTime
, iSNULL ((LEAD(p.MinValue ) OVER (PARTITION BY e.Name ORDER BY p.DateTime) -P.MinValue), 0) AS 'Printed Pages'
,E.DisplayName
,e.Name
,MEP.Location
,MEP.IPAddress
,mep.Vendor
FROM #ObjectList AS E
INNER JOIN Perf.vPerfDaily AS P ON E.ManagedEntityRowId = P.ManagedEntityRowId
INNER JOIN (
SELECT *
FROM (
SELECT [ManagedEntityPropertyRowId]
,[ManagedEntityRowId]
,PropertyXML.value('(/Root/Property[@Guid=sql:variable("@GuidPrinterIPAddress")]/text())[1]', 'nvarchar(100)') AS IPAddress
,PropertyXML.value('(/Root/Property[@Guid=sql:variable("@GuidPrinterLocation")]/text())[1]', 'nvarchar(100)') AS Location
,PropertyXML.value('(/Root/Property[@Guid=sql:variable("@GuidPrinterVendor")]/text())[1]', 'nvarchar(100)') AS Vendor
FROM [OperationsManagerDW].[dbo].[vManagedEntityProperty]
WHERE ToDateTime IS NULL
) AS Tabl1
) AS MEP ON E.ManagedEntityRowId = MEP.ManagedEntityRowId
LEFT OUTER JOIN dbo.vPerformanceRuleInstance AS PR ON P.PerformanceRuleInstanceRowId = PR.PerformanceRuleInstanceRowId
INNER JOIN dbo.vRule AS R ON PR.RuleRowId = R.RuleRowId
WHERE (r.RuleGuid = '78F9ED0C-5391-E8A6-331C-B90C4B7A06CC')
AND (p.DateTime >= CONVERT(DATETIME, CONVERT(VARCHAR(8), @dtStart, 112)))
AND (p.DateTime < DATEADD(hour, 24, CONVERT(DATETIME, CONVERT(VARCHAR(8), @dtEnd, 112))))
END
ELSE
BEGIN
-- hourly aggregation
SELECT p.DateTime
, iSNULL ((LEAD(p.MinValue ) OVER (PARTITION BY e.Name ORDER BY p.DateTime) -P.MinValue), 0) AS 'Printed Pages'
,E.DisplayName
,e.Name
,MEP.Location
,MEP.IPAddress
,mep.Vendor
FROM #ObjectList AS E
INNER JOIN Perf.vPerfHourly AS P ON E.ManagedEntityRowId = P.ManagedEntityRowId
INNER JOIN (
SELECT *
FROM (
SELECT [ManagedEntityPropertyRowId]
,[ManagedEntityRowId]
,PropertyXML.value('(/Root/Property[@Guid=sql:variable("@GuidPrinterIPAddress")]/text())[1]', 'nvarchar(100)') AS IPAddress
,PropertyXML.value('(/Root/Property[@Guid=sql:variable("@GuidPrinterLocation")]/text())[1]', 'nvarchar(100)') AS Location
,PropertyXML.value('(/Root/Property[@Guid=sql:variable("@GuidPrinterVendor")]/text())[1]', 'nvarchar(100)') AS Vendor
FROM [OperationsManagerDW].[dbo].[vManagedEntityProperty]
WHERE ToDateTime IS NULL
) AS Tabl1
) AS MEP ON E.ManagedEntityRowId = MEP.ManagedEntityRowId
LEFT OUTER JOIN dbo.vPerformanceRuleInstance AS PR ON P.PerformanceRuleInstanceRowId = PR.PerformanceRuleInstanceRowId
INNER JOIN dbo.vRule AS R ON PR.RuleRowId = R.RuleRowId
WHERE (r.RuleGuid = '78F9ED0C-5391-E8A6-331C-B90C4B7A06CC')
AND (p.DateTime >= DATEADD(hour, DATEPART(hour, @dtStart), convert(VARCHAR(8), @dtStart, 112)))
AND (p.DateTime < DATEADD(hour, DATEPART(hour, @dtEnd), convert(VARCHAR(8), @dtEnd, 112)))
END
QuitError:
IF OBJECT_ID('tempdb..#ObjectList') IS NOT NULL
BEGIN
drop table #ObjectList
END
RETURN @Error
END
GO
GRANT EXEC
ON [dbo].[SCUtils_Printer_DataWarehouse_Report_PrintedPagesSP_Install]
TO OpsMgrReader
GO
Home