IF EXISTS (SELECT * FROM StandardDatasetAggregationStorage WHERE DatasetId IN (SELECT DatasetId FROM StandardDataset WHERE SchemaName = 'Vista') AND BaseTableName = 'ShellPerfRaw')
BEGIN
SET @DatasetId = (SELECT DatasetId FROM StandardDatasetAggregationStorage WHERE DatasetId IN (SELECT DatasetId FROM StandardDataset WHERE SchemaName = 'Vista') AND BaseTableName = 'ShellPerfRaw')
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 'ShellPerfAggregate'
END
,CASE AggregationType
WHEN 'Raw' THEN NULL
ELSE 'ShellPerfAggregationDelete'
END
,'ShellPerfGroom'
,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] = 'Vista')
BEGIN
EXECUTE('CREATE SCHEMA Vista')
END
GO
-- change schema ownership to allow index rebuild and reorganize
ALTER AUTHORIZATION ON SCHEMA::Vista TO OpsMgrWriter
GO
-- change schema alter to allow domain table index rebuild
GRANT ALTER ON SCHEMA::dbo TO OpsMgrWriter
GO
-- grant create table permissions
GRANT CREATE TABLE TO OpsMgrWriter
GO
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessageText nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
SET @ErrorInd = 0
DECLARE
@ExecResult int
,@XmlDocHandle int
,@XmlDocText nvarchar(max)
BEGIN TRY
SET @XmlDocText = '<Aggregations>$Config/Aggregations$</Aggregations>'
WHILE EXISTS (SELECT *
FROM OPENXML(@XmlDocHandle, 'Aggregations/*', 2) WITH (
AggregationType varchar(50) '@mp:localname')
WHERE AggregationType > @AggregationType
)
BEGIN
SELECT TOP 1 @AggregationType = AggregationType
FROM OPENXML(@XmlDocHandle, 'Aggregations/*', 2) WITH (
AggregationType varchar(50) '@mp:localname')
WHERE AggregationType > @AggregationType
ORDER BY AggregationType
SET @Statement = 'EXEC StandardDatasetAllocateStorage @DatasetId = ''$Config/DatasetId$'', @AggregationTypeId='
+ CASE @AggregationType
WHEN 'Raw' THEN '0'
WHEN 'Daily' THEN '30'
END
EXECUTE (@Statement)
END
END TRY
BEGIN CATCH
SELECT
@ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorLine = ERROR_LINE()
,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')
,@ErrorMessageText = ERROR_MESSAGE()
SET @ErrorInd = 1
END CATCH
IF (@XmlDocHandle IS NOT NULL)
EXEC sp_xml_removedocument @XmlDocHandle
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'EventData' AND TABLE_SCHEMA = 'Vista')
BEGIN
DROP TABLE Vista.EventData
END
GO
CREATE TABLE Vista.EventData
(
EventDataRowId int NOT NULL IDENTITY(1, 1)
,EventId int NOT NULL
,PublisherName nvarchar(256) NOT NULL
,CONSTRAINT PK_Vista_EventRule PRIMARY KEY CLUSTERED (EventDataRowId)
)
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ComputerData' AND TABLE_SCHEMA = 'Vista')
BEGIN
DROP TABLE Vista.ComputerData
END
GO
CREATE TABLE Vista.ComputerData
(
ComputerDataRowId int NOT NULL IDENTITY(1, 1)
,Manufacturer nvarchar(256) NOT NULL
,Model nvarchar(256) NOT NULL
,CONSTRAINT PK_Vista_ComputerRule PRIMARY KEY CLUSTERED (ComputerDataRowId)
)
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ShellPerfStaging' AND TABLE_SCHEMA = 'Vista')
BEGIN
DROP TABLE Vista.ShellPerfStaging
END
GO
CREATE TABLE Vista.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 = 'ShellPerfAggregationDelete')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.ShellPerfAggregationDelete AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.ShellPerfAggregationDelete
@DatasetId uniqueidentifier
,@AggregationTypeId tinyint
,@TableGuid uniqueidentifier
,@AggregationDateTime datetime
,@RowsDeleted int OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(4000)
SET @ErrorInd = 0
DECLARE
@Statement nvarchar(max)
BEGIN TRY
IF @AggregationTypeId = 30
BEGIN
SET @Statement = 'DELETE FROM Vista.vShellPerfAggregationComputer'
+ ' WHERE ([DateTime] = CONVERT(datetime, ''' + CONVERT(varchar(50), @AggregationDateTime, 120) + ''', 120))'
EXECUTE(@Statement)
SET @RowsDeleted = @@ROWCOUNT
SET @Statement = 'DELETE FROM Vista.vShellPerfAggregationRootCause'
+ ' 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 = 'ShellPerfAggregate')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.ShellPerfAggregate AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.ShellPerfAggregate
@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 Vista.vShellPerfRootCause (
[DateTime]
,AppName
,ShellPerfRawRowId
,EventDataRowId
) SELECT Vista.ShellPerfStaging.[DateTime], Vista.ShellPerfStaging.AppName, Vista.vShellPerfRaw.ShellPerfRawRowId, Vista.ShellPerfStaging.EventDataRowId
FROM Vista.ShellPerfStaging
JOIN Vista.vShellPerfRaw ON (Vista.ShellPerfStaging.CorrelationId = Vista.vShellPerfRaw.CorrelationId)
DELETE FROM Vista.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.Vista.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.Vista.Aggregate.Computer'
SELECT @CompModelGuid = vManagedEntityTypeProperty.PropertyGuid
FROM vManagedEntityTypeProperty
JOIN vManagedEntityType ON (vManagedEntityType.ManagedEntityTypeRowId = vManagedEntityTypeProperty.ManagedEntityTypeRowId)
WHERE vManagedEntityTypeProperty.PropertySystemName = 'Model'
AND vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Client.Vista.Aggregate.Computer'
INSERT @NumComputers (
NumComputers
,ComputerDataRowId
) SELECT COUNT(vManagedEntityProperty.ManagedEntityRowId)
,Vista.ComputerData.ComputerDataRowId
FROM vManagedEntityProperty
JOIN Vista.ComputerData ON (Vista.ComputerData.Manufacturer = CONVERT(nvarchar(256), vManagedEntityProperty.PropertyXml.query('data(/Root/Property[@Guid = sql:variable("@CompManufacturerGuid")])'))
AND Vista.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 Vista.ComputerData.ComputerDataRowId
INSERT Vista.vShellPerfAggregationComputer (
[DateTime]
,NumEvents
,NumComputers
,ComputerDataRowId
) SELECT DISTINCT CONVERT(varchar(50), @IntervalStartDateTime, 120), COUNT(*), AVG(ComputerTable.NumComputers), Vista.vShellPerfRaw.ComputerDataRowId
FROM Vista.vShellPerfRaw
JOIN @NumComputers AS ComputerTable ON (ComputerTable.ComputerDataRowId = Vista.vShellPerfRaw.ComputerDataRowId)
WHERE ([DateTime] >= CONVERT(varchar(50), @IntervalStartDateTime, 120))
AND ([DateTime] < CONVERT(varchar(50), @IntervalEndDateTime, 120))
GROUP BY Vista.vShellPerfRaw.ComputerDataRowId
SELECT @TotalNumComputers = SUM(ComputerTable.NumComputers) FROM @NumComputers AS ComputerTable
INSERT Vista.vShellPerfAggregationRootCause (
[DateTime]
,NumEvents
,NumComputers
,EventDataRowId
) SELECT DISTINCT CONVERT(varchar(50), @IntervalStartDateTime, 120), COUNT(DISTINCT Vista.vShellPerfRootCause.ShellPerfRawRowId), @TotalNumComputers, Vista.vShellPerfRootCause.EventDataRowId
FROM Vista.vShellPerfRootCause
JOIN Vista.vShellPerfRaw ON (Vista.vShellPerfRootCause.ShellPerfRawRowId = Vista.vShellPerfRaw.ShellPerfRawRowId)
WHERE (Vista.vShellPerfRaw.[DateTime] >= CONVERT(varchar(50), @IntervalStartDateTime, 120))
AND (Vista.vShellPerfRaw.[DateTime] < CONVERT(varchar(50), @IntervalEndDateTime, 120))
GROUP BY Vista.vShellPerfRootCause.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 = 'ShellPerfGroom')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.ShellPerfGroom AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.ShellPerfGroom
@DatasetId uniqueidentifier
,@AggregationTypeId tinyint
,@TableGuid uniqueidentifier
,@CutoffDateTime datetime
,@MaxRowsToGroom int
,@RowsDeleted int OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(4000)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(4000)
SET @ErrorInd = 0
DECLARE @Statement nvarchar(max)
BEGIN TRY
IF @AggregationTypeId = 0
BEGIN
SET @Statement = 'DELETE TOP (' + CAST(@MaxRowsToGroom AS varchar(15)) + ')'
+ ' FROM Vista.vShellPerfRaw'
+ ' 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 Vista.vShellPerfRootCause'
+ ' 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 Vista.vShellPerfAggregationComputer'
+ ' WHERE ([DateTime] < CONVERT(datetime, ''' + CONVERT(varchar(50), @CutoffDateTime, 120) + ''', 120))'
EXECUTE(@Statement)
SET @RowsDeleted = @@ROWCOUNT
SET @Statement = 'DELETE FROM Vista.vShellPerfAggregationRootCause'
+ ' 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 = 'ShellPerfInsert')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.ShellPerfInsert AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.ShellPerfInsert
@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.Vista.Aggregate.Computer'
SELECT @CompModelGuid = vManagedEntityTypeProperty.PropertyGuid
FROM vManagedEntityTypeProperty
JOIN vManagedEntityType ON (vManagedEntityType.ManagedEntityTypeRowId = vManagedEntityTypeProperty.ManagedEntityTypeRowId)
WHERE vManagedEntityTypeProperty.PropertySystemName = 'Model'
AND vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Client.Vista.Aggregate.Computer'
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 Vista.ComputerData WHERE Manufacturer = @CompManufacturer AND Model = @CompModel)
BEGIN
INSERT INTO Vista.ComputerData (Manufacturer, Model) VALUES (@CompManufacturer, @CompModel)
END
IF NOT EXISTS (SELECT * FROM Vista.EventData WHERE EventId = @EventId AND PublisherName = @PublisherName)
BEGIN
INSERT INTO Vista.EventData (EventId, PublisherName) VALUES (@EventId, @PublisherName)
END
SELECT @EventDataRowId = EventDataRowId FROM Vista.EventData WHERE EventId = @EventId AND PublisherName = @PublisherName
SELECT @ComputerDataRowId = ComputerDataRowId FROM Vista.ComputerData WHERE Manufacturer = @CompManufacturer AND Model = @CompModel
INSERT INTO Vista.vShellPerfRaw (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 ShellPerfInsert TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ShellPerfRootCauseInsert')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.ShellPerfRootCauseInsert AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.ShellPerfRootCauseInsert
@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
,@ShellPerfRawRowId int
IF NOT EXISTS (SELECT * FROM Vista.EventData WHERE EventId = @EventId AND PublisherName = @PublisherName)
BEGIN
INSERT INTO Vista.EventData (EventId, PublisherName) VALUES (@EventId, @PublisherName)
END
SELECT @EventDataRowId = EventDataRowId FROM Vista.EventData WHERE EventId = @EventId AND PublisherName = @PublisherName
SELECT @ShellPerfRawRowId = ShellPerfRawRowId
FROM Vista.vShellPerfRaw
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 @ShellPerfRawRowId IS NOT NULL
BEGIN
INSERT INTO Vista.vShellPerfRootCause (DateTime, AppName, ShellPerfRawRowId, EventDataRowId) VALUES (GETUTCDATE(), @AppName, @ShellPerfRawRowId, @EventDataRowId)
END
ELSE
BEGIN
INSERT INTO Vista.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 ShellPerfRootCauseInsert TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ShellPerfRootCauseReport')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.ShellPerfRootCauseReport AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.ShellPerfRootCauseReport
AS
BEGIN
SET NOCOUNT ON
INSERT INTO @Types SELECT DISTINCT (EventId)
FROM Vista.vShellPerfAggregationRootCause
JOIN Vista.EventData ON (Vista.vShellPerfAggregationRootCause.EventDataRowId = Vista.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 Vista.vShellPerfAggregationRootCause
JOIN Vista.EventData ON (Vista.vShellPerfAggregationRootCause.EventDataRowId = Vista.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 = 'ShellPerfMachineReport')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.ShellPerfMachineReport AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.ShellPerfMachineReport
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 Vista.vShellPerfAggregationComputer
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 Vista.ComputerData
WHERE EXISTS (SELECT * FROM @series AS SeriesList WHERE SeriesList.series = Vista.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 Vista.vShellPerfAggregationComputer
JOIN Vista.ComputerData ON (Vista.vShellPerfAggregationComputer.ComputerDataRowId = Vista.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 = 'ShellPerfMachineDrilldownReport')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.ShellPerfMachineDrilldownReport AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.ShellPerfMachineDrilldownReport
AS
BEGIN
SET NOCOUNT ON
INSERT INTO @RootCauses SELECT DISTINCT Vista.vShellPerfRaw.ShellPerfRawRowId, 1, 0, 0
FROM Vista.vShellPerfRaw
JOIN Vista.vShellPerfRootCause ON (Vista.vShellPerfRaw.ShellPerfRawRowId = Vista.vShellPerfRootCause.ShellPerfRawRowId)
JOIN Vista.EventData ON (Vista.vShellPerfRootCause.EventDataRowId = Vista.EventData.EventDataRowId)
WHERE Vista.EventData.EventId = 401
INSERT INTO @RootCauses SELECT DISTINCT Vista.vShellPerfRaw.ShellPerfRawRowId, 0, 1, 0
FROM Vista.vShellPerfRaw
JOIN Vista.vShellPerfRootCause ON (Vista.vShellPerfRaw.ShellPerfRawRowId = Vista.vShellPerfRootCause.ShellPerfRawRowId)
JOIN Vista.EventData ON (Vista.vShellPerfRootCause.EventDataRowId = Vista.EventData.EventDataRowId)
WHERE Vista.EventData.EventId = 407
INSERT INTO @RootCauses SELECT DISTINCT Vista.vShellPerfRaw.ShellPerfRawRowId, 0, 0, 1
FROM Vista.vShellPerfRaw
JOIN Vista.vShellPerfRootCause ON (Vista.vShellPerfRaw.ShellPerfRawRowId = Vista.vShellPerfRootCause.ShellPerfRawRowId)
JOIN Vista.EventData ON (Vista.vShellPerfRootCause.EventDataRowId = Vista.EventData.EventDataRowId)
WHERE Vista.EventData.EventId = 402
INSERT INTO @RootCauses SELECT DISTINCT Vista.vShellPerfRaw.ShellPerfRawRowId, 0, 0, 0
FROM Vista.vShellPerfRaw
WHERE NOT EXISTS (SELECT * FROM @RootCauses AS RootCauses WHERE Vista.vShellPerfRaw.ShellPerfRawRowId = RootCauses.ShellPerfRawRowId)
SELECT Vista.vShellPerfRaw.PrincipalName AS PrincipalName, MAX(Vista.ComputerData.Manufacturer + ' ' + Vista.ComputerData.Model) AS Type, COUNT(*) AS NumInstances, SUM(RootCauses.CPUIssues) AS CPU, SUM(RootCauses.MemoryIssues) AS Memory, SUM(RootCauses.DiskIssues) AS Disk
FROM Vista.vShellPerfRaw
JOIN @RootCauses AS RootCauses ON (Vista.vShellPerfRaw.ShellPerfRawRowId = RootCauses.ShellPerfRawRowId)
JOIN Vista.ComputerData ON (Vista.vShellPerfRaw.ComputerDataRowId = Vista.ComputerData.ComputerDataRowId)
WHERE DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), DateTime), CURRENT_TIMESTAMP) <= 7
GROUP BY Vista.vShellPerfRaw.PrincipalName
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ShellPerfRootCauseAppReport')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.ShellPerfRootCauseAppReport AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.ShellPerfRootCauseAppReport
@EventId int
AS
BEGIN
SET NOCOUNT ON
DECLARE @MaxSeries int
SET @MaxSeries = 9
SET ROWCOUNT @MaxSeries
SELECT AppName AS Name, COUNT(Vista.vShellPerfRaw.ShellPerfRawRowId) AS NumIssues
FROM Vista.vShellPerfRootCause
JOIN Vista.vShellPerfRaw ON (Vista.vShellPerfRootCause.ShellPerfRawRowId = Vista.vShellPerfRaw.ShellPerfRawRowId)
Join Vista.EventData ON (Vista.vShellPerfRootCause.EventDataRowId = Vista.EventData.EventDataRowId)
WHERE (@EventId = Vista.EventData.EventId)
AND DateDiff(day, DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), CURRENT_TIMESTAMP), Vista.vShellPerfRaw.DateTime), CURRENT_TIMESTAMP) <= 7
GROUP BY AppName
ORDER BY COUNT(Vista.vShellPerfRaw.ShellPerfRawRowId) DESC
SET ROWCOUNT 0
END
GO</Install>
<Uninstall/> <Upgrade>SET NOCOUNT ON</Upgrade>
</DataWarehouseDataSet>