<DataWarehouseScript ID="Dell.WindowsServer.Report.Script.FirmwareAndDriverVerDataGet" Accessibility="Public"> <Install>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Dell_WindowsServer_Report_FirmwareamdDriverVerDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Dell_WindowsServer_Report_FirmwareamdDriverVerDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[Dell_WindowsServer_Report_FirmwareamdDriverVerDataGet]
@StartDate datetime,
@EndDate datetime,
@ObjectList xml,
@LanguageCode varchar(10)
AS
BEGIN
SET NOCOUNT ON
DECLARE @Error int
DECLARE @ExecError int
---------------------------------------------------------------------
--Create a list of Managed Entities
---------------------------------------------------------------------
CREATE TABLE #ObjectList (ManagedEntityRowId int)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
SET @Error = @@ERROR
IF @Error <> 0 OR @ExecError <> 0 GOTO QuitError
---------------------------------------------------------------------
--Create list of all relationships derived from 'Dell.WindowsServer.Hosts.DellStorage'
---------------------------------------------------------------------
CREATE TABLE #RelationshipList_1(RelationshipTypeRowId int)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT #RelationshipList_1
SELECT RelationshipTypeRowId
FROM dbo.vRelationshipType
WHERE RelationshipTypeSystemName = 'Dell.WindowsServer.Hosts.DellStorage'
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
---------------------------------------------------------------------
--Create list of all entities above the selected objects in their respective hosting chains
---------------------------------------------------------------------
CREATE TABLE #Entity_Tree_1 (ManagedEntityRowId int)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT #Entity_Tree_1
SELECT r.TargetManagedEntityRowId AS ManagedEntityRowId
FROM vRelationship AS r
JOIN #ObjectList AS ol ON r.SourceManagedEntityRowId = ol.ManagedEntityRowId
JOIN #RelationshipList_1 rl ON r.RelationshipTypeRowId = rl.RelationshipTypeRowId
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
---------------------------------------------------------------------
--Create list of all relationships derived from 'DellStorage.Hosts.DellStorageController'
---------------------------------------------------------------------
CREATE TABLE #RelationshipList_2(RelationshipTypeRowId int)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT #RelationshipList_2
SELECT RelationshipTypeRowId
FROM dbo.vRelationshipType
WHERE RelationshipTypeSystemName = 'DellStorage.Hosts.DellStorageController'
CREATE TABLE #Entity_Tree_2(ManagedEntityRowId int not null)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT #Entity_Tree_2
SELECT r.TargetManagedEntityRowId AS ManagedEntityRowId
FROM vRelationship AS r
JOIN #Entity_Tree_1 AS et ON r.SourceManagedEntityRowId = et.ManagedEntityRowId
JOIN #RelationshipList_2 rl ON r.RelationshipTypeRowId = rl.RelationshipTypeRowId
CREATE TABLE #ColumnPicker
(
PropertyGuid nvarchar(255)
)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT INTO #ColumnPicker
SELECT distinct t.PropertyGuid AS PropertyGuid
FROM vManagedEntityTypeProperty AS t
JOIN vManagedEntity AS mt ON t.ManagedEntityTypeRowId = mt.ManagedEntityTypeRowId
JOIN #Entity_Tree_2 AS et ON mt.ManagedEntityRowId = et.ManagedEntityRowId
WHERE t.PropertySystemName='DriverVer' OR t.PropertySystemName='FirmwareVer'
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
---------------------------------------------------------------------
--Create List of Selected Properties and values for selected Objects
---------------------------------------------------------------------
CREATE TABLE #PropertyList
(
ManagedEntityRowId int
,PropertyValue nvarchar(256)
,DWLastModifiedDateTime datetime
,FromDateTime datetime
,TypeGuid uniqueidentifier
,TypeName nvarchar(256)
,PropertyGuid uniqueidentifier
)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT INTO #PropertyList
SELECT
PropertyTable.ManagedEntityRowId
,PropertyTable.PropertyValue
,PropertyTable.DWLastModifiedDateTime
,PropertyTable.FromDateTime
,vManagedEntityType.ManagedEntityTypeGuid
,vManagedEntityType.ManagedEntityTypeDefaultName
,#ColumnPicker.PropertyGuid
FROM
(
#ColumnPicker
INNER JOIN
(Select
vManagedEntityProperty.ManagedEntityRowId
,DWLastModifiedDateTime
,FromDateTime
,T.PropertyXML.value('@Guid','uniqueidentifier') AS PropertyGuid
,T.PropertyXML.value('.','nvarchar(256)') AS PropertyValue
FROM
vManagedEntityProperty INNER JOIN
#Entity_Tree_2 ON vManagedEntityProperty.ManagedEntityRowId = #Entity_Tree_2.ManagedEntityRowId
CROSS APPLY PropertyXml.nodes('/Root/Property') AS T(PropertyXML)
WHERE DWLastModifiedDateTime > @StartDate AND DWLastModifiedDateTime <= @EndDate
)AS PropertyTable
ON PropertyTable.PropertyGuid = #ColumnPicker.PropertyGuid
INNER JOIN vManagedEntity ON PropertyTable.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId
INNER JOIN vManagedEntityType ON vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId
)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
---------------------------------------------------------------------
--Select Filtered list of properties for selected objects
---------------------------------------------------------------------
SELECT
#PropertyList.TypeGuid
,#PropertyList.TypeName
,#PropertyList.PropertyGuid
,#PropertyList.DWLastModifiedDateTime
,#PropertyList.FromDateTime
,#PropertyList.PropertyValue
,#PropertyList.ManagedEntityRowId
,vManagedEntity.ManagedEntityDefaultName AS RelatedManagedEntityDefaultName
,vManagedEntity.ManagedEntityRowId AS RelatedManagedEntityRowId
,vManagedEntity.ManagedEntityGuid
,vManagedEntity.ManagedEntityDefaultName
,vManagedEntity.Path
,vManagementGroup.ManagementGroupDefaultName
,vManagementGroup.ManagementGroupGuid
,vManagedEntityType.ManagedEntityTypeDefaultName
,vManagedEntityTypeImage.Image
,vManagedEntityTypeProperty.PropertyDefaultName
FROM
(
#PropertyList
INNER JOIN vManagedEntity ON #PropertyList.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId
INNER JOIN vManagementGroup ON vManagedEntity.ManagementGroupRowId = vManagementGroup.ManagementGroupRowId
INNER JOIN vManagedEntityType ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityType.ManagedEntityTypeRowId
INNER JOIN vManagedEntityTypeProperty ON vManagedEntityTypeProperty.PropertyGuid = #PropertyList.PropertyGuid
INNER JOIN vManagedEntityTypeImage ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityTypeImage.ManagedEntityTypeRowId AND
vManagedEntityTypeImage.ImageCategory = N'u16x16Icon'
INNER JOIN #ColumnPicker ON #ColumnPicker.PropertyGuid = #PropertyList.PropertyGuid
)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
---------------------------------------------------------------------
--Error Handling & Cleanup code
---------------------------------------------------------------------
QuitError:
IF ((@Error = 0) AND (@ExecError <> 0)) SET @Error = @ExecError
DROP TABLE #PropertyList
DROP TABLE #ObjectList
DROP TABLE #RelationshipList_1
DROP TABLE #Entity_Tree_1
DROP TABLE #RelationshipList_2
DROP TABLE #Entity_Tree_2
DROP TABLE #ColumnPicker
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.[Dell_WindowsServer_Report_FirmwareamdDriverVerDataGet] TO OpsMgrReader
GO
</Install> <Uninstall>
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Dell_WindowsServer_Report_FirmwareamdDriverVerDataGet')
BEGIN
DROP PROCEDURE dbo.[Dell_WindowsServer_Report_FirmwareamdDriverVerDataGet]
END
GO </Uninstall> <Upgrade>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Dell_WindowsServer_Report_FirmwareamdDriverVerDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Dell_WindowsServer_Report_FirmwareamdDriverVerDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[Dell_WindowsServer_Report_FirmwareamdDriverVerDataGet]
@StartDate datetime,
@EndDate datetime,
@ObjectList xml,
@LanguageCode varchar(10)
AS
BEGIN
SET NOCOUNT ON
DECLARE @Error int
DECLARE @ExecError int
---------------------------------------------------------------------
--Create a list of Managed Entities
---------------------------------------------------------------------
CREATE TABLE #ObjectList (ManagedEntityRowId int)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
SET @Error = @@ERROR
IF @Error <> 0 OR @ExecError <> 0 GOTO QuitError
---------------------------------------------------------------------
--Create list of all relationships derived from 'Dell.WindowsServer.Hosts.DellStorage'
---------------------------------------------------------------------
CREATE TABLE #RelationshipList_1(RelationshipTypeRowId int)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT #RelationshipList_1
SELECT RelationshipTypeRowId
FROM dbo.vRelationshipType
WHERE RelationshipTypeSystemName = 'Dell.WindowsServer.Hosts.DellStorage'
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
---------------------------------------------------------------------
--Create list of all entities above the selected objects in their respective hosting chains
---------------------------------------------------------------------
CREATE TABLE #Entity_Tree_1 (ManagedEntityRowId int)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT #Entity_Tree_1
SELECT r.TargetManagedEntityRowId AS ManagedEntityRowId
FROM vRelationship AS r
JOIN #ObjectList AS ol ON r.SourceManagedEntityRowId = ol.ManagedEntityRowId
JOIN #RelationshipList_1 rl ON r.RelationshipTypeRowId = rl.RelationshipTypeRowId
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
---------------------------------------------------------------------
--Create list of all relationships derived from 'DellStorage.Hosts.DellStorageController'
---------------------------------------------------------------------
CREATE TABLE #RelationshipList_2(RelationshipTypeRowId int)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT #RelationshipList_2
SELECT RelationshipTypeRowId
FROM dbo.vRelationshipType
WHERE RelationshipTypeSystemName = 'DellStorage.Hosts.DellStorageController'
CREATE TABLE #Entity_Tree_2(ManagedEntityRowId int not null)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT #Entity_Tree_2
SELECT r.TargetManagedEntityRowId AS ManagedEntityRowId
FROM vRelationship AS r
JOIN #Entity_Tree_1 AS et ON r.SourceManagedEntityRowId = et.ManagedEntityRowId
JOIN #RelationshipList_2 rl ON r.RelationshipTypeRowId = rl.RelationshipTypeRowId
CREATE TABLE #ColumnPicker
(
PropertyGuid nvarchar(255)
)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT INTO #ColumnPicker
SELECT distinct t.PropertyGuid AS PropertyGuid
FROM vManagedEntityTypeProperty AS t
JOIN vManagedEntity AS mt ON t.ManagedEntityTypeRowId = mt.ManagedEntityTypeRowId
JOIN #Entity_Tree_2 AS et ON mt.ManagedEntityRowId = et.ManagedEntityRowId
WHERE t.PropertySystemName='DriverVer' OR t.PropertySystemName='FirmwareVer'
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
---------------------------------------------------------------------
--Create List of Selected Properties and values for selected Objects
---------------------------------------------------------------------
CREATE TABLE #PropertyList
(
ManagedEntityRowId int
,PropertyValue nvarchar(256)
,DWLastModifiedDateTime datetime
,FromDateTime datetime
,TypeGuid uniqueidentifier
,TypeName nvarchar(256)
,PropertyGuid uniqueidentifier
)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT INTO #PropertyList
SELECT
PropertyTable.ManagedEntityRowId
,PropertyTable.PropertyValue
,PropertyTable.DWLastModifiedDateTime
,PropertyTable.FromDateTime
,vManagedEntityType.ManagedEntityTypeGuid
,vManagedEntityType.ManagedEntityTypeDefaultName
,#ColumnPicker.PropertyGuid
FROM
(
#ColumnPicker
INNER JOIN
(Select
vManagedEntityProperty.ManagedEntityRowId
,DWLastModifiedDateTime
,FromDateTime
,T.PropertyXML.value('@Guid','uniqueidentifier') AS PropertyGuid
,T.PropertyXML.value('.','nvarchar(256)') AS PropertyValue
FROM
vManagedEntityProperty INNER JOIN
#Entity_Tree_2 ON vManagedEntityProperty.ManagedEntityRowId = #Entity_Tree_2.ManagedEntityRowId
CROSS APPLY PropertyXml.nodes('/Root/Property') AS T(PropertyXML)
WHERE DWLastModifiedDateTime > @StartDate AND DWLastModifiedDateTime <= @EndDate
)AS PropertyTable
ON PropertyTable.PropertyGuid = #ColumnPicker.PropertyGuid
INNER JOIN vManagedEntity ON PropertyTable.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId
INNER JOIN vManagedEntityType ON vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId
)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
---------------------------------------------------------------------
--Select Filtered list of properties for selected objects
---------------------------------------------------------------------
SELECT
#PropertyList.TypeGuid
,#PropertyList.TypeName
,#PropertyList.PropertyGuid
,#PropertyList.DWLastModifiedDateTime
,#PropertyList.FromDateTime
,#PropertyList.PropertyValue
,#PropertyList.ManagedEntityRowId
,vManagedEntity.ManagedEntityDefaultName AS RelatedManagedEntityDefaultName
,vManagedEntity.ManagedEntityRowId AS RelatedManagedEntityRowId
,vManagedEntity.ManagedEntityGuid
,vManagedEntity.ManagedEntityDefaultName
,vManagedEntity.Path
,vManagementGroup.ManagementGroupDefaultName
,vManagementGroup.ManagementGroupGuid
,vManagedEntityType.ManagedEntityTypeDefaultName
,vManagedEntityTypeImage.Image
,vManagedEntityTypeProperty.PropertyDefaultName
FROM
(
#PropertyList
INNER JOIN vManagedEntity ON #PropertyList.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId
INNER JOIN vManagementGroup ON vManagedEntity.ManagementGroupRowId = vManagementGroup.ManagementGroupRowId
INNER JOIN vManagedEntityType ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityType.ManagedEntityTypeRowId
INNER JOIN vManagedEntityTypeProperty ON vManagedEntityTypeProperty.PropertyGuid = #PropertyList.PropertyGuid
INNER JOIN vManagedEntityTypeImage ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityTypeImage.ManagedEntityTypeRowId AND
vManagedEntityTypeImage.ImageCategory = N'u16x16Icon'
INNER JOIN #ColumnPicker ON #ColumnPicker.PropertyGuid = #PropertyList.PropertyGuid
)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
---------------------------------------------------------------------
--Error Handling & Cleanup code
---------------------------------------------------------------------
QuitError:
IF ((@Error = 0) AND (@ExecError <> 0)) SET @Error = @ExecError
DROP TABLE #PropertyList
DROP TABLE #ObjectList
DROP TABLE #RelationshipList_1
DROP TABLE #Entity_Tree_1
DROP TABLE #RelationshipList_2
DROP TABLE #Entity_Tree_2
DROP TABLE #ColumnPicker
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.[Dell_WindowsServer_Report_FirmwareamdDriverVerDataGet] TO OpsMgrReader
GO </Upgrade>
</DataWarehouseScript>
Home