IF EXISTS (SELECT * FROM StandardDatasetAggregationStorage WHERE DatasetId IN (SELECT DatasetId FROM StandardDataset WHERE SchemaName = 'Vista') AND BaseTableName = 'DiskFailureRaw')
BEGIN
SET @DatasetId = (SELECT DatasetId FROM StandardDatasetAggregationStorage WHERE DatasetId IN (SELECT DatasetId FROM StandardDataset WHERE SchemaName = 'Vista') AND BaseTableName = 'DiskFailureRaw')
EXEC StandardDatasetDelete @DatasetId
END
SET NOCOUNT ON
GO
DECLARE
@ErrorInd bit
,@ErrorMessageText nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
SET @ErrorInd = 0
DECLARE
@ExecResult int
,@XmlDocHandle int
,@XmlDocText nvarchar(max)
BEGIN TRY
SET @XmlDocText = '<Aggregations>$Config/Aggregations$</Aggregations>'
INSERT StandardDatasetAggregation
(
DatasetId
,AggregationTypeId
,AggregationIntervalDurationMinutes
,AggregationStartDelayMinutes
,BuildAggregationStoredProcedureName
,DeleteAggregationStoredProcedureName
,GroomStoredProcedureName
,IndexOptimizationIntervalMinutes
,MaxDataAgeDays
,GroomingIntervalMinutes
,MaxRowsToGroom
,LastGroomingDateTime
,DataFileGroupName
,IndexFileGroupName
)
SELECT
'$Config/DatasetId$'
,CASE AggregationType
WHEN 'Raw' THEN 0
WHEN 'Daily' THEN 30
END
,CASE AggregationType
WHEN 'Raw' THEN 0
WHEN 'Daily' THEN 24*60
END
,AggregationStartDelayMinutes
,CASE AggregationType
WHEN 'Raw' THEN NULL
ELSE 'DiskFailureAggregate'
END
,CASE AggregationType
WHEN 'Raw' THEN NULL
ELSE 'DiskFailureAggregationDelete'
END
,'DiskFailureGroom'
,IndexOptimizationIntervalMinutes
,MaxDataAgeDays
,GroomingIntervalMinutes
,MaxRowsToGroom
,GETUTCDATE()
,DataFileGroupName
,IndexFileGroupName
FROM OPENXML(@XmlDocHandle, 'Aggregations/*', 2) WITH (
AggregationType varchar(50) '@mp:localname'
,MaxTableRowCount int
,MaxTableSizeKb int
,AggregationStartDelayMinutes int
,MaxDataAgeDays int
,GroomingIntervalMinutes int
,MaxRowsToGroom int
,IndexOptimizationIntervalMinutes int
,DataFileGroupName nvarchar(256)
,IndexFileGroupName nvarchar(256)
,AggregationIntervalDurationMinutes int
)
END TRY
BEGIN CATCH
SELECT
@ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorLine = ERROR_LINE()
,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')
,@ErrorMessageText = ERROR_MESSAGE()
SET @ErrorInd = 1
END CATCH
IF (@XmlDocHandle IS NOT NULL)
EXEC sp_xml_removedocument @XmlDocHandle
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
GO
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessageText nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
SET @ErrorInd = 0
DECLARE
@ExecResult int
,@XmlDocHandle int
,@XmlDocText nvarchar(max)
,@MaxTableRowCount int
,@MaxTableSizeKb int
-- Inserting info about raw aggregations
INSERT StandardDatasetAggregationStorage
(
DatasetId
,AggregationTypeId
,BaseTableName
,DependentTableInd
,TableTemplate
,CoverViewSelectClause
,MaxTableRowCount
,MaxTableSizeKb
)
VALUES
(
'$Config/DatasetId$'
,0
,'DiskFailureRaw'
,0
,'
CREATE TABLE [Vista].[DiskFailureRaw_$Guid$]
(
DiskFailureRawRowId int NOT NULL IDENTITY(1, 1)
,[DateTime] datetime NOT NULL
,EventDataRowId int NOT NULL
,DiskDataRowId int NOT NULL
,ComputerDataRowId int NOT NULL
,PrincipalName nvarchar(256) NOT NULL
,Size bigint NOT NULL
,ContainsSystemDrive int NOT NULL
,CONSTRAINT [PK_DiskFailureRaw_$Guid$] PRIMARY KEY CLUSTERED (DiskFailureRawRowId) ON $DataFileGroupName$
) ON $DataFileGroupName$
'
,'
SELECT
[DateTime]
,DiskFailureRawRowId
,EventDataRowId
,DiskDataRowId
,ComputerDataRowId
,PrincipalName
,Size
,ContainsSystemDrive
'
,@MaxTableRowCount
,@MaxTableSizeKb
)
-- Now getting to Daily Aggregations
-- Disk Chart Aggregation
INSERT StandardDatasetAggregationStorage
(
DatasetId
,AggregationTypeId
,BaseTableName
,DependentTableInd
,TableTemplate
,CoverViewSelectClause
,MaxTableRowCount
,MaxTableSizeKb
)
VALUES
(
'$Config/DatasetId$'
,30
,'DiskFailureAggregationDisk'
,0
,'
CREATE TABLE [Vista].[DiskFailureAggregationDisk_$Guid$]
(
DiskFailureAggregationDiskRowId int NOT NULL IDENTITY(1, 1)
,[DateTime] datetime NOT NULL
,DiskDataRowId int NOT NULL
,NumDrives int NOT NULL
,CONSTRAINT [PK_DiskFailureAggregationDisk_$Guid$] PRIMARY KEY CLUSTERED (DiskFailureAggregationDiskRowId) ON $DataFileGroupName$
) ON $DataFileGroupName$
'
,'
SELECT
[DateTime]
,DiskFailureAggregationDiskRowId
,DiskDataRowId
,NumDrives
'
,@MaxTableRowCount
,@MaxTableSizeKb
)
-- Computer Chart Aggregation
INSERT StandardDatasetAggregationStorage
(
DatasetId
,AggregationTypeId
,BaseTableName
,TableTag
,DependentTableInd
,TableTemplate
,CoverViewSelectClause
,MaxTableRowCount
,MaxTableSizeKb
)
VALUES
(
'$Config/DatasetId$'
,30
,'DiskFailureAggregationComputer'
,'Disk Computer'
,1
,'
CREATE TABLE [Vista].[DiskFailureAggregationComputer_$Guid$]
(
DiskFailureAggregationComputerRowId int NOT NULL IDENTITY(1, 1)
,[DateTime] datetime NOT NULL
,ComputerDataRowId int NOT NULL
,NumDrives int NOT NULL
,CONSTRAINT [PK_DiskFailureAggregationComputer_$Guid$] PRIMARY KEY CLUSTERED (DiskFailureAggregationComputerRowId) ON $DataFileGroupName$
) ON $DataFileGroupName$
'
,'
SELECT
[DateTime]
,DiskFailureAggregationComputerRowId
,ComputerDataRowId
,NumDrives
'
,@MaxTableRowCount
,@MaxTableSizeKb
)
-- Utilization chart aggregation by size
INSERT StandardDatasetAggregationStorage
(
DatasetId
,AggregationTypeId
,BaseTableName
,TableTag
,DependentTableInd
,TableTemplate
,CoverViewSelectClause
,MaxTableRowCount
,MaxTableSizeKb
)
VALUES
(
'$Config/DatasetId$'
,30
,'DiskFailureAggregationUtilizationBySize'
,'Disk Utilization By Size'
,1
,'
CREATE TABLE [Vista].[DiskFailureAggregationUtilizationBySize_$Guid$]
(
DiskFailureAggregationUtilizationBySizeRowId int NOT NULL IDENTITY(1, 1)
,[DateTime] datetime NOT NULL
,DriveSize bigint NOT NULL
,DriveUtilizationAvg float NOT NULL
,NumDrives int NOT NULL
,CONSTRAINT [PK_DiskFailureAggregationUtilizationBySize_$Guid$] PRIMARY KEY CLUSTERED (DiskFailureAggregationUtilizationBySizeRowId) ON $DataFileGroupName$
) ON $DataFileGroupName$
'
,'
SELECT
[DateTime]
,DiskFailureAggregationUtilizationBySizeRowId
,DriveSize
,NumDrives
,DriveUtilizationAvg
'
,@MaxTableRowCount
,@MaxTableSizeKb
)
-- Utilization chart aggregation by drive
INSERT StandardDatasetAggregationStorage
(
DatasetId
,AggregationTypeId
,BaseTableName
,TableTag
,DependentTableInd
,TableTemplate
,CoverViewSelectClause
,MaxTableRowCount
,MaxTableSizeKb
)
VALUES
(
'$Config/DatasetId$'
,30
,'DiskFailureAggregationUtilizationByDrive'
,'Disk Utilization By Drive'
,1
,'
CREATE TABLE [Vista].[DiskFailureAggregationUtilizationByDrive_$Guid$]
(
DiskFailureAggregationUtilizationByDriveRowId int NOT NULL IDENTITY(1, 1)
,[DateTime] datetime NOT NULL
,DriveUtilization int NOT NULL
,NumDrives int NOT NULL
,CONSTRAINT [PK_DiskFailureAggregationUtilizationByDrive_$Guid$] PRIMARY KEY CLUSTERED (DiskFailureAggregationUtilizationByDriveRowId) ON $DataFileGroupName$
) ON $DataFileGroupName$
'
,'
SELECT
[DateTime]
,DiskFailureAggregationUtilizationByDriveRowId
,DriveUtilization
,NumDrives
'
,@MaxTableRowCount
,@MaxTableSizeKb
)
IF (@XmlDocHandle IS NOT NULL)
EXEC sp_xml_removedocument @XmlDocHandle
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE [name] = 'Vista')
BEGIN
EXECUTE('CREATE SCHEMA Vista')
END
GO
-- change schema ownership to allow index rebuild and reorganize
ALTER AUTHORIZATION ON SCHEMA::Vista TO OpsMgrWriter
GO
-- change schema alter to allow domain table index rebuild
GRANT ALTER ON SCHEMA::dbo TO OpsMgrWriter
GO
-- grant create table permissions
GRANT CREATE TABLE TO OpsMgrWriter
GO
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessageText nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
SET @ErrorInd = 0
DECLARE
@ExecResult int
,@XmlDocHandle int
,@XmlDocText nvarchar(max)
BEGIN TRY
SET @XmlDocText = '<Aggregations>$Config/Aggregations$</Aggregations>'
WHILE EXISTS (SELECT *
FROM OPENXML(@XmlDocHandle, 'Aggregations/*', 2) WITH (
AggregationType varchar(50) '@mp:localname')
WHERE AggregationType > @AggregationType
)
BEGIN
SELECT TOP 1 @AggregationType = AggregationType
FROM OPENXML(@XmlDocHandle, 'Aggregations/*', 2) WITH (
AggregationType varchar(50) '@mp:localname')
WHERE AggregationType > @AggregationType
ORDER BY AggregationType
SET @Statement = 'EXEC StandardDatasetAllocateStorage @DatasetId = ''$Config/DatasetId$'', @AggregationTypeId='
+ CASE @AggregationType
WHEN 'Raw' THEN '0'
WHEN 'Daily' THEN '30'
END
EXECUTE (@Statement)
END
END TRY
BEGIN CATCH
SELECT
@ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorLine = ERROR_LINE()
,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')
,@ErrorMessageText = ERROR_MESSAGE()
SET @ErrorInd = 1
END CATCH
IF (@XmlDocHandle IS NOT NULL)
EXEC sp_xml_removedocument @XmlDocHandle
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'EventData' AND TABLE_SCHEMA = 'Vista')
BEGIN
DROP TABLE Vista.EventData
END
GO
CREATE TABLE Vista.EventData
(
EventDataRowId int NOT NULL IDENTITY(1, 1)
,EventId int NOT NULL
,PublisherName nvarchar(256) NOT NULL
,CONSTRAINT PK_Vista_EventRule PRIMARY KEY CLUSTERED (EventDataRowId)
)
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DiskData' AND TABLE_SCHEMA = 'Vista')
BEGIN
DROP TABLE Vista.DiskData
END
GO
CREATE TABLE Vista.DiskData
(
DiskDataRowId int NOT NULL IDENTITY(1, 1)
,Manufacturer nvarchar(256) NOT NULL
,Model nvarchar(256) NOT NULL
,CONSTRAINT PK_Vista_DiskRule PRIMARY KEY CLUSTERED (DiskDataRowId)
)
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ComputerData' AND TABLE_SCHEMA = 'Vista')
BEGIN
DROP TABLE Vista.ComputerData
END
GO
CREATE TABLE Vista.ComputerData
(
ComputerDataRowId int NOT NULL IDENTITY(1, 1)
,Manufacturer nvarchar(256) NOT NULL
,Model nvarchar(256) NOT NULL
,CONSTRAINT PK_Vista_ComputerRule PRIMARY KEY CLUSTERED (ComputerDataRowId)
)
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'DiskFailureGroom')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.DiskFailureGroom AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.DiskFailureGroom
@DatasetId uniqueidentifier
,@AggregationTypeId tinyint
,@TableGuid uniqueidentifier
,@CutoffDateTime datetime
,@MaxRowsToGroom int
,@RowsDeleted int OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(4000)
SET @ErrorInd = 0
DECLARE
@Statement nvarchar(max)
BEGIN TRY
IF @AggregationTypeId = 0
BEGIN
SET @Statement = 'DELETE TOP (' + CAST(@MaxRowsToGroom AS varchar(15)) + ')'
+ ' FROM Vista.vDiskFailureRaw'
+ ' WHERE ([DateTime] < CONVERT(datetime, ''' + CONVERT(varchar(50), @CutoffDateTime, 120) + ''', 120))'
EXECUTE (@Statement)
SET @RowsDeleted = @@ROWCOUNT
END
IF @AggregationTypeId = 30
BEGIN
SET @Statement = 'DELETE FROM Vista.vDiskFailureAggregationDisk'
+ ' WHERE ([DateTime] < CONVERT(datetime, ''' + CONVERT(varchar(50), @CutoffDateTime, 120) + ''', 120))'
EXECUTE(@Statement)
SET @RowsDeleted = @@ROWCOUNT
SET @Statement = 'DELETE FROM Vista.vDiskFailureAggregationComputer'
+ ' WHERE ([DateTime] < CONVERT(datetime, ''' + CONVERT(varchar(50), @CutoffDateTime, 120) + ''', 120))'
EXECUTE(@Statement)
SET @RowsDeleted = @@ROWCOUNT + @RowsDeleted
SET @Statement = 'DELETE FROM Vista.vDiskFailureAggregationUtilizationBySize'
+ ' WHERE ([DateTime] < CONVERT(datetime, ''' + CONVERT(varchar(50), @CutoffDateTime, 120) + ''', 120))'
EXECUTE(@Statement)
SET @RowsDeleted = @@ROWCOUNT + @RowsDeleted
SET @Statement = 'DELETE FROM Vista.vDiskFailureAggregationUtilizationByDrive'
+ ' WHERE ([DateTime] < CONVERT(datetime, ''' + CONVERT(varchar(50), @CutoffDateTime, 120) + ''', 120))'
EXECUTE(@Statement)
SET @RowsDeleted = @@ROWCOUNT + @RowsDeleted
END
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'DiskFailureInsert')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.DiskFailureInsert AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.DiskFailureInsert
@ManagementGroupId uniqueidentifier
,@DatasetId uniqueidentifier
,@DataXml ntext
AS
BEGIN
SET NOCOUNT ON
DECLARE @CheckExecResult int
,@ManagementGroupRowId int
SELECT @ManagementGroupRowId = ManagementGroupRowId
FROM vManagementGroup
WHERE (ManagementGroupGuid = @ManagementGroupId) AND (WriterLoginName = SUSER_Name())
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
SET @ErrorInd = 0
DECLARE
@ExecResult int
,@XmlDocHandle int
,@ResultingState int
SET @ResultingState = 1
INSERT INTO @events
SELECT xml.EventId, xml.PrincipalName, xml.PublisherName, xml.DiskGuid
FROM OPENXML(@XmlDocHandle, 'Root/Item', 2) WITH (
EventId int
,PrincipalName nvarchar(256)
,PublisherName nvarchar(256)
,DiskGuid uniqueidentifier
) xml
WHILE EXISTS (SELECT * FROM @events)
BEGIN
SELECT @RowId = RowId, @EventId = EventId, @PrincipalName = PrincipalName, @PublisherName = PublisherName, @DiskGuid = DiskGuid
FROM @events
WHERE RowId = (SELECT MAX(RowId) FROM @events)
SELECT @PrincipalNameGuid = vManagedEntityTypeProperty.PropertyGuid
FROM vManagedEntityTypeProperty
JOIN vManagedEntityType ON (vManagedEntityType.ManagedEntityTypeRowId = vManagedEntityTypeProperty.ManagedEntityTypeRowId)
WHERE vManagedEntityTypeProperty.PropertySystemName = 'PrincipalName'
AND vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Computer'
SELECT @CompManufacturerGuid = vManagedEntityTypeProperty.PropertyGuid
FROM vManagedEntityTypeProperty
JOIN vManagedEntityType ON (vManagedEntityType.ManagedEntityTypeRowId = vManagedEntityTypeProperty.ManagedEntityTypeRowId)
WHERE vManagedEntityTypeProperty.PropertySystemName = 'Manufacturer'
AND vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Client.Vista.Aggregate.Computer'
SELECT @CompModelGuid = vManagedEntityTypeProperty.PropertyGuid
FROM vManagedEntityTypeProperty
JOIN vManagedEntityType ON (vManagedEntityType.ManagedEntityTypeRowId = vManagedEntityTypeProperty.ManagedEntityTypeRowId)
WHERE vManagedEntityTypeProperty.PropertySystemName = 'Model'
AND vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Client.Vista.Aggregate.Computer'
SELECT @ManufacturerGuid = vManagedEntityTypeProperty.PropertyGuid
FROM vManagedEntityTypeProperty
JOIN vManagedEntityType ON (vManagedEntityType.ManagedEntityTypeRowId = vManagedEntityTypeProperty.ManagedEntityTypeRowId)
WHERE vManagedEntityTypeProperty.PropertySystemName = 'Manufacturer'
AND vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Client.PhysicalDisk'
SELECT @ModelGuid = vManagedEntityTypeProperty.PropertyGuid
FROM vManagedEntityTypeProperty
JOIN vManagedEntityType ON (vManagedEntityType.ManagedEntityTypeRowId = vManagedEntityTypeProperty.ManagedEntityTypeRowId)
WHERE vManagedEntityTypeProperty.PropertySystemName = 'Model'
AND vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Client.PhysicalDisk'
SELECT @SizeGuid = vManagedEntityTypeProperty.PropertyGuid
FROM vManagedEntityTypeProperty
JOIN vManagedEntityType ON (vManagedEntityType.ManagedEntityTypeRowId = vManagedEntityTypeProperty.ManagedEntityTypeRowId)
WHERE vManagedEntityTypeProperty.PropertySystemName = 'Size'
AND vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Client.PhysicalDisk'
SELECT @ContainsSystemDriveGuid = vManagedEntityTypeProperty.PropertyGuid
FROM vManagedEntityTypeProperty
JOIN vManagedEntityType ON (vManagedEntityType.ManagedEntityTypeRowId = vManagedEntityTypeProperty.ManagedEntityTypeRowId)
WHERE vManagedEntityTypeProperty.PropertySystemName = 'ContainsSystemDrive'
AND vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Client.Vista.Aggregate.PhysicalDisk'
DECLARE @Manufacturer nvarchar(256)
,@Model nvarchar(256)
,@Size bigint
,@ContainsSystemDrive int
,@CompManufacturer nvarchar(256)
,@CompModel nvarchar(256)
,@CompRowId int
SELECT @CompRowId = ManagedEntityRowId
FROM vManagedEntity
WHERE vManagedEntity.Name = @PrincipalName
SELECT @CompManufacturer = CONVERT(nvarchar(256), vManagedEntityProperty.PropertyXml.query('data(/Root/Property[@Guid = sql:variable("@CompManufacturerGuid")])'))
,@CompModel = CONVERT(nvarchar(256), vManagedEntityProperty.PropertyXml.query('data(/Root/Property[@Guid = sql:variable("@CompModelGuid")])'))
FROM vManagedEntityProperty
WHERE ManagedEntityRowId = @CompRowId
IF @Size > 0 AND (@CompModel <> '' OR @CompManufacturer <> '') AND (@Manufacturer <> '' OR @Model <> '')
BEGIN
DECLARE @EventDataRowId int
,@DiskDataRowId int
,@ComputerDataRowId int
IF NOT EXISTS (SELECT * FROM Vista.ComputerData WHERE Manufacturer = @CompManufacturer AND Model = @CompModel)
BEGIN
INSERT INTO Vista.ComputerData (Manufacturer, Model) VALUES (@CompManufacturer, @CompModel)
SELECT @ComputerDataRowId = IDENT_CURRENT('Vista.ComputerData')
END
IF NOT EXISTS (SELECT * FROM Vista.EventData WHERE EventId = @EventId AND PublisherName = @PublisherName)
BEGIN
INSERT INTO Vista.EventData (EventId, PublisherName) VALUES (@EventId, @PublisherName)
END
IF NOT EXISTS (SELECT * FROM Vista.DiskData WHERE Manufacturer = @Manufacturer AND Model = @Model)
BEGIN
INSERT INTO Vista.DiskData (Manufacturer, Model) VALUES (@Manufacturer, @Model)
END
SELECT @EventDataRowId = EventDataRowId FROM Vista.EventData WHERE EventId = @EventId AND PublisherName = @PublisherName
SELECT @ComputerDataRowId = ComputerDataRowId FROM Vista.ComputerData WHERE Manufacturer = @CompManufacturer AND Model = @CompModel
SELECT @DiskDataRowId = DiskDataRowId FROM Vista.DiskData WHERE Manufacturer = @Manufacturer AND Model = @Model
INSERT INTO Vista.vDiskFailureRaw (DateTime, EventDataRowId, DiskDataRowId, ComputerDataRowId, PrincipalName, Size, ContainsSystemDrive) VALUES (GETUTCDATE(), @EventDataRowId, @DiskDataRowId, @ComputerDataRowId, @PrincipalName, @Size, @ContainsSystemDrive)
END
DELETE FROM @events WHERE RowId = @RowId
END
SET @ResultingState = 10
EXEC StandardDatasetMaintenance @DatasetId
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
IF (@XmlDocHandle IS NOT NULL)
EXEC sp_xml_removedocument @XmlDocHandle
-- report error if any
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, @ResultingState
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
GRANT EXECUTE ON DiskFailureInsert TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'DiskFailureAggregate')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.DiskFailureAggregate AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.DiskFailureAggregate
@DatasetId uniqueidentifier
,@AggregationTypeId int
,@InsertTableGuid varchar(256)
,@IntervalStartDateTime datetime
,@IntervalEndDateTime datetime
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
INSERT Vista.vDiskFailureAggregationDisk (
[DateTime]
,DiskDataRowId
,NumDrives
) SELECT DISTINCT CONVERT(varchar(50), @IntervalStartDateTime, 120), Vista.vDiskFailureRaw.DiskDataRowId, COUNT(*)
FROM Vista.vDiskFailureRaw
JOIN Vista.DiskData ON (Vista.vDiskFailureRaw.DiskDataRowId = Vista.DiskData.DiskDataRowId)
WHERE ([DateTime] >= CONVERT(varchar(50), @IntervalStartDateTime, 120))
AND ([DateTime] < CONVERT(varchar(50), @IntervalEndDateTime, 120))
GROUP BY Vista.vDiskFailureRaw.DiskDataRowId
INSERT Vista.vDiskFailureAggregationComputer (
[DateTime]
,ComputerDataRowId
,NumDrives
) SELECT DISTINCT CONVERT(varchar(50), @IntervalStartDateTime, 120), Vista.vDiskFailureRaw.ComputerDataRowId, COUNT(*)
FROM Vista.vDiskFailureRaw
WHERE ([DateTime] >= CONVERT(varchar(50), @IntervalStartDateTime, 120))
AND ([DateTime] < CONVERT(varchar(50), @IntervalEndDateTime, 120))
GROUP BY Vista.vDiskFailureRaw.ComputerDataRowId
SELECT @FreeSpaceGuid = vManagedEntityTypeProperty.PropertyGuid
FROM vManagedEntityTypeProperty
JOIN vManagedEntityType ON (vManagedEntityType.ManagedEntityTypeRowId = vManagedEntityTypeProperty.ManagedEntityTypeRowId)
WHERE vManagedEntityTypeProperty.PropertySystemName = 'FreeSpace'
AND vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Client.Vista.Aggregate.LogicalDisk'
SELECT @SizeGuid = vManagedEntityTypeProperty.PropertyGuid
FROM vManagedEntityTypeProperty
JOIN vManagedEntityType ON (vManagedEntityType.ManagedEntityTypeRowId = vManagedEntityTypeProperty.ManagedEntityTypeRowId)
WHERE vManagedEntityTypeProperty.PropertySystemName = 'Size'
AND vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Client.LogicalDisk'
SELECT @DeviceIdGuid = vManagedEntityTypeProperty.PropertyGuid
FROM vManagedEntityTypeProperty
JOIN vManagedEntityType ON (vManagedEntityType.ManagedEntityTypeRowId = vManagedEntityTypeProperty.ManagedEntityTypeRowId)
WHERE vManagedEntityTypeProperty.PropertySystemName = 'DeviceId'
AND vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.LogicalDevice'
SELECT @SystemDriveGuid = vManagedEntityTypeProperty.PropertyGuid
FROM vManagedEntityTypeProperty
JOIN vManagedEntityType ON (vManagedEntityType.ManagedEntityTypeRowId = vManagedEntityTypeProperty.ManagedEntityTypeRowId)
WHERE vManagedEntityTypeProperty.PropertySystemName = 'SystemDrive'
AND vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Client.Vista.Aggregate.LogicalDisk'
SELECT @PrincipalNameGuid = vManagedEntityTypeProperty.PropertyGuid
FROM vManagedEntityTypeProperty
JOIN vManagedEntityType ON (vManagedEntityType.ManagedEntityTypeRowId = vManagedEntityTypeProperty.ManagedEntityTypeRowId)
WHERE vManagedEntityTypeProperty.PropertySystemName = 'PrincipalName'
AND vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Computer'
CREATE TABLE #DiskUtilizationStaging
(
Size bigint NOT NULL
,FreeSpace bigint NOT NULL
)
INSERT #DiskUtilizationStaging (
Size
,FreeSpace
)
SELECT
MAX(CONVERT(bigint, CONVERT(nvarchar(256), vManagedEntityProperty.PropertyXml.query('data(/Root/Property[@Guid = sql:variable("@SizeGuid")])')))/100000000)
,MAX(CONVERT(bigint, CONVERT(nvarchar(256), vManagedEntityProperty.PropertyXml.query('data(/Root/Property[@Guid = sql:variable("@FreeSpaceGuid")])')))/100000000)
FROM vManagedEntityProperty
JOIN vManagedEntity ON (vManagedEntity.ManagedEntityRowId = vManagedEntityProperty.ManagedEntityRowId)
JOIN vManagedEntityType ON (vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId)
WHERE vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Client.Vista.LogicalDisk'
AND DATEDIFF(day, vManagedEntityProperty.FromDateTime, GETUTCDATE()) <= 7
AND CONVERT(nvarchar(256), vManagedEntityProperty.PropertyXml.query('data(/Root/Property[@Guid = sql:variable("@DeviceIdGuid")])')) =
CONVERT(nvarchar(256), vManagedEntityProperty.PropertyXml.query('data(/Root/Property[@Guid = sql:variable("@SystemDriveGuid")])'))
GROUP BY vManagedEntity.Path
DELETE FROM Vista.vDiskFailureAggregationUtilizationBySize
DELETE FROM Vista.vDiskFailureAggregationUtilizationByDrive
SET ROWCOUNT 40
INSERT Vista.vDiskFailureAggregationUtilizationBySize (
[DateTime]
,DriveSize
,DriveUtilizationAvg
,NumDrives
)
SELECT DISTINCT
CONVERT(varchar(50), @IntervalStartDateTime, 120)
,Size/100
,AVG(1.0 - CONVERT(float, FreeSpace)/CONVERT(float, Size))
,COUNT(*)
FROM #DiskUtilizationStaging
GROUP BY Size/100
DELETE FROM Vista.vDiskFailureAggregationUtilizationBySize WHERE DriveSize = 0
INSERT Vista.vDiskFailureAggregationUtilizationByDrive (
[DateTime]
,DriveUtilization
,NumDrives
)
SELECT DISTINCT
CONVERT(varchar(50), @IntervalStartDateTime, 120)
,CONVERT(int, 100*(1.0 - CONVERT(float, FreeSpace)/CONVERT(float, Size)))/10*10
,COUNT(*)
FROM #DiskUtilizationStaging
GROUP BY CONVERT(int, 100*(1.0 - CONVERT(float, FreeSpace)/CONVERT(float, Size)))/10*10
SET ROWCOUNT 0
DROP TABLE #DiskUtilizationStaging
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
SET @ErrorInd = 0
SELECT
@ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorLine = ERROR_LINE()
,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')
,@ErrorMessageText = ERROR_MESSAGE()
SET @ErrorInd = 1
END CATCH
-- report error if any
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 0
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'DiskFailureAggregationDelete')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.DiskFailureAggregationDelete AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.DiskFailureAggregationDelete
@DatasetId uniqueidentifier
,@AggregationTypeId tinyint
,@TableGuid uniqueidentifier
,@AggregationDateTime datetime
,@RowsDeleted int OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(4000)
SET @ErrorInd = 0
DECLARE
@Statement nvarchar(max)
BEGIN TRY
IF @AggregationTypeId = 30
BEGIN
SET @Statement = 'DELETE FROM Vista.vDiskFailureAggregationDisk'
+ ' WHERE ([DateTime] = CONVERT(datetime, ''' + CONVERT(varchar(50), @AggregationDateTime, 120) + ''', 120))'
EXECUTE(@Statement)
SET @RowsDeleted = @@ROWCOUNT
SET @Statement = 'DELETE FROM Vista.vDiskFailureAggregationComputer'
+ ' WHERE ([DateTime] = CONVERT(datetime, ''' + CONVERT(varchar(50), @AggregationDateTime, 120) + ''', 120))'
EXECUTE(@Statement)
SET @RowsDeleted = @@ROWCOUNT + @RowsDeleted
SET @Statement = 'DELETE FROM Vista.vDiskFailureAggregationUtilizationBySize'
+ ' WHERE ([DateTime] = CONVERT(datetime, ''' + CONVERT(varchar(50), @AggregationDateTime, 120) + ''', 120))'
EXECUTE(@Statement)
SET @RowsDeleted = @@ROWCOUNT + @RowsDeleted
SET @Statement = 'DELETE FROM Vista.vDiskFailureAggregationUtilizationByDrive'
+ ' WHERE ([DateTime] = CONVERT(datetime, ''' + CONVERT(varchar(50), @AggregationDateTime, 120) + ''', 120))'
EXECUTE(@Statement)
SET @RowsDeleted = @@ROWCOUNT + @RowsDeleted
END
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'DiskFailureMachineReport')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.DiskFailureMachineReport AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.DiskFailureMachineReport
AS
BEGIN
SET NOCOUNT ON
DECLARE @MaxSeries int
SET @MaxSeries = 10
SET ROWCOUNT @MaxSeries
DECLARE @series TABLE (series int, NumIssues int)
INSERT INTO @series SELECT ComputerDataRowId, SUM(NumDrives)
FROM Vista.vDiskFailureAggregationComputer
WHERE DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), DateTime), CURRENT_TIMESTAMP) <= 182
GROUP BY ComputerDataRowId
ORDER BY SUM(NumDrives) DESC
SET ROWCOUNT 0
INSERT INTO @avgWeek SELECT ComputerDataRowId, SUM(CONVERT(float, NumDrives))/26
FROM Vista.vDiskFailureAggregationComputer
WHERE DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), DateTime), CURRENT_TIMESTAMP) <= 182
AND EXISTS (SELECT * FROM @series AS SeriesList WHERE Vista.vDiskFailureAggregationComputer.ComputerDataRowId = SeriesList.series)
GROUP BY ComputerDataRowId
INSERT INTO @thisWeek SELECT ComputerDataRowId, SUM(NumDrives)
FROM Vista.vDiskFailureAggregationComputer
WHERE DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), DateTime), CURRENT_TIMESTAMP) <= 7
AND EXISTS (SELECT * FROM @series AS SeriesList WHERE Vista.vDiskFailureAggregationComputer.ComputerDataRowId = SeriesList.series)
GROUP BY ComputerDataRowId
INSERT INTO @thisWeek SELECT ComputerDataRowId, 0
FROM @avgWeek AS avgWeek
WHERE NOT EXISTS (SELECT * FROM @thisWeek AS thisWeek WHERE thisWeek.ComputerDataRowId = avgWeek.ComputerDataRowId)
INSERT INTO @lastWeek SELECT ComputerDataRowId, SUM(NumDrives)
FROM Vista.vDiskFailureAggregationComputer
WHERE DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), DateTime), CURRENT_TIMESTAMP) <= 14
AND DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), DateTime), CURRENT_TIMESTAMP) > 7
AND EXISTS (SELECT * FROM @series AS SeriesList WHERE Vista.vDiskFailureAggregationComputer.ComputerDataRowId = SeriesList.series)
GROUP BY ComputerDataRowId
INSERT INTO @lastWeek SELECT ComputerDataRowId, 0
FROM @avgWeek AS avgWeek
WHERE NOT EXISTS (SELECT * FROM @lastWeek AS lastWeek WHERE lastWeek.ComputerDataRowId = avgWeek.ComputerDataRowId)
SELECT thisWeek.NumCurrent, lastWeek.NumOld, avgWeek.NumAverage, Vista.ComputerData.Manufacturer + ' ' + Vista.ComputerData.Model AS Model
FROM @avgWeek AS avgWeek
JOIN @lastWeek AS lastWeek ON avgWeek.ComputerDataRowId = lastWeek.ComputerDataRowId
JOIN @thisWeek AS thisWeek ON avgWeek.ComputerDataRowId = thisWeek.ComputerDataRowId
JOIN Vista.ComputerData ON lastWeek.ComputerDataRowId = Vista.ComputerData.ComputerDataRowId
ORDER BY Vista.ComputerData.Manufacturer + ' ' + Vista.ComputerData.Model ASC
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'DiskFailureDiskReport')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.DiskFailureDiskReport AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.DiskFailureDiskReport
AS
BEGIN
SET NOCOUNT ON
DECLARE @MaxSeries int
SET @MaxSeries = 10
SET ROWCOUNT @MaxSeries
DECLARE @series TABLE (series int, NumIssues int)
INSERT INTO @series SELECT DiskDataRowId, SUM(NumDrives)
FROM Vista.vDiskFailureAggregationDisk
WHERE DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), DateTime), CURRENT_TIMESTAMP) <= 182
GROUP BY DiskDataRowId
ORDER BY SUM(NumDrives) DESC
SET ROWCOUNT 0
INSERT INTO @avgWeek SELECT DiskDataRowId, SUM(CONVERT(float, NumDrives))/26
FROM Vista.vDiskFailureAggregationDisk
WHERE DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), DateTime), CURRENT_TIMESTAMP) <= 182
AND EXISTS (SELECT * FROM @series AS SeriesList WHERE Vista.vDiskFailureAggregationDisk.DiskDataRowId = SeriesList.series)
GROUP BY DiskDataRowId
INSERT INTO @thisWeek SELECT DiskDataRowId, SUM(NumDrives)
FROM Vista.vDiskFailureAggregationDisk
WHERE DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), DateTime), CURRENT_TIMESTAMP) <= 7
AND EXISTS (SELECT * FROM @series AS SeriesList WHERE Vista.vDiskFailureAggregationDisk.DiskDataRowId = SeriesList.series)
GROUP BY DiskDataRowId
INSERT INTO @thisWeek SELECT DiskDataRowId, 0
FROM @avgWeek AS avgWeek
WHERE NOT EXISTS (SELECT * FROM @thisWeek AS thisWeek WHERE thisWeek.DiskDataRowId = avgWeek.DiskDataRowId)
INSERT INTO @lastWeek SELECT DiskDataRowId, SUM(NumDrives)
FROM Vista.vDiskFailureAggregationDisk
WHERE DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), DateTime), CURRENT_TIMESTAMP) <= 14
AND DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), DateTime), CURRENT_TIMESTAMP) > 7
AND EXISTS (SELECT * FROM @series AS SeriesList WHERE Vista.vDiskFailureAggregationDisk.DiskDataRowId = SeriesList.series)
GROUP BY DiskDataRowId
INSERT INTO @lastWeek SELECT DiskDataRowId, 0
FROM @avgWeek AS avgWeek
WHERE NOT EXISTS (SELECT * FROM @lastWeek AS lastWeek WHERE lastWeek.DiskDataRowId = avgWeek.DiskDataRowId)
SELECT thisWeek.NumCurrent, lastWeek.NumOld, avgWeek.NumAverage, Vista.DiskData.Model AS Model
FROM @avgWeek AS avgWeek
JOIN @lastWeek AS lastWeek ON avgWeek.DiskDataRowId = lastWeek.DiskDataRowId
JOIN @thisWeek AS thisWeek ON avgWeek.DiskDataRowId = thisWeek.DiskDataRowId
JOIN Vista.DiskData ON lastWeek.DiskDataRowId = Vista.DiskData.DiskDataRowId
ORDER BY Vista.DiskData.Model ASC
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'DiskHealthBarReport')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.DiskHealthBarReport AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.DiskHealthBarReport
AS
BEGIN
SET NOCOUNT ON
DECLARE @MaxSeries int
SET @MaxSeries = 10
DECLARE @series TABLE (series nvarchar(256), NumDrives int)
SELECT @ModelGuid = vManagedEntityTypeProperty.PropertyGuid
FROM vManagedEntityTypeProperty
JOIN vManagedEntityType ON (vManagedEntityType.ManagedEntityTypeRowId = vManagedEntityTypeProperty.ManagedEntityTypeRowId)
WHERE vManagedEntityTypeProperty.PropertySystemName = 'Model'
AND vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Client.PhysicalDisk'
SELECT @ContainsSystemDriveGuid = vManagedEntityTypeProperty.PropertyGuid
FROM vManagedEntityTypeProperty
JOIN vManagedEntityType ON (vManagedEntityType.ManagedEntityTypeRowId = vManagedEntityTypeProperty.ManagedEntityTypeRowId)
WHERE vManagedEntityTypeProperty.PropertySystemName = 'ContainsSystemDrive'
AND vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Client.Vista.Aggregate.PhysicalDisk'
DECLARE @DriveList TABLE (
ManagedEntityMonitorRowId int
,Model nvarchar(256)
,ContainsSystemDrive int
)
INSERT @DriveList
SELECT DISTINCT vManagedEntityMonitor.ManagedEntityMonitorRowId, CONVERT(nvarchar(256), vManagedEntityProperty.PropertyXml.query('data(/Root/Property[@Guid = sql:variable("@ModelGuid")])')),
CONVERT(int, CONVERT(nvarchar(256), vManagedEntityProperty.PropertyXml.query('data(/Root/Property[@Guid = sql:variable("@ContainsSystemDriveGuid")])')))
FROM vManagedEntityMonitor
JOIN vManagedEntity ON (vManagedEntity.ManagedEntityRowId = vManagedEntityMonitor.ManagedEntityRowId)
JOIN vManagedEntityType ON (vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId)
JOIN vMonitor ON (vMonitor.MonitorRowId = vManagedEntityMonitor.MonitorRowId)
JOIN vManagedEntityProperty ON (vManagedEntity.ManagedEntityRowId = vManagedEntityProperty.ManagedEntityRowId)
JOIN State.vStateDaily ON (vManagedEntityMonitor.ManagedEntityMonitorRowId = State.vStateDaily.ManagedEntityMonitorRowId)
WHERE (vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Client.Vista.Aggregate.PhysicalDisk')
AND (vMonitor.MonitorSystemName = 'System.Health.EntityState')
AND DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), State.vStateDaily.DateTime), CURRENT_TIMESTAMP) <= 1
INSERT @DriveList
SELECT DISTINCT vManagedEntityMonitor.ManagedEntityMonitorRowId, CONVERT(nvarchar(256), vManagedEntityProperty.PropertyXml.query('data(/Root/Property[@Guid = sql:variable("@ModelGuid")])')),
CONVERT(int, CONVERT(nvarchar(256), vManagedEntityProperty.PropertyXml.query('data(/Root/Property[@Guid = sql:variable("@ContainsSystemDriveGuid")])')))
FROM vManagedEntityMonitor
JOIN vManagedEntity ON (vManagedEntity.ManagedEntityRowId = vManagedEntityMonitor.ManagedEntityRowId)
JOIN vManagedEntityType ON (vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId)
JOIN vMonitor ON (vMonitor.MonitorRowId = vManagedEntityMonitor.MonitorRowId)
JOIN vManagedEntityProperty ON (vManagedEntity.ManagedEntityRowId = vManagedEntityProperty.ManagedEntityRowId)
WHERE (vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Client.Vista.Aggregate.PhysicalDisk')
AND (vMonitor.MonitorSystemName = 'System.Health.EntityState')
AND NOT EXISTS (SELECT * FROM @DriveList AS DriveList WHERE DriveList.ManagedEntityMonitorRowId = vManagedEntityMonitor.ManagedEntityMonitorRowId)
SET ROWCOUNT @MaxSeries
INSERT @series
SELECT DriveList.Model, COUNT(*)
FROM @DriveList AS DriveList
GROUP BY DriveList.Model
ORDER BY COUNT(*) DESC
SET ROWCOUNT 0
DELETE FROM @DriveList WHERE NOT EXISTS (SELECT * FROM @series AS SeriesList WHERE SeriesList.series = Model)
DECLARE @RedList TABLE (
NumDrives int
,Model nvarchar(256)
,ContainsSystemDrive int
)
DECLARE @YellowList TABLE (
NumDrives int
,Model nvarchar(256)
,ContainsSystemDrive int
)
DECLARE @TotalList TABLE (
NumDrives int
,Model nvarchar(256)
,ContainsSystemDrive int
)
INSERT @TotalList
SELECT COUNT(*), DriveList.Model, DriveList.ContainsSystemDrive
FROM @DriveList AS DriveList
GROUP BY DriveList.Model, DriveList.ContainsSystemDrive
INSERT @RedList
SELECT COUNT(*), DriveList.Model, DriveList.ContainsSystemDrive
FROM @DriveList AS DriveList
JOIN State.vStateDaily ON (DriveList.ManagedEntityMonitorRowId = State.vStateDaily.ManagedEntityMonitorRowId)
WHERE DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), DateTime), CURRENT_TIMESTAMP) <= 1
AND(State.vStateDaily.InRedStateMilliseconds > 0)
GROUP BY DriveList.Model, DriveList.ContainsSystemDrive
INSERT @RedList
SELECT 0, TotalList.Model, TotalList.ContainsSystemDrive
FROM @TotalList AS TotalList
WHERE NOT EXISTS (SELECT Model FROM @RedList AS RedList WHERE TotalList.Model = RedList.Model AND TotalList.ContainsSystemDrive = RedList.ContainsSystemDrive)
INSERT @YellowList
SELECT COUNT(*), DriveList.Model, DriveList.ContainsSystemDrive
FROM @DriveList AS DriveList
JOIN State.vStateDaily ON (DriveList.ManagedEntityMonitorRowId = State.vStateDaily.ManagedEntityMonitorRowId)
WHERE DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), DateTime), CURRENT_TIMESTAMP) <= 1
AND (State.vStateDaily.InRedStateMilliseconds = 0)
AND (State.vStateDaily.InYellowStateMilliseconds > 0)
GROUP BY DriveList.Model, DriveList.ContainsSystemDrive
INSERT @YellowList
SELECT 0, TotalList.Model, TotalList.ContainsSystemDrive
FROM @TotalList AS TotalList
WHERE NOT EXISTS (SELECT Model FROM @YellowList AS YellowList WHERE TotalList.Model = YellowList.Model AND TotalList.ContainsSystemDrive = YellowList.ContainsSystemDrive)
GROUP BY TotalList.Model, TotalList.ContainsSystemDrive
SELECT TotalList.NumDrives AS TotalDrives, YellowList.NumDrives AS YellowDrives, RedList.NumDrives AS RedDrives, TotalList.Model, TotalList.ContainsSystemDrive
FROM @TotalList AS TotalList
JOIN @YellowList AS YellowList ON (TotalList.Model = YellowList.Model AND TotalList.ContainsSystemDrive = YellowList.ContainsSystemDrive)
JOIN @RedList AS RedList ON (TotalList.Model = RedList.Model AND TotalList.ContainsSystemDrive = RedList.ContainsSystemDrive)
ORDER BY TotalList.Model DESC
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'DiskHealthUtilizationBySizeReport')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.DiskHealthUtilizationBySizeReport AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.DiskHealthUtilizationBySizeReport
AS
BEGIN
SET NOCOUNT ON
SELECT DriveSize*10 AS DriveSize, DriveUtilizationAvg*100 AS DriveUtilizationAvg
FROM Vista.vDiskFailureAggregationUtilizationBySize
WHERE DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), DateTime), CURRENT_TIMESTAMP) <= 1
ORDER BY 1.0/DriveSize
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'DiskHealthUtilizationByDriveReport')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.DiskHealthUtilizationByDriveReport AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.DiskHealthUtilizationByDriveReport
AS
BEGIN
SET NOCOUNT ON
SELECT NumDrives, DriveUtilization AS Utilization
FROM Vista.vDiskFailureAggregationUtilizationByDrive
WHERE DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), DateTime), CURRENT_TIMESTAMP) <= 1
ORDER BY Utilization
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'DiskHealthGreenDrilldownReport')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.DiskHealthGreenDrilldownReport AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.DiskHealthGreenDrilldownReport
AS
BEGIN
SET NOCOUNT ON
SELECT @ModelGuid = vManagedEntityTypeProperty.PropertyGuid
FROM vManagedEntityTypeProperty
JOIN vManagedEntityType ON (vManagedEntityType.ManagedEntityTypeRowId = vManagedEntityTypeProperty.ManagedEntityTypeRowId)
WHERE vManagedEntityTypeProperty.PropertySystemName = 'Model'
AND vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Client.PhysicalDisk'
SELECT @ContainsSystemDriveGuid = vManagedEntityTypeProperty.PropertyGuid
FROM vManagedEntityTypeProperty
JOIN vManagedEntityType ON (vManagedEntityType.ManagedEntityTypeRowId = vManagedEntityTypeProperty.ManagedEntityTypeRowId)
WHERE vManagedEntityTypeProperty.PropertySystemName = 'ContainsSystemDrive'
AND vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Client.Vista.Aggregate.PhysicalDisk'
SELECT @DeviceIdGuid = vManagedEntityTypeProperty.PropertyGuid
FROM vManagedEntityTypeProperty
JOIN vManagedEntityType ON (vManagedEntityType.ManagedEntityTypeRowId = vManagedEntityTypeProperty.ManagedEntityTypeRowId)
WHERE vManagedEntityTypeProperty.PropertySystemName = 'DeviceId'
AND vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.LogicalDevice'
DECLARE @DriveList TABLE (
ManagedEntityMonitorRowId int
,Model nvarchar(256)
,DeviceId nvarchar(256)
,PrincipalName nvarchar(256)
,ContainsSystemDrive int
)
INSERT @DriveList
SELECT DISTINCT vManagedEntityMonitor.ManagedEntityMonitorRowId,
CONVERT(nvarchar(256), vManagedEntityProperty.PropertyXml.query('data(/Root/Property[@Guid = sql:variable("@ModelGuid")])')),
CONVERT(nvarchar(256), vManagedEntityProperty.PropertyXml.query('data(/Root/Property[@Guid = sql:variable("@DeviceIdGuid")])')),
vManagedEntity.Path,
CONVERT(int, CONVERT(nvarchar(256), vManagedEntityProperty.PropertyXml.query('data(/Root/Property[@Guid = sql:variable("@ContainsSystemDriveGuid")])')))
FROM vManagedEntityMonitor
JOIN vManagedEntity ON (vManagedEntity.ManagedEntityRowId = vManagedEntityMonitor.ManagedEntityRowId)
JOIN vManagedEntityType ON (vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId)
JOIN vMonitor ON (vMonitor.MonitorRowId = vManagedEntityMonitor.MonitorRowId)
JOIN vManagedEntityProperty ON (vManagedEntity.ManagedEntityRowId = vManagedEntityProperty.ManagedEntityRowId)
WHERE (vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Client.Vista.Aggregate.PhysicalDisk')
AND (vMonitor.MonitorSystemName = 'System.Health.EntityState')
SELECT DriveList.PrincipalName, DriveList.DeviceId, DriveList.Model, DriveList.ContainsSystemDrive
FROM @DriveList AS DriveList
WHERE NOT EXISTS (SELECT *
FROM State.vStateDaily
WHERE DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), DateTime), CURRENT_TIMESTAMP) <= 1
AND State.vStateDaily.ManagedEntityMonitorRowId = DriveList.ManagedEntityMonitorRowId)
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'DiskHealthYellowDrilldownReport')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.DiskHealthYellowDrilldownReport AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.DiskHealthYellowDrilldownReport
AS
BEGIN
SET NOCOUNT ON
SELECT @ModelGuid = vManagedEntityTypeProperty.PropertyGuid
FROM vManagedEntityTypeProperty
JOIN vManagedEntityType ON (vManagedEntityType.ManagedEntityTypeRowId = vManagedEntityTypeProperty.ManagedEntityTypeRowId)
WHERE vManagedEntityTypeProperty.PropertySystemName = 'Model'
AND vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Client.PhysicalDisk'
SELECT @ContainsSystemDriveGuid = vManagedEntityTypeProperty.PropertyGuid
FROM vManagedEntityTypeProperty
JOIN vManagedEntityType ON (vManagedEntityType.ManagedEntityTypeRowId = vManagedEntityTypeProperty.ManagedEntityTypeRowId)
WHERE vManagedEntityTypeProperty.PropertySystemName = 'ContainsSystemDrive'
AND vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Client.Vista.Aggregate.PhysicalDisk'
SELECT @DeviceIdGuid = vManagedEntityTypeProperty.PropertyGuid
FROM vManagedEntityTypeProperty
JOIN vManagedEntityType ON (vManagedEntityType.ManagedEntityTypeRowId = vManagedEntityTypeProperty.ManagedEntityTypeRowId)
WHERE vManagedEntityTypeProperty.PropertySystemName = 'DeviceId'
AND vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.LogicalDevice'
DECLARE @DriveList TABLE (
ManagedEntityMonitorRowId int
,Model nvarchar(256)
,DeviceId nvarchar(256)
,PrincipalName nvarchar(256)
,ContainsSystemDrive int
)
INSERT @DriveList
SELECT DISTINCT vManagedEntityMonitor.ManagedEntityMonitorRowId,
CONVERT(nvarchar(256), vManagedEntityProperty.PropertyXml.query('data(/Root/Property[@Guid = sql:variable("@ModelGuid")])')),
CONVERT(nvarchar(256), vManagedEntityProperty.PropertyXml.query('data(/Root/Property[@Guid = sql:variable("@DeviceIdGuid")])')),
vManagedEntity.Path,
CONVERT(int, CONVERT(nvarchar(256), vManagedEntityProperty.PropertyXml.query('data(/Root/Property[@Guid = sql:variable("@ContainsSystemDriveGuid")])')))
FROM vManagedEntityMonitor
JOIN vManagedEntity ON (vManagedEntity.ManagedEntityRowId = vManagedEntityMonitor.ManagedEntityRowId)
JOIN vManagedEntityType ON (vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId)
JOIN vMonitor ON (vMonitor.MonitorRowId = vManagedEntityMonitor.MonitorRowId)
JOIN vManagedEntityProperty ON (vManagedEntity.ManagedEntityRowId = vManagedEntityProperty.ManagedEntityRowId)
WHERE (vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Client.Vista.Aggregate.PhysicalDisk')
AND (vMonitor.MonitorSystemName = 'System.Health.EntityState')
SELECT DriveList.PrincipalName, DriveList.DeviceId, DriveList.Model, DriveList.ContainsSystemDrive
FROM @DriveList AS DriveList
JOIN State.vStateDaily ON (DriveList.ManagedEntityMonitorRowId = State.vStateDaily.ManagedEntityMonitorRowId)
WHERE DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), DateTime), CURRENT_TIMESTAMP) <= 1
AND (State.vStateDaily.InRedStateMilliseconds = 0)
AND (State.vStateDaily.InYellowStateMilliseconds > 0)
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'DiskHealthRedDrilldownReport')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.DiskHealthRedDrilldownReport AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.DiskHealthRedDrilldownReport
AS
BEGIN
SET NOCOUNT ON
SELECT @ModelGuid = vManagedEntityTypeProperty.PropertyGuid
FROM vManagedEntityTypeProperty
JOIN vManagedEntityType ON (vManagedEntityType.ManagedEntityTypeRowId = vManagedEntityTypeProperty.ManagedEntityTypeRowId)
WHERE vManagedEntityTypeProperty.PropertySystemName = 'Model'
AND vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Client.PhysicalDisk'
SELECT @ContainsSystemDriveGuid = vManagedEntityTypeProperty.PropertyGuid
FROM vManagedEntityTypeProperty
JOIN vManagedEntityType ON (vManagedEntityType.ManagedEntityTypeRowId = vManagedEntityTypeProperty.ManagedEntityTypeRowId)
WHERE vManagedEntityTypeProperty.PropertySystemName = 'ContainsSystemDrive'
AND vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Client.Vista.Aggregate.PhysicalDisk'
SELECT @DeviceIdGuid = vManagedEntityTypeProperty.PropertyGuid
FROM vManagedEntityTypeProperty
JOIN vManagedEntityType ON (vManagedEntityType.ManagedEntityTypeRowId = vManagedEntityTypeProperty.ManagedEntityTypeRowId)
WHERE vManagedEntityTypeProperty.PropertySystemName = 'DeviceId'
AND vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.LogicalDevice'
DECLARE @DriveList TABLE (
ManagedEntityMonitorRowId int
,Model nvarchar(256)
,DeviceId nvarchar(256)
,PrincipalName nvarchar(256)
,ContainsSystemDrive int
)
INSERT @DriveList
SELECT DISTINCT vManagedEntityMonitor.ManagedEntityMonitorRowId,
CONVERT(nvarchar(256), vManagedEntityProperty.PropertyXml.query('data(/Root/Property[@Guid = sql:variable("@ModelGuid")])')),
CONVERT(nvarchar(256), vManagedEntityProperty.PropertyXml.query('data(/Root/Property[@Guid = sql:variable("@DeviceIdGuid")])')),
vManagedEntity.Path,
CONVERT(int, CONVERT(nvarchar(256), vManagedEntityProperty.PropertyXml.query('data(/Root/Property[@Guid = sql:variable("@ContainsSystemDriveGuid")])')))
FROM vManagedEntityMonitor
JOIN vManagedEntity ON (vManagedEntity.ManagedEntityRowId = vManagedEntityMonitor.ManagedEntityRowId)
JOIN vManagedEntityType ON (vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId)
JOIN vMonitor ON (vMonitor.MonitorRowId = vManagedEntityMonitor.MonitorRowId)
JOIN vManagedEntityProperty ON (vManagedEntity.ManagedEntityRowId = vManagedEntityProperty.ManagedEntityRowId)
WHERE (vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Client.Vista.Aggregate.PhysicalDisk')
AND (vMonitor.MonitorSystemName = 'System.Health.EntityState')
SELECT DriveList.PrincipalName, DriveList.DeviceId, DriveList.Model, DriveList.ContainsSystemDrive
FROM @DriveList AS DriveList
JOIN State.vStateDaily ON (DriveList.ManagedEntityMonitorRowId = State.vStateDaily.ManagedEntityMonitorRowId)
WHERE DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), DateTime), CURRENT_TIMESTAMP) <= 1
AND(State.vStateDaily.InRedStateMilliseconds > 0)
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'DiskUtilizationDrilldownReport')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.DiskUtilizationDrilldownReport AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.DiskUtilizationDrilldownReport
AS
BEGIN
SET NOCOUNT ON
DECLARE
@SizeGuid uniqueidentifier
,@FreeSpaceGuid uniqueidentifier
,@DeviceIdGuid uniqueidentifier
,@SystemDriveGuid uniqueidentifier
,@PrincipalNameGuid uniqueidentifier
SELECT @FreeSpaceGuid = vManagedEntityTypeProperty.PropertyGuid
FROM vManagedEntityTypeProperty
JOIN vManagedEntityType ON (vManagedEntityType.ManagedEntityTypeRowId = vManagedEntityTypeProperty.ManagedEntityTypeRowId)
WHERE vManagedEntityTypeProperty.PropertySystemName = 'FreeSpace'
AND vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Client.Vista.Aggregate.LogicalDisk'
SELECT @SizeGuid = vManagedEntityTypeProperty.PropertyGuid
FROM vManagedEntityTypeProperty
JOIN vManagedEntityType ON (vManagedEntityType.ManagedEntityTypeRowId = vManagedEntityTypeProperty.ManagedEntityTypeRowId)
WHERE vManagedEntityTypeProperty.PropertySystemName = 'Size'
AND vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Client.LogicalDisk'
SELECT @DeviceIdGuid = vManagedEntityTypeProperty.PropertyGuid
FROM vManagedEntityTypeProperty
JOIN vManagedEntityType ON (vManagedEntityType.ManagedEntityTypeRowId = vManagedEntityTypeProperty.ManagedEntityTypeRowId)
WHERE vManagedEntityTypeProperty.PropertySystemName = 'DeviceId'
AND vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.LogicalDevice'
SELECT @SystemDriveGuid = vManagedEntityTypeProperty.PropertyGuid
FROM vManagedEntityTypeProperty
JOIN vManagedEntityType ON (vManagedEntityType.ManagedEntityTypeRowId = vManagedEntityTypeProperty.ManagedEntityTypeRowId)
WHERE vManagedEntityTypeProperty.PropertySystemName = 'SystemDrive'
AND vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Client.Vista.Aggregate.LogicalDisk'
SELECT @PrincipalNameGuid = vManagedEntityTypeProperty.PropertyGuid
FROM vManagedEntityTypeProperty
JOIN vManagedEntityType ON (vManagedEntityType.ManagedEntityTypeRowId = vManagedEntityTypeProperty.ManagedEntityTypeRowId)
WHERE vManagedEntityTypeProperty.PropertySystemName = 'PrincipalName'
AND vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Computer'
SELECT
MAX(CONVERT(bigint, CONVERT(nvarchar(256), vManagedEntityProperty.PropertyXml.query('data(/Root/Property[@Guid = sql:variable("@SizeGuid")])')))/1000000000) AS Size
,MAX(CONVERT(bigint, CONVERT(nvarchar(256), vManagedEntityProperty.PropertyXml.query('data(/Root/Property[@Guid = sql:variable("@FreeSpaceGuid")])')))/1000000000) AS FreeSpace
,MAX(CONVERT(nvarchar(256), vManagedEntityProperty.PropertyXml.query('data(/Root/Property[@Guid = sql:variable("@DeviceIdGuid")])'))) AS DeviceId
,vManagedEntity.Path AS PrincipalName
FROM vManagedEntityProperty
JOIN vManagedEntity ON (vManagedEntity.ManagedEntityRowId = vManagedEntityProperty.ManagedEntityRowId)
JOIN vManagedEntityType ON (vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId)
WHERE vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Client.Vista.LogicalDisk'
AND DATEDIFF(day, vManagedEntityProperty.FromDateTime, GETUTCDATE()) <= 7
AND CONVERT(bigint, CONVERT(nvarchar(256), vManagedEntityProperty.PropertyXml.query('data(/Root/Property[@Guid = sql:variable("@SizeGuid")])'))) > 0
AND CONVERT(nvarchar(256), vManagedEntityProperty.PropertyXml.query('data(/Root/Property[@Guid = sql:variable("@DeviceIdGuid")])')) =
CONVERT(nvarchar(256), vManagedEntityProperty.PropertyXml.query('data(/Root/Property[@Guid = sql:variable("@SystemDriveGuid")])'))
GROUP BY vManagedEntity.Path
ORDER BY MAX(CONVERT(bigint, CONVERT(nvarchar(256), vManagedEntityProperty.PropertyXml.query('data(/Root/Property[@Guid = sql:variable("@FreeSpaceGuid")])')))/1000000000) ASC
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'DiskFailureDrilldownReport')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.DiskFailureDrilldownReport AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.DiskFailureDrilldownReport
AS
BEGIN
SET NOCOUNT ON
SELECT DateTime, PrincipalName, PublisherName, EventId, Manufacturer, Model, Size, ContainsSystemDrive
FROM Vista.vDiskFailureRaw
JOIN Vista.DiskData ON Vista.vDiskFailureRaw.DiskDataRowId = Vista.DiskData.DiskDataRowId
JOIN Vista.EventData ON Vista.vDiskFailureRaw.EventDataRowId = Vista.EventData.EventDataRowId
WHERE DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), DateTime), CURRENT_TIMESTAMP) <= 7
END
GO</Install> <Uninstall>-- drop SPs
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'DiskFailureGroom')
BEGIN
EXECUTE ('DROP PROCEDURE dbo.DiskFailureGroom')
END
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'DiskFailureInsert')
BEGIN
EXECUTE ('DROP PROCEDURE dbo.DiskFailureInsert')
END
-- drop Tables
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DiskData' AND TABLE_SCHEMA = 'Vista')
BEGIN
DROP TABLE Vista.DiskData
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'EventData' AND TABLE_SCHEMA = 'Vista')
BEGIN
DROP TABLE Vista.EventData
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ComputerData' AND TABLE_SCHEMA = 'Vista')
BEGIN
DROP TABLE Vista.ComputerData
END
-- delete std dataset related data
EXEC StandardDatasetDelete '$Config/DatasetId$'
GO</Uninstall> <Upgrade>SET NOCOUNT ON</Upgrade>
</DataWarehouseDataSet>
Home