<DataWarehouseScript ID="Dell.WindowsServer.Report.Script.BIOSInfoDataGet" Accessibility="Public"> <Install>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Dell_WindowsServer_Report_BIOSInfoDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Dell_WindowsServer_Report_BIOSInfoDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[Dell_WindowsServer_Report_BIOSInfoDataGet]
@StartDate datetime,
@EndDate datetime,
@ObjectList xml,
@Properties 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.ServerBIOS'
---------------------------------------------------------------------
CREATE TABLE #RelationshipList_BIOS(RelationshipTypeRowId int)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT #RelationshipList_BIOS
SELECT RelationshipTypeRowId
FROM dbo.vRelationshipType
WHERE RelationshipTypeSystemName = 'Dell.WindowsServer.Hosts.ServerBIOS'
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
---------------------------------------------------------------------
--Create list of all entities above the selected objects in their respective hosting chains
---------------------------------------------------------------------
CREATE TABLE #ObjectList_BIOS (ManagedEntityRowId int)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT #ObjectList_BIOS
SELECT r.TargetManagedEntityRowId AS ManagedEntityRowId
FROM vRelationship AS r
JOIN #ObjectList AS ol ON r.SourceManagedEntityRowId = ol.ManagedEntityRowId
JOIN #RelationshipList_BIOS rl ON r.RelationshipTypeRowId = rl.RelationshipTypeRowId
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT INTO #ColumnPicker
SELECT PropertiesList.Properties.value('.','nvarchar(255)') AS PropertyGuid
FROM @Properties.nodes('/Data/Value') AS PropertiesList(Properties)
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
#ObjectList_BIOS ON vManagedEntityProperty.ManagedEntityRowId = #ObjectList_BIOS.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.ManagedEntityRowId
,#PropertyList.PropertyValue
,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 #ColumnPicker
DROP TABLE #RelationshipList_BIOS
DROP TABLE #ObjectList_BIOS
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.[Dell_WindowsServer_Report_BIOSInfoDataGet] TO OpsMgrReader
GO
</Install> <Uninstall>
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Dell_WindowsServer_Report_BIOSInfoDataGet')
BEGIN
DROP PROCEDURE dbo.[Dell_WindowsServer_Report_BIOSInfoDataGet]
END
GO </Uninstall> <Upgrade>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Dell_WindowsServer_Report_BIOSInfoDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Dell_WindowsServer_Report_BIOSInfoDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[Dell_WindowsServer_Report_BIOSInfoDataGet]
@StartDate datetime,
@EndDate datetime,
@ObjectList xml,
@Properties 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.ServerBIOS'
---------------------------------------------------------------------
CREATE TABLE #RelationshipList_BIOS(RelationshipTypeRowId int)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT #RelationshipList_BIOS
SELECT RelationshipTypeRowId
FROM dbo.vRelationshipType
WHERE RelationshipTypeSystemName = 'Dell.WindowsServer.Hosts.ServerBIOS'
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
---------------------------------------------------------------------
--Create list of all entities above the selected objects in their respective hosting chains
---------------------------------------------------------------------
CREATE TABLE #ObjectList_BIOS (ManagedEntityRowId int)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT #ObjectList_BIOS
SELECT r.TargetManagedEntityRowId AS ManagedEntityRowId
FROM vRelationship AS r
JOIN #ObjectList AS ol ON r.SourceManagedEntityRowId = ol.ManagedEntityRowId
JOIN #RelationshipList_BIOS rl ON r.RelationshipTypeRowId = rl.RelationshipTypeRowId
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
---------------------------------------------------------------------
--Create list of all relationships derived from 'System.Hosting'
-- Assumes a MAX depth of 9999
---------------------------------------------------------------------
CREATE TABLE #RelationshipList(RelationshipTypeRowId int)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT #RelationshipList
SELECT RelationshipTypeRowId
from RelationshipDerivedTypeHierarchy
(
(
SELECT RelationshipTypeRowId
FROM dbo.vRelationshipType
WHERE RelationshipTypeSystemName = 'System.Hosting'
)
,0
)
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 (ManagedEntityRowId int ,GroupManagedEntityRowId int,Level int )
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT #Entity_Tree
SELECT ManagedEntityRowId AS ManagedEntityRowId
,ManagedEntityRowId AS GroupManagedEntityRowId
,0 AS Level
FROM #ObjectList_BIOS
INSERT #Entity_Tree
SELECT r.SourceManagedEntityRowId
,et.GroupManagedEntityRowId
,et.Level +1
FROM vRelationship AS r
JOIN #Entity_Tree AS et
ON r.TargetManagedEntityRowId = et.ManagedEntityRowId
AND
RelationshipTypeRowId IN (SELECT * FROM #RelationshipList)
AND et.Level < 9999
WHERE r.SourceManagedEntityRowId NOT IN (SELECT ManagedEntityRowId FROM #Entity_Tree)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT INTO #ColumnPicker
SELECT PropertiesList.Properties.value('.','nvarchar(255)') AS PropertyGuid
FROM @Properties.nodes('/Data/Value') AS PropertiesList(Properties)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
---------------------------------------------------------------------
--Create List of Selected Properties and values for selected Objects
---------------------------------------------------------------------
CREATE TABLE #PropertyList
(
ManagedEntityRowId int
,GroupManagedEntityRowId int
,Level 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.GroupManagedEntityRowId
,PropertyTable.Level
,PropertyTable.PropertyValue
,PropertyTable.DWLastModifiedDateTime
,PropertyTable.FromDateTime
,vManagedEntityType.ManagedEntityTypeGuid
,vManagedEntityType.ManagedEntityTypeDefaultName
,#ColumnPicker.PropertyGuid
FROM
(
#ColumnPicker
INNER JOIN
(Select
vManagedEntityProperty.ManagedEntityRowId
,#Entity_Tree.GroupManagedEntityRowId
,#Entity_Tree.Level
,DWLastModifiedDateTime
,FromDateTime
,T.PropertyXML.value('@Guid','uniqueidentifier') AS PropertyGuid
,T.PropertyXML.value('.','nvarchar(256)') AS PropertyValue
FROM
vManagedEntityProperty INNER JOIN
#Entity_Tree ON vManagedEntityProperty.ManagedEntityRowId = #Entity_Tree.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.GroupManagedEntityRowId AS ManagedEntityRowId
,#PropertyList.PropertyValue
,vManagedEntity.ManagedEntityDefaultName AS RelatedManagedEntityDefaultName
,vManagedEntity.ManagedEntityRowId AS RelatedManagedEntityRowId
,GroupEntity.ManagedEntityGuid
,GroupEntity.ManagedEntityDefaultName
,GroupEntity.Path
,vManagementGroup.ManagementGroupDefaultName
,vManagementGroup.ManagementGroupGuid
,vManagedEntityType.ManagedEntityTypeDefaultName
,vManagedEntityTypeImage.Image
,vManagedEntityTypeProperty.PropertyDefaultName
FROM
#PropertyList
INNER JOIN
-- PropertyMinTable filters #PropertyList to only the
-- first occurrence of a property in a hosting chain.
(
Select
GroupManagedEntityRowId
,MIN(Level) as Level
,PropertyGuid
From #PropertyList
Group By
GroupManagedEntityRowId
,PropertyGuid
)As PropertyMinTable
ON
#PropertyList.PropertyGuid = PropertyMinTable.PropertyGuid
AND #PropertyList.Level = PropertyMinTable.Level
AND #PropertyList.GroupManagedEntityRowId = PropertyMinTable.GroupManagedEntityRowId
INNER JOIN vManagedEntity ON #PropertyList.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId
INNER JOIN vManagedEntity AS GroupEntity ON #PropertyList.GroupManagedEntityRowId = GroupEntity.ManagedEntityRowId
INNER JOIN vManagementGroup ON GroupEntity.ManagementGroupRowId = vManagementGroup.ManagementGroupRowId
INNER JOIN vManagedEntityType ON GroupEntity.ManagedEntityTypeRowId = vManagedEntityType.ManagedEntityTypeRowId
INNER JOIN vManagedEntityTypeProperty ON vManagedEntityTypeProperty.PropertyGuid = #PropertyList.PropertyGuid
INNER JOIN vManagedEntityTypeImage ON GroupEntity.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
DROP TABLE #Entity_Tree
DROP TABLE #ColumnPicker
DROP TABLE #RelationshipList_BIOS
DROP TABLE #ObjectList_BIOS
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.[Dell_WindowsServer_Report_BIOSInfoDataGet] TO OpsMgrReader
GO </Upgrade>
</DataWarehouseScript>
Home