IF EXISTS (SELECT * FROM StandardDatasetAggregationStorage WHERE DatasetId IN (SELECT DatasetId FROM StandardDataset WHERE SchemaName = 'Win7') AND BaseTableName = 'Win7ShellPerfRaw')
BEGIN
SET @DatasetId = (SELECT DatasetId FROM StandardDatasetAggregationStorage WHERE DatasetId IN (SELECT DatasetId FROM StandardDataset WHERE SchemaName = 'Win7') AND BaseTableName = 'Win7ShellPerfRaw')
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 'Win7ShellPerfAggregate'
END
,CASE AggregationType
WHEN 'Raw' THEN NULL
ELSE 'Win7ShellPerfAggregationDelete'
END
,'Win7ShellPerfGroom'
,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] = 'Win7')
BEGIN
EXECUTE('CREATE SCHEMA Win7')
END
GO
-- change schema ownership to allow index rebuild and reorganize
ALTER AUTHORIZATION ON SCHEMA::Win7 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 = 'Win7')
BEGIN
DROP TABLE Win7.EventData
END
GO
CREATE TABLE Win7.EventData
(
EventDataRowId int NOT NULL IDENTITY(1, 1)
,EventId int NOT NULL
,PublisherName nvarchar(256) NOT NULL
,CONSTRAINT PK_Win7_EventRule PRIMARY KEY CLUSTERED (EventDataRowId)
)
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ComputerData' AND TABLE_SCHEMA = 'Win7')
BEGIN
DROP TABLE Win7.ComputerData
END
GO
CREATE TABLE Win7.ComputerData
(
ComputerDataRowId int NOT NULL IDENTITY(1, 1)
,Manufacturer nvarchar(256) NOT NULL
,Model nvarchar(256) NOT NULL
,CONSTRAINT PK_Win7_ComputerRule PRIMARY KEY CLUSTERED (ComputerDataRowId)
)
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ShellPerfStaging' AND TABLE_SCHEMA = 'Win7')
BEGIN
DROP TABLE Win7.ShellPerfStaging
END
GO
CREATE TABLE Win7.ShellPerfStaging
(
[DateTime] datetime NOT NULL
,AppName nvarchar(256)
,CorrelationId uniqueidentifier NOT NULL
,EventDataRowId int NOT NULL
)
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win7ShellPerfAggregationDelete')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Win7ShellPerfAggregationDelete AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.Win7ShellPerfAggregationDelete
@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 Win7.vWin7ShellPerfAggregationComputer'
+ ' WHERE ([DateTime] = CONVERT(datetime, ''' + CONVERT(varchar(50), @AggregationDateTime, 120) + ''', 120))'
EXECUTE(@Statement)
SET @RowsDeleted = @@ROWCOUNT
SET @Statement = 'DELETE FROM Win7.vWin7ShellPerfAggregationRootCause'
+ ' 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 = 'Win7ShellPerfAggregate')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Win7ShellPerfAggregate AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.Win7ShellPerfAggregate
@DatasetId uniqueidentifier
,@AggregationTypeId int
,@InsertTableGuid varchar(256)
,@IntervalStartDateTime datetime
,@IntervalEndDateTime datetime
AS
BEGIN
SET NOCOUNT ON
-- Check staging table for any orphaned root causes
INSERT Win7.vWin7ShellPerfRootCause (
[DateTime]
,AppName
,Win7ShellPerfRawRowId
,EventDataRowId
) SELECT Win7.ShellPerfStaging.[DateTime], Win7.ShellPerfStaging.AppName, Win7.vWin7ShellPerfRaw.Win7ShellPerfRawRowId, Win7.ShellPerfStaging.EventDataRowId
FROM Win7.ShellPerfStaging
JOIN Win7.vWin7ShellPerfRaw ON (Win7.ShellPerfStaging.CorrelationId = Win7.vWin7ShellPerfRaw.CorrelationId)
DELETE FROM Win7.ShellPerfStaging
BEGIN TRY
DECLARE
@TotalNumComputers int
,@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.Win7.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.Win7.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.Win7.Aggregate.Computer'
INSERT @NumComputers (
NumComputers
,ComputerDataRowId
) SELECT COUNT(vManagedEntityProperty.ManagedEntityRowId)
,Win7.ComputerData.ComputerDataRowId
FROM vManagedEntityProperty
JOIN Win7.ComputerData ON (Win7.ComputerData.Manufacturer = CONVERT(nvarchar(256), vManagedEntityProperty.PropertyXml.query('data(/Root/Property[@Guid = sql:variable("@CompManufacturerGuid")])'))
AND Win7.ComputerData.Model = CONVERT(nvarchar(256), vManagedEntityProperty.PropertyXml.query('data(/Root/Property[@Guid = sql:variable("@CompModelGuid")])')))
WHERE (CONVERT(bigint, CONVERT(nvarchar(256), vManagedEntityProperty.PropertyXml.query('data(/Root/Property[@Guid = sql:variable("@RAMGuid")])'))) > 0)
GROUP BY Win7.ComputerData.ComputerDataRowId
INSERT Win7.vWin7ShellPerfAggregationComputer (
[DateTime]
,NumEvents
,NumComputers
,ComputerDataRowId
) SELECT DISTINCT CONVERT(varchar(50), @IntervalStartDateTime, 120), COUNT(*), AVG(ComputerTable.NumComputers), Win7.vWin7ShellPerfRaw.ComputerDataRowId
FROM Win7.vWin7ShellPerfRaw
JOIN @NumComputers AS ComputerTable ON (ComputerTable.ComputerDataRowId = Win7.vWin7ShellPerfRaw.ComputerDataRowId)
WHERE ([DateTime] >= CONVERT(varchar(50), @IntervalStartDateTime, 120))
AND ([DateTime] < CONVERT(varchar(50), @IntervalEndDateTime, 120))
GROUP BY Win7.vWin7ShellPerfRaw.ComputerDataRowId
SELECT @TotalNumComputers = SUM(ComputerTable.NumComputers) FROM @NumComputers AS ComputerTable
INSERT Win7.vWin7ShellPerfAggregationRootCause (
[DateTime]
,NumEvents
,NumComputers
,EventDataRowId
) SELECT DISTINCT CONVERT(varchar(50), @IntervalStartDateTime, 120), COUNT(DISTINCT Win7.vWin7ShellPerfRootCause.Win7ShellPerfRawRowId), @TotalNumComputers, Win7.vWin7ShellPerfRootCause.EventDataRowId
FROM Win7.vWin7ShellPerfRootCause
JOIN Win7.vWin7ShellPerfRaw ON (Win7.vWin7ShellPerfRootCause.Win7ShellPerfRawRowId = Win7.vWin7ShellPerfRaw.Win7ShellPerfRawRowId)
WHERE (Win7.vWin7ShellPerfRaw.[DateTime] >= CONVERT(varchar(50), @IntervalStartDateTime, 120))
AND (Win7.vWin7ShellPerfRaw.[DateTime] < CONVERT(varchar(50), @IntervalEndDateTime, 120))
GROUP BY Win7.vWin7ShellPerfRootCause.EventDataRowId
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 = 'Win7ShellPerfGroom')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Win7ShellPerfGroom AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.Win7ShellPerfGroom
@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 Win7.vWin7ShellPerfRaw'
+ ' 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 Win7.vWin7ShellPerfRootCause'
+ ' 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 Win7.vWin7ShellPerfAggregationComputer'
+ ' WHERE ([DateTime] < CONVERT(datetime, ''' + CONVERT(varchar(50), @CutoffDateTime, 120) + ''', 120))'
EXECUTE(@Statement)
SET @RowsDeleted = @@ROWCOUNT
SET @Statement = 'DELETE FROM Win7.vWin7ShellPerfAggregationRootCause'
+ ' 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 = 'Win7ShellPerfInsert')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Win7ShellPerfInsert AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.Win7ShellPerfInsert
@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.CorrelationId
FROM OPENXML(@XmlDocHandle, 'Root/Item', 2) WITH (
EventId int
,PrincipalName nvarchar(256)
,PublisherName nvarchar(256)
,CorrelationId uniqueidentifier
) xml
WHILE EXISTS (SELECT * FROM @events)
BEGIN
SELECT @RowId = RowId, @EventId = EventId, @PrincipalName = PrincipalName, @PublisherName = PublisherName, @CorrelationId = CorrelationId
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.Win7.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.Win7.Aggregate.Computer'
DECLARE @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 @CompManufacturer <> '' OR @CompModel <> ''
BEGIN
DECLARE @EventDataRowId int
,@ComputerDataRowId int
IF NOT EXISTS (SELECT * FROM Win7.ComputerData WHERE Manufacturer = @CompManufacturer AND Model = @CompModel)
BEGIN
INSERT INTO Win7.ComputerData (Manufacturer, Model) VALUES (@CompManufacturer, @CompModel)
END
IF NOT EXISTS (SELECT * FROM Win7.EventData WHERE EventId = @EventId AND PublisherName = @PublisherName)
BEGIN
INSERT INTO Win7.EventData (EventId, PublisherName) VALUES (@EventId, @PublisherName)
END
SELECT @EventDataRowId = EventDataRowId FROM Win7.EventData WHERE EventId = @EventId AND PublisherName = @PublisherName
SELECT @ComputerDataRowId = ComputerDataRowId FROM Win7.ComputerData WHERE Manufacturer = @CompManufacturer AND Model = @CompModel
INSERT INTO Win7.vWin7ShellPerfRaw (DateTime, EventDataRowId, ComputerDataRowId, PrincipalName, CorrelationId) VALUES (GETUTCDATE(), @EventDataRowId, @ComputerDataRowId, @PrincipalName, @CorrelationId)
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 Win7ShellPerfInsert TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win7ShellPerfRootCauseInsert')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Win7ShellPerfRootCauseInsert AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.Win7ShellPerfRootCauseInsert
@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
BEGIN TRY
EXEC StandardDatasetMaintenance @DatasetId
INSERT INTO @events
SELECT xml.EventId, xml.PublisherName, xml.AppName, xml.CorrelationId
FROM OPENXML(@XmlDocHandle, 'Root/Item', 2) WITH (
EventId int
,PublisherName nvarchar(256)
,AppName nvarchar(256)
,CorrelationId uniqueidentifier
) xml
WHILE EXISTS (SELECT * FROM @events)
BEGIN
SELECT @RowId = RowId, @EventId = EventId, @PublisherName = PublisherName, @AppName = AppName, @CorrelationId = CorrelationId
FROM @events
WHERE RowId = (SELECT MAX(RowId) FROM @events)
DECLARE @EventDataRowId int
,@Win7ShellPerfRawRowId int
IF NOT EXISTS (SELECT * FROM Win7.EventData WHERE EventId = @EventId AND PublisherName = @PublisherName)
BEGIN
INSERT INTO Win7.EventData (EventId, PublisherName) VALUES (@EventId, @PublisherName)
END
SELECT @EventDataRowId = EventDataRowId FROM Win7.EventData WHERE EventId = @EventId AND PublisherName = @PublisherName
SELECT @Win7ShellPerfRawRowId = Win7ShellPerfRawRowId
FROM Win7.vWin7ShellPerfRaw
WHERE @CorrelationId = CorrelationId
DECLARE @Index int
,@NextIndex int
SET @NextIndex = -1
WHILE @NextIndex <> 0
BEGIN
SET @Index = @NextIndex + 1
SET @NextIndex = CHARINDEX('\', @AppName, @Index)
END
IF @Index = 0 SET @Index = 1
SET @AppName = SUBSTRING(@AppName, @Index, LEN(@AppName) - @Index + 1)
SET @ResultingState = 10
IF @Win7ShellPerfRawRowId IS NOT NULL
BEGIN
INSERT INTO Win7.vWin7ShellPerfRootCause (DateTime, AppName, Win7ShellPerfRawRowId, EventDataRowId) VALUES (GETUTCDATE(), @AppName, @Win7ShellPerfRawRowId, @EventDataRowId)
END
ELSE
BEGIN
INSERT INTO Win7.ShellPerfStaging (DateTime, AppName, CorrelationId, EventDataRowId) VALUES (GETUTCDATE(), @AppName, @CorrelationId, @EventDataRowId)
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 Win7ShellPerfRootCauseInsert TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win7ShellPerfRootCauseReport')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Win7ShellPerfRootCauseReport AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.Win7ShellPerfRootCauseReport
AS
BEGIN
SET NOCOUNT ON
INSERT INTO @Types SELECT DISTINCT (EventId)
FROM Win7.vWin7ShellPerfAggregationRootCause
JOIN Win7.EventData ON (Win7.vWin7ShellPerfAggregationRootCause.EventDataRowId = Win7.EventData.EventDataRowId)
WHERE DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), DateTime), CURRENT_TIMESTAMP) <= 91
SET @WeekCounter = -1
WHILE @WeekCounter > -14
BEGIN
INSERT INTO @Weeks VALUES (DATEADD(week, @WeekCounter, @CurTime))
SET @WeekCounter = @WeekCounter - 1
END
SELECT @Type = MIN(Type) FROM @Types
WHILE @Type 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 Win7.vWin7ShellPerfAggregationRootCause
JOIN Win7.EventData ON (Win7.vWin7ShellPerfAggregationRootCause.EventDataRowId = Win7.EventData.EventDataRowId)
WHERE (EventId = @Type)
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 @PerfMatrix VALUES (DATEADD(week, 1, @Week), @Type, @AvgIssues)
SELECT @Week = MIN(ListedWeek) FROM @Weeks WHERE ListedWeek > @Week
END
SELECT @Type = MIN(Type) FROM @Types WHERE Type > @Type
END
SELECT * FROM @PerfMatrix ORDER BY Type DESC
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win7ShellPerfMachineReport')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Win7ShellPerfMachineReport AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.Win7ShellPerfMachineReport
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 Win7.vWin7ShellPerfAggregationComputer
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 Win7.ComputerData
WHERE EXISTS (SELECT * FROM @series AS SeriesList WHERE SeriesList.series = Win7.ComputerData.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 Win7.vWin7ShellPerfAggregationComputer
JOIN Win7.ComputerData ON (Win7.vWin7ShellPerfAggregationComputer.ComputerDataRowId = Win7.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 @PerfMatrix 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 @PerfMatrix ORDER BY Type ASC
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win7ShellPerfMachineDrilldownReport')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Win7ShellPerfMachineDrilldownReport AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.Win7ShellPerfMachineDrilldownReport
AS
BEGIN
SET NOCOUNT ON
INSERT INTO @RootCauses SELECT DISTINCT Win7.vWin7ShellPerfRaw.Win7ShellPerfRawRowId, 1, 0, 0
FROM Win7.vWin7ShellPerfRaw
JOIN Win7.vWin7ShellPerfRootCause ON (Win7.vWin7ShellPerfRaw.Win7ShellPerfRawRowId = Win7.vWin7ShellPerfRootCause.Win7ShellPerfRawRowId)
JOIN Win7.EventData ON (Win7.vWin7ShellPerfRootCause.EventDataRowId = Win7.EventData.EventDataRowId)
WHERE Win7.EventData.EventId = 401
INSERT INTO @RootCauses SELECT DISTINCT Win7.vWin7ShellPerfRaw.Win7ShellPerfRawRowId, 0, 1, 0
FROM Win7.vWin7ShellPerfRaw
JOIN Win7.vWin7ShellPerfRootCause ON (Win7.vWin7ShellPerfRaw.Win7ShellPerfRawRowId = Win7.vWin7ShellPerfRootCause.Win7ShellPerfRawRowId)
JOIN Win7.EventData ON (Win7.vWin7ShellPerfRootCause.EventDataRowId = Win7.EventData.EventDataRowId)
WHERE Win7.EventData.EventId = 407
INSERT INTO @RootCauses SELECT DISTINCT Win7.vWin7ShellPerfRaw.Win7ShellPerfRawRowId, 0, 0, 1
FROM Win7.vWin7ShellPerfRaw
JOIN Win7.vWin7ShellPerfRootCause ON (Win7.vWin7ShellPerfRaw.Win7ShellPerfRawRowId = Win7.vWin7ShellPerfRootCause.Win7ShellPerfRawRowId)
JOIN Win7.EventData ON (Win7.vWin7ShellPerfRootCause.EventDataRowId = Win7.EventData.EventDataRowId)
WHERE Win7.EventData.EventId = 402
INSERT INTO @RootCauses SELECT DISTINCT Win7.vWin7ShellPerfRaw.Win7ShellPerfRawRowId, 0, 0, 0
FROM Win7.vWin7ShellPerfRaw
WHERE NOT EXISTS (SELECT * FROM @RootCauses AS RootCauses WHERE Win7.vWin7ShellPerfRaw.Win7ShellPerfRawRowId = RootCauses.Win7ShellPerfRawRowId)
SELECT Win7.vWin7ShellPerfRaw.PrincipalName AS PrincipalName, MAX(Win7.ComputerData.Manufacturer + ' ' + Win7.ComputerData.Model) AS Type, COUNT(*) AS NumInstances, SUM(RootCauses.CPUIssues) AS CPU, SUM(RootCauses.MemoryIssues) AS Memory, SUM(RootCauses.DiskIssues) AS Disk
FROM Win7.vWin7ShellPerfRaw
JOIN @RootCauses AS RootCauses ON (Win7.vWin7ShellPerfRaw.Win7ShellPerfRawRowId = RootCauses.Win7ShellPerfRawRowId)
JOIN Win7.ComputerData ON (Win7.vWin7ShellPerfRaw.ComputerDataRowId = Win7.ComputerData.ComputerDataRowId)
WHERE DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), DateTime), CURRENT_TIMESTAMP) <= 7
GROUP BY Win7.vWin7ShellPerfRaw.PrincipalName
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Win7ShellPerfRootCauseAppReport')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.Win7ShellPerfRootCauseAppReport AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.Win7ShellPerfRootCauseAppReport
@EventId int
AS
BEGIN
SET NOCOUNT ON
DECLARE @MaxSeries int
SET @MaxSeries = 9
SET ROWCOUNT @MaxSeries
SELECT AppName AS Name, COUNT(Win7.vWin7ShellPerfRaw.Win7ShellPerfRawRowId) AS NumIssues
FROM Win7.vWin7ShellPerfRootCause
JOIN Win7.vWin7ShellPerfRaw ON (Win7.vWin7ShellPerfRootCause.Win7ShellPerfRawRowId = Win7.vWin7ShellPerfRaw.Win7ShellPerfRawRowId)
Join Win7.EventData ON (Win7.vWin7ShellPerfRootCause.EventDataRowId = Win7.EventData.EventDataRowId)
WHERE (@EventId = Win7.EventData.EventId)
AND DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), Win7.vWin7ShellPerfRaw.DateTime), CURRENT_TIMESTAMP) <= 7
GROUP BY AppName
ORDER BY COUNT(Win7.vWin7ShellPerfRaw.Win7ShellPerfRawRowId) DESC
SET ROWCOUNT 0
END
GO </Install>
<Uninstall/> <Upgrade>
SET NOCOUNT ON </Upgrade>
</DataWarehouseDataSet>
Home