IF EXISTS (SELECT * FROM StandardDatasetAggregationStorage WHERE DatasetId IN (SELECT DatasetId FROM StandardDataset WHERE SchemaName = 'Win10') AND BaseTableName = 'Win10MemoryHealthRaw')
BEGIN
SET @DatasetId = (SELECT DatasetId FROM StandardDatasetAggregationStorage WHERE DatasetId IN (SELECT DatasetId FROM StandardDataset WHERE SchemaName = 'Win10') AND BaseTableName = 'Win10MemoryHealthRaw')
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 'Win10MemoryAggregate'
END
,CASE AggregationType
WHEN 'Raw' THEN NULL
ELSE 'Win10MemoryAggregationDelete'
END
,'Win10MemoryGroom'
,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] = 'Win10')
BEGIN
EXECUTE('CREATE SCHEMA Win10')
END
GO
-- change schema ownership to allow index rebuild and reorganize
ALTER AUTHORIZATION ON SCHEMA::Win10 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 = 'Win10')
BEGIN
DROP TABLE Win10.EventData
END
GO
CREATE TABLE Win10.EventData
(
EventDataRowId int NOT NULL IDENTITY(1, 1)
,EventId int NOT NULL
,PublisherName nvarchar(256) NOT NULL
,CONSTRAINT PK_Win10_EventRule PRIMARY KEY CLUSTERED (EventDataRowId)
)
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ComputerData' AND TABLE_SCHEMA = 'Win10')
BEGIN
DROP TABLE Win10.ComputerData
END
GO
CREATE TABLE Win10.ComputerData
(
ComputerDataRowId int NOT NULL IDENTITY(1, 1)
,Manufacturer nvarchar(256) NOT NULL
,Model nvarchar(256) NOT NULL
,CONSTRAINT PK_Win10_ComputerRule PRIMARY KEY CLUSTERED (ComputerDataRowId)
)
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win10MemoryAggregationDelete')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Win10MemoryAggregationDelete AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.Win10MemoryAggregationDelete
@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 Win10.vWin10MemoryHealthAggregationRAM'
+ ' WHERE ([DateTime] = CONVERT(datetime, ''' + CONVERT(varchar(50), @AggregationDateTime, 120) + ''', 120))'
EXECUTE(@Statement)
SET @RowsDeleted = @@ROWCOUNT
SET @Statement = 'DELETE FROM Win10.vWin10MemoryHealthAggregationComputer'
+ ' WHERE ([DateTime] = CONVERT(datetime, ''' + CONVERT(varchar(50), @AggregationDateTime, 120) + ''', 120))'
EXECUTE(@Statement)
SET @RowsDeleted = @@ROWCOUNT + @RowsDeleted
SET @Statement = 'DELETE FROM Win10.vWin10MemoryHealthAggregationApp'
+ ' 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 = 'Win10MemoryAggregate')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Win10MemoryAggregate AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.Win10MemoryAggregate
@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.Win10.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.Win10.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.Win10.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 Win10.vWin10MemoryHealthAggregationRAM (
[DateTime]
,NumEvents
,NumComputers
,RAM
) SELECT DISTINCT CONVERT(varchar(50), @IntervalStartDateTime, 120), COUNT(*), MAX(RAMTable.NumComputers), Win10.vWin10MemoryHealthRaw.RAM
FROM Win10.vWin10MemoryHealthRaw
JOIN @RAMTable As RAMTable ON (RAMTable.RAM = Win10.vWin10MemoryHealthRaw.RAM)
WHERE ([DateTime] >= CONVERT(varchar(50), @IntervalStartDateTime, 120))
AND ([DateTime] < CONVERT(varchar(50), @IntervalEndDateTime, 120))
GROUP BY Win10.vWin10MemoryHealthRaw.RAM
INSERT Win10.vWin10MemoryHealthAggregationComputer (
[DateTime]
,ComputerDataRowId
,NumEvents
,NumComputers
) SELECT DISTINCT CONVERT(varchar(50), @IntervalStartDateTime, 120), Win10.vWin10MemoryHealthRaw.ComputerDataRowId, COUNT(*), MAX(ComputerTable.NumComputers)
FROM Win10.vWin10MemoryHealthRaw
JOIN @ComputerTable As ComputerTable ON (ComputerTable.ComputerDataRowId = Win10.vWin10MemoryHealthRaw.ComputerDataRowId)
WHERE ([DateTime] >= CONVERT(varchar(50), @IntervalStartDateTime, 120))
AND ([DateTime] < CONVERT(varchar(50), @IntervalEndDateTime, 120))
GROUP BY Win10.vWin10MemoryHealthRaw.ComputerDataRowId
CREATE TABLE #MemoryAppStaging
(
AppName nvarchar(256) NOT NULL
,App_Mem bigint NOT NULL
)
INSERT #MemoryAppStaging (
AppName
,App_Mem
) SELECT App1, App1_Mem
FROM Win10.vWin10MemoryHealthRaw
WHERE ([DateTime] >= CONVERT(varchar(50), @IntervalStartDateTime, 120))
AND ([DateTime] < CONVERT(varchar(50), @IntervalEndDateTime, 120))
INSERT #MemoryAppStaging (
AppName
,App_Mem
) SELECT App2, App2_Mem
FROM Win10.vWin10MemoryHealthRaw
WHERE ([DateTime] >= CONVERT(varchar(50), @IntervalStartDateTime, 120))
AND ([DateTime] < CONVERT(varchar(50), @IntervalEndDateTime, 120))
INSERT #MemoryAppStaging (
AppName
,App_Mem
) SELECT App3, App3_Mem
FROM Win10.vWin10MemoryHealthRaw
WHERE ([DateTime] >= CONVERT(varchar(50), @IntervalStartDateTime, 120))
AND ([DateTime] < CONVERT(varchar(50), @IntervalEndDateTime, 120))
INSERT Win10.vWin10MemoryHealthAggregationApp (
[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 = 'Win10MemoryGroom')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Win10MemoryGroom AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.Win10MemoryGroom
@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 Win10.vWin10MemoryHealthRaw'
+ ' 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 Win10.vWin10MemoryFailureRaw'
+ ' 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 Win10.vWin10MemoryHealthAggregationRAM'
+ ' WHERE ([DateTime] < CONVERT(datetime, ''' + CONVERT(varchar(50), @CutoffDateTime, 120) + ''', 120))'
EXECUTE(@Statement)
SET @RowsDeleted = @@ROWCOUNT
SET @Statement = 'DELETE FROM Win10.vWin10MemoryHealthAggregationComputer'
+ ' WHERE ([DateTime] < CONVERT(datetime, ''' + CONVERT(varchar(50), @CutoffDateTime, 120) + ''', 120))'
EXECUTE(@Statement)
SET @RowsDeleted = @@ROWCOUNT + @RowsDeleted
SET @Statement = 'DELETE FROM Win10.vWin10MemoryHealthAggregationApp'
+ ' 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 = 'Win10MemoryFailureInsert')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Win10MemoryFailureInsert AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.Win10MemoryFailureInsert
@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.Win10.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.Win10.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.Win10.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 Win10.ComputerData WHERE Manufacturer = @CompManufacturer AND Model = @CompModel)
BEGIN
INSERT INTO Win10.ComputerData (Manufacturer, Model) VALUES (@CompManufacturer, @CompModel)
END
IF NOT EXISTS (SELECT * FROM Win10.EventData WHERE EventId = @EventId AND PublisherName = @PublisherName)
BEGIN
INSERT INTO Win10.EventData (EventId, PublisherName) VALUES (@EventId, @PublisherName)
END
SELECT @EventDataRowId = EventDataRowId FROM Win10.EventData WHERE EventId = @EventId AND PublisherName = @PublisherName
SELECT @ComputerDataRowId = ComputerDataRowId FROM Win10.ComputerData WHERE Manufacturer = @CompManufacturer AND Model = @CompModel
INSERT INTO Win10.vWin10MemoryFailureRaw (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 Win10MemoryFailureInsert TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win10MemoryHealthInsert')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Win10MemoryHealthInsert AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.Win10MemoryHealthInsert
@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.Win10.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.Win10.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.Win10.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 Win10.ComputerData WHERE Manufacturer = @CompManufacturer AND Model = @CompModel)
BEGIN
INSERT INTO Win10.ComputerData (Manufacturer, Model) VALUES (@CompManufacturer, @CompModel)
END
IF NOT EXISTS (SELECT * FROM Win10.EventData WHERE EventId = @EventId AND PublisherName = @PublisherName)
BEGIN
INSERT INTO Win10.EventData (EventId, PublisherName) VALUES (@EventId, @PublisherName)
END
SELECT @EventDataRowId = EventDataRowId FROM Win10.EventData WHERE EventId = @EventId AND PublisherName = @PublisherName
SELECT @ComputerDataRowId = ComputerDataRowId FROM Win10.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 Win10MemoryHealthInsert TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win10MemoryRAMReport')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Win10MemoryRAMReport AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.Win10MemoryRAMReport
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 Win10.vWin10MemoryHealthAggregationRAM
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 Win10.vWin10MemoryHealthAggregationRAM
WHERE DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), DateTime), CURRENT_TIMESTAMP) <= 91
AND EXISTS (SELECT * FROM @series AS SeriesList WHERE SeriesList.series = Win10.vWin10MemoryHealthAggregationRAM.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 Win10.vWin10MemoryHealthAggregationRAM
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.Win10MemoryRAMReport TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win10MemoryMachineReport')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Win10MemoryMachineReport AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.Win10MemoryMachineReport
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 Win10.vWin10MemoryHealthAggregationComputer
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 Win10.vWin10MemoryHealthAggregationComputer
JOIN Win10.ComputerData ON (Win10.vWin10MemoryHealthAggregationComputer.ComputerDataRowId = Win10.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 = Win10.vWin10MemoryHealthAggregationComputer.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 Win10.vWin10MemoryHealthAggregationComputer
JOIN Win10.ComputerData ON (Win10.vWin10MemoryHealthAggregationComputer.ComputerDataRowId = Win10.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.Win10MemoryMachineReport TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win10MemorySufficiencyReport')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Win10MemorySufficiencyReport AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.Win10MemorySufficiencyReport
AS
BEGIN
SET NOCOUNT ON
SELECT PrincipalName, Manufacturer + ' ' + Model AS Type, RAM, COUNT(*) AS NumInstances
FROM Win10.vWin10MemoryHealthRaw
JOIN Win10.ComputerData ON (Win10.vWin10MemoryHealthRaw.ComputerDataRowId = Win10.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.Win10MemorySufficiencyReport TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win10MemoryAppMemoryReport')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Win10MemoryAppMemoryReport AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.Win10MemoryAppMemoryReport
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 Win10.vWin10MemoryHealthAggregationApp
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.Win10MemoryAppMemoryReport TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win10MemoryAppFrequencyReport')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Win10MemoryAppFrequencyReport AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.Win10MemoryAppFrequencyReport
AS
BEGIN
SET NOCOUNT ON
DECLARE @MaxSeries int
SET @MaxSeries = 10
SET ROWCOUNT @MaxSeries
SELECT SUM(NumInstances) AS Instances, AppName
FROM Win10.vWin10MemoryHealthAggregationApp
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.Win10MemoryAppFrequencyReport TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win10MemoryAppDrilldownReport')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Win10MemoryAppDrilldownReport AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.Win10MemoryAppDrilldownReport
AS
BEGIN
SET NOCOUNT ON
INSERT INTO @LatestData SELECT Win10.vWin10MemoryHealthAggregationApp.NumInstances, Win10.vWin10MemoryHealthAggregationApp.AvgApp_Mem, Win10.vWin10MemoryHealthAggregationApp.AppName
FROM Win10.vWin10MemoryHealthAggregationApp
WHERE DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), DateTime), CURRENT_TIMESTAMP) <= 1
INSERT INTO @LatestData SELECT 0, 0, Win10.vWin10MemoryHealthAggregationApp.AppName
FROM Win10.vWin10MemoryHealthAggregationApp
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 = Win10.vWin10MemoryHealthAggregationApp.AppName)
GROUP BY AppName
SELECT Win10.vWin10MemoryHealthAggregationApp.AppName, AVG(LatestData.Events) AS EventsToday, AVG(LatestData.Memory) AS MemoryToday, SUM(Win10.vWin10MemoryHealthAggregationApp.NumInstances)/90 AS AvgEvents, AVG(Win10.vWin10MemoryHealthAggregationApp.AvgApp_Mem) AS AvgMemory
FROM Win10.vWin10MemoryHealthAggregationApp
JOIN @LatestData AS LatestData ON (LatestData.AppName = Win10.vWin10MemoryHealthAggregationApp.AppName)
WHERE DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), DateTime), CURRENT_TIMESTAMP) <= 90
GROUP BY Win10.vWin10MemoryHealthAggregationApp.AppName
END
GO
GRANT EXECUTE ON dbo.Win10MemoryAppDrilldownReport TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win10FrequencyRAMReport')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Win10FrequencyRAMReport AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.Win10FrequencyRAMReport
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 Win10.vWin10MemoryHealthRaw
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 Win10.vWin10MemoryHealthRaw
WHERE DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), DateTime), CURRENT_TIMESTAMP) <= 7
AND EXISTS (SELECT * FROM @series AS SeriesList WHERE SeriesList.series = Win10.vWin10MemoryHealthRaw.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.Win10FrequencyRAMReport TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win10FrequencyMachineReport')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Win10FrequencyMachineReport AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.Win10FrequencyMachineReport
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 Win10.vWin10MemoryHealthRaw
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 Win10.vWin10MemoryHealthRaw
JOIN Win10.ComputerData ON (Win10.vWin10MemoryHealthRaw.ComputerDataRowId = Win10.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 = Win10.vWin10MemoryHealthRaw.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.Win10FrequencyMachineReport TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win10MemoryFailureDrilldownReport')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Win10MemoryFailureDrilldownReport AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.Win10MemoryFailureDrilldownReport
AS
BEGIN
SET NOCOUNT ON
SELECT DateTime, PrincipalName, PublisherName, EventId, Manufacturer, Model, RAM
FROM Win10.vWin10MemoryFailureRaw
JOIN Win10.ComputerData ON Win10.vWin10MemoryFailureRaw.ComputerDataRowId = Win10.ComputerData.ComputerDataRowId
JOIN Win10.EventData ON Win10.vWin10MemoryFailureRaw.EventDataRowId = Win10.EventData.EventDataRowId
WHERE DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), DateTime), CURRENT_TIMESTAMP) <= 7
END
GO
GRANT EXECUTE ON dbo.Win10MemoryFailureDrilldownReport TO OpsMgrReader
GO </Install>
<Uninstall/> <Upgrade> SET NOCOUNT ON
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win10FrequencyMachineReport')
BEGIN
GRANT EXECUTE ON dbo.Win10FrequencyMachineReport TO OpsMgrReader
END
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win10FrequencyRAMReport')
BEGIN
GRANT EXECUTE ON dbo.Win10FrequencyRAMReport TO OpsMgrReader
END
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win10MemoryAppDrilldownReport')
BEGIN
GRANT EXECUTE ON dbo.Win10MemoryAppDrilldownReport TO OpsMgrReader
END
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win10MemoryAppFrequencyReport')
BEGIN
GRANT EXECUTE ON dbo.Win10MemoryAppFrequencyReport TO OpsMgrReader
END
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win10MemoryAppMemoryReport')
BEGIN
GRANT EXECUTE ON dbo.Win10MemoryAppMemoryReport TO OpsMgrReader
END
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win10MemoryFailureDrilldownReport')
BEGIN
GRANT EXECUTE ON dbo.Win10MemoryFailureDrilldownReport TO OpsMgrReader
END
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win10MemoryMachineReport')
BEGIN
GRANT EXECUTE ON dbo.Win10MemoryMachineReport TO OpsMgrReader
END
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win10MemoryRAMReport')
BEGIN
GRANT EXECUTE ON dbo.Win10MemoryRAMReport TO OpsMgrReader
END
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win10MemorySufficiencyReport')
BEGIN
GRANT EXECUTE ON dbo.Win10MemorySufficiencyReport TO OpsMgrReader
END
GO </Upgrade>
</DataWarehouseDataSet>
Home