IF EXISTS (SELECT * FROM StandardDatasetAggregationStorage WHERE DatasetId IN (SELECT DatasetId FROM StandardDataset WHERE SchemaName = 'Win8') AND BaseTableName = 'Win8MemoryHealthRaw')
BEGIN
SET @DatasetId = (SELECT DatasetId FROM StandardDatasetAggregationStorage WHERE DatasetId IN (SELECT DatasetId FROM StandardDataset WHERE SchemaName = 'Win8') AND BaseTableName = 'Win8MemoryHealthRaw')
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 'Win8MemoryAggregate'
END
,CASE AggregationType
WHEN 'Raw' THEN NULL
ELSE 'Win8MemoryAggregationDelete'
END
,'Win8MemoryGroom'
,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
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] = 'Win8')
BEGIN
EXECUTE('CREATE SCHEMA Win8')
END
GO
-- change schema ownership to allow index rebuild and reorganize
ALTER AUTHORIZATION ON SCHEMA::Win8 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 = 'Win8')
BEGIN
DROP TABLE Win8.EventData
END
GO
CREATE TABLE Win8.EventData
(
EventDataRowId int NOT NULL IDENTITY(1, 1)
,EventId int NOT NULL
,PublisherName nvarchar(256) NOT NULL
,CONSTRAINT PK_Win8_EventRule PRIMARY KEY CLUSTERED (EventDataRowId)
)
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ComputerData' AND TABLE_SCHEMA = 'Win8')
BEGIN
DROP TABLE Win8.ComputerData
END
GO
CREATE TABLE Win8.ComputerData
(
ComputerDataRowId int NOT NULL IDENTITY(1, 1)
,Manufacturer nvarchar(256) NOT NULL
,Model nvarchar(256) NOT NULL
,CONSTRAINT PK_Win8_ComputerRule PRIMARY KEY CLUSTERED (ComputerDataRowId)
)
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win8MemoryAggregationDelete')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Win8MemoryAggregationDelete AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.Win8MemoryAggregationDelete
@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 Win8.vWin8MemoryHealthAggregationRAM'
+ ' WHERE ([DateTime] = CONVERT(datetime, ''' + CONVERT(varchar(50), @AggregationDateTime, 120) + ''', 120))'
EXECUTE(@Statement)
SET @RowsDeleted = @@ROWCOUNT
SET @Statement = 'DELETE FROM Win8.vWin8MemoryHealthAggregationComputer'
+ ' WHERE ([DateTime] = CONVERT(datetime, ''' + CONVERT(varchar(50), @AggregationDateTime, 120) + ''', 120))'
EXECUTE(@Statement)
SET @RowsDeleted = @@ROWCOUNT + @RowsDeleted
SET @Statement = 'DELETE FROM Win8.vWin8MemoryHealthAggregationApp'
+ ' 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 = 'Win8MemoryAggregate')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Win8MemoryAggregate AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.Win8MemoryAggregate
@DatasetId uniqueidentifier
,@AggregationTypeId int
,@InsertTableGuid varchar(256)
,@IntervalStartDateTime datetime
,@IntervalEndDateTime datetime
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
DECLARE
@CompManufacturerGuid uniqueidentifier
,@CompModelGuid uniqueidentifier
,@RAMGuid uniqueidentifier
SELECT @RAMGuid = vManagedEntityTypeProperty.PropertyGuid
FROM vManagedEntityTypeProperty
JOIN vManagedEntityType ON (vManagedEntityType.ManagedEntityTypeRowId = vManagedEntityTypeProperty.ManagedEntityTypeRowId)
WHERE vManagedEntityTypeProperty.PropertySystemName = 'RAM'
AND vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Client.Win8.Aggregate.Computer'
SELECT @CompManufacturerGuid = vManagedEntityTypeProperty.PropertyGuid
FROM vManagedEntityTypeProperty
JOIN vManagedEntityType ON (vManagedEntityType.ManagedEntityTypeRowId = vManagedEntityTypeProperty.ManagedEntityTypeRowId)
WHERE vManagedEntityTypeProperty.PropertySystemName = 'Manufacturer'
AND vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Client.Win8.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.Win8.Aggregate.Computer'
INSERT @RAMTable SELECT COUNT(ManagedEntityRowId), RAM
FROM @NoXml
GROUP BY RAM
INSERT @ComputerTable SELECT COUNT(ManagedEntityRowId), ComputerDataRowId
FROM @NoXml
GROUP BY ComputerDataRowId
INSERT Win8.vWin8MemoryHealthAggregationRAM (
[DateTime]
,NumEvents
,NumComputers
,RAM
) SELECT DISTINCT CONVERT(varchar(50), @IntervalStartDateTime, 120), COUNT(*), MAX(RAMTable.NumComputers), Win8.vWin8MemoryHealthRaw.RAM
FROM Win8.vWin8MemoryHealthRaw
JOIN @RAMTable As RAMTable ON (RAMTable.RAM = Win8.vWin8MemoryHealthRaw.RAM)
WHERE ([DateTime] >= CONVERT(varchar(50), @IntervalStartDateTime, 120))
AND ([DateTime] < CONVERT(varchar(50), @IntervalEndDateTime, 120))
GROUP BY Win8.vWin8MemoryHealthRaw.RAM
INSERT Win8.vWin8MemoryHealthAggregationComputer (
[DateTime]
,ComputerDataRowId
,NumEvents
,NumComputers
) SELECT DISTINCT CONVERT(varchar(50), @IntervalStartDateTime, 120), Win8.vWin8MemoryHealthRaw.ComputerDataRowId, COUNT(*), MAX(ComputerTable.NumComputers)
FROM Win8.vWin8MemoryHealthRaw
JOIN @ComputerTable As ComputerTable ON (ComputerTable.ComputerDataRowId = Win8.vWin8MemoryHealthRaw.ComputerDataRowId)
WHERE ([DateTime] >= CONVERT(varchar(50), @IntervalStartDateTime, 120))
AND ([DateTime] < CONVERT(varchar(50), @IntervalEndDateTime, 120))
GROUP BY Win8.vWin8MemoryHealthRaw.ComputerDataRowId
CREATE TABLE #MemoryAppStaging
(
AppName nvarchar(256) NOT NULL
,App_Mem bigint NOT NULL
)
INSERT #MemoryAppStaging (
AppName
,App_Mem
) SELECT App1, App1_Mem
FROM Win8.vWin8MemoryHealthRaw
WHERE ([DateTime] >= CONVERT(varchar(50), @IntervalStartDateTime, 120))
AND ([DateTime] < CONVERT(varchar(50), @IntervalEndDateTime, 120))
INSERT #MemoryAppStaging (
AppName
,App_Mem
) SELECT App2, App2_Mem
FROM Win8.vWin8MemoryHealthRaw
WHERE ([DateTime] >= CONVERT(varchar(50), @IntervalStartDateTime, 120))
AND ([DateTime] < CONVERT(varchar(50), @IntervalEndDateTime, 120))
INSERT #MemoryAppStaging (
AppName
,App_Mem
) SELECT App3, App3_Mem
FROM Win8.vWin8MemoryHealthRaw
WHERE ([DateTime] >= CONVERT(varchar(50), @IntervalStartDateTime, 120))
AND ([DateTime] < CONVERT(varchar(50), @IntervalEndDateTime, 120))
INSERT Win8.vWin8MemoryHealthAggregationApp (
[DateTime]
,AppName
,AvgApp_Mem
,NumInstances
) SELECT DISTINCT CONVERT(varchar(50), @IntervalStartDateTime, 120), AppName, AVG(App_Mem), COUNT(*)
FROM #MemoryAppStaging
GROUP BY AppName
DROP TABLE #MemoryAppStaging
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 = 'Win8MemoryGroom')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Win8MemoryGroom AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.Win8MemoryGroom
@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 Win8.vWin8MemoryHealthRaw'
+ ' WHERE ([DateTime] < CONVERT(datetime, ''' + CONVERT(varchar(50), @CutoffDateTime, 120) + ''', 120))'
EXECUTE (@Statement)
SET @RowsDeleted = @@ROWCOUNT
SET @Statement = 'DELETE TOP (' + CAST(@MaxRowsToGroom AS varchar(15)) + ')'
+ ' FROM Win8.vWin8MemoryFailureRaw'
+ ' WHERE ([DateTime] < CONVERT(datetime, ''' + CONVERT(varchar(50), @CutoffDateTime, 120) + ''', 120))'
SET @RowsDeleted = @@ROWCOUNT + @RowsDeleted
EXECUTE (@Statement)
END
IF @AggregationTypeId = 30
BEGIN
SET @Statement = 'DELETE FROM Win8.vWin8MemoryHealthAggregationRAM'
+ ' WHERE ([DateTime] < CONVERT(datetime, ''' + CONVERT(varchar(50), @CutoffDateTime, 120) + ''', 120))'
EXECUTE(@Statement)
SET @RowsDeleted = @@ROWCOUNT
SET @Statement = 'DELETE FROM Win8.vWin8MemoryHealthAggregationComputer'
+ ' WHERE ([DateTime] < CONVERT(datetime, ''' + CONVERT(varchar(50), @CutoffDateTime, 120) + ''', 120))'
EXECUTE(@Statement)
SET @RowsDeleted = @@ROWCOUNT + @RowsDeleted
SET @Statement = 'DELETE FROM Win8.vWin8MemoryHealthAggregationApp'
+ ' 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 = 'Win8MemoryFailureInsert')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Win8MemoryFailureInsert AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.Win8MemoryFailureInsert
@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
FROM OPENXML(@XmlDocHandle, 'Root/Item', 2) WITH (
EventId int
,PrincipalName nvarchar(256)
,PublisherName nvarchar(256)
) xml
WHILE EXISTS (SELECT * FROM @events)
BEGIN
SELECT @RowId = RowId, @EventId = EventId, @PrincipalName = PrincipalName, @PublisherName = PublisherName
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.Win8.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.Win8.Aggregate.Computer'
SELECT @RAMGuid = vManagedEntityTypeProperty.PropertyGuid
FROM vManagedEntityTypeProperty
JOIN vManagedEntityType ON (vManagedEntityType.ManagedEntityTypeRowId = vManagedEntityTypeProperty.ManagedEntityTypeRowId)
WHERE vManagedEntityTypeProperty.PropertySystemName = 'RAM'
AND vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Client.Win8.Aggregate.Computer'
DECLARE @RAM int
,@CompManufacturer nvarchar(256)
,@CompModel nvarchar(256)
,@CompRowId int
SELECT @CompRowId = ManagedEntityRowId
FROM vManagedEntity
WHERE vManagedEntity.Name = @PrincipalName
IF @RAM > 0 AND (@CompManufacturer <> '' OR @CompModel <> '')
BEGIN
SET @RAM = (@RAM - (@RAM % 128)) + (@RAM % 128) / 64 * 128
DECLARE @EventDataRowId int
,@ComputerDataRowId int
IF NOT EXISTS (SELECT * FROM Win8.ComputerData WHERE Manufacturer = @CompManufacturer AND Model = @CompModel)
BEGIN
INSERT INTO Win8.ComputerData (Manufacturer, Model) VALUES (@CompManufacturer, @CompModel)
END
IF NOT EXISTS (SELECT * FROM Win8.EventData WHERE EventId = @EventId AND PublisherName = @PublisherName)
BEGIN
INSERT INTO Win8.EventData (EventId, PublisherName) VALUES (@EventId, @PublisherName)
END
SELECT @EventDataRowId = EventDataRowId FROM Win8.EventData WHERE EventId = @EventId AND PublisherName = @PublisherName
SELECT @ComputerDataRowId = ComputerDataRowId FROM Win8.ComputerData WHERE Manufacturer = @CompManufacturer AND Model = @CompModel
INSERT INTO Win8.vWin8MemoryFailureRaw (DateTime, EventDataRowId, ComputerDataRowId, PrincipalName, RAM) VALUES (GETUTCDATE(), @EventDataRowId, @ComputerDataRowId, @PrincipalName, @RAM)
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 Win8MemoryFailureInsert TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win8MemoryHealthInsert')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Win8MemoryHealthInsert AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.Win8MemoryHealthInsert
@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.Details
FROM OPENXML(@XmlDocHandle, 'Root/Item', 2) WITH (
EventId int
,PrincipalName nvarchar(256)
,PublisherName nvarchar(256)
,Details xml
) xml
WHILE EXISTS (SELECT * FROM @events)
BEGIN
SELECT @RowId = RowId, @EventId = EventId, @PrincipalName = PrincipalName, @PublisherName = PublisherName, @Details = Details
FROM @events
WHERE RowId = (SELECT MAX(RowId) FROM @events)
SET @App1 = CONVERT(nvarchar(256), @Details.query('declare namespace RADAR="http://www.microsoft.com/Windows/Resource/Exhaustion/Detector/Events";data(/Details/RADAR:MemoryExhaustionInfo/RADAR:ProcessInfo/RADAR:Process_1/RADAR:Name)'))
SET @App2 = CONVERT(nvarchar(256), @Details.query('declare namespace RADAR="http://www.microsoft.com/Windows/Resource/Exhaustion/Detector/Events";data(/Details/RADAR:MemoryExhaustionInfo/RADAR:ProcessInfo/RADAR:Process_2/RADAR:Name)'))
SET @App3 = CONVERT(nvarchar(256), @Details.query('declare namespace RADAR="http://www.microsoft.com/Windows/Resource/Exhaustion/Detector/Events";data(/Details/RADAR:MemoryExhaustionInfo/RADAR:ProcessInfo/RADAR:Process_3/RADAR:Name)'))
SET @App1_Mem = CONVERT(bigint, CONVERT(nvarchar(256), @Details.query('declare namespace RADAR="http://www.microsoft.com/Windows/Resource/Exhaustion/Detector/Events";data(/Details/RADAR:MemoryExhaustionInfo/RADAR:ProcessInfo/RADAR:Process_1/RADAR:CommitCharge)')))/1000000
SET @App2_Mem = CONVERT(bigint, CONVERT(nvarchar(256), @Details.query('declare namespace RADAR="http://www.microsoft.com/Windows/Resource/Exhaustion/Detector/Events";data(/Details/RADAR:MemoryExhaustionInfo/RADAR:ProcessInfo/RADAR:Process_2/RADAR:CommitCharge)')))/1000000
SET @App3_Mem = CONVERT(bigint, CONVERT(nvarchar(256), @Details.query('declare namespace RADAR="http://www.microsoft.com/Windows/Resource/Exhaustion/Detector/Events";data(/Details/RADAR:MemoryExhaustionInfo/RADAR:ProcessInfo/RADAR:Process_3/RADAR:CommitCharge)')))/1000000
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.Win8.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.Win8.Aggregate.Computer'
SELECT @RAMGuid = vManagedEntityTypeProperty.PropertyGuid
FROM vManagedEntityTypeProperty
JOIN vManagedEntityType ON (vManagedEntityType.ManagedEntityTypeRowId = vManagedEntityTypeProperty.ManagedEntityTypeRowId)
WHERE vManagedEntityTypeProperty.PropertySystemName = 'RAM'
AND vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Client.Win8.Aggregate.Computer'
DECLARE @RAM int
,@CompManufacturer nvarchar(256)
,@CompModel nvarchar(256)
,@CompRowId int
SELECT @CompRowId = ManagedEntityRowId
FROM vManagedEntity
WHERE vManagedEntity.Name = @PrincipalName
DECLARE @EventDataRowId int
,@ComputerDataRowId int
IF NOT EXISTS (SELECT * FROM Win8.ComputerData WHERE Manufacturer = @CompManufacturer AND Model = @CompModel)
BEGIN
INSERT INTO Win8.ComputerData (Manufacturer, Model) VALUES (@CompManufacturer, @CompModel)
END
IF NOT EXISTS (SELECT * FROM Win8.EventData WHERE EventId = @EventId AND PublisherName = @PublisherName)
BEGIN
INSERT INTO Win8.EventData (EventId, PublisherName) VALUES (@EventId, @PublisherName)
END
SELECT @EventDataRowId = EventDataRowId FROM Win8.EventData WHERE EventId = @EventId AND PublisherName = @PublisherName
SELECT @ComputerDataRowId = ComputerDataRowId FROM Win8.ComputerData WHERE Manufacturer = @CompManufacturer AND Model = @CompModel
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 Win8MemoryHealthInsert TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win8MemoryRAMReport')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Win8MemoryRAMReport AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.Win8MemoryRAMReport
AS
BEGIN
SET NOCOUNT ON
SET @CurTime = CURRENT_TIMESTAMP
DECLARE @MaxSeries int
SET @MaxSeries = 10
SET ROWCOUNT @MaxSeries
DECLARE @series TABLE (series int, NumIssues int)
INSERT INTO @series SELECT RAM, SUM(NumComputers)
FROM Win8.vWin8MemoryHealthAggregationRAM
WHERE DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), DateTime), CURRENT_TIMESTAMP) <= 91
GROUP BY RAM
ORDER BY SUM(CONVERT(float, NumEvents)/CONVERT(float, NumComputers)) DESC
SET ROWCOUNT 0
INSERT INTO @RAMAmounts SELECT DISTINCT RAM
FROM Win8.vWin8MemoryHealthAggregationRAM
WHERE DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), DateTime), CURRENT_TIMESTAMP) <= 91
AND EXISTS (SELECT * FROM @series AS SeriesList WHERE SeriesList.series = Win8.vWin8MemoryHealthAggregationRAM.RAM)
SET @WeekCounter = -1
WHILE @WeekCounter > -14
BEGIN
INSERT INTO @Weeks VALUES (DATEADD(week, @WeekCounter, @CurTime))
SET @WeekCounter = @WeekCounter - 1
END
SELECT @RAMAmount = MIN(RAM) FROM @RAMAmounts
WHILE @RAMAmount IS NOT NULL
BEGIN
SELECT @Week = MIN(ListedWeek) FROM @Weeks
WHILE @Week IS NOT NULL
BEGIN
SELECT @AvgIssues = SUM(CONVERT(float, NumEvents)/CONVERT(float, NumComputers))
FROM Win8.vWin8MemoryHealthAggregationRAM
WHERE (RAM = @RAMAmount)
AND DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), @Week), DateTime) <= 7
AND DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), @Week), DateTime) > 0
IF @AvgIssues IS NULL
BEGIN
SET @AvgIssues = 0
END
INSERT INTO @ExhaustionMatrix VALUES (DATEADD(week, 1, @Week), CONVERT(nvarchar(256), @RAMAmount), @AvgIssues)
SELECT @Week = MIN(ListedWeek) FROM @Weeks WHERE ListedWeek > @Week
END
SELECT @RAMAmount = MIN(RAM) FROM @RAMAmounts WHERE RAM > @RAMAmount
END
SELECT * FROM @ExhaustionMatrix ORDER BY Type ASC
END
GO
GRANT EXECUTE ON dbo.Win8MemoryRAMReport TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win8MemoryMachineReport')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Win8MemoryMachineReport AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.Win8MemoryMachineReport
AS
BEGIN
SET NOCOUNT ON
SET @CurTime = CURRENT_TIMESTAMP
DECLARE @MaxSeries int
SET @MaxSeries = 10
SET ROWCOUNT @MaxSeries
DECLARE @series TABLE (series int, NumIssues int)
INSERT INTO @series SELECT ComputerDataRowId, SUM(NumComputers)
FROM Win8.vWin8MemoryHealthAggregationComputer
WHERE DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), DateTime), CURRENT_TIMESTAMP) <= 91
GROUP BY ComputerDataRowId
ORDER BY SUM(CONVERT(float, NumEvents)/CONVERT(float, NumComputers)) DESC
SET ROWCOUNT 0
INSERT INTO @MachineTypes SELECT DISTINCT (Manufacturer + ' ' + Model)
FROM Win8.vWin8MemoryHealthAggregationComputer
JOIN Win8.ComputerData ON (Win8.vWin8MemoryHealthAggregationComputer.ComputerDataRowId = Win8.ComputerData.ComputerDataRowId)
WHERE DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), DateTime), CURRENT_TIMESTAMP) <= 91
AND EXISTS (SELECT * FROM @series AS SeriesList WHERE SeriesList.series = Win8.vWin8MemoryHealthAggregationComputer.ComputerDataRowId)
SET @WeekCounter = -1
WHILE @WeekCounter > -14
BEGIN
INSERT INTO @Weeks VALUES (DATEADD(week, @WeekCounter, @CurTime))
SET @WeekCounter = @WeekCounter - 1
END
SELECT @MachineType = MIN(MachineType) FROM @MachineTypes
WHILE @MachineType IS NOT NULL
BEGIN
SELECT @Week = MIN(ListedWeek) FROM @Weeks
WHILE @Week IS NOT NULL
BEGIN
SELECT @AvgIssues = SUM(CONVERT(float, NumEvents)/CONVERT(float, NumComputers))
FROM Win8.vWin8MemoryHealthAggregationComputer
JOIN Win8.ComputerData ON (Win8.vWin8MemoryHealthAggregationComputer.ComputerDataRowId = Win8.ComputerData.ComputerDataRowId)
WHERE ((Manufacturer + ' ' + Model) = @MachineType)
AND DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), @Week), DateTime) <= 7
AND DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), @Week), DateTime) > 0
IF @AvgIssues IS NULL
BEGIN
SET @AvgIssues = 0
END
INSERT INTO @ExhaustionMatrix VALUES (DATEADD(week, 1, @Week), @MachineType, @AvgIssues)
SELECT @Week = MIN(ListedWeek) FROM @Weeks WHERE ListedWeek > @Week
END
SELECT @MachineType = MIN(MachineType) FROM @MachineTypes WHERE MachineType > @MachineType
END
SELECT * FROM @ExhaustionMatrix ORDER BY Type ASC
END
GO
GRANT EXECUTE ON dbo.Win8MemoryMachineReport TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win8MemorySufficiencyReport')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Win8MemorySufficiencyReport AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.Win8MemorySufficiencyReport
AS
BEGIN
SET NOCOUNT ON
SELECT PrincipalName, Manufacturer + ' ' + Model AS Type, RAM, COUNT(*) AS NumInstances
FROM Win8.vWin8MemoryHealthRaw
JOIN Win8.ComputerData ON (Win8.vWin8MemoryHealthRaw.ComputerDataRowId = Win8.ComputerData.ComputerDataRowId)
WHERE DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), DateTime), CURRENT_TIMESTAMP) <= 7
GROUP BY PrincipalName, RAM, Manufacturer + ' ' + Model
ORDER BY COUNT (*) DESC
END
GO
GRANT EXECUTE ON dbo.Win8MemorySufficiencyReport TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win8MemoryAppMemoryReport')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Win8MemoryAppMemoryReport AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.Win8MemoryAppMemoryReport
AS
BEGIN
SET NOCOUNT ON
DECLARE @MaxSeries int
SET @MaxSeries = 10
SET ROWCOUNT @MaxSeries
SELECT SUM(AvgApp_Mem*NumInstances)/SUM(NumInstances) AS AvgApp_Mem, AppName
FROM Win8.vWin8MemoryHealthAggregationApp
WHERE DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), DateTime), CURRENT_TIMESTAMP) <= 90
GROUP BY AppName
ORDER BY SUM(AvgApp_Mem*NumInstances)/SUM(NumInstances) ASC
SET ROWCOUNT 0
END
GO
GRANT EXECUTE ON dbo.Win8MemoryAppMemoryReport TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win8MemoryAppFrequencyReport')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Win8MemoryAppFrequencyReport AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.Win8MemoryAppFrequencyReport
AS
BEGIN
SET NOCOUNT ON
DECLARE @MaxSeries int
SET @MaxSeries = 10
SET ROWCOUNT @MaxSeries
SELECT SUM(NumInstances) AS Instances, AppName
FROM Win8.vWin8MemoryHealthAggregationApp
WHERE DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), DateTime), CURRENT_TIMESTAMP) <= 90
GROUP BY AppName
ORDER BY SUM(NumInstances) ASC
SET ROWCOUNT 0
END
GO
GRANT EXECUTE ON dbo.Win8MemoryAppFrequencyReport TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win8MemoryAppDrilldownReport')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Win8MemoryAppDrilldownReport AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.Win8MemoryAppDrilldownReport
AS
BEGIN
SET NOCOUNT ON
INSERT INTO @LatestData SELECT Win8.vWin8MemoryHealthAggregationApp.NumInstances, Win8.vWin8MemoryHealthAggregationApp.AvgApp_Mem, Win8.vWin8MemoryHealthAggregationApp.AppName
FROM Win8.vWin8MemoryHealthAggregationApp
WHERE DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), DateTime), CURRENT_TIMESTAMP) <= 1
INSERT INTO @LatestData SELECT 0, 0, Win8.vWin8MemoryHealthAggregationApp.AppName
FROM Win8.vWin8MemoryHealthAggregationApp
WHERE DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), DateTime), CURRENT_TIMESTAMP) <= 90
AND NOT EXISTS (SELECT * FROM @LatestData AS LatestData WHERE LatestData.AppName = Win8.vWin8MemoryHealthAggregationApp.AppName)
GROUP BY AppName
SELECT Win8.vWin8MemoryHealthAggregationApp.AppName, AVG(LatestData.Events) AS EventsToday, AVG(LatestData.Memory) AS MemoryToday, SUM(Win8.vWin8MemoryHealthAggregationApp.NumInstances)/90 AS AvgEvents, AVG(Win8.vWin8MemoryHealthAggregationApp.AvgApp_Mem) AS AvgMemory
FROM Win8.vWin8MemoryHealthAggregationApp
JOIN @LatestData AS LatestData ON (LatestData.AppName = Win8.vWin8MemoryHealthAggregationApp.AppName)
WHERE DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), DateTime), CURRENT_TIMESTAMP) <= 90
GROUP BY Win8.vWin8MemoryHealthAggregationApp.AppName
END
GO
GRANT EXECUTE ON dbo.Win8MemoryAppDrilldownReport TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win8FrequencyRAMReport')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Win8FrequencyRAMReport AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.Win8FrequencyRAMReport
AS
BEGIN
SET NOCOUNT ON
DECLARE @MaxSeries int
SET @MaxSeries = 9
SET ROWCOUNT @MaxSeries
DECLARE @series TABLE (series int, NumIssues int)
INSERT INTO @series SELECT RAM, COUNT(*)
FROM Win8.vWin8MemoryHealthRaw
WHERE DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), DateTime), CURRENT_TIMESTAMP) <= 7
GROUP BY RAM
ORDER BY COUNT(*) DESC
SET ROWCOUNT 0
INSERT @FrequencyTable (
NumExhaustions
,RAM
,PrincipalName
) SELECT COUNT(*), CONVERT(nvarchar(256), RAM), PrincipalName
FROM Win8.vWin8MemoryHealthRaw
WHERE DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), DateTime), CURRENT_TIMESTAMP) <= 7
AND EXISTS (SELECT * FROM @series AS SeriesList WHERE SeriesList.series = Win8.vWin8MemoryHealthRaw.RAM)
GROUP BY RAM, PrincipalName
SELECT RAM AS Type, NumExhaustions, COUNT(PrincipalName) AS NumMachines
FROM @FrequencyTable
GROUP BY RAM, NumExhaustions
ORDER BY NumExhaustions ASC
END
GO
GRANT EXECUTE ON dbo.Win8FrequencyRAMReport TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win8FrequencyMachineReport')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Win8FrequencyMachineReport AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.Win8FrequencyMachineReport
AS
BEGIN
SET NOCOUNT ON
DECLARE @MaxSeries int
SET @MaxSeries = 9
SET ROWCOUNT @MaxSeries
DECLARE @series TABLE (series int, NumIssues int)
INSERT INTO @series SELECT ComputerDataRowId, COUNT(*)
FROM Win8.vWin8MemoryHealthRaw
WHERE DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), DateTime), CURRENT_TIMESTAMP) <= 7
GROUP BY ComputerDataRowId
ORDER BY COUNT(*) DESC
SET ROWCOUNT 0
INSERT @FrequencyTable (
NumExhaustions
,Machine
,PrincipalName
) SELECT COUNT(*), (Manufacturer + ' ' + Model), PrincipalName
FROM Win8.vWin8MemoryHealthRaw
JOIN Win8.ComputerData ON (Win8.vWin8MemoryHealthRaw.ComputerDataRowId = Win8.ComputerData.ComputerDataRowId)
WHERE DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), DateTime), CURRENT_TIMESTAMP) <= 7
AND EXISTS (SELECT * FROM @series AS SeriesList WHERE SeriesList.series = Win8.vWin8MemoryHealthRaw.ComputerDataRowId)
GROUP BY (Manufacturer + ' ' + Model), PrincipalName
SELECT Machine AS Type, NumExhaustions, COUNT(PrincipalName) AS NumMachines
FROM @FrequencyTable
GROUP BY Machine, NumExhaustions
ORDER BY NumExhaustions ASC
END
GO
GRANT EXECUTE ON dbo.Win8FrequencyMachineReport TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win8MemoryFailureDrilldownReport')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Win8MemoryFailureDrilldownReport AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.Win8MemoryFailureDrilldownReport
AS
BEGIN
SET NOCOUNT ON
SELECT DateTime, PrincipalName, PublisherName, EventId, Manufacturer, Model, RAM
FROM Win8.vWin8MemoryFailureRaw
JOIN Win8.ComputerData ON Win8.vWin8MemoryFailureRaw.ComputerDataRowId = Win8.ComputerData.ComputerDataRowId
JOIN Win8.EventData ON Win8.vWin8MemoryFailureRaw.EventDataRowId = Win8.EventData.EventDataRowId
WHERE DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), DateTime), CURRENT_TIMESTAMP) <= 7
END
GO
GRANT EXECUTE ON dbo.Win8MemoryFailureDrilldownReport TO OpsMgrReader
GO </Install>
<Uninstall/> <Upgrade> SET NOCOUNT ON
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win8FrequencyMachineReport')
BEGIN
GRANT EXECUTE ON dbo.Win8FrequencyMachineReport TO OpsMgrReader
END
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win8FrequencyRAMReport')
BEGIN
GRANT EXECUTE ON dbo.Win8FrequencyRAMReport TO OpsMgrReader
END
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win8MemoryAppDrilldownReport')
BEGIN
GRANT EXECUTE ON dbo.Win8MemoryAppDrilldownReport TO OpsMgrReader
END
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win8MemoryAppFrequencyReport')
BEGIN
GRANT EXECUTE ON dbo.Win8MemoryAppFrequencyReport TO OpsMgrReader
END
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win8MemoryAppMemoryReport')
BEGIN
GRANT EXECUTE ON dbo.Win8MemoryAppMemoryReport TO OpsMgrReader
END
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win8MemoryFailureDrilldownReport')
BEGIN
GRANT EXECUTE ON dbo.Win8MemoryFailureDrilldownReport TO OpsMgrReader
END
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win8MemoryMachineReport')
BEGIN
GRANT EXECUTE ON dbo.Win8MemoryMachineReport TO OpsMgrReader
END
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win8MemoryRAMReport')
BEGIN
GRANT EXECUTE ON dbo.Win8MemoryRAMReport TO OpsMgrReader
END
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win8MemorySufficiencyReport')
BEGIN
GRANT EXECUTE ON dbo.Win8MemorySufficiencyReport TO OpsMgrReader
END
GO </Upgrade>
</DataWarehouseDataSet>