IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'Microsoft_SQLServer_Visualization_Library_DB_Version' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE (N'CREATE TABLE sdk.Microsoft_SQLServer_Visualization_Library_DB_Version (
[CurrentVersion] [int] NOT NULL,
) ON [PRIMARY]')
END
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON [SDK].Microsoft_SQLServer_Visualization_Library_DB_Version TO OpsMgrReader
GO
IF NOT EXISTS (SELECT NULL FROM sdk.Microsoft_SQLServer_Visualization_Library_DB_Version)
BEGIN
INSERT INTO sdk.Microsoft_SQLServer_Visualization_Library_DB_Version VALUES(7)
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SQLServer_Visualization_Library_UpdateTablesList' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE ('CREATE PROCEDURE sdk.Microsoft_SQLServer_Visualization_Library_UpdateTablesList AS RETURN 1')
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SQLServer_Visualization_Library_GetInstanceViewData' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE ('CREATE PROCEDURE sdk.Microsoft_SQLServer_Visualization_Library_GetInstanceViewData AS RETURN 1')
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SQLServer_Visualization_Library_GetDataCenterDashboardData' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE ('CREATE PROCEDURE sdk.Microsoft_SQLServer_Visualization_Library_GetDataCenterDashboardData AS RETURN 1')
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SQLServer_Visualization_Library_UpdateLastValues' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE ('CREATE PROCEDURE sdk.Microsoft_SQLServer_Visualization_Library_UpdateLastValues AS RETURN 1')
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'Microsoft_SQLServer_Visualization_Library_LastPerfValues' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE (N'CREATE TABLE sdk.Microsoft_SQLServer_Visualization_Library_LastPerfValues (
[ManagedEntityRowId] [int] NOT NULL,
[PerformanceRuleInstanceRowId] [int] NOT NULL,
[DateTime] [datetime] NOT NULL,
[SampleValue] [float] NULL,
CONSTRAINT [PK_Microsoft_SQLServer_Visualization_Library_LastPerfValues] PRIMARY KEY CLUSTERED (
[ManagedEntityRowId] ASC, [PerformanceRuleInstanceRowId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]')
END
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON [SDK].[Microsoft_SQLServer_Visualization_Library_LastPerfValues] TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'Microsoft_SQLServer_Visualization_Library_LastMonitorValues' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE (N'CREATE TABLE sdk.Microsoft_SQLServer_Visualization_Library_LastMonitorValues (
[ManagedEntityRowId] [int] NOT NULL,
[MonitorRowId] [int] NOT NULL,
[DateTime] [datetime] NOT NULL,
[HealthState] [tinyint] NULL,
CONSTRAINT [PK_Microsoft_SQLServer_Visualization_Library_LastMonitorValues] PRIMARY KEY CLUSTERED (
[ManagedEntityRowId] ASC, [MonitorRowId] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]')
END
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON [SDK].[Microsoft_SQLServer_Visualization_Library_LastMonitorValues] TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'Microsoft_SQLServer_Visualization_Library_LastAlertValues' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE (N'CREATE TABLE sdk.Microsoft_SQLServer_Visualization_Library_LastAlertValues (
[AlertGuid] [uniqueidentifier] NOT NULL,
[ManagedEntityRowId] [int] NOT NULL,
[DateTime] [datetime] NOT NULL,
[Severity] [tinyint] NOT NULL,
[ResolutionState] [tinyint] NULL,
CONSTRAINT [PK_Microsoft_SQLServer_Visualization_Library_LastAlertValues] PRIMARY KEY CLUSTERED (
[AlertGuid] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]')
EXECUTE (N'
CREATE NONCLUSTERED INDEX IX_Microsoft_SQLServer_Visualization_Library_LastAlertValues_ManagedEntityRowId_ResolutionState
ON [sdk].[Microsoft_SQLServer_Visualization_Library_LastAlertValues] ([ManagedEntityRowId],[ResolutionState])
')
END
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON [SDK].[Microsoft_SQLServer_Visualization_Library_LastMonitorValues] TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'Microsoft_SQLServer_Visualization_Library_Tables' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE (N'CREATE TABLE sdk.Microsoft_SQLServer_Visualization_Library_Tables (
[TableId] [bigint] IDENTITY(1,1) NOT NULL,
[Name] [sysname] NOT NULL,
[Type] [tinyint] NOT NULL,
[LastProcessedId] [bigint] NOT NULL,
[FirstDate] [datetime] NOT NULL,
CONSTRAINT [PK_Microsoft_SQLServer_Visualization_Library_PerfTables] PRIMARY KEY CLUSTERED (
[TableId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]')
END
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON [SDK].Microsoft_SQLServer_Visualization_Library_Tables TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'Microsoft_SQLServer_Visualization_Library_Table_Batches' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE (N'CREATE TABLE sdk.Microsoft_SQLServer_Visualization_Library_Table_Batches (
[BatchId] [bigint] IDENTITY(1,1) NOT NULL,
[TableId] [bigint] NOT NULL,
[FirstId] [bigint] NOT NULL,
[LastId] [bigint] NOT NULL,
[CreateDate] [datetime] NOT NULL,
[StartDate] [datetime] NULL,
[FinishDate] [datetime] NULL,
[Tries] [int] NOT NULL,
CONSTRAINT [PK_Microsoft_SQLServer_Visualization_Library_Table_Batches] PRIMARY KEY CLUSTERED (
[BatchId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]')
END
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON [SDK].Microsoft_SQLServer_Visualization_Library_Table_Batches TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SQLServer_Visualization_Library_GetGroups' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE ('CREATE PROCEDURE sdk.Microsoft_SQLServer_Visualization_Library_GetGroups AS RETURN 1')
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SQLServer_Visualization_Library_GetGroupClassMetadata' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE ('CREATE PROCEDURE sdk.Microsoft_SQLServer_Visualization_Library_GetGroupClassMetadata AS RETURN 1')
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SQLServer_Visualization_Library_UpdateHierarchy' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE ('CREATE PROCEDURE sdk.Microsoft_SQLServer_Visualization_Library_UpdateHierarchy AS RETURN 1')
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SQLServer_Visualization_Library_RethrowError' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE ('CREATE PROCEDURE sdk.Microsoft_SQLServer_Visualization_Library_RethrowError AS RETURN 1')
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'Microsoft_SQLServer_Visualization_Library_RelationshipType_Hierarchy' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE (N'CREATE TABLE sdk.Microsoft_SQLServer_Visualization_Library_RelationshipType_Hierarchy (
[Parent] [int] NOT NULL,
[Child] [int] NOT NULL
CONSTRAINT [PK_Microsoft_SQLServer_Visualization_Library_RelationshipType_Hierarchy] PRIMARY KEY CLUSTERED (
[Parent] ASC, [Child] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]');
EXECUTE (N'
CREATE NONCLUSTERED INDEX [IX_Microsoft_SQLServer_Visualization_Library_RelationshipType_Hierarchy_Child]
ON [sdk].[Microsoft_SQLServer_Visualization_Library_RelationshipType_Hierarchy] ([Child], [Parent])
');
END
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON [SDK].Microsoft_SQLServer_Visualization_Library_RelationshipType_Hierarchy TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE (N'CREATE TABLE sdk.Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy (
[IsGroup] [int] NOT NULL,
[RelationshipManagementPackRowId] [int] NOT NULL,
[Parent] [int] NOT NULL,
[Child] [int] NOT NULL
CONSTRAINT [PK_Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy] PRIMARY KEY CLUSTERED (
[IsGroup] ASC, [RelationshipManagementPackRowId] ASC, [Parent] ASC, [Child] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]');
EXECUTE (N'
CREATE NONCLUSTERED INDEX IX_Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy_Parent
ON [sdk].[Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy] ([Parent], [Child])
')
EXECUTE (N'
CREATE NONCLUSTERED INDEX [IX_Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy_Child]
ON [sdk].[Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy] ([Child], [Parent])
')
END
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON [SDK].Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy TO OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'Microsoft_SQLServer_Visualization_Library_OpsManagerSettings' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE (N'CREATE TABLE sdk.Microsoft_SQLServer_Visualization_Library_OpsManagerSettings (
[ManagementGroupGuid] [uniqueidentifier] NOT NULL,
[Name] nvarchar(50) NOT NULL,
[Value] [int] NULL
) ON [PRIMARY]');
END
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON [SDK].Microsoft_SQLServer_Visualization_Library_OpsManagerSettings TO OpsMgrReader
GO
-- update script.
IF EXISTS (SELECT NULL FROM sdk.Microsoft_SQLServer_Visualization_Library_DB_Version WHERE CurrentVersion < 1)
BEGIN
DELETE FROM sdk.Microsoft_SQLServer_Visualization_Library_Tables
DELETE FROM sdk.Microsoft_SQLServer_Visualization_Library_LastAlertValues
DELETE FROM sdk.Microsoft_SQLServer_Visualization_Library_LastMonitorValues
DELETE FROM sdk.Microsoft_SQLServer_Visualization_Library_LastPerfValues
UPDATE sdk.Microsoft_SQLServer_Visualization_Library_DB_Version SET CurrentVersion = 1
END
GO
-- update script.
IF EXISTS (SELECT NULL FROM sdk.Microsoft_SQLServer_Visualization_Library_DB_Version WHERE CurrentVersion < 2)
BEGIN
EXECUTE (N'DROP INDEX [<Name of Missing Index, sysname,>] ON [sdk].[Microsoft_SQLServer_Visualization_Library_LastMonitorValues]');
EXECUTE (N'
CREATE NONCLUSTERED INDEX IX_Microsoft_SQLServer_Visualization_Library_LastMonitorValues_ManagedEntityRowId_MonitorRowId
ON [sdk].[Microsoft_SQLServer_Visualization_Library_LastMonitorValues] ([ManagedEntityRowId],[MonitorRowId])
INCLUDE ([LastMonitorValueId],[DateTime],[ManagedEntityMonitorRowId],[HealthState])
')
EXECUTE (N'
CREATE NONCLUSTERED INDEX IX_Microsoft_SQLServer_Visualization_Library_LastMonitorValues_ManagedEntityMonitorRowId
ON [sdk].[Microsoft_SQLServer_Visualization_Library_LastMonitorValues] ([ManagedEntityMonitorRowId])
INCLUDE ([LastMonitorValueId],[DateTime],[HealthState])
')
UPDATE sdk.Microsoft_SQLServer_Visualization_Library_DB_Version SET CurrentVersion = 2
END
GO
IF EXISTS (SELECT NULL FROM sdk.Microsoft_SQLServer_Visualization_Library_DB_Version WHERE CurrentVersion < 3)
BEGIN
DELETE FROM sdk.Microsoft_SQLServer_Visualization_Library_Tables;
ALTER TABLE sdk.Microsoft_SQLServer_Visualization_Library_Tables ADD [FirstDate] [datetime] NOT NULL
UPDATE sdk.Microsoft_SQLServer_Visualization_Library_DB_Version SET CurrentVersion = 3
END
GO
IF EXISTS (SELECT NULL FROM sdk.Microsoft_SQLServer_Visualization_Library_DB_Version WHERE CurrentVersion < 4)
BEGIN
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SQLServer_Visualization_Library_GetTablesList' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE (N'DROP PROCEDURE sdk.Microsoft_SQLServer_Visualization_Library_GetTablesList');
END
UPDATE sdk.Microsoft_SQLServer_Visualization_Library_DB_Version SET CurrentVersion = 4
END
GO
IF EXISTS (SELECT NULL FROM sdk.Microsoft_SQLServer_Visualization_Library_DB_Version WHERE CurrentVersion < 5)
BEGIN
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SQLServer_Visualization_Library_GetTablesList' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE (N'DROP PROCEDURE sdk.Microsoft_SQLServer_Visualization_Library_GetTablesList');
END
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE (N'DROP TABLE sdk.Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy');
END
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'Microsoft_SQLServer_Visualization_Library_RelationshipType_Hierarchy' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE (N'DROP TABLE sdk.Microsoft_SQLServer_Visualization_Library_RelationshipType_Hierarchy');
END
EXECUTE (N'CREATE TABLE sdk.Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy (
[IsGroup] [int] NOT NULL,
[RelationshipManagementPackRowId] [int] NOT NULL,
[Parent] [int] NOT NULL,
[Child] [int] NOT NULL
CONSTRAINT [PK_Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy] PRIMARY KEY CLUSTERED (
[IsGroup] ASC, [RelationshipManagementPackRowId] ASC, [Parent] ASC, [Child] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]');
EXECUTE (N'
CREATE NONCLUSTERED INDEX IX_Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy_Parent
ON [sdk].[Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy] ([Parent], [IsGroup], [Child])
');
EXECUTE (N'
CREATE NONCLUSTERED INDEX [IX_Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy_Child]
ON [sdk].[Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy] ([Child], [IsGroup], [Parent])
');
EXECUTE (N'GRANT SELECT, INSERT, UPDATE, DELETE ON [SDK].Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy TO OpsMgrReader')
EXECUTE (N'CREATE TABLE sdk.Microsoft_SQLServer_Visualization_Library_RelationshipType_Hierarchy (
[Parent] [int] NOT NULL,
[Child] [int] NOT NULL
CONSTRAINT [PK_Microsoft_SQLServer_Visualization_Library_RelationshipType_Hierarchy] PRIMARY KEY CLUSTERED (
[Parent] ASC, [Child] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]');
EXECUTE (N'
CREATE NONCLUSTERED INDEX [IX_Microsoft_SQLServer_Visualization_Library_RelationshipType_Hierarchy_Child]
ON [sdk].[Microsoft_SQLServer_Visualization_Library_RelationshipType_Hierarchy] ([Child], [Parent])
');
EXECUTE (N'GRANT SELECT, INSERT, UPDATE, DELETE ON [SDK].Microsoft_SQLServer_Visualization_Library_RelationshipType_Hierarchy TO OpsMgrReader');
DELETE FROM sdk.Microsoft_SQLServer_Visualization_Library_Tables;
DELETE FROM sdk.Microsoft_SQLServer_Visualization_Library_Table_Batches;
UPDATE sdk.Microsoft_SQLServer_Visualization_Library_DB_Version SET CurrentVersion = 5
END
GO
IF EXISTS (SELECT NULL FROM sdk.Microsoft_SQLServer_Visualization_Library_DB_Version WHERE CurrentVersion < 6)
BEGIN
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'Microsoft_SQLServer_Visualization_Library_LastPerfValues' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE (N'DROP TABLE sdk.Microsoft_SQLServer_Visualization_Library_LastPerfValues');
END
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'Microsoft_SQLServer_Visualization_Library_LastMonitorValues' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE (N'DROP TABLE sdk.Microsoft_SQLServer_Visualization_Library_LastMonitorValues');
END
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'Microsoft_SQLServer_Visualization_Library_LastAlertValues' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE (N'DROP TABLE sdk.Microsoft_SQLServer_Visualization_Library_LastAlertValues');
END
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'Microsoft_SQLServer_Visualization_Library_LastPerfValues' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE (N'CREATE TABLE sdk.Microsoft_SQLServer_Visualization_Library_LastPerfValues (
[ManagedEntityRowId] [int] NOT NULL,
[PerformanceRuleInstanceRowId] [int] NOT NULL,
[DateTime] [datetime] NOT NULL,
[SampleValue] [float] NULL,
CONSTRAINT [PK_Microsoft_SQLServer_Visualization_Library_LastPerfValues] PRIMARY KEY CLUSTERED (
[ManagedEntityRowId] ASC, [PerformanceRuleInstanceRowId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]')
END
EXECUTE (N'GRANT SELECT, INSERT, UPDATE, DELETE ON [SDK].[Microsoft_SQLServer_Visualization_Library_LastPerfValues] TO OpsMgrReader')
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'Microsoft_SQLServer_Visualization_Library_LastMonitorValues' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE (N'CREATE TABLE sdk.Microsoft_SQLServer_Visualization_Library_LastMonitorValues (
[ManagedEntityRowId] [int] NOT NULL,
[MonitorRowId] [int] NOT NULL,
[DateTime] [datetime] NOT NULL,
[HealthState] [tinyint] NULL,
CONSTRAINT [PK_Microsoft_SQLServer_Visualization_Library_LastMonitorValues] PRIMARY KEY CLUSTERED (
[ManagedEntityRowId] ASC, [MonitorRowId] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]')
END
EXECUTE (N'GRANT SELECT, INSERT, UPDATE, DELETE ON [SDK].[Microsoft_SQLServer_Visualization_Library_LastMonitorValues] TO OpsMgrReader')
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'Microsoft_SQLServer_Visualization_Library_LastAlertValues' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE (N'CREATE TABLE sdk.Microsoft_SQLServer_Visualization_Library_LastAlertValues (
[AlertGuid] [uniqueidentifier] NOT NULL,
[ManagedEntityRowId] [int] NOT NULL,
[DateTime] [datetime] NOT NULL,
[Severity] [tinyint] NOT NULL,
[ResolutionState] [tinyint] NULL,
CONSTRAINT [PK_Microsoft_SQLServer_Visualization_Library_LastAlertValues] PRIMARY KEY CLUSTERED (
[AlertGuid] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]')
EXECUTE (N'
CREATE NONCLUSTERED INDEX IX_Microsoft_SQLServer_Visualization_Library_LastAlertValues_ManagedEntityRowId_ResolutionState
ON [sdk].[Microsoft_SQLServer_Visualization_Library_LastAlertValues] ([ManagedEntityRowId],[ResolutionState])
')
END
EXECUTE (N'GRANT SELECT, INSERT, UPDATE, DELETE ON [SDK].[Microsoft_SQLServer_Visualization_Library_LastMonitorValues] TO OpsMgrReader')
DELETE FROM sdk.Microsoft_SQLServer_Visualization_Library_Tables;
DELETE FROM sdk.Microsoft_SQLServer_Visualization_Library_Table_Batches;
UPDATE sdk.Microsoft_SQLServer_Visualization_Library_DB_Version SET CurrentVersion = 6
END
GO
IF EXISTS (SELECT NULL FROM sdk.Microsoft_SQLServer_Visualization_Library_DB_Version WHERE CurrentVersion < 7)
BEGIN
UPDATE tb
SET StartDate = null , FinishDate = null
FROM sdk.Microsoft_SQLServer_Visualization_Library_Table_Batches tb
INNER JOIN sdk.Microsoft_SQLServer_Visualization_Library_Tables t ON t.TableId = tb.TableId
WHERE t.[Type] IN (5,6);
UPDATE sdk.Microsoft_SQLServer_Visualization_Library_DB_Version SET CurrentVersion = 7;
END
GO
ALTER PROCEDURE [sdk].[Microsoft_SQLServer_Visualization_Library_GetGroupClassMetadata]
(
@LANGUAGE_CODE varchar(10),
@MANAGEMENT_GROUP_GUID uniqueidentifier,
@GROUP_GUID uniqueidentifier
)
AS
BEGIN
DECLARE @ManagementGroupRowId int;
select @ManagementGroupRowId = mg.ManagementGroupRowId from vManagementGroup mg where mg.ManagementGroupGuid = @MANAGEMENT_GROUP_GUID
DECLARE @GroupRowId int;
select @GroupRowId = me.ManagedEntityRowId from vManagedEntity me with (NOLOCK) where me.ManagedEntityGuid = @GROUP_GUID
DECLARE @LatestMpVersions TABLE (
ManagementPackRowId int NOT NULL,
ManagementPackVersionRowId int NOT NULL
UNIQUE CLUSTERED (ManagementPackVersionRowId, ManagementPackRowId)
);
insert into @latestMpVersions
select mpv2.ManagementPackRowId, max(ManagementPackVersionRowId) as ManagementPackVersionRowId
from dbo.vManagementPackVersion mpv2 (NOLOCK)
group by mpv2.ManagementPackRowId
order by ManagementPackVersionRowId, ManagementPackRowId;
DECLARE @FilteredRT TABLE (
RelationshipTypeRowId int
);
; with parentRT as (
SELECT TOP 1 rt.RelationshipTypeRowId
from dbo.vRelationshipType rt WITH (NOLOCK)
where rt.RelationshipTypeSystemName = 'System.Containment'
),
filteredRT as (
SELECT RelationshipTypeRowId
from parentRT
UNION ALL
SELECT rth.Child as RelationshipTypeRowId
from parentRT rt WITH (NOLOCK)
inner join sdk.Microsoft_SQLServer_Visualization_Library_RelationshipType_Hierarchy rth WITH (NOLOCK) on rt.RelationshipTypeRowId = rth.Parent
)
insert into @FilteredRT
select * from filteredRT;
DECLARE @seed TABLE (
ManagedEntityRowId int NOT NULL,
ManagedEntityTypeRowId int NOT NULL,
[Level] int NOT NULL
UNIQUE CLUSTERED (ManagedEntityRowId, ManagedEntityTypeRowId, [Level])
);
; with data as (
select top 1 me.ManagedEntityRowId, 0 as [Level], cast(1 as bit) as [IsGroup] from dbo.vManagedEntity me where me.ManagedEntityRowId = @GroupRowId
UNION ALL
select
rhg.Child as ManagedEntityRowId,
s.Level + CASE WHEN s.Level = 2 THEN 0 ELSE 1 END as [Level],
CASE WHEN s.IsGroup = 1 THEN cast(0 as bit) ELSE cast(1 as bit) END as IsGroup
from data s
inner join sdk.Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy rhg with (nolock) on s.ManagedEntityRowId = rhg.Parent and s.IsGroup = rhg.IsGroup
),
updated_data as (
select distinct d.ManagedEntityRowId, vme.ManagedEntityTypeRowId, d.Level
from data d
inner join dbo.vManagedEntity vme with (nolock) on d.ManagedEntityRowId = vme.ManagedEntityRowId
inner join dbo.vManagedEntityManagementGroup memg WITH (NOLOCK) on vme.ManagedEntityRowId = memg.ManagedEntityRowId
where memg.ToDateTime is null and d.Level > 0
)
insert into @seed
select * from updated_data
order by ManagedEntityRowId, ManagedEntityTypeRowId, Level;
insert into @seed
select distinct -1 as ManagedEntityRowId, rtmpv.TargetManagedEntityTypeRowId as ManagedEntityTypeRowId, 1 as [Level]
from dbo.vManagedEntity me
inner join dbo.vManagedEntityManagementGroup memg1 WITH (NOLOCK) on me.ManagedEntityRowId = memg1.ManagedEntityRowId
inner join dbo.vRelationshipTypeManagementPackVersion rtmpv WITH (NOLOCK) on rtmpv.SourceManagedEntityTypeRowId = me.ManagedEntityTypeRowId
inner join vManagementPackVersion mpv with (NOLOCK) on rtmpv.ManagementPackVersionRowId = mpv.ManagementPackVersionRowId
where me.ManagedEntityRowId = @GroupRowId
and memg1.ToDateTime is null
and EXISTS (select null from @LatestMpVersions lm where rtmpv.ManagementPackVersionRowId = lm.ManagementPackVersionRowId and mpv.ManagementPackRowId = lm.ManagementPackRowId) ;
; with seed as (
select s.Level, s.ManagedEntityRowId, t.TypedManagedEntityRowId, t.ManagedEntityTypeRowId as TypedManagedEntityTypeRowId, s.ManagedEntityTypeRowId as ManagedEntityTypeRowId from @seed s
left join dbo.TypedManagedEntity t (NOLOCK) on s.ManagedEntityRowId = t.ManagedEntityRowId
),
typeIds as (
select distinct TypedManagedEntityTypeRowId as TypeRowId from seed
),
depthToAbstractRaw as (
select TypeRowId, TypeRowId as currentTypeRowId, 0 as depth from typeIds
UNION ALL
SELECT TypeRowId, t.BaseManagedEntityTypeRowId, d.depth + 1 as depth from depthToAbstractRaw d
inner join vManagedEntityTypeManagementPackVersion t (NOLOCK) on d.currentTypeRowId = t.ManagedEntityTypeRowId
inner join vManagementPackVersion mpv with (NOLOCK) on t.ManagementPackVersionRowId = mpv.ManagementPackVersionRowId
where t.AbstractInd = 0
and EXISTS (select null from @LatestMpVersions lm where t.ManagementPackVersionRowId = lm.ManagementPackVersionRowId and mpv.ManagementPackRowId = lm.ManagementPackRowId)
),
depthToAbstract as (
select TypeRowId, max(depth) as depth from depthToAbstractRaw group by TypeRowId
),
seedWithDepth as (
select s.Level, s.ManagedEntityRowId, s.ManagedEntityTypeRowId, s.TypedManagedEntityRowId, s.TypedManagedEntityTypeRowId, d.depth from seed s
left join depthToAbstract d on s.TypedManagedEntityTypeRowId = d.TypeRowId
),
updatedSeed as (
select s1.Level, s1.ManagedEntityRowId, COALESCE(t.TypedManagedEntityTypeRowId, s1.ManagedEntityTypeRowId) as ManagedEntityTypeRowId from @seed s1
CROSS APPLY (
select top 1 s2.TypedManagedEntityTypeRowId
from seedWithDepth s2
where s2.ManagedEntityRowId = s1.ManagedEntityRowId
order by s2.depth desc, s2.TypedManagedEntityRowId desc
) t
where s1.ManagedEntityRowId >= 0
)
update @seed
set ManagedEntityTypeRowId = u.ManagedEntityTypeRowId
from @seed s
inner join updatedSeed u on s.Level = u.Level and s.ManagedEntityRowId = u.ManagedEntityRowId
; with filteredTypes as (
select ManagedEntityTypeRowId, MIN([Level]) as [Level] from @seed group by ManagedEntityTypeRowId
)
insert into @classes
select
mpGroup.ManagementPackSystemName + '!' + metGroup.ManagedEntityTypeSystemName as ClassName,
COALESCE(ds.Name, metGroup.ManagedEntityTypeDefaultName) as DisplayName,
ft.ManagedEntityTypeRowId,
ft.[Level]
from filteredTypes ft
inner join dbo.vManagedEntityType metGroup with (nolock) on ft.ManagedEntityTypeRowId = metGroup.ManagedEntityTypeRowId
inner join dbo.vManagementPack mpGroup with (nolock) on metGroup.ManagementPackRowId = mpGroup.ManagementPackRowId
left join dbo.vDisplayString ds with (nolock) on metGroup.ManagedEntityTypeGuid = ds.ElementGuid and ds.LanguageCode = @LANGUAGE_CODE
;with fullTypeSpectre as
(
select distinct
s.ManagedEntityTypeRowId as GroupTypeRowId,
COALESCE(tme.ManagedEntityTypeRowId,s.ManagedEntityTypeRowId) as ManagedEntityTypeRowId
from dbo.vTypedManagedEntity tme WITH (NOLOCK)
right join @seed s on tme.ManagedEntityRowId = s.ManagedEntityRowId
UNION ALL
select fts.GroupTypeRowId, metmpv.BaseManagedEntityTypeRowId from fullTypeSpectre fts
inner join dbo.vManagedEntityTypeManagementPackVersion metmpv with (nolock) on fts.ManagedEntityTypeRowId = metmpv.ManagedEntityTypeRowId
inner join vManagementPackVersion mpv with (NOLOCK) on metmpv.ManagementPackVersionRowId = mpv.ManagementPackVersionRowId
where EXISTS (select null from @LatestMpVersions lm where metmpv.ManagementPackVersionRowId = lm.ManagementPackVersionRowId and mpv.ManagementPackRowId = lm.ManagementPackRowId)
)
insert into @ClassMapping
select DISTINCT GroupTypeRowId, ManagedEntityTypeRowId
from fullTypeSpectre
order by GroupTypeRowId, ManagedEntityTypeRowId;
INSERT INTO @Monitors
select
cm.GroupTypeRowId as GroupTypeRowId,
m.MonitorGuid as "Item!4!MonitorGuid",
mp.ManagementPackSystemName as "Item!4!MpSystemName",
m.MonitorSystemName as "Item!4!MonitorSystemName",
m.MonitorDefaultName as "Item!4!MonitorDefaultName",
ds.Name as "Item!4!MonitorName"
from @ClassMapping cm
join dbo.vMonitorManagementPackVersion mmpv WITH (NOLOCK) on cm.ManagedEntityTypeRowId = mmpv.TargetManagedEntityTypeRowId
join dbo.vMonitor m WITH (NOLOCK) on m.MonitorRowId = mmpv.MonitorRowId
inner join vManagementPackVersion mpv with (NOLOCK) on mmpv.ManagementPackVersionRowId = mpv.ManagementPackVersionRowId
join @LatestMpVersions lmv on lmv.ManagementPackVersionRowId = mmpv.ManagementPackVersionRowId and lmv.ManagementPackRowId = mpv.ManagementPackRowId
join dbo.vManagementPack mp WITH (NOLOCK) on mp.ManagementPackRowId = lmv.ManagementPackRowId
left join dbo.vDisplayString ds WITH (NOLOCK) on ds.ElementGuid = m.MonitorGuid and ds.LanguageCode = @LANGUAGE_CODE
INSERT INTO @Rules
select
cm.GroupTypeRowId as GroupTypeRowId,
r.RuleGuid as "Item!5!RuleGuid",
r.RuleDefaultName as "Item!5!RuleDefaultName",
ds.Name as "Item!5!RuleName"
from @ClassMapping cm
join dbo.vRuleManagementPackVersion rmpv WITH (NOLOCK) on cm.ManagedEntityTypeRowId = rmpv.TargetManagedEntityTypeRowId
join dbo.vWorkflowCategory wc WITH (NOLOCK) on rmpv.WorkflowCategoryRowId = wc.WorkflowCategoryRowId
join dbo.vRule r WITH (NOLOCK) on r.RuleRowId = rmpv.RuleRowId
inner join vManagementPackVersion mpv with (NOLOCK) on rmpv.ManagementPackVersionRowId = mpv.ManagementPackVersionRowId
join @LatestMpVersions lmv on lmv.ManagementPackVersionRowId = rmpv.ManagementPackVersionRowId and lmv.ManagementPackRowId = mpv.ManagementPackRowId
join dbo.vManagementPack mp WITH (NOLOCK) on mp.ManagementPackRowId = lmv.ManagementPackRowId
left join dbo.vDisplayString ds WITH (NOLOCK) on ds.ElementGuid = r.RuleGuid and ds.LanguageCode = @LANGUAGE_CODE
where wc.WorkflowCategorySystemName = 'PerformanceCollection'
SELECT
999 As TAG,
null As PARENT,
'' As [ArrayOfMetadata!999],
null as [Metadata!1!Level],
null as [Metadata!1!ClassName],
null as [Metadata!1!DisplayName],
null as [Monitors!2],
null as [Rules!3],
null as [Item!4!MonitorGuid],
null as "Item!4!MpSystemName",
null as "Item!4!MonitorSystemName",
null as "Item!4!MonitorDefaultName",
null as "Item!4!MonitorName",
null as "Item!5!RuleGuid",
null as "Item!5!RuleDefaultName",
null as "Item!5!RuleName"
UNION ALL
SELECT
1 As TAG,
999 As PARENT,
'' As [ArrayOfMetadata!999],
c.[Level] as [Metadata!1!Level],
c.ClassName as [Metadata!1!ClassName],
c.DisplayName as [Metadata!1!DisplayName],
null as [Monitors!2],
null as [Rules!3],
null as [Item!4!MonitorGuid],
null as "Item!4!MpSystemName",
null as "Item!4!MonitorSystemName",
null as "Item!4!MonitorDefaultName",
null as "Item!4!MonitorName",
null as "Item!5!RuleGuid",
null as "Item!5!RuleDefaultName",
null as "Item!5!RuleName"
FROM @classes c
UNION ALL
SELECT
2 As TAG,
1 As PARENT,
'' As [ArrayOfMetadata!999],
c.[Level] as [Metadata!1!Level],
c.ClassName as [Metadata!1!ClassName],
c.DisplayName as [Metadata!1!DisplayName],
'' as [Monitors!2],
null as [Rules!3],
null as [Item!4!MonitorGuid],
null as "Item!4!MpSystemName",
null as "Item!4!MonitorSystemName",
null as "Item!4!MonitorDefaultName",
null as "Item!4!MonitorName",
null as "Item!5!RuleGuid",
null as "Item!5!RuleDefaultName",
null as "Item!5!RuleName"
FROM @classes c
UNION ALL
SELECT
4 As TAG,
2 As PARENT,
'' As [ArrayOfMetadata!999],
c.[Level] as [Metadata!1!Level],
c.ClassName as [Metadata!1!ClassName],
c.DisplayName as [Metadata!1!DisplayName],
'' as [Monitors!2],
null as [Rules!3],
m.[Item!4!MonitorGuid] as [Item!4!MonitorGuid],
m.[Item!4!MpSystemName] as "Item!4!MpSystemName",
m.[Item!4!MonitorSystemName] as "Item!4!MonitorSystemName",
m.[Item!4!MonitorDefaultName] as "Item!4!MonitorDefaultName",
m.[Item!4!MonitorName] as "Item!4!MonitorName",
null as "Item!5!RuleGuid",
null as "Item!5!RuleDefaultName",
null as "Item!5!RuleName"
FROM @classes c
inner join @Monitors m on c.ManagedEntityTypeRowId = m.GroupTypeRowId
UNION ALL
SELECT
3 As TAG,
1 As PARENT,
'' As [ArrayOfMetadata!999],
c.[Level] as [Metadata!1!Level],
c.ClassName as [Metadata!1!ClassName],
c.DisplayName as [Metadata!1!DisplayName],
null as [Monitors!2],
'' as [Rules!3],
null as [Item!4!MonitorGuid],
null as "Item!4!MpSystemName",
null as "Item!4!MonitorSystemName",
null as "Item!4!MonitorDefaultName",
null as "Item!4!MonitorName",
null as "Item!5!RuleGuid",
null as "Item!5!RuleDefaultName",
null as "Item!5!RuleName"
FROM @classes c
UNION ALL
SELECT
5 As TAG,
3 As PARENT,
'' As [ArrayOfMetadata!999],
c.[Level] as [Metadata!1!Level],
c.ClassName as [Metadata!1!ClassName],
c.DisplayName as [Metadata!1!DisplayName],
null as [Monitors!2],
'' as [Rules!3],
null as [Item!4!MonitorGuid],
null as "Item!4!MpSystemName",
null as "Item!4!MonitorSystemName",
null as "Item!4!MonitorDefaultName",
null as "Item!4!MonitorName",
r.[Item!5!RuleGuid] as "Item!5!RuleGuid",
r.[Item!5!RuleDefaultName] as "Item!5!RuleDefaultName",
r.[Item!5!RuleName] as "Item!5!RuleName"
FROM @classes c
inner join @Rules r on c.ManagedEntityTypeRowId = r.GroupTypeRowId
ORDER BY [ArrayOfMetadata!999],
[Metadata!1!Level],
[Metadata!1!ClassName],
[Metadata!1!DisplayName],
[Monitors!2],
[Item!4!MonitorName],
[Item!4!MonitorDefaultName],
[Item!4!MonitorSystemName],
[Rules!3],
[Item!5!RuleName],
[Item!5!RuleDefaultName]
FOR XML EXPLICIT
END
GO
GRANT EXECUTE ON [SDK].[Microsoft_SQLServer_Visualization_Library_GetGroupClassMetadata] TO OpsMgrReader
GO
ALTER PROCEDURE [sdk].[Microsoft_SQLServer_Visualization_Library_GetGroups]
(
@LANGUAGE_CODE nvarchar(max),
@MANAGEMENT_GROUP_GUID uniqueidentifier,
@XML_DATA XML
)
AS
BEGIN
DECLARE @managementGroupRowId int;
select @managementGroupRowId = mg.ManagementGroupRowId
from dbo.vManagementGroup mg WITH (NOLOCK)
where mg.ManagementGroupGuid = @MANAGEMENT_GROUP_GUID;
DECLARE @allowedGroups TABLE (
Id int PRIMARY KEY
);
;with allowedGroups as (
select DISTINCT ParamValues.x.value('@ID','uniqueidentifier') as [Guid] from @XML_DATA.nodes('/OpsManagerConfiguration/AllowedGroup') AS ParamValues(x)
)
insert into @allowedGroups
select me.ManagedEntityRowId from allowedGroups a
inner join dbo.ManagedEntity me WITH (NOLOCK) on a.[Guid] = me.ManagedEntityGuid
inner join dbo.ManagedEntityManagementGroup memg WITH (NOLOCK) on me.ManagedEntityRowId = memg.ManagedEntityRowId
where me.ManagementGroupRowId = @managementGroupRowId
and memg.ToDateTime is null;
;with mp as (
select ManagementPackRowId
from dbo.vManagementPack (NOLOCK)
where ManagementPackSystemName = 'System.Library'
),
latestMpVersion as
(select mpv2.ManagementPackRowId, max(ManagementPackVersionRowId) as ManagementPackVersionRowId
from dbo.vManagementPackVersion mpv2 (NOLOCK)
group by mpv2.ManagementPackRowId),
met as (
select vmet.ManagedEntityTypeRowId, vmet.ManagedEntityTypeGuid, vmet.ManagedEntityTypeSystemName
from dbo.vManagedEntityType vmet (NOLOCK)
inner join dbo.vManagedEntityTypeManagementPackVersion vmetmpv (NOLOCK) on vmet.ManagedEntityTypeRowId = vmetmpv.ManagedEntityTypeRowId
inner join latestMpVersion on latestMpVersion.ManagementPackVersionRowId = vmetmpv.ManagementPackVersionRowId
inner join mp on latestMpVersion.ManagementPackRowId = mp.ManagementPackRowId
where vmet.ManagedEntityTypeSystemName = 'System.Group'
UNION ALL
select vmet.ManagedEntityTypeRowId, vmet.ManagedEntityTypeGuid, vmet.ManagedEntityTypeSystemName
from dbo.vManagedEntityType vmet (NOLOCK)
inner join dbo.vManagedEntityTypeManagementPackVersion vmetmpv (NOLOCK) on vmet.ManagedEntityTypeRowId = vmetmpv.ManagedEntityTypeRowId
inner join latestMpVersion on latestMpVersion.ManagementPackVersionRowId = vmetmpv.ManagementPackVersionRowId
inner join met on met.ManagedEntityTypeRowId = vmetmpv.BaseManagedEntityTypeRowId
where vmetmpv.Accessibility = 'Public'
),
loc as (
select
met.ManagedEntityTypeGuid,
vme.ManagedEntityGuid,
vme.FullName,
vme.DisplayName,
CASE
WHEN met.ManagedEntityTypeGuid != vme.ManagedEntityGuid
THEN REPLACE(REPLACE(REPLACE(vme.FullName, ';'+vme.DisplayName, ''), ':'+vme.DisplayName, ''), met.ManagedEntityTypeSystemName, vme.DisplayName)
ELSE COALESCE(vds_loc.Name, vds.Name, met.ManagedEntityTypeSystemName)
END as Name
from met
inner join dbo.vManagedEntity vme (NOLOCK) on vme.ManagedEntityTypeRowId = met.ManagedEntityTypeRowId
inner join @allowedGroups ag on vme.ManagedEntityRowId = ag.Id
inner join dbo.vManagedEntityManagementGroup vmemg (NOLOCK) on vme.ManagedEntityRowId = vmemg.ManagedEntityRowId
left join dbo.vDisplayString vds (NOLOCK) on met.ManagedEntityTypeGuid = vds.ElementGuid and vds.LanguageCode = 'ENU'
left join dbo.vDisplayString vds_loc (NOLOCK) on met.ManagedEntityTypeGuid = vds_loc.ElementGuid and vds_loc.LanguageCode = @LANGUAGE_CODE
where vmemg.ToDateTime is null and vme.ManagementGroupRowId = @managementGroupRowId
)
select * from loc order by DisplayName
END
GO
GRANT EXECUTE ON [SDK].[Microsoft_SQLServer_Visualization_Library_GetGroups] TO OpsMgrReader
GO
ALTER PROCEDURE [sdk].[Microsoft_SQLServer_Visualization_Library_UpdateTablesList]
AS
BEGIN
SET NOCOUNT ON
declare @hangOffset int = -180;
DECLARE @tablesInternal TABLE (
TableId bigint NULL,
[Name] sysname NOT NULL,
[Type] tinyint NOT NULL,
LastId bigint NOT NULL,
FirstDate datetime null
);
insert into @tablesInternal ([Name], [Type], LastId)
SELECT so.name as [Name], 1 as [Type], -1 as LastId FROM sysobjects so
WHERE so.type = 'U' AND so.name LIKE 'PerfRaw_________________________________' AND so.UID = SCHEMA_ID('PERF')
UNION ALL
SELECT so.name as [Name], 2 as [Type], -1 as LastId FROM sysobjects so
WHERE so.type = 'U' AND so.name LIKE 'StateRaw_________________________________' AND so.UID = SCHEMA_ID('STATE')
UNION ALL
SELECT so.name as [Name], 4 as [Type], -1 as LastId FROM sysobjects so
WHERE so.type = 'U' AND so.name LIKE 'AlertResolutionState_________________________________' AND so.UID = SCHEMA_ID('ALERT')
UNION ALL
SELECT so.name as [Name], 5 as [Type], -1 as LastId FROM sysobjects so
WHERE so.type = 'U' AND so.name = 'ManagementPackVersion' AND so.UID = SCHEMA_ID('dbo')
UNION ALL
SELECT so.name as [Name], 6 as [Type], -1 as LastId FROM sysobjects so
WHERE so.type = 'U' AND so.name = 'RelationshipManagementGroup' AND so.UID = SCHEMA_ID('dbo');
update @tablesInternal
set TableId = t.TableId, FirstDate = t.FirstDate
from @tablesInternal ti
join sdk.Microsoft_SQLServer_Visualization_Library_Tables t (nolock) on t.Name = ti.Name and t.[Type] = ti.[Type];
declare @query nvarchar(200), @tiName sysname, @tiDateTime datetime, @tiLastId bigint
Declare temp Cursor LOCAL For Select Name From @tablesInternal ti;
Open temp;
Fetch next From temp into @tiName;
While @@Fetch_Status=0 Begin
SET @query = CASE
WHEN @tiName like 'PerfRaw_________________________________' THEN N'SELECT TOP 1 @tiDateTime = [DateTime] FROM Perf.' + @tiName + ' WITH (NOLOCK) ORDER BY [PerfRawRowID] ASC'
WHEN @tiName like 'StateRaw_________________________________' THEN N'SELECT TOP 1 @tiDateTime = [DateTime] FROM State.' + @tiName + ' WITH (NOLOCK) ORDER BY [StateRawRowId] ASC'
WHEN @tiName like 'AlertResolutionState_________________________________' THEN N'SELECT TOP 1 @tiDateTime = [DWCreatedDateTime] FROM Alert.' + @tiName + ' WITH (NOLOCK) ORDER BY [AlertResolutionStateRowId] ASC'
WHEN @tiName = 'ManagementPackVersion' THEN N'SELECT TOP 1 @tiDateTime = [DWCreatedDateTime] FROM dbo.' + @tiName + ' WITH (NOLOCK) ORDER BY [ManagementPackVersionRowId] ASC'
WHEN @tiName = 'RelationshipManagementGroup' THEN N'SELECT TOP 1 @tiDateTime = [DWCreatedDateTime] FROM dbo.' + @tiName + ' WITH (NOLOCK) ORDER BY [RelationshipManagementGroupRowId] ASC'
END;
SET @query = CASE
WHEN @tiName like 'PerfRaw_________________________________' THEN N'SELECT TOP 1 @tiLastId = [PerfRawRowID] FROM Perf.' + @tiName + ' WITH (NOLOCK) ORDER BY [PerfRawRowID] DESC'
WHEN @tiName like 'StateRaw_________________________________' THEN N'SELECT TOP 1 @tiLastId = [StateRawRowId] FROM State.' + @tiName + ' WITH (NOLOCK) ORDER BY [StateRawRowId] DESC'
WHEN @tiName like 'AlertResolutionState_________________________________' THEN N'SELECT TOP 1 @tiLastId = [AlertResolutionStateRowId] FROM Alert.' + @tiName + ' WITH (NOLOCK) ORDER BY [AlertResolutionStateRowId] DESC'
WHEN @tiName = 'ManagementPackVersion' THEN N'SELECT TOP 1 @tiLastId = [ManagementPackVersionRowId] FROM dbo.' + @tiName + ' WITH (NOLOCK) ORDER BY [ManagementPackVersionRowId] DESC'
WHEN @tiName = 'RelationshipManagementGroup' THEN N'SELECT TOP 1 @tiLastId = [RelationshipManagementGroupRowId] FROM dbo.' + @tiName + ' WITH (NOLOCK) ORDER BY [RelationshipManagementGroupRowId] DESC'
END;
EXEC sp_executesql @query,
N'@tiLastId int OUTPUT',
@tiLastId = @tiLastId OUTPUT
update @tablesInternal
set FirstDate = @tiDateTime, LastId = @tiLastId
where Name = @tiName;
Fetch next From temp into @tiName;
End
Close temp;
Deallocate temp;
BEGIN TRAN
insert into sdk.Microsoft_SQLServer_Visualization_Library_Tables
SELECT t.Name as Name, t.[Type] as [Type], t.LastId as LastProcessedId, t.FirstDate FROM @tablesInternal t
WHERE NOT EXISTS (
SELECT NULL
FROM sdk.Microsoft_SQLServer_Visualization_Library_Tables pt
WHERE t.Name = pt.Name and t.[Type] = pt.[Type]);
DELETE FROM sdk.Microsoft_SQLServer_Visualization_Library_Tables
where name in (SELECT pt.Name FROM @tablesInternal t
RIGHT JOIN sdk.Microsoft_SQLServer_Visualization_Library_Tables pt on t.Name = pt.Name and t.[Type] = pt.[Type]
where t.Name is null);
COMMIT
-- remove batches for non-exist tables
DELETE tb from sdk.Microsoft_SQLServer_Visualization_Library_Table_Batches tb
left join sdk.Microsoft_SQLServer_Visualization_Library_Tables t on tb.TableId = t.TableId
where t.TableId is null;
--load table Ids for inserted tables
update ti
set TableId = t.TableId
from @tablesInternal ti
join sdk.Microsoft_SQLServer_Visualization_Library_Tables t (nolock) on t.Name = ti.Name and t.[Type] = ti.[Type]
where ti.TableId is null;
declare @step int = 50000;
declare @now datetime = GetUtcDate();
;with someRowsSeed as (
SELECT 1 as Dummy
UNION ALL
SELECT 1
UNION ALL
SELECT 1
UNION ALL
SELECT 1
UNION ALL
SELECT 1
UNION ALL
SELECT 1),
someRows as (SELECT f1.Dummy FROM someRowsSeed f1
CROSS JOIN someRowsSeed f2
CROSS JOIN someRowsSeed f3
CROSS JOIN someRowsSeed f4),
tableList as (
SELECT pt.TableId, pt.LastId as LastId, COALESCE(max(tb.LastId), -1) as PreviousId from @tablesInternal pt
left join sdk.Microsoft_SQLServer_Visualization_Library_Table_Batches tb with (nolock) on pt.TableId = tb.TableId
group by pt.TableId, pt.LastId
),
diapazones as (
select
t.TableId as TableId,
t.PreviousId + @step*(ROW_NUMBER() over (PARTITION BY t.TableId ORDER BY p.Dummy) - 1) as FirstId,
CASE WHEN t.PreviousId + @step*(ROW_NUMBER() over (PARTITION BY t.TableId ORDER BY p.Dummy)) < t.LastId THEN t.PreviousId + @step*(ROW_NUMBER() over (PARTITION BY t.TableId ORDER BY p.Dummy)) ELSE t.LastId END as LastId
FROM someRows p
cross join tableList t
)
insert into sdk.Microsoft_SQLServer_Visualization_Library_Table_Batches(TableId, FirstId, LastId, Tries, CreateDate)
select d.TableId, d.FirstId, d.LastId, 0, @now from diapazones d
inner join tableList tl on d.TableId = tl.TableId
where tl.LastId > d.FirstId
update sdk.Microsoft_SQLServer_Visualization_Library_Table_Batches
set StartDate = null, Tries = Tries + 1
where StartDate is not null and StartDate < @hangOffsetDate and FinishDate is null;
END
GO
GRANT EXECUTE ON [SDK].[Microsoft_SQLServer_Visualization_Library_UpdateTablesList] TO OpsMgrReader
GO
ALTER PROCEDURE [sdk].[Microsoft_SQLServer_Visualization_Library_UpdateLastValues]
AS
BEGIN
SET NOCOUNT ON
declare @delay int = 5;
while (@delay > 0)
begin
IF EXISTS (SELECT NULL
FROM sdk.Microsoft_SQLServer_Visualization_Library_Table_Batches b
inner join sdk.Microsoft_SQLServer_Visualization_Library_Tables t on b.TableId = t.TableId
where b.StartDate is null and t.Type in (1,2,4))
begin
BREAK
end
IF EXISTS (SELECT NULL
FROM sdk.Microsoft_SQLServer_Visualization_Library_Table_Batches b
inner join sdk.Microsoft_SQLServer_Visualization_Library_Tables t on b.TableId = t.TableId
where b.StartDate is not null and b.FinishDate is null and t.Type in (1,2,4))
BEGIN
WAITFOR DELAY '00:00:01'
END
set @delay = @delay - 1;
end
-- Performance V2
WHILE (1=1)
BEGIN
update b
set StartDate = GetUtcDate()
output inserted.TableId, inserted.BatchId, inserted.FirstId, inserted.LastId into @CurrentBatch
from sdk.Microsoft_SQLServer_Visualization_Library_Table_Batches b
where b.BatchId = (select top 1 b.BatchId from sdk.Microsoft_SQLServer_Visualization_Library_Table_Batches b
inner join sdk.Microsoft_SQLServer_Visualization_Library_Tables t on b.TableId = t.TableId
where b.StartDate is null and t.Type = 1
order by t.FirstDate desc, b.BatchId desc)
select top 1 @firstId = FirstId, @lastId = LastId from @CurrentBatch
IF @@ROWCOUNT = 0
BREAK
select @quotedName = QUOTENAME(t.Name ,'"')
from sdk.Microsoft_SQLServer_Visualization_Library_Tables t
inner join @CurrentBatch b on b.TableId = t.TableId;
set @sql = N';with latestPerf as (
select
max(vpr.PerfRawRowId) as maxPerfRawRowId,
vpr.PerformanceRuleInstanceRowId,
vpr.ManagedEntityRowId
FROM [Perf].' + @quotedName + N' vpr WITH (NOLOCK)
where vpr.PerfRawRowId > @firstId and vpr.PerfRawRowId <= @lastId
GROUP BY vpr.PerformanceRuleInstanceRowId, vpr.ManagedEntityRowId
),
insertable as (
select
vpr.PerfRawRowId,
vpr.[DateTime],
vpr.ManagedEntityRowId,
vpr.PerformanceRuleInstanceRowId,
vpr.SampleValue,
ROW_NUMBER() OVER (PARTITION BY vpr.PerformanceRuleInstanceRowId, vpr.ManagedEntityRowId, vpr.[DateTime] ORDER BY vpr.PerfRawRowId DESC) AS rn
FROM latestPerf m
inner join [Perf].' + @quotedName + N' vpr WITH (NOLOCK)
on m.PerformanceRuleInstanceRowId = vpr.PerformanceRuleInstanceRowId
and vpr.ManagedEntityRowId = m.ManagedEntityRowId
and vpr.PerfRawRowId = m.maxPerfRawRowId
where vpr.PerfRawRowId > @firstId and vpr.PerfRawRowId <= @lastId
)
insert into sdk.Microsoft_SQLServer_Visualization_Library_LastPerfValues (ManagedEntityRowId, PerformanceRuleInstanceRowId, [DateTime], SampleValue)
SELECT fv.ManagedEntityRowId, fv.PerformanceRuleInstanceRowId, fv.[DateTime], fv.SampleValue from insertable fv
WHERE fv.rn = 1
order by fv.ManagedEntityRowId, fv.PerformanceRuleInstanceRowId;
';
set @deadlockRetries = @maxDeadlockCount;
while (@deadlockRetries > 0)
begin
BEGIN TRY
BEGIN TRANSACTION
EXEC @ExecError = sp_executesql @sql, N'@firstId bigint, @lastId bigint', @firstId, @lastId;
COMMIT TRANSACTION
BREAK
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION
IF ERROR_NUMBER() = 1205 and @deadlockRetries > 0
SET @deadlockretries = @deadlockretries - 1
ELSE
EXEC [SDK].[Microsoft_SQLServer_Visualization_Library_RethrowError]
END CATCH
end;
set @sql = N';with latestPerf as (
select
max(vpr.PerfRawRowId) as maxPerfRawRowId,
vpr.PerformanceRuleInstanceRowId,
vpr.ManagedEntityRowId
FROM [Perf].' + @quotedName + N' vpr WITH (NOLOCK)
where vpr.PerfRawRowId > @firstId and vpr.PerfRawRowId <= @lastId
GROUP BY vpr.PerformanceRuleInstanceRowId, vpr.ManagedEntityRowId
),
insertable as (
select
vpr.PerfRawRowId,
vpr.[DateTime],
vpr.ManagedEntityRowId,
vpr.PerformanceRuleInstanceRowId,
vpr.SampleValue,
ROW_NUMBER() OVER (PARTITION BY vpr.PerformanceRuleInstanceRowId, vpr.ManagedEntityRowId, vpr.[DateTime] ORDER BY vpr.PerfRawRowId DESC) AS rn
FROM latestPerf m
inner join [Perf].' + @quotedName + N' vpr WITH (NOLOCK)
on m.PerformanceRuleInstanceRowId = vpr.PerformanceRuleInstanceRowId
and vpr.ManagedEntityRowId = m.ManagedEntityRowId
and vpr.PerfRawRowId = m.maxPerfRawRowId
where vpr.PerfRawRowId > @firstId and vpr.PerfRawRowId <= @lastId
)
update pt
SET DateTime = fv.DateTime,
SampleValue = fv.SampleValue
FROM sdk.Microsoft_SQLServer_Visualization_Library_LastPerfValues pt
inner join insertable fv
on pt.ManagedEntityRowId = fv.ManagedEntityRowId
and pt.PerformanceRuleInstanceRowId = fv.PerformanceRuleInstanceRowId
where fv.DateTime > pt.DateTime and fv.rn = 1;';
set @deadlockRetries = @maxDeadlockCount;
while (@deadlockRetries > 0)
begin
BEGIN TRY
BEGIN TRANSACTION
EXEC @ExecError = sp_executesql @sql, N'@firstId bigint, @lastId bigint', @firstId, @lastId;
COMMIT TRANSACTION
BREAK
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION
IF ERROR_NUMBER() = 1205 and @deadlockRetries > 0
SET @deadlockretries = @deadlockretries - 1
ELSE
EXEC [SDK].[Microsoft_SQLServer_Visualization_Library_RethrowError]
END CATCH
end;
update b
set FinishDate = GetUtcDate()
from sdk.Microsoft_SQLServer_Visualization_Library_Table_Batches b
inner join @CurrentBatch cb on b.BatchId = cb.BatchId;
delete from @CurrentBatch;
END; -- WHILE (1=1)
-- State V2
WHILE (1=1)
BEGIN
update b
set StartDate = GetUtcDate()
output inserted.TableId, inserted.BatchId, inserted.FirstId, inserted.LastId into @CurrentBatch
from sdk.Microsoft_SQLServer_Visualization_Library_Table_Batches b
where b.BatchId = (select top 1 b.BatchId from sdk.Microsoft_SQLServer_Visualization_Library_Table_Batches b
inner join sdk.Microsoft_SQLServer_Visualization_Library_Tables t on b.TableId = t.TableId
where b.StartDate is null and t.Type = 2
order by t.FirstDate desc, b.BatchId desc)
select top 1 @firstId = FirstId, @lastId = LastId from @CurrentBatch
IF @@ROWCOUNT = 0
BREAK
select @quotedName = QUOTENAME(t.Name ,'"')
from sdk.Microsoft_SQLServer_Visualization_Library_Tables t
inner join @CurrentBatch b on b.TableId = t.TableId;
set @sql = N';with latestState as (
select
max(vsr.DateTime) as maxDateTime,
vsr.ManagedEntityMonitorRowId
FROM [State].' + @quotedName + N' vsr WITH (NOLOCK)
where vsr.StateRawRowId > @firstId and vsr.StateRawRowId <= @lastId
GROUP BY vsr.ManagedEntityMonitorRowId
),
insertable as (
select
vsr.StateRawRowId,
vsr.[DateTime],
vsr.ManagedEntityMonitorRowId,
vsr.NewHealthState as HealthState,
ROW_NUMBER() OVER (PARTITION BY vsr.ManagedEntityMonitorRowId, vsr.[DateTime] ORDER BY vsr.StateRawRowId DESC) AS rn
FROM latestState m
inner join [State].' + @quotedName + N' vsr WITH (NOLOCK)
on vsr.ManagedEntityMonitorRowId = m.ManagedEntityMonitorRowId
and vsr.DateTime = m.maxDateTime
where vsr.StateRawRowId > @firstId and vsr.StateRawRowId <= @lastId
)
insert into sdk.Microsoft_SQLServer_Visualization_Library_LastMonitorValues (ManagedEntityRowId, MonitorRowId, [DateTime], [HealthState])
SELECT mem.ManagedEntityRowId, mem.MonitorRowId, fv.[DateTime], fv.[HealthState] from insertable fv
inner join dbo.ManagedEntityMonitor mem on mem.ManagedEntityMonitorRowId = fv.ManagedEntityMonitorRowId
WHERE fv.rn = 1
ORDER BY mem.ManagedEntityRowId, mem.MonitorRowId;
';
set @deadlockRetries = @maxDeadlockCount;
while (@deadlockRetries > 0)
begin
BEGIN TRY
BEGIN TRANSACTION
EXEC @ExecError = sp_executesql @sql, N'@firstId bigint, @lastId bigint', @firstId, @lastId;
COMMIT TRANSACTION
BREAK
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION
IF ERROR_NUMBER() = 1205 and @deadlockRetries > 0
SET @deadlockretries = @deadlockretries - 1
ELSE
EXEC [SDK].[Microsoft_SQLServer_Visualization_Library_RethrowError]
END CATCH
end;
set @sql = N';with latestState as (
select
max(vsr.DateTime) as maxDateTime,
vsr.ManagedEntityMonitorRowId
FROM [State].' + @quotedName + N' vsr WITH (NOLOCK)
where vsr.StateRawRowId > @firstId and vsr.StateRawRowId <= @lastId
GROUP BY vsr.ManagedEntityMonitorRowId
),
insertable as (
select
vsr.StateRawRowId,
vsr.[DateTime],
vsr.ManagedEntityMonitorRowId,
vsr.NewHealthState as HealthState,
ROW_NUMBER() OVER (PARTITION BY vsr.ManagedEntityMonitorRowId, vsr.[DateTime] ORDER BY vsr.StateRawRowId DESC) AS rn
FROM latestState m
inner join [State].' + @quotedName + N' vsr WITH (NOLOCK)
on vsr.ManagedEntityMonitorRowId = m.ManagedEntityMonitorRowId
and vsr.DateTime = m.maxDateTime
where vsr.StateRawRowId > @firstId and vsr.StateRawRowId <= @lastId
)
update st
SET DateTime = fv.DateTime,
[HealthState] = fv.[HealthState]
FROM sdk.Microsoft_SQLServer_Visualization_Library_LastMonitorValues st
inner join dbo.ManagedEntityMonitor mem on mem.ManagedEntityRowId = st.ManagedEntityRowId and mem.MonitorRowId = st.MonitorRowId
inner join insertable fv on mem.[ManagedEntityMonitorRowId] = fv.[ManagedEntityMonitorRowId]
where fv.DateTime > st.DateTime and fv.rn = 1;';
set @deadlockRetries = @maxDeadlockCount;
while (@deadlockRetries > 0)
begin
BEGIN TRY
BEGIN TRANSACTION
EXEC @ExecError = sp_executesql @sql, N'@firstId bigint, @lastId bigint', @firstId, @lastId;
COMMIT TRANSACTION
BREAK
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION
IF ERROR_NUMBER() = 1205 and @deadlockRetries > 0
SET @deadlockretries = @deadlockretries - 1
ELSE
EXEC [SDK].[Microsoft_SQLServer_Visualization_Library_RethrowError]
END CATCH
end;
update b
set FinishDate = GetUtcDate()
from sdk.Microsoft_SQLServer_Visualization_Library_Table_Batches b
inner join @CurrentBatch cb on b.BatchId = cb.BatchId;
delete from @CurrentBatch;
END; -- WHILE (1=1)
-- Alert Resolution State V2
WHILE (1=1)
BEGIN
update b
set StartDate = GetUtcDate()
output inserted.TableId, inserted.BatchId, inserted.FirstId, inserted.LastId into @CurrentBatch
from sdk.Microsoft_SQLServer_Visualization_Library_Table_Batches b
where b.BatchId = (select top 1 b.BatchId from sdk.Microsoft_SQLServer_Visualization_Library_Table_Batches b
inner join sdk.Microsoft_SQLServer_Visualization_Library_Tables t on b.TableId = t.TableId
where b.StartDate is null and t.Type = 4
order by t.FirstDate desc, b.BatchId desc)
select top 1 @firstId = FirstId, @lastId = LastId from @CurrentBatch
IF @@ROWCOUNT = 0
BREAK
select @quotedName = QUOTENAME(t.Name ,'"')
from sdk.Microsoft_SQLServer_Visualization_Library_Tables t
inner join @CurrentBatch b on b.TableId = t.TableId;
set @sql = N';with latestResolutionState as (
select
max(vsr.StateSetDateTime) as maxDateTime,
vsr.[AlertGuid]
FROM [Alert].' + @quotedName + N' vsr WITH (NOLOCK)
where vsr.AlertResolutionStateRowId > @firstId and vsr.AlertResolutionStateRowId <= @lastId
GROUP BY vsr.AlertGuid
),
insertable as (
select
vsr.[AlertResolutionStateRowId],
vsr.StateSetDateTime as DateTime,
vsr.[AlertGuid],
vsr.[ResolutionState],
ROW_NUMBER() OVER (PARTITION BY vsr.AlertGuid, vsr.[StateSetDateTime] ORDER BY vsr.AlertResolutionStateRowId DESC) AS rn
FROM latestResolutionState m
inner join [Alert].' + @quotedName + N' vsr WITH (NOLOCK)
on m.AlertGuid = vsr.AlertGuid
and vsr.[StateSetDateTime] = m.maxDateTime
where vsr.AlertResolutionStateRowId > @firstId and vsr.AlertResolutionStateRowId <= @lastId
)
insert into sdk.Microsoft_SQLServer_Visualization_Library_LastAlertValues (AlertGuid, [ManagedEntityRowId], [DateTime], Severity, ResolutionState)
SELECT fv.AlertGuid, a.[ManagedEntityRowId], fv.[DateTime], a.Severity, fv.ResolutionState as ResolutionState from insertable fv
inner join alert.vAlert a on fv.AlertGuid = a.AlertGuid
WHERE fv.rn = 1
ORDER BY fv.AlertGuid;
';
set @deadlockRetries = @maxDeadlockCount;
while (@deadlockRetries > 0)
begin
BEGIN TRY
BEGIN TRANSACTION
EXEC @ExecError = sp_executesql @sql, N'@firstId bigint, @lastId bigint', @firstId, @lastId;
COMMIT TRANSACTION
BREAK
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION
IF ERROR_NUMBER() = 1205 and @deadlockRetries > 0
SET @deadlockretries = @deadlockretries - 1
ELSE
EXEC [SDK].[Microsoft_SQLServer_Visualization_Library_RethrowError]
END CATCH
end;
set @sql = N';with latestResolutionState as (
select
max(vsr.StateSetDateTime) as maxDateTime,
vsr.[AlertGuid]
FROM [Alert].' + @quotedName + N' vsr WITH (NOLOCK)
where vsr.AlertResolutionStateRowId > @firstId and vsr.AlertResolutionStateRowId <= @lastId
GROUP BY vsr.AlertGuid
),
insertable as (
select
vsr.[AlertResolutionStateRowId],
vsr.StateSetDateTime as DateTime,
vsr.[AlertGuid],
vsr.[ResolutionState],
ROW_NUMBER() OVER (PARTITION BY vsr.AlertGuid, vsr.[StateSetDateTime] ORDER BY vsr.AlertResolutionStateRowId DESC) AS rn
FROM latestResolutionState m
inner join [Alert].' + @quotedName + N' vsr WITH (NOLOCK)
on m.AlertGuid = vsr.AlertGuid
and vsr.[StateSetDateTime] = m.maxDateTime
where vsr.AlertResolutionStateRowId > @firstId and vsr.AlertResolutionStateRowId <= @lastId
)
update avt
SET DateTime = fv.DateTime,
[ResolutionState] = fv.[ResolutionState]
FROM sdk.Microsoft_SQLServer_Visualization_Library_LastAlertValues avt
inner join insertable fv
on avt.AlertGuid = fv.AlertGuid
where fv.DateTime > avt.DateTime and fv.rn = 1;';
set @deadlockRetries = @maxDeadlockCount;
while (@deadlockRetries > 0)
begin
BEGIN TRY
BEGIN TRANSACTION
EXEC @ExecError = sp_executesql @sql, N'@firstId bigint, @lastId bigint', @firstId, @lastId;
COMMIT TRANSACTION
BREAK
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION
IF ERROR_NUMBER() = 1205 and @deadlockRetries > 0
SET @deadlockretries = @deadlockretries - 1
ELSE
EXEC [SDK].[Microsoft_SQLServer_Visualization_Library_RethrowError]
END CATCH
end;
update b
set FinishDate = GetUtcDate()
from sdk.Microsoft_SQLServer_Visualization_Library_Table_Batches b
inner join @CurrentBatch cb on b.BatchId = cb.BatchId;
delete from @CurrentBatch;
END; -- WHILE (1=1)
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
EXEC [SDK].[Microsoft_SQLServer_Visualization_Library_RethrowError]
END CATCH
END
GO
GRANT EXECUTE ON [SDK].[Microsoft_SQLServer_Visualization_Library_UpdateLastValues] TO OpsMgrReader
GO
ALTER PROCEDURE [sdk].[Microsoft_SQLServer_Visualization_Library_GetDataCenterDashboardData]
@ManagementGroupGuid uniqueidentifier,
@XmlData XML,
@profiling bit = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
IF NOT @ExecError = 0
RAISERROR('Text %s %d', 16, 1
,'ClassXml'
,@ExecError)
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('Update values', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
IF NOT @ExecError = 0
RAISERROR('Text %s %d', 16, 1
,'ClassXml'
,@ExecError)
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('Update hierarchy', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
DECLARE @managementGroupRowId int;
DECLARE @timeRange int;
select @managementGroupRowId = mg.ManagementGroupRowId, @timeRange = ParamValues.x.value('@TimeRange', 'int')
from @xmlData.nodes('/DatacenterViewQuery') AS ParamValues(x)
inner join dbo.vManagementGroup mg WITH (NOLOCK) on mg.ManagementGroupGuid = @ManagementGroupGuid;
DECLARE @resolvedAlertDaysToKeep int = null;
DECLARE @autoResolveDays int = null;
DECLARE @autoResolveHealthyObjDays int = null;
select @resolvedAlertDaysToKeep = ParamValues.x.value('@ResolvedAlertDaysToKeep', 'int'),
@autoResolveDays = ParamValues.x.value('@AutoResolveDays', 'int'),
@autoResolveHealthyObjDays = ParamValues.x.value('@AutoResolveHealthyObjDays', 'int')
from @xmlData.nodes('/DatacenterViewQuery/OpsManagerConfiguration') AS ParamValues(x)
; with inserts as (
select @ManagementGroupGuid as ManagementGroupGuid, 'ResolvedAlertDaysToKeep' as Name, null as Value
UNION ALL
select @ManagementGroupGuid as ManagementGroupGuid, 'AutoResolveDays' as Name, null as Value
UNION ALL
select @ManagementGroupGuid as ManagementGroupGuid, 'AutoResolveHealthyObjDays' as Name, null as Value
)
insert into sdk.Microsoft_SQLServer_Visualization_Library_OpsManagerSettings
select * from inserts i
where not exists (
select null
from sdk.Microsoft_SQLServer_Visualization_Library_OpsManagerSettings oms
where i.ManagementGroupGuid = oms.ManagementGroupGuid
and i.Name = oms.Name
)
if @resolvedAlertDaysToKeep is not null and @autoResolveDays is not null and @autoResolveHealthyObjDays is not null
begin
update sdk.Microsoft_SQLServer_Visualization_Library_OpsManagerSettings
set Value = @resolvedAlertDaysToKeep
where ManagementGroupGuid = @ManagementGroupGuid
and Name = 'ResolvedAlertDaysToKeep';
update sdk.Microsoft_SQLServer_Visualization_Library_OpsManagerSettings
set Value = @autoResolveDays
where ManagementGroupGuid = @ManagementGroupGuid
and Name = 'AutoResolveDays';
update sdk.Microsoft_SQLServer_Visualization_Library_OpsManagerSettings
set Value = @autoResolveHealthyObjDays
where ManagementGroupGuid = @ManagementGroupGuid
and Name = 'AutoResolveHealthyObjDays';
end
else
begin
select @resolvedAlertDaysToKeep = Value
from sdk.Microsoft_SQLServer_Visualization_Library_OpsManagerSettings with (NOLOCK)
where ManagementGroupGuid = @ManagementGroupGuid
and Name = 'ResolvedAlertDaysToKeep'
if @resolvedAlertDaysToKeep is null
set @resolvedAlertDaysToKeep = 7
select @autoResolveDays = Value
from sdk.Microsoft_SQLServer_Visualization_Library_OpsManagerSettings with (NOLOCK)
where ManagementGroupGuid = @ManagementGroupGuid
and Name = 'AutoResolveDays'
if @autoResolveDays is null
set @autoResolveDays = 30
select @autoResolveHealthyObjDays = Value
from sdk.Microsoft_SQLServer_Visualization_Library_OpsManagerSettings with (NOLOCK)
where ManagementGroupGuid = @ManagementGroupGuid
and Name = 'AutoResolveHealthyObjDays'
if @autoResolveHealthyObjDays is null
set @autoResolveHealthyObjDays = 7
end
DECLARE @groups TABLE (
Id uniqueidentifier PRIMARY KEY
);
;with allowedGroups as (
select DISTINCT ParamValues.x.value('@ID','uniqueidentifier') as Id from @xmlData.nodes('/DatacenterViewQuery/OpsManagerConfiguration/AllowedGroup') AS ParamValues(x)
),
configuredGroups as (
select DISTINCT ParamValues.x.value('@Id','uniqueidentifier') as Id from @xmlData.nodes('/DatacenterViewQuery/DatacenterGroup') AS ParamValues(x)
)
insert into @groups
select c.Id from configuredGroups c
inner join allowedGroups a on c.Id = a.Id;
-- select * from @groups
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('DECLARE @groups', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
DECLARE @LatestMpVersions TABLE (
ManagementPackRowId int,
ManagementPackVersionRowId int
);
insert into @LatestMpVersions
select mpv2.ManagementPackRowId, max(ManagementPackVersionRowId) as ManagementPackVersionRowId
from dbo.vManagementPackVersion mpv2 (NOLOCK)
group by mpv2.ManagementPackRowId
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('insert @LatestMpVersions', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
DECLARE @FilteredRT TABLE (
RelationshipTypeRowId int PRIMARY KEY
);
; with parentRT as (
SELECT TOP 1 rt.RelationshipTypeRowId
from dbo.vRelationshipType rt WITH (NOLOCK)
where rt.RelationshipTypeSystemName = 'System.Containment'
),
filteredRT as (
SELECT RelationshipTypeRowId
from parentRT
UNION ALL
SELECT rth.Child as RelationshipTypeRowId
from parentRT rt WITH (NOLOCK)
inner join sdk.Microsoft_SQLServer_Visualization_Library_RelationshipType_Hierarchy rth WITH (NOLOCK) on rt.RelationshipTypeRowId = rth.Parent
)
insert into @FilteredRT
select DISTINCT * from filteredRT;
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('insert @FilteredRT', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
insert into @metrics
select DISTINCT
x.value('@Id', 'uniqueidentifier') as GroupId,
x.value('@Id', 'uniqueidentifier') as Id,
255 as MetricType,
CASE WHEN g.Id is null THEN 1 ELSE 2 END as MonitorRowId
from @xmlData.nodes('/DatacenterViewQuery/DatacenterGroup') as ParamValues(x)
left outer join @groups g on g.Id = x.value('@Id', 'uniqueidentifier')
UNION ALL
select DISTINCT
x.value('@Id', 'uniqueidentifier') as GroupId,
x.value('@Id', 'uniqueidentifier') as Id,
0 as MetricType,
mon.MonitorRowId as MonitorRowId
from @xmlData.nodes('/DatacenterViewQuery/DatacenterGroup') as ParamValues(x)
inner join dbo.Monitor mon WITH (NOLOCK) on mon.MonitorSystemName = 'System.Health.EntityState'
UNION ALL
select DISTINCT
x.value('@Id', 'uniqueidentifier') as GroupId,
x.value('@Id', 'uniqueidentifier') as Id,
1 as MetricType,
0 as MonitorRowId
from @xmlData.nodes('/DatacenterViewQuery/DatacenterGroup') as ParamValues(x)
UNION ALL
select
x.value('../../@Id', 'uniqueidentifier') as GroupId,
x.value('@Id', 'uniqueidentifier') as Id,
2 as MetricType,
0 as MonitorRowId
from @xmlData.nodes('/DatacenterViewQuery/DatacenterGroup/MonitorMetrics/AggregatedMonitorMetric') as ParamValues(x)
UNION ALL
select
x.value('../../@Id', 'uniqueidentifier') as GroupId,
x.value('@Id', 'uniqueidentifier') as Id,
3 as MetricType,
0 as MonitorRowId
from @xmlData.nodes('/DatacenterViewQuery/DatacenterGroup/PerformanceMetrics/AggregatedPerformanceMetric') as ParamValues(x)
UNION ALL
select
x.value('../../@Id', 'uniqueidentifier') as GroupId,
x.value('@Id', 'uniqueidentifier') as Id,
4 as MetricType,
0 as MonitorRowId
from @xmlData.nodes('/DatacenterViewQuery/DatacenterGroup/CountItemsMetrics/AggregatedCountItemsMetric') as ParamValues(x)
UNION ALL
select
x.value('../../@Id', 'uniqueidentifier') as GroupId,
x.value('@Id', 'uniqueidentifier') as Id,
5 as MetricType,
0 as MonitorRowId
from @xmlData.nodes('/DatacenterViewQuery/DatacenterGroup/AveragePerformanceMetrics/AggregatedAveragePerformanceMetric') as ParamValues(x);
-- select * from @metrics;
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('DECLARE @metrics', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
; with initial_cm as (
select
x.value('../@Id', 'uniqueidentifier') as MetricId,
LOWER(SUBSTRING(x.value('@ClassName', 'nvarchar(2000)'), 1, CHARINDEX('!', x.value('@ClassName', 'nvarchar(2000)')) - 1)) as MpName,
LOWER(SUBSTRING(x.value('@ClassName', 'nvarchar(2000)'), CHARINDEX('!', x.value('@ClassName', 'nvarchar(2000)')) + 1, 2000)) as ClassName,
x.value('@MonitorId', 'uniqueidentifier') as MappedId,
mon.MonitorRowId as MonitorRowId
from @xmlData.nodes('/DatacenterViewQuery/DatacenterGroup/MonitorMetrics/AggregatedMonitorMetric/ClassMonitorMapping') as ParamValues(x)
inner join dbo.Monitor mon WITH (NOLOCK) on mon.MonitorGuid = x.value('@MonitorId', 'uniqueidentifier')
inner join dbo.vMonitorManagementPackVersion mmpv WITH (NOLOCK) on mon.MonitorRowId = mmpv.MonitorRowId
inner join @latestMpVersions lmv on mmpv.ManagementPackVersionRowId = lmv.ManagementPackVersionRowId
UNION ALL
select
x.value('../@Id', 'uniqueidentifier') as MetricId,
LOWER(SUBSTRING(x.value('@ClassName', 'nvarchar(2000)'), 1, CHARINDEX('!', x.value('@ClassName', 'nvarchar(2000)')) - 1)) as MpName,
LOWER(SUBSTRING(x.value('@ClassName', 'nvarchar(2000)'), CHARINDEX('!', x.value('@ClassName', 'nvarchar(2000)')) + 1, 2000)) as ClassName,
x.value('@PerformanceCollectionRuleId', 'uniqueidentifier') as MappedId,
0 as MonitorRowId
from @xmlData.nodes('/DatacenterViewQuery/DatacenterGroup/PerformanceMetrics/AggregatedPerformanceMetric/ClassPerformanceCollectionRuleMapping') as ParamValues(x)
UNION ALL
select
x.value('../@Id', 'uniqueidentifier') as MetricId,
LOWER(SUBSTRING(x.value('@ClassName', 'nvarchar(2000)'), 1, CHARINDEX('!', x.value('@ClassName', 'nvarchar(2000)')) - 1)) as MpName,
LOWER(SUBSTRING(x.value('@ClassName', 'nvarchar(2000)'), CHARINDEX('!', x.value('@ClassName', 'nvarchar(2000)')) + 1, 2000)) as ClassName,
NULL as MappedId,
0 as MonitorRowId
from @xmlData.nodes('/DatacenterViewQuery/DatacenterGroup/CountItemsMetrics/AggregatedCountItemsMetric/CountableItemMapping') as ParamValues(x)
UNION ALL
select
x.value('../@Id', 'uniqueidentifier') as MetricId,
LOWER(SUBSTRING(x.value('@ClassName', 'nvarchar(2000)'), 1, CHARINDEX('!', x.value('@ClassName', 'nvarchar(2000)')) - 1)) as MpName,
LOWER(SUBSTRING(x.value('@ClassName', 'nvarchar(2000)'), CHARINDEX('!', x.value('@ClassName', 'nvarchar(2000)')) + 1, 2000)) as ClassName,
x.value('@PerformanceCollectionRuleId', 'uniqueidentifier') as MappedId,
0 as MonitorRowId
from @xmlData.nodes('/DatacenterViewQuery/DatacenterGroup/AveragePerformanceMetrics/AggregatedAveragePerformanceMetric/ClassPerformanceCollectionRuleMapping') as ParamValues(x)
),
updated_initial as (
select s.*, mt.ManagedEntityTypeRowId from initial_cm s
INNER JOIN dbo.[ManagedEntityType] mt WITH (NOLOCK) on s.ClassName = Lower(mt.ManagedEntityTypeSystemName)
inner join dbo.[ManagementPack] mp WITH (NOLOCK) on mp.ManagementPackRowId = mt.ManagementPackRowId and s.MpName = Lower(mp.ManagementPackSystemName)
),
reverse_seed as (
select u.MetricId, u.MappedId, u.MonitorRowId, u.ManagedEntityTypeRowId, 0 as [Level] from updated_initial u
UNION ALL
select
s.MetricId,
s.MappedId,
s.MonitorRowId,
mt.ManagedEntityTypeRowId,
s.[Level] + 1 as [Level]
from reverse_seed s
INNER JOIN dbo.[ManagedEntityTypeManagementPackVersion] mtmpv WITH (NOLOCK) on mtmpv.BaseManagedEntityTypeRowId = s.ManagedEntityTypeRowId
inner hash join @latestMpVersions mpv on mtmpv.ManagementPackVersionRowId = mpv.ManagementPackVersionRowId
INNER JOIN dbo.[ManagedEntityType] mt WITH (NOLOCK) on mtmpv.ManagedEntityTypeRowId = mt.ManagedEntityTypeRowId
)
insert into @classMappings
SELECT rs.MetricId, rs.ManagedEntityTypeRowId, max(rs.MappedId), max(rs.MonitorRowId) FROM reverse_seed rs
inner join (
select
rsg.MetricId,
rsg.ManagedEntityTypeRowId,
min(rsg.[Level]) as Level
from reverse_seed rsg
group by rsg.MetricId, rsg.ManagedEntityTypeRowId
) rsm on rsm.MetricId = rs.MetricId
and rsm.ManagedEntityTypeRowId = rs.ManagedEntityTypeRowId
and rsm.Level = rs.Level
group by rs.ManagedEntityTypeRowId, rs.MetricId
order by ManagedEntityTypeRowId, MetricId;
-- select * from @classMappings order by MetricId, mpname, ClassName;
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('DECLARE @classMappings', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
-- check from opsmanager
--insert into @items
--select parent.BaseManagedEntityId as GroupId, child.BaseManagedEntityId as Id, me.ManagedEntityRowId as RowId, mp.MPName +'!'+ mt.TypeName as ClassName from @groups g
--inner join OperationsManager.dbo.BaseManagedEntity parent WITH (NOLOCK) on g.Id = parent.BaseManagedEntityId
--inner join OperationsManager.dbo.Relationship rel WITH (NOLOCK) on parent.BaseManagedEntityId = rel.SourceEntityId
--inner join OperationsManager.dbo.RelationshipType rt WITH (NOLOCK) on rel.RelationshipTypeId = rt.RelationshipTypeId
--inner join OperationsManager.dbo.BaseManagedEntity child WITH (NOLOCK) on rel.TargetEntityId = child.BaseManagedEntityId
--inner join OperationsManager.dbo.ManagedType mt WITH (NOLOCK) on child.BaseManagedTypeId = mt.ManagedTypeId
--inner join OperationsManager.dbo.ManagementPack mp WITH (NOLOCK) on mt.ManagementPackId = mp.ManagementPackId
--inner hash join ManagedEntity me WITH (NOLOCK) on me.ManagedEntityGuid = child.BaseManagedEntityId
--where rel.IsDeleted = 0
--and parent.IsDeleted = 0
--and child.IsDeleted = 0
--and (rt.IsHosting = 1 or rt.IsContainment = 1);
-- TODO: Add the check for management group and for managemement pack versions
insert into @items
select
g.Id as GroupId,
child.ManagedEntityRowId as RowId,
0 as Unmonitored,
max(case when mm.MaintenanceModeRowId is null then 0 else 1 end) as Maintenance,
max(isnull(cast(mm.PlannedMaintenanceInd as int), 0)) as PlannedMaintenance,
max(memg2.FromDateTime) as LastPresencePeriodStarted
from @groups g
inner loop join dbo.vManagedEntity parent WITH (NOLOCK) on g.Id = parent.ManagedEntityGuid and parent.ManagementGroupRowId = @managementGroupRowId
inner join dbo.vManagedEntityManagementGroup memg1 WITH (NOLOCK) on parent.ManagedEntityRowId = memg1.ManagedEntityRowId
inner join dbo.vRelationship rel WITH (NOLOCK) on parent.ManagedEntityRowId = rel.SourceManagedEntityRowId and rel.ManagementGroupRowId = @managementGroupRowId
inner join dbo.vRelationshipManagementGroup rmg WITH (NOLOCK) on rel.RelationshipRowId = rmg.RelationshipRowId
inner hash join @filteredRT frt on rel.RelationshipTypeRowId = frt.RelationshipTypeRowId
inner join dbo.vManagedEntity child WITH (NOLOCK) on rel.TargetManagedEntityRowId = child.ManagedEntityRowId and child.ManagementGroupRowId = @managementGroupRowId
inner join dbo.vManagedEntityManagementGroup memg2 WITH (NOLOCK) on child.ManagedEntityRowId = memg2.ManagedEntityRowId
left join dbo.vMaintenanceMode mm WITH (NOLOCK) on mm.ManagedEntityRowId = child.ManagedEntityRowId and mm.EndDateTime is null
where memg1.ToDateTime is null
and memg2.ToDateTime is null
and rmg.ToDateTime is null
group by child.ManagedEntityRowId, g.Id
order by RowId, GroupId;
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('declare @items', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
update @items
Set Unmonitored = 1
FROM dbo.vRelationship AS R WITH (NOLOCK)
inner join dbo.vRelationshipManagementGroup rmg WITH (NOLOCK) on r.RelationshipRowId = rmg.RelationshipRowId
INNER JOIN dbo.vRelationshipType AS RT WITH (NOLOCK) ON RT.RelationshipTypeRowId = R.RelationshipTypeRowId
INNER JOIN dbo.vManagedEntity AS ME WITH (NOLOCK) ON ME.TopLevelHostManagedEntityRowId = R.TargetManagedEntityRowId
inner join dbo.vManagedEntityManagementGroup memg WITH (NOLOCK) on ME.ManagedEntityRowId = memg.ManagedEntityRowId
INNER JOIN dbo.vHealthServiceOutage AS HSO WITH (NOLOCK) ON HSO.ManagedEntityRowId = R.SourceManagedEntityRowId
inner join @items as i on i.RowId = me.ManagedEntityRowId
WHERE RT.RelationshipTypeSystemName = 'Microsoft.SystemCenter.HealthServiceManagesEntity'
and rmg.ToDateTime is null
and memg.ToDateTime is null
AND HSO.EndDateTime is NULL
AND NOT EXISTS
(SELECT * FROM dbo.vHealthServiceOutage as HSO2 WITH (NOLOCK)
WHERE HSO2.DWLastModifiedDateTime = HSO.DWLastModifiedDateTime
AND HSO2.ManagedEntityRowId = HSO.ManagedEntityRowId
AND HSO2.ReasonCode = HSO.ReasonCode
AND HSO2.RootHealthServiceInd = HSO.RootHealthServiceInd
AND HSO2.StartDateTime = HSO.StartDateTime
AND HSO2.EndDateTime IS NOT NULL)
-- select * from @items order by groupid;
-- select count(*) from @items;
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('update @items', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
; with seed as (
select g.Id as GroupId, rh.Child as RowId, cast(Unmonitored as int) as Unmonitored, cast(0 as bit) as IsGroup
from @groups g
inner join dbo.vManagedEntity parent WITH (NOLOCK) on g.Id = parent.ManagedEntityGuid and parent.ManagementGroupRowId = @managementGroupRowId
inner join sdk.Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy rh WITH (NOLOCK) on parent.ManagedEntityRowId = rh.Parent and IsGroup = 1
inner join @items i on rh.Child = i.RowId
),
data as (
select * from seed
UNION ALL
select
s.GroupId as GroupId,
rhg.Child as RowId,
s.Unmonitored,
CASE WHEN s.IsGroup = 1 THEN cast(0 as bit) ELSE cast(1 as bit) END as IsGroup
from data s
inner join sdk.Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy rhg with (nolock) on s.RowId = rhg.Parent and s.IsGroup = rhg.IsGroup
),
hItems as (
select
GroupId,
RowId,
max(memg.FromDateTime) as LastPresencePeriodStarted,
max(Unmonitored) as Unmonitored,
max(CASE WHEN mm.MaintenanceModeRowId is not null THEN 1 ELSE 0 END) as Maintenance
from data
inner join dbo.vManagedEntity me WITH (NOLOCK) on data.RowId = me.ManagedEntityRowId and me.ManagementGroupRowId = @managementGroupRowId
inner join dbo.vManagedEntityManagementGroup memg WITH (NOLOCK) on me.ManagedEntityRowId = memg.ManagedEntityRowId
left join dbo.vMaintenanceMode mm WITH (NOLOCK) on mm.ManagedEntityRowId = data.RowId and mm.EndDateTime is null
where memg.ToDateTime is null
group by RowId, GroupId
),
hItemsWithHealth as(
select
hItems.GroupId,
hItems.RowId,
hItems.LastPresencePeriodStarted,
CASE WHEN s.HealthState = 1 THEN 1 ELSE 0 END as IsHealthy,
hItems.Unmonitored,
hItems.Maintenance,
ROW_NUMBER() OVER (PARTITION BY hItems.RowId, hItems.GroupId ORDER BY s.DateTime desc) as rn
from hItems
inner join @groups g on hItems.GroupId = g.Id
inner join @metrics m on g.Id = m.GroupId and m.MetricType = 0
left JOIN sdk.Microsoft_SQLServer_Visualization_Library_LastMonitorValues AS s WITH (NOLOCK) ON s.ManagedEntityRowId = hItems.RowId and s.MonitorRowId = m.MonitorRowId and s.DateTime > hItems.LastPresencePeriodStarted
)
insert into @hierarchicItems
select
GroupId,
RowId,
LastPresencePeriodStarted,
IsHealthy,
Unmonitored,
Maintenance
from hItemsWithHealth
where rn = 1
order by RowId, GroupId;
-- select * from @hierarchicItems;
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('update @hierarchicItems', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
insert into @itemMappings
select distinct t.ManagedEntityRowId as RowId, mt.ManagedEntityTypeRowId as ManagedEntityTypeRowId
from dbo.vTypedManagedEntity t (NOLOCK)
inner join dbo.vManagedEntityType mt WITH (NOLOCK) on t.ManagedEntityTypeRowId = mt.ManagedEntityTypeRowId
inner join dbo.vManagementPack mp WITH (NOLOCK) on mt.ManagementPackRowId = mp.ManagementPackRowId
where t.ManagedEntityRowId
in (select distinct RowId from @hierarchicItems)
-- select * from @itemMappings
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('insert @itemMappings', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
DECLARE @metricValues TABLE (
GroupId uniqueidentifier,
MetricId uniqueidentifier,
MetricType smallint,
name varchar(200),
value integer
);
-- state
-- check from OpsManager
--SELECT g.Id as GroupId, m.Id as MetricId, m.MetricType, s.HealthState as Name, count(s.HealthState) as Value
-- FROM [OperationsManager].[dbo].[State] S WITH (NOLOCK)
-- inner join @items i on s.BaseManagedEntityId = i.Id
-- inner join @groups g on i.GroupId = g.Id
-- inner join @metrics m on m.GroupId = g.Id and m.MetricType = 0
-- where MonitorId = [OperationsManager].dbo.fn_ManagedTypeId_SystemHealthEntityState()
-- group by g.Id, m.Id, m.MetricType, s.HealthState;
insert into @metricValues
select GroupId, MetricId, MetricType, Name, sum(Value) from (
SELECT
g.Id as GroupId,
m.Id as MetricId,
m.MetricType,
case
when i.Maintenance = 1 then 4
when i.Unmonitored = 1 then 3
when s.HealthState = 0 then 5
when s.HealthState = 1 then 6
when s.HealthState = 2 then 2
when s.HealthState = 3 then 1
end as Name,
count(i.RowId) as Value
FROM @items i
inner join @groups g on i.GroupId = g.Id
inner join @metrics m on g.Id = m.GroupId and m.MetricType = 0
inner JOIN sdk.Microsoft_SQLServer_Visualization_Library_LastMonitorValues AS s WITH (NOLOCK) ON s.ManagedEntityRowId = i.RowId and s.MonitorRowId = m.MonitorRowId
where s.DateTime > i.LastPresencePeriodStarted
group by g.Id, m.Id, m.MetricType,
case
when i.Maintenance = 1 then 4
when i.Unmonitored = 1 then 3
when s.HealthState = 0 then 5
when s.HealthState = 1 then 6
when s.HealthState = 2 then 2
when s.HealthState = 3 then 1
end
UNION ALL
SELECT
g.Id as GroupId,
m.Id as MetricId,
m.MetricType,
case
when i.Maintenance = 1 then 4
when i.Unmonitored = 1 then 3
else 5
end as Name,
count(i.RowId) as Value
FROM @items i
inner join @groups g on i.GroupId = g.Id
inner join @metrics m on g.Id = m.GroupId and m.MetricType = 0
WHERE NOT EXISTS (
SELECT NULL
FROM sdk.Microsoft_SQLServer_Visualization_Library_LastMonitorValues AS s WITH (NOLOCK)
where s.ManagedEntityRowId = i.RowId and s.MonitorRowId = m.MonitorRowId and s.DateTime > i.LastPresencePeriodStarted)
group by g.Id, m.Id, m.MetricType,
case
when i.Maintenance = 1 then 4
when i.Unmonitored = 1 then 3
else 5
end) as monitors
group by GroupId, MetricId, MetricType, Name;
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('Aggregate state', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
-- count items (type 4)
insert into @metricValues
SELECT
g.Id as GroupId,
m.Id as MetricId,
m.MetricType,
'1' as Name,
count(distinct i.RowId) as Value
FROM @hierarchicItems i
inner join @groups g on i.GroupId = g.Id
inner join @metrics m on g.Id = m.GroupId and m.MetricType = 4
inner join @itemMappings im on i.RowId = im.rowid
inner join @classMappings cm on cm.ManagedEntityTypeRowId = im.ManagedEntityTypeRowId and cm.MetricId = m.Id
group by g.Id, m.Id, m.MetricType;
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('Aggregate count items', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
-- alert
-- check from OpsManager
--SELECT g.Id as GroupId, m.Id as MetricId, m.MetricType, a.Severity as Name, count(a.Severity) as Value
-- FROM [OperationsManager].[dbo].Alert A WITH (NOLOCK)
-- inner join @items i on a.BaseManagedEntityId = i.Id
-- inner join @groups g on i.GroupId = g.Id
-- inner join @metrics m on m.GroupId = g.Id and m.MetricType = 1
-- group by g.Id, m.Id, m.MetricType, a.Severity;
insert into @metricValues
SELECT g.Id as GroupId, m.Id as MetricId, m.MetricType, a.Severity as Name, count(a.Severity) as Value
FROM @hierarchicItems i
INNER JOIN sdk.Microsoft_SQLServer_Visualization_Library_LastAlertValues AS A WITH (NOLOCK) ON A.ManagedEntityRowId = i.RowId
inner join @groups g on i.GroupId = g.Id
inner join @metrics m on g.Id = m.GroupId and m.MetricType = 1
WHERE a.ResolutionState <> 255
and a.DateTime > i.LastPresencePeriodStarted -- filtering by Instance presence
and a.DateTime > DATEADD(DAY, -1 * (CASE
WHEN i.IsHealthy = 1
THEN @autoResolveHealthyObjDays
ELSE @autoResolveDays
END + @resolvedAlertDaysToKeep + 1), @launchDateTime)
group by g.Id, m.Id, m.MetricType, a.Severity;
; with PossibleAlertStates as (
SELECT 0 as State
UNION ALL
SELECT 1
UNION ALL
SELECT 2
),
PossibleHealthStates as (
SELECT 1 as State
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
),
PossibleCountStates as (
SELECT 1 as State
),
FullSet as (
select m.GroupId, m.Id as MetricId, m.MetricType, ps.State as Name, 0 as Value from PossibleAlertStates ps
full join @metrics m on 1=1
where m.MetricType = 1
union all
select m.GroupId, m.Id as MetricId, m.MetricType, ps.State as Name, 0 as Value from PossibleHealthStates ps
full join @metrics m on 1=1
where m.MetricType = 0
union all
select m.GroupId, m.Id as MetricId, m.MetricType, ps.State as Name, 0 as Value from PossibleCountStates ps
full join @metrics m on 1=1
where m.MetricType = 4
)
insert into @metricValues
select fs.* from FullSet fs
left join @metricValues mv on fs.GroupId = mv.GroupId and fs.MetricId = mv.MetricId and fs.MetricType = mv.MetricType and fs.Name = mv.name
where mv.Name is null
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('Aggregate alert', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
-- monitors
-- check from OperationsManager
--SELECT g.Id as GroupId, m.Id as MetricId, m.MetricType, s.HealthState as Name, count(s.HealthState) as Value
-- FROM [OperationsManager].[dbo].[State] S WITH (NOLOCK)
-- inner join @items i on s.BaseManagedEntityId = i.Id
-- inner join @groups g on i.GroupId = g.Id
-- inner join @metrics m on m.GroupId = g.Id and m.MetricType = 2
-- inner join @classMappings cm on cm.ClassName = i.ClassName and cm.MetricId = m.Id and s.MonitorId = cm.MappedId
-- group by g.Id, m.Id, m.MetricType, s.HealthState;
insert into @metricValues
select GroupId, MetricId, MetricType, Name, sum(Value) from (
SELECT
g.Id as GroupId,
m.Id as MetricId,
m.MetricType,
case
when i.Maintenance = 1 then 4
when i.Unmonitored = 1 then 3
when s.HealthState = 0 then 5
when s.HealthState = 1 then 6
when s.HealthState = 2 then 2
when s.HealthState = 3 then 1
end as Name,
count(distinct i.RowId) as Value
FROM @hierarchicItems i
inner join @groups g on i.GroupId = g.Id
inner hash join @metrics m on g.Id = m.GroupId and m.MetricType = 2
inner join @itemMappings im on i.RowId = im.rowid
inner join @classMappings cm on cm.ManagedEntityTypeRowId = im.ManagedEntityTypeRowId and cm.MetricId = m.Id
inner JOIN sdk.Microsoft_SQLServer_Visualization_Library_LastMonitorValues AS s WITH (NOLOCK) ON s.ManagedEntityRowId = i.RowId and s.MonitorRowId = cm.MonitorRowId
where s.DateTime > i.LastPresencePeriodStarted
group by g.Id, m.Id, m.MetricType,
case
when i.Maintenance = 1 then 4
when i.Unmonitored = 1 then 3
when s.HealthState = 0 then 5
when s.HealthState = 1 then 6
when s.HealthState = 2 then 2
when s.HealthState = 3 then 1
end
UNION ALL
SELECT
g.Id as GroupId,
m.Id as MetricId,
m.MetricType,
case
when i.Maintenance = 1 then 4
when i.Unmonitored = 1 then 3
else 5
end as Name,
count(distinct i.RowId) as Value
FROM @hierarchicItems i
inner join @groups g on i.GroupId = g.Id
inner hash join @metrics m on g.Id = m.GroupId and m.MetricType = 2
inner join @itemMappings im on i.RowId = im.rowid
inner join @classMappings cm on cm.ManagedEntityTypeRowId=im.ManagedEntityTypeRowId and cm.MetricId = m.Id
WHERE NOT EXISTS (
SELECT NULL
FROM sdk.Microsoft_SQLServer_Visualization_Library_LastMonitorValues AS s WITH (NOLOCK)
where s.ManagedEntityRowId = i.RowId and s.MonitorRowId = cm.MonitorRowId and s.DateTime > i.LastPresencePeriodStarted)
group by g.Id, m.Id, m.MetricType,
case
when i.Maintenance = 1 then 4
when i.Unmonitored = 1 then 3
else 5
end) as monitors
group by GroupId, MetricId, MetricType, Name;
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('Aggregate monitors', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
DECLARE @floatMetricValues TABLE (
GroupId uniqueidentifier,
MetricId uniqueidentifier,
MetricType smallint,
name varchar(200),
value float
);
-- average performance (type 5)
insert into @floatMetricValues
select
GroupId,
MetricId,
MetricType,
Name,
avg(Value) as Value
from (
SELECT
g.Id as GroupId,
m.Id as MetricId,
m.MetricType,
'1' as Name,
i.RowId as RowId,
avg(vpr.SampleValue) as Value
FROM @hierarchicItems i
inner join @groups g on i.GroupId = g.Id
inner join @metrics m on m.GroupId = g.Id and m.MetricType = 5
inner join @itemMappings im on i.RowId = im.rowid
inner join @classMappings cm on cm.ManagedEntityTypeRowId = im.ManagedEntityTypeRowId and cm.MetricId = m.Id
inner join dbo.[Rule] r WITH (NOLOCK) on r.RuleGuid = cm.MappedId
inner join dbo.PerformanceRuleInstance pri WITH (NOLOCK) on pri.RuleRowId = r.RuleRowId
inner join sdk.Microsoft_SQLServer_Visualization_Library_LastPerfValues vpr WITH (NOLOCK) on pri.PerformanceRuleInstanceRowId = vpr.PerformanceRuleInstanceRowId and vpr.ManagedEntityRowId = i.RowId
where vpr.DateTime >= @firstDateTime and vpr.DateTime > i.LastPresencePeriodStarted
group by g.Id, m.Id, m.MetricType, i.RowId
) a
group by GroupId, MetricId, MetricType, Name;
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('Aggregate average performance', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
;with perfValues as (
select
g.id as groupId,
m.id as metricId,
i.RowId,
--vpr.SampleValue * CASE WHEN vpr.SampleValue > 50 THEN -100000000 ELSE 100000 END as SampleValue
avg(vpr.SampleValue) as SampleValue
FROM @hierarchicItems i
inner join @groups g on i.GroupId = g.Id
inner join @metrics m on m.GroupId = g.Id and m.MetricType = 3
inner join @itemMappings im on i.RowId = im.rowid
inner join @classMappings cm on cm.ManagedEntityTypeRowId = im.ManagedEntityTypeRowId and cm.MetricId = m.Id
inner join dbo.[Rule] r WITH (NOLOCK) on r.RuleGuid = cm.MappedId
inner join dbo.PerformanceRuleInstance pri WITH (NOLOCK) on pri.RuleRowId = r.RuleRowId
inner join sdk.Microsoft_SQLServer_Visualization_Library_LastPerfValues vpr WITH (NOLOCK) on pri.PerformanceRuleInstanceRowId = vpr.PerformanceRuleInstanceRowId and vpr.ManagedEntityRowId = i.RowId
where vpr.DateTime >= @firstDateTime and vpr.DateTime > i.LastPresencePeriodStarted
group by g.id, m.id, i.RowId
),
limits as (
select
groupId as groupId,
metricId as MetricID,
min(SampleValue) as minValue,
max(SampleValue) as maxValue,
count(distinct SampleValue) as countValues
from perfValues
group by groupId, metricId
),
calc_e as (
select
groupId,
MetricID,
minValue,
maxValue,
CONVERT(integer, SUBSTRING(CONVERT(varchar, CASE WHEN ABS(maxValue) > ABS(minValue) THEN ABS(maxValue) ELSE ABS(minValue) END, 2), 19, 4)) as e
from limits
),
catch_one as (
select
groupId,
MetricID,
minValue,
maxValue,
case when e = 1 then 0 else e end as e
from calc_e
),
expand_minmax as (
select
groupId,
MetricID,
minValue,
case when (maxValue - minValue)/power(@float10, e) < @MinimalSpread then maxValue + @MinimalSpread*power(@float10, e) else maxValue end as maxValue,
--case when CEILING(maxValue/power(@float10, e)) - FLOOR(minValue/power(@float10, e)) < @MinimalSpread then minValue else minValue end as minValue,
--case when CEILING(maxValue/power(@float10, e)) - FLOOR(minValue/power(@float10, e)) < @MinimalSpread then maxValue + @MinimalSpread*power(@float10, e) else maxValue end as maxValue,
e
from catch_one
),
recalc_limits as (
select
groupId,
MetricID,
FLOOR(minValue/power(@float10, e - 1))*power(@float10, e - 1) as minValue,
CEILING(maxValue/power(@float10, e - 1))*power(@float10, e - 1) as maxValue,
(CEILING(maxValue/power(@float10, e - 1)) - FLOOR(minValue/power(@float10, e - 1))) as diff,
(CEILING(maxValue/power(@float10, e - 1)) - FLOOR(minValue/power(@float10, e - 1))) / @BarCount as step,
((CEILING(maxValue/power(@float10, e - 1)) - FLOOR(minValue/power(@float10, e - 1))) / @BarCount) * power(@float10, e - 1) as actualStep,
--minValue,
--maxValue,
--maxValue - minValue as diff,
--(maxValue - minValue) / @BarCount as step,
--(maxValue - minValue) / @BarCount as actualStep,
--CASE WHEN e < 0
--THEN LEFT(LEFT(LTRIM(STR(FLOOR(ABS(minValue)/power(@float10, e - 1))*power(@float10, e - 1), 38, 16)), 1 - e) + REPLICATE('0', 1 - e), 1 - e) + @replacement
--ELSE
-- CASE WHEN e = 0
-- THEN LEFT(@replacement, 1) + '.' + RIGHT(@replacement, 1)
-- ELSE CASE WHEN ABS(minValue) > power(@float10, e + 1) THEN LTRIM(STR(FLOOR(ABS(minValue)/power(@float10, e + 1)), 38, 0)) ELSE '' END + @replacement + REPLICATE('0', e - 1)
-- END
--END as part,
CASE WHEN e = 0 THEN '' ELSE '10^'+CAST(e as VARCHAR) END as part,
e as e
from expand_minmax
),
intervals as (
select
groupId,
MetricID,
minValue as value1,
minValue + actualStep * 1 as value2,
minValue + actualStep * 2 as value3,
minValue + actualStep * 3 as value4,
minValue + actualStep * 4 as value5,
maxValue as value6
from recalc_limits
),
count_intervals as (
select
i.groupId,
i.MetricId,
count(CASE WHEN p.SampleValue >= i.value1 AND p.SampleValue < i.value2 THEN 1 ELSE NULL END) as count1,
count(CASE WHEN p.SampleValue >= i.value2 AND p.SampleValue < i.value3 THEN 1 ELSE NULL END) as count2,
count(CASE WHEN p.SampleValue >= i.value3 AND p.SampleValue < i.value4 THEN 1 ELSE NULL END) as count3,
count(CASE WHEN p.SampleValue >= i.value4 AND p.SampleValue < i.value5 THEN 1 ELSE NULL END) as count4,
count(CASE WHEN p.SampleValue >= i.value5 AND p.SampleValue <= i.value6 THEN 1 ELSE NULL END) as count5
from recalc_intervals i
inner join perfValues p on p.groupId = i.groupId and p.metricId = i.MetricID
group by i.groupId, i.MetricId
)
insert into @perfRuleValues
select
ci.groupId,
ci.MetricId as MetricId,
i.part,
ROUND(i.value1, - (e - 1)),
ROUND(i.value2, - (e - 1)),
ROUND(i.value3, - (e - 1)),
ROUND(i.value4, - (e - 1)),
ROUND(i.value5, - (e - 1)),
ROUND(i.value6, - (e - 1)),
i.value1_text,
i.value2_text,
i.value3_text,
i.value4_text,
i.value5_text,
i.value6_text,
ci.count1,
ci.count2,
ci.count3,
ci.count4,
ci.count5
from count_intervals ci
inner join recalc_intervals i on ci.MetricID = i.MetricID and ci.groupId = i.groupId;
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('Aggregate performance', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END;
---------- XML generation
;SELECT
999 As TAG,
NULL As PARENT,
'' As [ArrayOfAggregatedData!999],
NULL As [AggregatedData!1!GroupId],
NULL As [AggregatedData!1!MetricId],
NULL As [AggregatedData!1!DataTypeName],
NULL As [AggregatedData!1!AdditionalData],
NULL As [DataPointType!2!IndependentValue],
NULL As [DataPointType!2!DependentValue],
NULL As [DataPointType!2!IndependentEndValue],
NULL As [DataPointType!2!DependentValueDesc],
NULL As [DataPointType!2!IndependentValueDesc]
UNION ALL
SELECT
1 As TAG,
999 As PARENT,
'' As [ArrayOfAggregatedData!999],
m.GroupId As [AggregatedData!1!GroupId],
CASE WHEN m.MetricType in (0,1,255) THEN NULL ELSE m.Id END As [AggregatedData!1!MetricId],
CASE
WHEN m.MetricType = 0 THEN 'State'
WHEN m.MetricType = 1 THEN 'Alerts'
WHEN m.MetricType in (2,4,5) THEN 'Metric'
WHEN m.MetricType = 255 and m.MonitorRowId = 1 THEN 'Forbidden'
WHEN m.MetricType = 255 and m.MonitorRowId = 2 THEN 'Allowed'
END As [AggregatedData!1!DataTypeName],
NULL As [AggregatedData!1!AdditionalData],
NULL As [DataPointType!2!IndependentValue],
NULL As [DataPointType!2!DependentValue],
NULL As [DataPointType!2!IndependentEndValue],
NULL As [DataPointType!2!DependentValueDesc],
NULL As [DataPointType!2!IndependentValueDesc]
FROM @metrics m
WHERE m.MetricType in (0,1,2,4,5,255)
UNION ALL
SELECT
2 As TAG,
1 As PARENT,
'' As [ArrayOfAggregatedData!999],
m.GroupId As [AggregatedData!1!GroupId],
CASE WHEN m.MetricType in (0,1) THEN NULL ELSE m.Id END As [AggregatedData!1!MetricId],
CASE
WHEN m.MetricType = 0 THEN 'State'
WHEN m.MetricType = 1 THEN 'Alerts'
WHEN m.MetricType in (2,4,5) THEN 'Metric'
END As [AggregatedData!1!DataTypeName],
NULL As [AggregatedData!1!AdditionalData],
mv.name As [DataPointType!2!IndependentValue],
mv.value As [DataPointType!2!DependentValue],
NULL As [DataPointType!2!IndependentEndValue],
NULL As [DataPointType!2!DependentValueDesc],
NULL As [DataPointType!2!IndependentValueDesc]
FROM @metrics m
inner join @metricValues mv on m.Id = mv.MetricId and m.GroupId = mv.GroupId and m.MetricType = mv.MetricType
WHERE m.MetricType in (0,1,2,4)
UNION ALL
SELECT
2 As TAG,
1 As PARENT,
'' As [ArrayOfAggregatedData!999],
m.GroupId As [AggregatedData!1!GroupId],
CASE WHEN m.MetricType in (0,1) THEN NULL ELSE m.Id END As [AggregatedData!1!MetricId],
CASE
WHEN m.MetricType = 0 THEN 'State'
WHEN m.MetricType = 1 THEN 'Alerts'
WHEN m.MetricType in (2,4,5) THEN 'Metric'
END As [AggregatedData!1!DataTypeName],
NULL As [AggregatedData!1!AdditionalData],
mv.value As [DataPointType!2!IndependentValue],
mv.name As [DataPointType!2!DependentValue],
NULL As [DataPointType!2!IndependentEndValue],
NULL As [DataPointType!2!DependentValueDesc],
NULL As [DataPointType!2!IndependentValueDesc]
FROM @metrics m
inner join @floatMetricValues mv on m.Id = mv.MetricId and m.GroupId = mv.GroupId and m.MetricType = mv.MetricType
WHERE m.MetricType in (5)
UNION ALL
SELECT
1 As TAG,
999 As PARENT,
'' As [ArrayOfAggregatedData!999],
m.GroupId As [AggregatedData!1!GroupId],
m.Id As [AggregatedData!1!MetricId],
'Metric' As [AggregatedData!1!DataTypeName],
NULL As [AggregatedData!1!AdditionalData],
NULL As [DataPointType!2!IndependentValue],
NULL As [DataPointType!2!DependentValue],
NULL As [DataPointType!2!IndependentEndValue],
NULL As [DataPointType!2!DependentValueDesc],
NULL As [DataPointType!2!IndependentValueDesc]
FROM @perfRuleValues prv
RIGHT JOIN @metrics m on prv.GroupId = m.GroupId and prv.MetricId = m.Id
where prv.MetricId is null and m.MetricType = 3
UNION ALL
SELECT
1 As TAG,
999 As PARENT,
'' As [ArrayOfAggregatedData!999],
m.GroupId As [AggregatedData!1!GroupId],
m.MetricId As [AggregatedData!1!MetricId],
'Metric' As [AggregatedData!1!DataTypeName],
m.part As [AggregatedData!1!AdditionalData],
NULL As [DataPointType!2!IndependentValue],
NULL As [DataPointType!2!DependentValue],
NULL As [DataPointType!2!IndependentEndValue],
NULL As [DataPointType!2!DependentValueDesc],
NULL As [DataPointType!2!IndependentValueDesc]
FROM @perfRuleValues m
UNION ALL
SELECT
2 As TAG,
1 As PARENT,
'' As [ArrayOfAggregatedData!999],
m.GroupId As [AggregatedData!1!GroupId],
m.MetricId As [AggregatedData!1!MetricId],
'Metric' As [AggregatedData!1!DataTypeName],
m.part As [AggregatedData!1!AdditionalData],
m.value1 As [DataPointType!2!IndependentValue],
m.count1 As [DataPointType!2!DependentValue],
m.value2 As [DataPointType!2!IndependentEndValue],
m.value1_text As [DataPointType!2!DependentValueDesc],
m.value2_text As [DataPointType!2!IndependentValueDesc]
FROM @perfRuleValues m
UNION ALL
SELECT
2 As TAG,
1 As PARENT,
'' As [ArrayOfAggregatedData!999],
m.GroupId As [AggregatedData!1!GroupId],
m.MetricId As [AggregatedData!1!MetricId],
'Metric' As [AggregatedData!1!DataTypeName],
m.part As [AggregatedData!1!AdditionalData],
m.value2 As [DataPointType!2!IndependentValue],
m.count2 As [DataPointType!2!DependentValue],
m.value3 As [DataPointType!2!IndependentEndValue],
m.value2_text As [DataPointType!2!DependentValueDesc],
m.value3_text As [DataPointType!2!IndependentValueDesc]
FROM @perfRuleValues m
UNION ALL
SELECT
2 As TAG,
1 As PARENT,
'' As [ArrayOfAggregatedData!999],
m.GroupId As [AggregatedData!1!GroupId],
m.MetricId As [AggregatedData!1!MetricId],
'Metric' As [AggregatedData!1!DataTypeName],
m.part As [AggregatedData!1!AdditionalData],
m.value3 As [DataPointType!2!IndependentValue],
m.count3 As [DataPointType!2!DependentValue],
m.value4 As [DataPointType!2!IndependentEndValue],
m.value3_text As [DataPointType!2!DependentValueDesc],
m.value4_text As [DataPointType!2!IndependentValueDesc]
FROM @perfRuleValues m
UNION ALL
SELECT
2 As TAG,
1 As PARENT,
'' As [ArrayOfAggregatedData!999],
m.GroupId As [AggregatedData!1!GroupId],
m.MetricId As [AggregatedData!1!MetricId],
'Metric' As [AggregatedData!1!DataTypeName],
m.part As [AggregatedData!1!AdditionalData],
m.value4 As [DataPointType!2!IndependentValue],
m.count4 As [DataPointType!2!DependentValue],
m.value5 As [DataPointType!2!IndependentEndValue],
m.value4_text As [DataPointType!2!DependentValueDesc],
m.value5_text As [DataPointType!2!IndependentValueDesc]
FROM @perfRuleValues m
UNION ALL
SELECT
2 As TAG,
1 As PARENT,
'' As [ArrayOfAggregatedData!999],
m.GroupId As [AggregatedData!1!GroupId],
m.MetricId As [AggregatedData!1!MetricId],
'Metric' As [AggregatedData!1!DataTypeName],
m.part As [AggregatedData!1!AdditionalData],
m.value5 As [DataPointType!2!IndependentValue],
m.count5 As [DataPointType!2!DependentValue],
m.value6 As [DataPointType!2!IndependentEndValue],
m.value5_text As [DataPointType!2!DependentValueDesc],
m.value6_text As [DataPointType!2!IndependentValueDesc]
FROM @perfRuleValues m
ORDER BY
[ArrayOfAggregatedData!999],
[AggregatedData!1!GroupId],
[AggregatedData!1!MetricId],
[AggregatedData!1!DataTypeName],
[AggregatedData!1!AdditionalData],
[DataPointType!2!IndependentValue],
[DataPointType!2!IndependentEndValue],
[DataPointType!2!DependentValueDesc],
[DataPointType!2!IndependentValueDesc],
[DataPointType!2!DependentValue]
FOR XML EXPLICIT
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('Produce output', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
DECLARE @EndTime DateTime = getdate();
insert into @profilingdata values ('Total time', DATEDIFF(MILLISECOND, @StartTime, @EndTime))
select * from @profilingdata
END
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
GRANT EXECUTE ON [SDK].[Microsoft_SQLServer_Visualization_Library_GetDataCenterDashboardData] TO OpsMgrReader
GO
ALTER PROCEDURE [sdk].[Microsoft_SQLServer_Visualization_Library_GetInstanceViewData]
@ManagementGroupGuid uniqueidentifier,
@XmlData XML,
@profiling bit = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
IF NOT @ExecError = 0
RAISERROR('Text %s %d', 16, 1
,'ClassXml'
,@ExecError)
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('Update values', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
IF NOT @ExecError = 0
RAISERROR('Text %s %d', 16, 1
,'ClassXml'
,@ExecError)
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('Update hierarchy', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
DECLARE @parentGuid uniqueidentifier;
select @parentGuid = ParamValues.x.value('@Id','uniqueidentifier') from @xmlData.nodes('/Data/SelectedParent') AS ParamValues(x);
DECLARE @filter varchar(1000);
select @filter = '%' + ParamValues.x.value('@Value','varchar(1000)') + '%' from @xmlData.nodes('/Data/Filter') AS ParamValues(x);
DECLARE @managementGroupRowId int;
select @managementGroupRowId = mg.ManagementGroupRowId
from dbo.vManagementGroup mg
where mg.ManagementGroupGuid = @ManagementGroupGuid
DECLARE @ShowAlertsFromDescendants int;
select
@ShowAlertsFromDescendants = CASE WHEN lower(ParamValues.x.value('@ShowAlertsFromDescendants','varchar(5)')) = 'true' then 1 else 0 end
from @xmlData.nodes('/Data') AS ParamValues(x);
DECLARE @interval int;
select @interval = ParamValues.x.value('@Value','int') from @xmlData.nodes('/Data/Interval') AS ParamValues(x);
Declare @languagecode varchar(10);
select @languagecode = ParamValues.x.value('@Value','varchar(10)') from @xmlData.nodes('/Data/LanguageCode') AS ParamValues(x);
DECLARE @resolvedAlertDaysToKeep int = null;
DECLARE @autoResolveDays int = null;
DECLARE @autoResolveHealthyObjDays int = null;
select @resolvedAlertDaysToKeep = ParamValues.x.value('@ResolvedAlertDaysToKeep', 'int'),
@autoResolveDays = ParamValues.x.value('@AutoResolveDays', 'int'),
@autoResolveHealthyObjDays = ParamValues.x.value('@AutoResolveHealthyObjDays', 'int')
from @xmlData.nodes('/DatacenterViewQuery/OpsManagerConfiguration') AS ParamValues(x)
; with inserts as (
select @ManagementGroupGuid as ManagementGroupGuid, 'ResolvedAlertDaysToKeep' as Name, null as Value
UNION ALL
select @ManagementGroupGuid as ManagementGroupGuid, 'AutoResolveDays' as Name, null as Value
UNION ALL
select @ManagementGroupGuid as ManagementGroupGuid, 'AutoResolveHealthyObjDays' as Name, null as Value
)
insert into sdk.Microsoft_SQLServer_Visualization_Library_OpsManagerSettings
select * from inserts i
where not exists (
select null
from sdk.Microsoft_SQLServer_Visualization_Library_OpsManagerSettings oms
where i.ManagementGroupGuid = oms.ManagementGroupGuid
and i.Name = oms.Name
)
if @resolvedAlertDaysToKeep is not null and @autoResolveDays is not null and @autoResolveHealthyObjDays is not null
begin
update sdk.Microsoft_SQLServer_Visualization_Library_OpsManagerSettings
set Value = @resolvedAlertDaysToKeep
where ManagementGroupGuid = @ManagementGroupGuid
and Name = 'ResolvedAlertDaysToKeep';
update sdk.Microsoft_SQLServer_Visualization_Library_OpsManagerSettings
set Value = @autoResolveDays
where ManagementGroupGuid = @ManagementGroupGuid
and Name = 'AutoResolveDays';
update sdk.Microsoft_SQLServer_Visualization_Library_OpsManagerSettings
set Value = @autoResolveHealthyObjDays
where ManagementGroupGuid = @ManagementGroupGuid
and Name = 'AutoResolveHealthyObjDays';
end
else
begin
select @resolvedAlertDaysToKeep = Value
from sdk.Microsoft_SQLServer_Visualization_Library_OpsManagerSettings with (NOLOCK)
where ManagementGroupGuid = @ManagementGroupGuid
and Name = 'ResolvedAlertDaysToKeep'
if @resolvedAlertDaysToKeep is null
set @resolvedAlertDaysToKeep = 7
select @autoResolveDays = Value
from sdk.Microsoft_SQLServer_Visualization_Library_OpsManagerSettings with (NOLOCK)
where ManagementGroupGuid = @ManagementGroupGuid
and Name = 'AutoResolveDays'
if @autoResolveDays is null
set @autoResolveDays = 30
select @autoResolveHealthyObjDays = Value
from sdk.Microsoft_SQLServer_Visualization_Library_OpsManagerSettings with (NOLOCK)
where ManagementGroupGuid = @ManagementGroupGuid
and Name = 'AutoResolveHealthyObjDays'
if @autoResolveHealthyObjDays is null
set @autoResolveHealthyObjDays = 7
end
DECLARE @allowedGroups TABLE (
Id int PRIMARY KEY
);
;with allowedGroups as (
select DISTINCT ParamValues.x.value('@ID','uniqueidentifier') as [Guid] from @xmlData.nodes('/Data/OpsManagerConfiguration/AllowedGroup') AS ParamValues(x)
)
insert into @allowedGroups
select me.ManagedEntityRowId from allowedGroups a
inner join dbo.ManagedEntity me WITH (NOLOCK) on a.[Guid] = me.ManagedEntityGuid
inner join dbo.ManagedEntityManagementGroup memg WITH (NOLOCK) on me.ManagedEntityRowId = memg.ManagedEntityRowId
where me.ManagementGroupRowId = @managementGroupRowId
and memg.ToDateTime is null;
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('Load parameters', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
DECLARE @LatestMpVersions TABLE (
ManagementPackRowId int,
ManagementPackVersionRowId int
);
insert into @LatestMpVersions
select mpv2.ManagementPackRowId, max(ManagementPackVersionRowId) as ManagementPackVersionRowId
from dbo.vManagementPackVersion mpv2 (NOLOCK)
group by mpv2.ManagementPackRowId
DECLARE @FilteredRT TABLE (
RelationshipTypeRowId int
);
; with parentRT as (
SELECT TOP 1 rt.RelationshipTypeRowId
from dbo.vRelationshipType rt WITH (NOLOCK)
where rt.RelationshipTypeSystemName = 'System.Containment'
),
filteredRT as (
SELECT RelationshipTypeRowId
from parentRT
UNION ALL
SELECT rth.Child as RelationshipTypeRowId
from parentRT rt WITH (NOLOCK)
inner join sdk.Microsoft_SQLServer_Visualization_Library_RelationshipType_Hierarchy rth WITH (NOLOCK) on rt.RelationshipTypeRowId = rth.Parent
)
insert into @FilteredRT
select DISTINCT * from filteredRT;
; with allowedItems as (
select ag.Id as Id, cast(1 as bit) as [IsGroup] from @allowedGroups ag
UNION ALL
select
rhg.Child as Id,
CASE WHEN s.IsGroup = 1 THEN cast(0 as bit) ELSE cast(1 as bit) END as IsGroup
from allowedItems s
inner join sdk.Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy rhg with (nolock) on s.Id = rhg.Parent and s.IsGroup = rhg.IsGroup
)
insert into @items
select
child.ManagedEntityGuid as ManagedEntityGuid,
child.ManagedEntityRowId as RowId,
0 as Unmonitored,
case when mm.MaintenanceModeRowId is null then 0 else 1 end as Maintenance,
isnull(mm.PlannedMaintenanceInd, 0) as PlannedMaintenance,
0 as HealthState,
child.FullName as FullName,
child.DisplayName as DisplayName,
child.ManagedEntityTypeRowId
from dbo.vManagedEntity parent with (NOLOCK)
inner join dbo.vManagedEntityManagementGroup memg1 WITH (NOLOCK) on parent.ManagedEntityRowId = memg1.ManagedEntityRowId
inner join dbo.vRelationship rel WITH (NOLOCK) on parent.ManagedEntityRowId = rel.SourceManagedEntityRowId and rel.ManagementGroupRowId = @managementGroupRowId
inner join dbo.vRelationshipManagementGroup rmg WITH (NOLOCK) on rel.RelationshipRowId = rmg.RelationshipRowId
inner hash join @FilteredRT frt on rel.RelationshipTypeRowId = frt.RelationshipTypeRowId
inner join dbo.vManagedEntity child WITH (NOLOCK) on rel.TargetManagedEntityRowId = child.ManagedEntityRowId and child.ManagementGroupRowId = @managementGroupRowId
inner join dbo.vManagedEntityManagementGroup memg2 WITH (NOLOCK) on child.ManagedEntityRowId = memg2.ManagedEntityRowId
left join dbo.vMaintenanceMode mm WITH (NOLOCK) on mm.ManagedEntityRowId = child.ManagedEntityRowId and mm.EndDateTime is null
where memg1.ToDateTime is null
and memg2.ToDateTime is null
and rmg.ToDateTime is null
and parent.ManagedEntityGuid = @parentGuid
and parent.ManagedEntityRowId in (select Id from allowedItems);
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('Insert items', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
update @items
Set Unmonitored = 1
FROM dbo.vRelationship AS R WITH (NOLOCK)
inner join dbo.vRelationshipManagementGroup rmg WITH (NOLOCK) on r.RelationshipRowId = rmg.RelationshipRowId
INNER JOIN dbo.vRelationshipType AS RT WITH (NOLOCK) ON RT.RelationshipTypeRowId = R.RelationshipTypeRowId
INNER JOIN dbo.vManagedEntity AS ME WITH (NOLOCK) ON ME.TopLevelHostManagedEntityRowId = R.TargetManagedEntityRowId
inner join dbo.vManagedEntityManagementGroup memg WITH (NOLOCK) on ME.ManagedEntityRowId = memg.ManagedEntityRowId
INNER JOIN dbo.vHealthServiceOutage AS HSO WITH (NOLOCK) ON HSO.ManagedEntityRowId = R.SourceManagedEntityRowId
inner join @items as i on i.RowId = me.ManagedEntityRowId
WHERE RT.RelationshipTypeSystemName = 'Microsoft.SystemCenter.HealthServiceManagesEntity'
and rmg.ToDateTime is null
and memg.ToDateTime is null
AND HSO.EndDateTime is NULL
AND NOT EXISTS
(SELECT * FROM dbo.vHealthServiceOutage as HSO2 WITH (NOLOCK)
WHERE HSO2.DWLastModifiedDateTime = HSO.DWLastModifiedDateTime
AND HSO2.ManagedEntityRowId = HSO.ManagedEntityRowId
AND HSO2.ReasonCode = HSO.ReasonCode
AND HSO2.RootHealthServiceInd = HSO.RootHealthServiceInd
AND HSO2.StartDateTime = HSO.StartDateTime
AND HSO2.EndDateTime IS NOT NULL)
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('Update unmonitored items', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
update @items
Set HealthState =
case
when i.Maintenance = 1 then 4
when i.Unmonitored = 1 then 3
when s.HealthState = 0 then 5
when s.HealthState = 1 then 6
when s.HealthState = 2 then 2
when s.HealthState = 3 then 1
end
from @items i
inner join sdk.Microsoft_SQLServer_Visualization_Library_LastMonitorValues s on i.rowId = s.ManagedEntityRowId
inner join dbo.Monitor mon WITH (NOLOCK) on mon.MonitorRowId = s.MonitorRowId
Where mon.MonitorSystemName = 'System.Health.EntityState'
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('Update health for items', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
update @items
Set HealthState =
case
when i.Maintenance = 1 then 4
when i.Unmonitored = 1 then 3
else 5
end
FROM @items i
WHERE NOT EXISTS (
SELECT NULL
FROM sdk.Microsoft_SQLServer_Visualization_Library_LastMonitorValues AS s WITH (NOLOCK)
inner join dbo.Monitor mon WITH (NOLOCK) on s.MonitorRowId = mon.MonitorRowId
where s.ManagedEntityRowId = i.RowId and mon.MonitorSystemName = 'System.Health.EntityState')
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('Update health for unmonitored items', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
; with seed as (
select s.RowId, t.TypedManagedEntityRowId, t.ManagedEntityTypeRowId as TypedManagedEntityTypeRowId, s.ManagedEntityTypeRowId as ManagedEntityTypeRowId from @items s
left join TypedManagedEntity t (NOLOCK) on s.RowId = t.ManagedEntityRowId
),
typeIds as (
select distinct TypedManagedEntityTypeRowId as TypeRowId from seed
),
depthToAbstractRaw as (
select TypeRowId, TypeRowId as currentTypeRowId, 0 as depth from typeIds
UNION ALL
SELECT TypeRowId, t.BaseManagedEntityTypeRowId, d.depth + 1 as depth from depthToAbstractRaw d
inner join vManagedEntityTypeManagementPackVersion t (NOLOCK) on d.currentTypeRowId = t.ManagedEntityTypeRowId
inner join @LatestMpVersions lm on t.ManagementPackVersionRowId = lm.ManagementPackVersionRowId
where t.AbstractInd = 0
),
depthToAbstract as (
select TypeRowId, max(depth) as depth from depthToAbstractRaw group by TypeRowId
),
seedWithDepth as (
select s.RowId, s.ManagedEntityTypeRowId, s.TypedManagedEntityRowId, s.TypedManagedEntityTypeRowId, d.depth from seed s
left join depthToAbstract d on s.TypedManagedEntityTypeRowId = d.TypeRowId
),
updatedSeed as (
select s1.RowId, COALESCE(t.TypedManagedEntityTypeRowId, s1.ManagedEntityTypeRowId) as ManagedEntityTypeRowId from @items s1
CROSS APPLY (
select top 1 s2.TypedManagedEntityTypeRowId
from seedWithDepth s2
where s2.RowId = s1.RowId
order by s2.depth desc, s2.TypedManagedEntityRowId desc
) t
)
update @items
set ManagedEntityTypeRowId = u.ManagedEntityTypeRowId
from @items s
inner join updatedSeed u on s.RowId = u.RowId
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('Update types for items', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('Declare xml', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
Declare @tempXml xml
set @tempXml = (select
1 as Tag,
null as Parent,
mp.ManagementPackSystemName + '!' + met.[ManagedEntityTypeSystemName] as "Item!1!ImageId",
meti.[Image] as "Item!1!Image"
from vManagedEntityTypeImage meti with(nolock)
join vManagedEntityType met with(nolock) on met.ManagedEntityTypeRowId = meti.ManagedEntityTypeRowId
join vManagementPack mp with (nolock) on mp.ManagementPackRowId = met.ManagementPackRowId
-- TODO: Add MPSystemName
where met.ManagedEntityTypeRowId in (select distinct i.ManagedEntityTypeRowId from @items i) and meti.ImageCategory = 'u16x16Icon'
for xml explicit, BINARY BASE64)
SET @resDoc.modify('insert sql:variable("@tempXml")
into (/Data/Images)[1] ');
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('Write images to XML', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
--Insert Monitors into @MetricDefinitions
; with xmlstrings as (
select
x.value('@MetricId', 'uniqueidentifier') as MetricId,
LOWER(SUBSTRING(x.value('../../@ClassName', 'nvarchar(2000)'), 1, CHARINDEX('!', x.value('../../@ClassName', 'nvarchar(2000)')) - 1)) as ClassMpName,
LOWER(SUBSTRING(x.value('../../@ClassName', 'nvarchar(2000)'), CHARINDEX('!', x.value('../../@ClassName', 'nvarchar(2000)')) + 1, 2000)) as ClassName,
CASE WHEN CHARINDEX('!', x.value('@MonitorId', 'nvarchar(2000)')) = 0 THEN null ELSE LOWER(SUBSTRING(x.value('@MonitorId', 'nvarchar(2000)'), 1, CHARINDEX('!', x.value('@MonitorId', 'nvarchar(2000)')) - 1)) END as MonitorMpName,
LOWER(SUBSTRING(x.value('@MonitorId', 'nvarchar(2000)'), CHARINDEX('!', x.value('@MonitorId', 'nvarchar(2000)')) + 1, 2000)) as MonitorName,
x.value('@ShowInGrid', 'bit') as ShowInGrid,
x.value('@Order', 'int') as [Order]
from @xmlData.nodes('/Data/DatacenterGroup/DataCenterClasses/DataCenterClass/MonitorMetrics/MonitorMetric') as ParamValues(x)
)
insert into @MetricDefinitions
select
x.MetricId,
met.ManagedEntityTypeRowId,
m.MonitorRowId as MetricRowId,
2 as MetricType,
x.ShowInGrid,
x.[Order]
from xmlstrings as x
join vMonitor m WITH (NOLOCK) on m.MonitorSystemName = x.MonitorName
inner join dbo.vMonitorManagementPackVersion mmpv WITH (NOLOCK) on m.MonitorRowId = mmpv.MonitorRowId
inner join @LatestMpVersions lmv on mmpv.ManagementPackVersionRowId = lmv.ManagementPackVersionRowId
join vManagementPack mpm WITH (NOLOCK) on mpm.ManagementPackRowId = m.ManagementPackRowId and (mpm.ManagementPackSystemName = x.MonitorMpName or x.MonitorMpName is null)
join vManagedEntityType met WITH (NOLOCK) on met.ManagedEntityTypeSystemName = x.ClassName
join vManagementPack mpt WITH (NOLOCK) on mpt.ManagementPackRowId = met.ManagementPackRowId and mpt.ManagementPackSystemName = x.ClassMpName;
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('Insert #monitormetricdefinitions', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
-- Insert Performance metrics
; with xmlstrings as (
select
x.value('@MetricId', 'uniqueidentifier') as MetricId,
x.value('@PerformanceCollectionRuleId', 'uniqueidentifier') as RuleGuid,
LOWER(SUBSTRING(x.value('../../@ClassName', 'nvarchar(2000)'), 1, CHARINDEX('!', x.value('../../@ClassName', 'nvarchar(2000)')) - 1)) as ClassMpName,
LOWER(SUBSTRING(x.value('../../@ClassName', 'nvarchar(2000)'), CHARINDEX('!', x.value('../../@ClassName', 'nvarchar(2000)')) + 1, 2000)) as ClassName,
3 as MetricType,
x.value('@ShowInGrid', 'bit') as ShowInGrid,
x.value('@Order', 'int') as [Order]
from @xmlData.nodes('/Data/DatacenterGroup/DataCenterClasses/DataCenterClass/SmallPerformanceMetrics/SmallPerformanceMetric') as ParamValues(x)
UNION ALL
select
x.value('@MetricId', 'uniqueidentifier') as MetricId,
x.value('@PerformanceCollectionRuleId', 'uniqueidentifier') as RuleGuid,
LOWER(SUBSTRING(x.value('../../@ClassName', 'nvarchar(2000)'), 1, CHARINDEX('!', x.value('../../@ClassName', 'nvarchar(2000)')) - 1)) as ClassMpName,
LOWER(SUBSTRING(x.value('../../@ClassName', 'nvarchar(2000)'), CHARINDEX('!', x.value('../../@ClassName', 'nvarchar(2000)')) + 1, 2000)) as ClassName,
4 as MetricType,
x.value('@ShowInGrid', 'bit') as ShowInGrid,
x.value('@Order', 'int') as [Order]
from @xmlData.nodes('/Data/DatacenterGroup/DataCenterClasses/DataCenterClass/PerformanceMetrics/PerformanceMetric') as ParamValues(x)
)
insert into @MetricDefinitions
select
x.MetricId,
met.ManagedEntityTypeRowId,
pri.PerformanceRuleInstanceRowId as MetricRowId,
x.MetricType as MetricType,
x.ShowInGrid,
x.[Order]
from xmlstrings as x
join vManagedEntityType met WITH (NOLOCK) on met.ManagedEntityTypeSystemName = x.ClassName
join vManagementPack mpt WITH (NOLOCK) on mpt.ManagementPackRowId = met.ManagementPackRowId and mpt.ManagementPackSystemName = x.ClassMpName
join vRule r WITH (NOLOCK) on r.RuleGuid = x.RuleGuid
join vPerformanceRuleInstance pri WITH (NOLOCK) on pri.RuleRowId = r.RuleRowId
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('Insert #perfmetricdefinitions', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
;with initialMonitorMetrics as (
select
me.RowId as ManagedEntityRowId,
md.MetricId as MetricId,
me.Unmonitored as Unmonitored,
me.Maintenance as Maintenance,
me.PlannedMaintenance as PlannedMaintenance,
COALESCE(s.HealthState, 0) as HealthState,
s.[DateTime] as LastTimeUpdate,
md.[Order] as [Order],
ROW_NUMBER() OVER (PARTITION BY me.RowId, md.MetricId, me.Unmonitored, me.Maintenance, me.PlannedMaintenance, md.[Order] ORDER BY s.[DateTime] DESC) AS rn
from @items me
join @MetricDefinitions md on me.ManagedEntityTypeRowId = md.ManagedEntityTypeRowId
left join sdk.Microsoft_SQLServer_Visualization_Library_LastMonitorValues s WITH (NOLOCK) on me.RowId = s.ManagedEntityRowId and md.MetricRowId = s.MonitorRowId
where md.ShowInGrid = 1 and md.MetricType = 2
)
insert into @ItemMonitorMetrics
select ManagedEntityRowId, MetricId, Unmonitored, Maintenance, PlannedMaintenance, HealthState, LastTimeUpdate, [Order]
from initialMonitorMetrics
where rn = 1;
-- select * from @ItemMonitorMetrics
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('Get @ItemMonitorMetrics', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
set @tempXml = (
select *
from (
select
1 as Tag,
null as Parent,
ManagedEntityGuid as "Item!1!Id",
DisplayName as "Item!1!DisplayName",
HealthState as "Item!1!HealthState",
Lower(mp.ManagementPackSystemName) + '!' + Lower(mt.ManagedEntityTypeSystemName) as "Item!1!ClassName",
null as "Metric!2!MetricId",
null as "Metric!2!MetricType",
null as "Metric!2!Value",
null as "Metric!2!LastTimeUpdate",
null as "Metric!2!Order"
from @items i
inner join dbo.vManagedEntityType mt WITH (NOLOCK) on i.ManagedEntityTypeRowId = mt.ManagedEntityTypeRowId
inner join dbo.vManagementPack mp WITH (NOLOCK) on mt.ManagementPackRowId = mp.ManagementPackRowId
UNION ALL
select
2 as Tag,
1 as Parent,
i.ManagedEntityGuid as "Item!1!Id",
i.DisplayName as "Item!1!DisplayName",
i.HealthState as "Item!1!HealthState",
Lower(mt.ManagedEntityTypeSystemName) as "Item!1!ClassName",
imm.MetricId as "Metric!2!MetricId",
2 as "Metric!2!MetricType",
case
when imm.Maintenance = 1 then 4
when imm.Unmonitored = 1 then 3
when imm.HealthState = 0 then 5
when imm.HealthState = 1 then 6
when imm.HealthState = 2 then 2
when imm.HealthState = 3 then 1
end as "Metric!2!Value",
imm.LastTimeUpdate as "Metric!2!LastTimeUpdate",
imm.[Order] as "Metric!2!Order"
FROM @ItemMonitorMetrics imm
join @items i on imm.ManagedEntityRowId = i.RowId
join dbo.vManagedEntityType mt WITH (NOLOCK) on i.ManagedEntityTypeRowId = mt.ManagedEntityTypeRowId
UNION ALL
select
2 as Tag,
1 as Parent,
me.ManagedEntityGuid as "Item!1!Id",
me.DisplayName as "Item!1!DisplayName",
me.HealthState as "Item!1!HealthState",
Lower(mt.ManagedEntityTypeSystemName) as "Item!1!ClassName",
md.MetricId as "Metric!2!MetricId",
md.MetricType as "Metric!2!MetricType",
s.SampleValue as "Metric!2!Value",
s.[DateTime] as "Metric!2!LastTimeUpdate",
md.[Order] as "Metric!2!Order"
from @items me
join dbo.vManagedEntityType mt WITH (NOLOCK) on me.ManagedEntityTypeRowId = mt.ManagedEntityTypeRowId
join @MetricDefinitions md on me.ManagedEntityTypeRowId = md.ManagedEntityTypeRowId
join sdk.Microsoft_SQLServer_Visualization_Library_LastPerfValues s WITH (NOLOCK) on me.RowId = s.ManagedEntityRowId and md.MetricRowId = s.PerformanceRuleInstanceRowId
where md.ShowInGrid = 1 and md.MetricType = 3
) t
order by t."Item!1!HealthState", t."Item!1!Id", t.Tag, t."Metric!2!Order"
for xml explicit)
SET @resDoc.modify('insert sql:variable("@tempXml")
into (/Data/Children)[1] ');
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('Collect and write children to XML', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
DECLARE @selectedItemGuid uniqueidentifier;
DECLARE @selectedItemRowId int;
DECLARE @selectedItemTypeRowId int;
select
@selectedItemGuid = ParamValues.x.value('@Id','uniqueidentifier')
from @xmlData.nodes('/Data/SelectedChild') AS ParamValues(x);
DECLARE @firstItemGuid uniqueidentifier;
select top 1 @firstItemGuid = i.ManagedEntityGuid
from @items i
join dbo.vManagedEntityType mt WITH (NOLOCK) on i.ManagedEntityTypeRowId = mt.ManagedEntityTypeRowId
where @filter is null or i.DisplayName like @filter or Lower(mt.ManagedEntityTypeSystemName) like @filter
order by i.HealthState, i.ManagedEntityGuid;
--TODO: Coalesce
Set @selectedItemGuid = case when @selectedItemGuid is NULL then @firstItemGuid else @selectedItemGuid end;
select @selectedItemRowId = RowId, @selectedItemTypeRowId = ManagedEntityTypeRowId from @items where ManagedEntityGuid = @selectedItemGuid;
set @tempXML = (
select
1 Tag,
0 Parent,
@selectedItemGuid as "SelectedChild!1!Id",
mp.ManagementPackSystemName + '!' + met.ManagedEntityTypeSystemName as "SelectedChild!1!TypeSystemName",
COALESCE(met.ManagedEntityTypeDefaultName, met.ManagedEntityTypeSystemName) as "SelectedChild!1!TypeDefaultName",
ds.Name "SelectedChild!1!TypeName"
from vManagedEntityType met with (nolock)
inner join dbo.vManagementPack mp on met.ManagementPackRowId = mp.ManagementPackRowId
left join vDisplayString ds with (nolock) on met.ManagedEntityTypeGuid = ds.ElementGuid and ds.LanguageCode = @languagecode
where met.ManagedEntityTypeRowId = @selectedItemTypeRowId
for xml explicit)
SET @resDoc.modify('insert sql:variable("@tempXml")
into (/Data)[1] ');
--SET @resDoc.modify('insert attribute Id {sql:variable("@selectedItemGuid")}
--into (/Data/SelectedChild)[1] ');
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('Calculate and write selected child to XML', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
DECLARE @groups TABLE (
Id uniqueidentifier
);
insert into @groups
select @selectedItemGuid;
insert into @relmetrics
select DISTINCT
x.value('@Id', 'uniqueidentifier') as GroupId,
x.value('@Id', 'uniqueidentifier') as Id,
0 as MetricType,
mon.MonitorRowId as MonitorRowId
from @resDoc.nodes('/Data/SelectedChild') as ParamValues(x)
inner join dbo.Monitor mon WITH (NOLOCK) on mon.MonitorSystemName = 'System.Health.EntityState'
UNION ALL
select DISTINCT
x.value('@Id', 'uniqueidentifier') as GroupId,
x.value('@Id', 'uniqueidentifier') as Id,
1 as MetricType,
0 as MonitorRowId
from @resDoc.nodes('/Data/SelectedChild') as ParamValues(x)
--select * from @relmetrics;
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('Prepare groups and relmetrics', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
insert into @relitems
select
g.Id as GroupId,
child.ManagedEntityRowId as RowId,
MAX(cast(i.Unmonitored as int)) as Unmonitored,
MAX(case when mm.MaintenanceModeRowId is null then 0 else 1 end) as Maintenance,
MAX(isnull(cast(mm.PlannedMaintenanceInd as int), 0)) as PlannedMaintenance
from @groups g
inner join @items i on g.Id = i.ManagedEntityGuid
inner join dbo.vManagedEntityManagementGroup memg1 WITH (NOLOCK) on i.RowId = memg1.ManagedEntityRowId
inner join dbo.vRelationship rel WITH (NOLOCK) on i.RowId = rel.SourceManagedEntityRowId and rel.ManagementGroupRowId = @managementGroupRowId
inner join dbo.vRelationshipManagementGroup rmg WITH (NOLOCK) on rel.RelationshipRowId = rmg.RelationshipRowId
inner hash join @FilteredRT frt on rel.RelationshipTypeRowId = frt.RelationshipTypeRowId
inner join dbo.vManagedEntity child WITH (NOLOCK) on rel.TargetManagedEntityRowId = child.ManagedEntityRowId and child.ManagementGroupRowId = @managementGroupRowId
inner join dbo.vManagedEntityManagementGroup memg2 WITH (NOLOCK) on child.ManagedEntityRowId = memg2.ManagedEntityRowId
left join dbo.vMaintenanceMode mm WITH (NOLOCK) on mm.ManagedEntityRowId = child.ManagedEntityRowId and mm.EndDateTime is null
where memg1.ToDateTime is null
and memg2.ToDateTime is null
and rmg.ToDateTime is null
group by child.ManagedEntityRowId, g.Id
order by RowId, GroupId;
--select * from @relitems
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('Get relitems', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
; with seed as (
select g.Id as GroupId, rh.Child as RowId, CASE WHEN groupDiversity.IsGroup = 1 THEN cast(0 as bit) ELSE cast(1 as bit) END as IsGroup
from @groups g
inner join @items i on g.Id = i.ManagedEntityGuid
-- we need this, because our object could be and could not be a group here.
CROSS APPLY (SELECT cast(1 as bit) as IsGroup UNION ALL SELECT cast(0 as bit) as IsGroup) as groupDiversity
inner join sdk.Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy rh WITH (NOLOCK) on i.RowId = rh.Parent and rh.IsGroup = groupDiversity.IsGroup
UNION ALL
SELECT g.Id as GroupId, i.RowId, NULL
from @groups g
inner join @items i on g.Id = i.ManagedEntityGuid
),
data as (
select * from seed
UNION ALL
select
s.GroupId as GroupId,
rhg.Child as RowId,
CASE WHEN s.IsGroup = 1 THEN cast(0 as bit) WHEN s.IsGroup = 0 THEN cast(1 as bit) ELSE NULL END as IsGroup
from data s
inner join sdk.Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy rhg with (nolock) on s.RowId = rhg.Parent and s.IsGroup = rhg.IsGroup
),
hItems as (
select GroupId, RowId, memg.FromDateTime as LastPresencePeriodStarted from data
inner join dbo.vManagedEntity me WITH (NOLOCK) on data.RowId = me.ManagedEntityRowId and me.ManagementGroupRowId = @managementGroupRowId
inner join dbo.vManagedEntityManagementGroup memg WITH (NOLOCK) on me.ManagedEntityRowId = memg.ManagedEntityRowId
where memg.ToDateTime is null
),
hItemsWithHealth as (
select DISTINCT
hItems.GroupId,
hItems.RowId,
hItems.LastPresencePeriodStarted,
CASE WHEN s.HealthState = 1 THEN 1 ELSE 0 END as IsHealthy,
ROW_NUMBER() OVER (PARTITION BY hItems.RowId, hItems.GroupId ORDER BY s.DateTime desc) as rn
from hItems
inner join @groups g on hItems.GroupId = g.Id
inner join @relmetrics m on g.Id = m.GroupId and m.MetricType = 0
left JOIN sdk.Microsoft_SQLServer_Visualization_Library_LastMonitorValues AS s WITH (NOLOCK) ON s.ManagedEntityRowId = hItems.RowId and s.MonitorRowId = m.MonitorRowId and s.DateTime > hItems.LastPresencePeriodStarted
)
insert into @hierarchicRelItems
select
GroupId,
RowId,
LastPresencePeriodStarted,
IsHealthy
from hItemsWithHealth
where rn = 1
order by RowId, GroupId;
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('Get hierarchicRelItems', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
--select * from @hierarchicRelItems;
DECLARE @relmetricValues TABLE (
GroupId uniqueidentifier,
MetricId uniqueidentifier,
MetricType smallint,
name varchar(200),
value integer
);
insert into @relmetricValues
select GroupId, MetricId, MetricType, Name, sum(Value) from (
SELECT
g.Id as GroupId,
m.Id as MetricId,
m.MetricType,
case
when i.Maintenance = 1 then 4
when i.Unmonitored = 1 then 3
when s.HealthState = 0 then 5
when s.HealthState = 1 then 6
when s.HealthState = 2 then 2
when s.HealthState = 3 then 1
end as Name,
count(i.RowId) as Value
FROM @relitems i
inner join @groups g on i.GroupId = g.Id
inner join @relmetrics m on g.Id = m.GroupId and m.MetricType = 0
inner JOIN sdk.Microsoft_SQLServer_Visualization_Library_LastMonitorValues AS s WITH (NOLOCK) ON s.ManagedEntityRowId = i.RowId and s.MonitorRowId = m.MonitorRowId
group by g.Id, m.Id, m.MetricType,
case
when i.Maintenance = 1 then 4
when i.Unmonitored = 1 then 3
when s.HealthState = 0 then 5
when s.HealthState = 1 then 6
when s.HealthState = 2 then 2
when s.HealthState = 3 then 1
end
UNION ALL
SELECT
g.Id as GroupId,
m.Id as MetricId,
m.MetricType,
case
when i.Maintenance = 1 then 4
when i.Unmonitored = 1 then 3
else 5
end as Name,
count(i.RowId) as Value
FROM @relitems i
inner join @groups g on i.GroupId = g.Id
inner join @relmetrics m on g.Id = m.GroupId and m.MetricType = 0
WHERE NOT EXISTS (
SELECT NULL
FROM sdk.Microsoft_SQLServer_Visualization_Library_LastMonitorValues AS s WITH (NOLOCK)
where s.ManagedEntityRowId = i.RowId and s.MonitorRowId = m.MonitorRowId)
group by g.Id, m.Id, m.MetricType,
case
when i.Maintenance = 1 then 4
when i.Unmonitored = 1 then 3
else 5
end) as monitors
group by GroupId, MetricId, MetricType, Name;
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('Get relmetricvalues - monitor', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
-- alert
insert into @relmetricValues
SELECT g.Id as GroupId, m.Id as MetricId, m.MetricType, a.Severity as Name, count(a.Severity) as Value
FROM @hierarchicRelItems i
INNER JOIN sdk.Microsoft_SQLServer_Visualization_Library_LastAlertValues AS A WITH (NOLOCK) ON A.ManagedEntityRowId = i.RowId
inner join @groups g on i.GroupId = g.Id
inner join @relmetrics m on g.Id = m.GroupId and m.MetricType = 1
WHERE a.ResolutionState < 255
and a.DateTime > i.LastPresencePeriodStarted -- filtering by Instance presence
and a.DateTime > DATEADD(DAY, -1 * (CASE
WHEN i.IsHealthy = 1
THEN @autoResolveHealthyObjDays
ELSE @autoResolveDays
END + @resolvedAlertDaysToKeep + 1), @launchDateTime)
group by g.Id, m.Id, m.MetricType, a.Severity;
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('Get relmetricvalues - alert', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
; with PossibleAlertStates as (
SELECT 0 as State
UNION ALL
SELECT 1
UNION ALL
SELECT 2
),
PossibleHealthStates as (
SELECT 1 as State
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
),
PossibleCountStates as (
SELECT 1 as State
),
FullSet as (
select m.GroupId, m.Id as MetricId, m.MetricType, ps.State as Name, 0 as Value from PossibleAlertStates ps
full join @relmetrics m on 1=1
where m.MetricType = 1
union all
select m.GroupId, m.Id as MetricId, m.MetricType, ps.State as Name, 0 as Value from PossibleHealthStates ps
full join @relmetrics m on 1=1
where m.MetricType = 0
)
insert into @relmetricValues
select fs.* from FullSet fs
left join @relmetricValues mv on fs.GroupId = mv.GroupId and fs.MetricId = mv.MetricId and fs.MetricType = mv.MetricType and fs.Name = mv.name
where mv.Name is null
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('insert skipped relmetricvalues', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
--select * from @relmetricValues
set @tempXml = (
select * from (
select 1 as Tag,
null as Parent,
MetricType as "Metric!1!Type",
null as "Item!2!Name",
null as "Item!2!Value"
from @relmetricValues
group by MetricType
UNION ALL
select
2 as Tag,
1 as Parent,
MetricType as "Metric!1!Type",
name as "Item!2!Name",
value as "Item!2!Value"
from @relmetricValues
) t
order by "Metric!1!Type","Item!2!Name","Item!2!Value"
for xml explicit)
SET @resDoc.modify('insert sql:variable("@tempXml")
into (/Data/RelatedMetrics)[1] ');
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('write relmetricvalues to xml', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
DECLARE @entityProperties TABLE (
PropertyDefaultName nvarchar(2000),
PropertyType varchar(2000),
PropertyLength varchar(2000),
Value nvarchar(2000),
Name nvarchar(2000)
);
DECLARE @TypeList TABLE (
ManagedEntityTypeRowId int
);
; with fullTypeSpectre as
(
select tme.ManagedEntityTypeRowId as ManagedEntityTypeRowId
from dbo.vTypedManagedEntity tme WITH (NOLOCK)
where tme.ManagedEntityRowId = @selectedItemRowId
UNION ALL
select met.ManagedEntityTypeRowId from fullTypeSpectre fts
inner join dbo.vManagedEntityTypeManagementPackVersion metmpv on fts.ManagedEntityTypeRowId = metmpv.ManagedEntityTypeRowId
inner join dbo.vManagedEntityType met WITH (NOLOCK) on met.ManagedEntityTypeRowId = metmpv.BaseManagedEntityTypeRowId
inner join @LatestMpVersions lmv on lmv.ManagementPackVersionRowId = metmpv.ManagementPackVersionRowId
)
insert into @TypeList
select distinct fts.ManagedEntityTypeRowId from fullTypeSpectre fts;
Declare @props XML
SELECT @props = PropertyXml
FROM [dbo].[vManagedEntityProperty] WITH (NOLOCK)
where vManagedEntityProperty.ManagedEntityRowID = @selectedItemRowId and ToDateTime is null;
Insert into @entityProperties
select
tp.PropertyDefaultName,
mpv.PropertyType,
mpv.PropertyLength,
ParamValues.x.value('(.)[1]', 'varchar(2000)'),
ds.Name
from @TypeList tl
join [dbo].[vManagedEntityTypeProperty] tp on tl.ManagedEntityTypeRowId = tp.ManagedEntityTypeRowId
join vManagedEntityTypePropertyManagementPackVersion mpv on tp.ManagedEntityTypePropertyRowId = mpv.ManagedEntityTypePropertyRowId
join @LatestMpVersions lm on lm.ManagementPackVersionRowId = mpv.ManagementPackVersionRowId
left join vDisplayString ds on ds.ElementGuid = tp.PropertyGuid and ds.LanguageCode = @languagecode
left join @props.nodes('/Root/Property') as ParamValues(x) on x.value('@Guid', 'uniqueidentifier') = tp.PropertyGuid
where tp.PropertyDefaultName NOT IN ('Asset Status', 'Notes', 'Object Status')
order by tp.ManagedEntityTypePropertyRowId;
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('get entityproperties', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
set @tempXml = (
select
1 as Tag,
null as Parent,
PropertyDefaultName as "Item!1!PropertyDefaultName",
PropertyType as "Item!1!PropertyType",
PropertyLength as "Item!1!PropertyLength",
Value as "Item!1!Value",
Name as "Item!1!Name"
from @entityProperties
for xml explicit)
SET @resDoc.modify('insert sql:variable("@tempXml")
into (/Data/Properties)[1] ');
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('write entityproperties to XML', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
set @tempXml = (
select
1 as Tag,
null as Parent,
a.[DateTime] as "Item!1!DateTime",
a.[AlertGuid] as "Item!1!AlertGuid",
a.[Severity] as "Item!1!Severity",
a.[ResolutionState] as "Item!1!ResolutionState",
al.[AlertName] as "Item!1!AlertDefaultName",
ds.[Name] as "Item!1!AlertName",
al.[RepeatCount] as "Item!1!RepeatCount",
rs.ResolutionStateName as "Item!1!ResolutionStateName",
me.ManagedEntityGuid as "Item!1!MonitoringObjectGuid"
FROM sdk.Microsoft_SQLServer_Visualization_Library_LastAlertValues AS A WITH (NOLOCK)
INNER JOIN [Alert].[vAlert] al WITH (NOLOCK) on a.AlertGuid = al.AlertGuid
INNER JOIN [dbo].[vResolutionState] rs WITH (NOLOCK) on a.[ResolutionState] = rs.[ResolutionStateId]
left join vDisplayString ds WITH (NOLOCK) on al.AlertStringGuid = ds.ElementGuid and ds.LanguageCode = @languagecode
inner join dbo.vManagedEntity me WITH (NOLOCK) on a.ManagedEntityRowId = me.ManagedEntityRowId
inner join (select * FROM @hierarchicRelItems i where @ShowAlertsFromDescendants = 1 or i.RowId = @selectedItemRowId) i on me.ManagedEntityRowId = i.RowId
WHERE a.ResolutionState < 255
and a.DateTime > i.LastPresencePeriodStarted -- filtering by Instance presence
and a.DateTime > DATEADD(DAY, -1 * (CASE
WHEN i.IsHealthy = 1
THEN @autoResolveHealthyObjDays
ELSE @autoResolveDays
END + @resolvedAlertDaysToKeep + 1), @launchDateTime)
order by a.Severity desc, a.[DateTime] desc
for xml explicit)
SET @resDoc.modify('insert sql:variable("@tempXml")
into (/Data/Alerts)[1] ');
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('get and write alerts to XML', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
-- Get Monitor Data
;with initialMonitorMetrics as (
select
@selectedItemRowId as ManagedEntityRowId,
md.MetricId as MetricId,
i.Unmonitored as Unmonitored,
i.Maintenance as Maintenance,
i.PlannedMaintenance as PlannedMaintenance,
COALESCE(s.HealthState, 0) as HealthState,
s.[DateTime] as LastTimeUpdate,
ROW_NUMBER() OVER (PARTITION BY @selectedItemRowId, md.MetricId, i.Unmonitored, i.Maintenance, i.PlannedMaintenance ORDER BY s.[DateTime] DESC) AS rn
from @items i
join @MetricDefinitions md on md.ManagedEntityTypeRowId = i.ManagedEntityTypeRowId
LEFT join sdk.Microsoft_SQLServer_Visualization_Library_LastMonitorValues s WITH (NOLOCK) on md.MetricRowId = s.MonitorRowId and i.RowId = s.ManagedEntityRowId
where i.RowId = @selectedItemRowId
and md.MetricType = 2
and md.ManagedEntityTypeRowId = @selectedItemTypeRowId
)
insert into @MonitorMetrics
select ManagedEntityRowId, MetricId, Unmonitored, Maintenance, PlannedMaintenance, HealthState, LastTimeUpdate
from initialMonitorMetrics
where rn = 1;
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('get monitormetrics', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
set @tempXml = (
select
1 as Tag,
null as Parent,
MetricId as "Item!1!MetricId",
2 as "Item!1!MetricType",
case
when Maintenance = 1 then 4
when Unmonitored = 1 then 3
when HealthState = 0 then 5
when HealthState = 1 then 6
when HealthState = 2 then 2
when HealthState = 3 then 1
end as "Item!1!Value",
LastTimeUpdate as "Item!1!LastTimeUpdate"
FROM @MonitorMetrics
for xml explicit)
SET @resDoc.modify('insert sql:variable("@tempXml")
into (/Data/Metrics/MonitorMetrics)[1] ');
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('write monitormetrics to XML', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
set @tempXml = (
select
1 as Tag,
null as Parent,
md.MetricId as "Item!1!MetricId",
s.SampleValue as "Item!1!Value",
s.[DateTime] as "Item!1!LastTimeUpdate"
from @MetricDefinitions md
join sdk.Microsoft_SQLServer_Visualization_Library_LastPerfValues s WITH (NOLOCK) on s.PerformanceRuleInstanceRowId = md.MetricRowId
where md.ManagedEntityTypeRowId = @selectedItemTypeRowId and md.MetricType = 3 and s.ManagedEntityRowId = @selectedItemRowId
for xml explicit
)
--TODO: Filter by startdate - interval
SET @resDoc.modify('insert sql:variable("@tempXml")
into (/Data/Metrics/SmallPerformanceMetrics)[1] ');
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('get and write SmallPerformanceMetrics to XML', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
declare @startdate datetime
SET @startdate = DATEADD(minute, -@interval,GETUTCDATE())
INSERT INTO @SomeRows
SELECT 1
UNION ALL
SELECT 1
UNION ALL
SELECT 1
UNION ALL
SELECT 1;
INSERT INTO @SomeRows
SELECT f1.* FROM @SomeRows f1
CROSS JOIN @SomeRows f2
CROSS JOIN @SomeRows f3;
;with heads as (select * from @MetricDefinitions where MetricType = 4 and ManagedEntityTypeRowId = @selectedItemTypeRowId),
vals as (
select
h.MetricId as MetricId,
p.[DateTime] as "DateTime",
p.SampleValue as "Value"
from Perf.vPerfRaw p WITH (NOLOCK)
inner join heads h on p.PerformanceRuleInstanceRowId = h.MetricRowId
where p.ManagedEntityRowId = @selectedItemRowId and p.DateTime > @startdate
),
dates as (
select top(49)
DATEADD(minute, @step*(ROW_NUMBER() over (ORDER BY p.Dummy) - 1),@startdate) pr_dt,
DATEADD(minute, @step*(ROW_NUMBER() over (ORDER BY p.Dummy)),@startdate) dt
FROM
@SomeRows p
)
insert into @PerfMetricValues
select
m.MetricId as MetricId,
d.dt as Dt,
avg(v.Value) as Value
from heads m
cross join dates d
inner join vals v on m.MetricId = v.MetricId and v.[DateTime] between d.pr_dt and d.dt
group by m.MetricId, d.dt;
insert into @PerfMetricValues
select
m.MetricId as MetricId,
v.DateTime as Dt,
v.SampleValue as Value
from @MetricDefinitions m
inner join sdk.Microsoft_SQLServer_Visualization_Library_LastPerfValues v on m.MetricRowId = v.PerformanceRuleInstanceRowId
where v.DateTime > DATEADD(minute, -@step, GETUTCDATE()) and v.ManagedEntityRowId = @selectedItemRowId and m.MetricType = 4 and m.ManagedEntityTypeRowId = @selectedItemTypeRowId
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('get perfMetrics new', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END;
set @tempXml = (
select
t.Tag as Tag,
t.Parent as Parent,
t.MetricId as "Metric!1!MetricId",
t.Dt as "Item!2!DateTime",
t.Value as "Item!2!Value"
from (
select distinct
1 as Tag,
null as Parent,
MetricId as MetricId,
null as Dt,
null as Value
from @MetricDefinitions
where MetricId in (SELECT DISTINCT MetricID FROM @PerfMetricValues) and MetricType = 4
UNION ALL
select
2 as Tag,
1 as Parent,
MetricId as MetricId,
Dt as Dt,
Value as Value
from @PerfMetricValues
) as t
order by "Metric!1!MetricId", "Item!2!DateTime", "Item!2!Value"
for xml explicit
)
SET @resDoc.modify('insert sql:variable("@tempXml")
into (/Data/Metrics/PerformanceMetrics)[1] ');
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('write perfMetrics new', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
INSERT INTO @Monitors
select
m.MonitorGuid as "Item!1!MonitorGuid",
mp.ManagementPackSystemName as "Item!1!MpSystemName",
m.MonitorSystemName as "Item!1!MonitorSystemName",
m.MonitorDefaultName as "Item!1!MonitorDefaultName",
ds.Name as "Item!1!MonitorName"
from @TypeList tme
join vMonitorManagementPackVersion mmpv WITH (NOLOCK) on tme.ManagedEntityTypeRowId = mmpv.TargetManagedEntityTypeRowId
join vMonitor m WITH (NOLOCK) on m.MonitorRowId = mmpv.MonitorRowId
join @LatestMpVersions lmv on lmv.ManagementPackVersionRowId = mmpv.ManagementPackVersionRowId
join dbo.vManagementPack mp WITH (NOLOCK) on mp.ManagementPackRowId = lmv.ManagementPackRowId
left join vDisplayString ds WITH (NOLOCK) on ds.ElementGuid = m.MonitorGuid and ds.LanguageCode = @languagecode
set @tempXml = (
select
1 as Tag,
null as Parent,
m.*
from @Monitors m
for xml explicit
)
SET @resDoc.modify('insert sql:variable("@tempXml")
into (/Data/Metadata/Monitors)[1] ');
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('write Monitor List', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
INSERT INTO @Rules
select
r.RuleGuid as "Item!1!RuleGuid",
r.RuleDefaultName as "Item!1!RuleDefaultName",
ds.Name as "Item!1!RuleName"
from @TypeList tme
join vRuleManagementPackVersion rmpv WITH (NOLOCK) on tme.ManagedEntityTypeRowId = rmpv.TargetManagedEntityTypeRowId
join vWorkflowCategory wc WITH (NOLOCK) on rmpv.WorkflowCategoryRowId = wc.WorkflowCategoryRowId
join vRule r WITH (NOLOCK) on r.RuleRowId = rmpv.RuleRowId
join @LatestMpVersions lmv on lmv.ManagementPackVersionRowId = rmpv.ManagementPackVersionRowId
join dbo.vManagementPack mp WITH (NOLOCK) on mp.ManagementPackRowId = lmv.ManagementPackRowId
left join vDisplayString ds WITH (NOLOCK) on ds.ElementGuid = r.RuleGuid and ds.LanguageCode = @languagecode
where wc.WorkflowCategorySystemName = 'PerformanceCollection';
set @tempXml = (
select
1 as Tag,
null as Parent,
r.*
from @Rules r
for xml explicit
)
SET @resDoc.modify('insert sql:variable("@tempXml")
into (/Data/Metadata/Rules)[1] ');
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('write Rule List', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
select @resDoc
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('Produce output', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
DECLARE @EndTime DateTime = getdate();
insert into @profilingdata values ('Total time', DATEDIFF(MILLISECOND, @StartTime, @EndTime))
select * from @profilingdata
END
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
GRANT EXECUTE ON [SDK].[Microsoft_SQLServer_Visualization_Library_GetInstanceViewData] TO OpsMgrReader
GO
ALTER PROCEDURE [sdk].[Microsoft_SQLServer_Visualization_Library_UpdateHierarchy]
(
@profiling bit = 0
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @ExecError int
declare @delay int = 5;
while (@delay > 0)
begin
IF EXISTS (SELECT NULL
FROM sdk.Microsoft_SQLServer_Visualization_Library_Table_Batches b
inner join sdk.Microsoft_SQLServer_Visualization_Library_Tables t on b.TableId = t.TableId
where b.StartDate is null and t.Type in (5,6))
begin
BREAK
end
IF EXISTS (SELECT NULL
FROM sdk.Microsoft_SQLServer_Visualization_Library_Table_Batches b
inner join sdk.Microsoft_SQLServer_Visualization_Library_Tables t on b.TableId = t.TableId
where b.StartDate is not null and b.FinishDate is null and t.Type in (5,6))
BEGIN
WAITFOR DELAY '00:00:01'
END
set @delay = @delay - 1;
end
WHILE (1=1)
BEGIN
update b
set StartDate = GetUtcDate()
output inserted.BatchId, inserted.FirstId, inserted.LastId into @CurrentBatch
from sdk.Microsoft_SQLServer_Visualization_Library_Table_Batches b
where b.BatchId = (select top 1 b.BatchId from sdk.Microsoft_SQLServer_Visualization_Library_Table_Batches b
inner join sdk.Microsoft_SQLServer_Visualization_Library_Tables t on b.TableId = t.TableId
where b.StartDate is null and t.Type = 5
order by b.BatchId desc)
select top 1 @firstId = FirstId, @lastId = LastId from @CurrentBatch
IF @@ROWCOUNT = 0
BREAK
DECLARE @updatedMPVersions TABLE (
ManagementPackVersionRowId [int] NOT NULL,
ManagementPackRowId [int] not null
);
insert into @updatedMPVersions
select max(mpv.ManagementPackVersionRowId) as ManagementPackVersionRowId, mpv.ManagementPackRowId as ManagementPackRowId
from dbo.ManagementPackVersion mpv WITH (NOLOCK)
where mpv.ManagementPackVersionRowId > @firstId and mpv.ManagementPackVersionRowId <= @lastId
group by mpv.ManagementPackRowId
DECLARE @removedMPVersions TABLE (
ManagementPackVersionRowId [int] NOT NULL
);
insert into @removedMPVersions
select mpv.ManagementPackVersionRowId
from dbo.ManagementPackVersion mpv WITH (NOLOCK)
where ManagementPackRowId in (select distinct managementPackRowId from @updatedMPVersions)
and ManagementPackVersionRowId not in (select managementPackVersionRowId from @updatedMPVersions)
-- delete outdated elements
set @firstRun = 1;
set @deadlockRetries = @maxDeadlockCount;
while (@firstRun = 1 or @testRowCount > 0)
begin
BEGIN TRY
BEGIN TRANSACTION
set @firstRun = 0;
;with RemovedIds as (
select rtmpv.RelationshipTypeRowId
from dbo.RelationshipTypeManagementPackVersion rtmpv WITH (NOLOCK)
where rtmpv.ManagementPackVersionRowId in (select ManagementPackVersionRowId from @removedMPVersions)
)
delete top(@batchSize) from sdk.Microsoft_SQLServer_Visualization_Library_RelationshipType_Hierarchy
where Child in (select RelationshipTypeRowId from RemovedIds) or Parent in (select RelationshipTypeRowId from RemovedIds)
set @testRowCount = @@ROWCOUNT
COMMIT TRANSACTION
set @deadlockRetries = @maxDeadlockCount
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION
IF ERROR_NUMBER() = 1205 and @deadlockRetries > 0
SET @deadlockretries = @deadlockretries - 1
ELSE
EXEC [SDK].[Microsoft_SQLServer_Visualization_Library_RethrowError]
END CATCH
end;
DECLARE @updatedRTs TABLE (
BaseRelationshipTypeRowId [int] not NULL,
RelationshipTypeRowId [int] not null,
UNIQUE CLUSTERED (BaseRelationshipTypeRowId, RelationshipTypeRowId)
);
insert into @updatedRTs
select distinct rtmpv.BaseRelationshipTypeRowId, rtmpv.RelationshipTypeRowId
from dbo.RelationshipTypeManagementPackVersion rtmpv WITH (NOLOCK)
where rtmpv.ManagementPackVersionRowId in (select ManagementPackVersionRowId from @updatedMPVersions)
and rtmpv.BaseRelationshipTypeRowId is not null
order by BaseRelationshipTypeRowId, RelationshipTypeRowId;
-- insert new elements
set @firstRun = 1;
set @deadlockRetries = @maxDeadlockCount;
while (@firstRun = 1 or @testRowCount > 0)
begin
BEGIN TRY
BEGIN TRANSACTION
set @firstRun = 0;
insert top(@batchSize) into sdk.Microsoft_SQLServer_Visualization_Library_RelationshipType_Hierarchy
select
u1.BaseRelationshipTypeRowId as Parent,
u1.RelationshipTypeRowId as Child
from @updatedRTs u1
set @testRowCount = @@ROWCOUNT
COMMIT TRANSACTION
set @deadlockRetries = @maxDeadlockCount
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION
IF ERROR_NUMBER() = 1205 and @deadlockRetries > 0
SET @deadlockretries = @deadlockretries - 1
ELSE
EXEC [SDK].[Microsoft_SQLServer_Visualization_Library_RethrowError]
END CATCH
end;
--parents branch
set @firstRun = 1;
set @deadlockRetries = @maxDeadlockCount;
while (@firstRun = 1 or @testRowCount > 0)
begin
BEGIN TRY
BEGIN TRANSACTION
set @firstRun = 0;
;with insertable as (
select distinct
h.Parent as Parent,
u2.RelationshipTypeRowId as Child
from @updatedRTs u2
inner join sdk.Microsoft_SQLServer_Visualization_Library_RelationshipType_Hierarchy h on h.Child = u2.BaseRelationshipTypeRowId
where not exists (
select NULL
from sdk.Microsoft_SQLServer_Visualization_Library_RelationshipType_Hierarchy h2
where h.Parent = h2.Parent
and u2.RelationshipTypeRowId = h2.Child
)
)
insert top(@batchSize) into sdk.Microsoft_SQLServer_Visualization_Library_RelationshipType_Hierarchy
select i.Parent, i.Child from insertable i
set @testRowCount = @@ROWCOUNT;
COMMIT TRANSACTION
set @deadlockRetries = @maxDeadlockCount;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION
IF ERROR_NUMBER() = 1205 and @deadlockRetries > 0
SET @deadlockretries = @deadlockretries - 1
ELSE
EXEC [SDK].[Microsoft_SQLServer_Visualization_Library_RethrowError]
END CATCH
end;
--children branch
set @firstRun = 1;
set @deadlockRetries = @maxDeadlockCount;
while (@firstRun = 1 or @testRowCount > 0)
begin
BEGIN TRY
BEGIN TRANSACTION
set @firstRun = 0;
;with insertable as (
select distinct
u2.BaseRelationshipTypeRowId as Parent,
h.Child as Child
from @updatedRTs u2
inner join sdk.Microsoft_SQLServer_Visualization_Library_RelationshipType_Hierarchy h on u2.RelationshipTypeRowId = h.Parent
where not exists (
select NULL
from sdk.Microsoft_SQLServer_Visualization_Library_RelationshipType_Hierarchy h2
where u2.BaseRelationshipTypeRowId = h2.Parent
and h.Child = h2.Child
)
)
insert top(@batchSize) into sdk.Microsoft_SQLServer_Visualization_Library_RelationshipType_Hierarchy
select i.Parent, i.Child from insertable i
set @testRowCount = @@ROWCOUNT;
COMMIT TRANSACTION
set @deadlockRetries = @maxDeadlockCount;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION
IF ERROR_NUMBER() = 1205 and @deadlockRetries > 0
SET @deadlockretries = @deadlockretries - 1
ELSE
EXEC [SDK].[Microsoft_SQLServer_Visualization_Library_RethrowError]
END CATCH
end;
--children other parents branch
set @firstRun = 1;
set @deadlockRetries = @maxDeadlockCount;
while (@firstRun = 1 or @testRowCount > 0)
begin
BEGIN TRY
BEGIN TRANSACTION
set @firstRun = 0;
;with inserted as (
select
u2.BaseRelationshipTypeRowId as Parent,
h.Child as Child
from @updatedRTs u2
inner join sdk.Microsoft_SQLServer_Visualization_Library_RelationshipType_Hierarchy h on u2.RelationshipTypeRowId = h.Parent
),
insertable as (
select distinct
h.Parent as Parent,
u2.Child as Child
from inserted u2
inner join sdk.Microsoft_SQLServer_Visualization_Library_RelationshipType_Hierarchy h on h.Child = u2.Parent
where not exists (
select NULL
from sdk.Microsoft_SQLServer_Visualization_Library_RelationshipType_Hierarchy h2
where h.Parent = h2.Parent
and u2.Child = h2.Child
)
)
insert top(@batchSize) into sdk.Microsoft_SQLServer_Visualization_Library_RelationshipType_Hierarchy
select i.Parent, i.Child from insertable i
set @testRowCount = @@ROWCOUNT;
COMMIT TRANSACTION
set @deadlockRetries = @maxDeadlockCount;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION
IF ERROR_NUMBER() = 1205 and @deadlockRetries > 0
SET @deadlockretries = @deadlockretries - 1
ELSE
EXEC [SDK].[Microsoft_SQLServer_Visualization_Library_RethrowError]
END CATCH
end;
delete from @updatedRTs;
delete from @updatedMPVersions;
delete from @removedMPVersions;
update b
set FinishDate = GetUtcDate()
from sdk.Microsoft_SQLServer_Visualization_Library_Table_Batches b
inner join @CurrentBatch cb on b.BatchId = cb.BatchId;
delete from @CurrentBatch
END
-- Contained and hosted objects hierarchy
declare @groupTypeId bigint;
select TOP 1 @groupTypeId = met.ManagedEntityTypeRowId
from dbo.ManagedEntityType met
where met.ManagedEntityTypeSystemName = 'System.Group'
IF @profiling = 1
BEGIN
DECLARE @StartTime DateTime;
DECLARE @StartTimeSegment DateTime = getdate();
DECLARE @EndTimeSegment DateTime;
DECLARE @profilingdata TABLE (
name varchar(200),
length int
);
END
WHILE (1=1)
BEGIN
IF @profiling = 1
BEGIN
set @StartTime = getdate()
END
update b
set StartDate = GetUtcDate()
output inserted.BatchId, inserted.FirstId, inserted.LastId into @CurrentBatch
from sdk.Microsoft_SQLServer_Visualization_Library_Table_Batches b
where b.BatchId = (select top 1 b.BatchId from sdk.Microsoft_SQLServer_Visualization_Library_Table_Batches b
inner join sdk.Microsoft_SQLServer_Visualization_Library_Tables t on b.TableId = t.TableId
where b.StartDate is null and t.Type = 6
order by b.BatchId desc)
select top 1 @firstId = FirstId, @lastId = LastId from @CurrentBatch
set @batchTestRowCount = @@ROWCOUNT
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('Get batch from ' + LTRIM(Str(@firstId, 38, 0)) + ' to ' +LTRIM(Str(@lastId, 38, 0)), DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
-- locate and remove descendant references
set @firstRun = 1;
set @deadlockRetries = @maxDeadlockCount;
while (@firstRun = 1 or @testRowCount = @batchSize)
begin
BEGIN TRY
BEGIN TRANSACTION
-- SET STATISTICS PROFILE ON
set @firstRun = 0;
; with to_delete_rowIds as (
select distinct
rh.IsGroup,
rh.RelationshipManagementPackRowId
from sdk.Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy rh with (nolock)
inner join dbo.RelationshipManagementGroup rmg WITH (NOLOCK)
on rh.RelationshipManagementPackRowId = rmg.RelationshipManagementGroupRowId
where rmg.ToDateTime is not null
),
to_delete as (
select
rh.IsGroup,
rh.RelationshipManagementPackRowId,
rh.Parent,
rh.Child
from sdk.Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy rh with (nolock)
inner join to_delete_rowIds r on rh.IsGroup = r.IsGroup and rh.RelationshipManagementPackRowId = r.RelationshipManagementPackRowId
),
not_affected_links as (
select
h.IsGroup,
h.RelationshipManagementPackRowId,
h.Parent,
h.Child
from sdk.Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy h
inner join to_delete d on h.Parent = d.Parent and h.Child = d.Child
except
select * from to_delete
),
affected_links as (
select Parent, Child from to_delete
except
select Parent, Child from not_affected_links
),
affected_rowIds as (
select distinct h.IsGroup, h.RelationshipManagementPackRowId, l.Parent, h.Child
from affected_links l
inner join sdk.Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy h
on l.Child = h.Parent
)
delete top(@batchSize) h
from sdk.Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy h
inner join affected_rowIds r
on h.IsGroup = r.IsGroup
and h.RelationshipManagementPackRowId = r.RelationshipManagementPackRowId
and h.Parent = r.Parent
and h.Child = r.Child;
set @testRowCount = @@ROWCOUNT;
-- SET STATISTICS PROFILE OFF
COMMIT TRANSACTION
set @deadlockRetries = @maxDeadlockCount;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION
IF ERROR_NUMBER() = 1205 and @deadlockRetries > 0
SET @deadlockretries = @deadlockretries - 1
ELSE
EXEC [SDK].[Microsoft_SQLServer_Visualization_Library_RethrowError]
END CATCH
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('Remove '+LTRIM(Str(@testRowCount, 38, 0))+' descendants', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
end;
set @firstRun = 1;
set @deadlockRetries = @maxDeadlockCount;
while (@firstRun = 1 or @testRowCount = @batchSize)
begin
BEGIN TRY
BEGIN TRANSACTION
set @firstRun = 0;
; with to_delete as (
select distinct
rh.IsGroup,
rh.RelationshipManagementPackRowId
from sdk.Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy rh with (nolock)
inner join dbo.RelationshipManagementGroup rmg WITH (NOLOCK)
on rh.RelationshipManagementPackRowId = rmg.RelationshipManagementGroupRowId
where rmg.ToDateTime is not null
)
delete top(@batchSize) h from sdk.Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy h
inner join to_delete d
on d.IsGroup = h.IsGroup
and d.RelationshipManagementPackRowId = h.RelationshipManagementPackRowId;
set @testRowCount = @@ROWCOUNT;
COMMIT TRANSACTION
set @deadlockRetries = @maxDeadlockCount;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION
IF ERROR_NUMBER() = 1205 and @deadlockRetries > 0
SET @deadlockretries = @deadlockretries - 1
ELSE
EXEC [SDK].[Microsoft_SQLServer_Visualization_Library_RethrowError]
END CATCH
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('Remove '+LTRIM(Str(@testRowCount, 38, 0))+' rows', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
end;
IF @batchTestRowCount = 0
BREAK
DECLARE @insertedRelationships TABLE (
[IsGroup] [bit] NOT NULL,
[RelationshipManagementPackRowId] [int] NOT NULL,
[Parent] [int] NOT NULL,
[Child] [int] NOT NULL,
UNIQUE CLUSTERED ([IsGroup], [RelationshipManagementPackRowId], [Parent], [Child])
);
; with parentRT as (
SELECT TOP 1 rt.RelationshipTypeRowId
from dbo.vRelationshipType rt WITH (NOLOCK)
where rt.RelationshipTypeSystemName = 'System.Containment'
),
filteredRT as (
SELECT RelationshipTypeRowId
from parentRT
UNION ALL
SELECT rth.Child as RelationshipTypeRowId
from parentRT rt WITH (NOLOCK)
inner join sdk.Microsoft_SQLServer_Visualization_Library_RelationshipType_Hierarchy rth WITH (NOLOCK) on rt.RelationshipTypeRowId = rth.Parent
),
groupTypeIDs as (
select h.ManagedEntityTypeRowId as typeRowId
from dbo.ManagedEntityDerivedTypeHierarchy(@groupTypeId, 0) h
)
insert into @insertedRelationships
select distinct
CASE WHEN g.TypeRowId is not null THEN 1 ELSE 0 END as IsGroup,
rmg.RelationshipManagementGroupRowId as RelationshipManagementGroupRowId,
r.SourceManagedEntityRowId as Parent,
r.TargetManagedEntityRowId as Child
from dbo.RelationshipManagementGroup rmg WITH (NOLOCK)
inner join dbo.Relationship r WITH (NOLOCK) on rmg.RelationshipRowId = r.RelationshipRowId
inner join dbo.ManagedEntity me WITH (NOLOCK) on r.SourceManagedEntityRowId = me.ManagedEntityRowId
left join groupTypeIDs g on me.ManagedEntityTypeRowId = g.TypeRowId
where rmg.RelationshipManagementGroupRowId between @firstId and @lastId
and rmg.ToDateTime is null
and EXISTS (SELECT NULL from filteredRT rt where rt.RelationshipTypeRowId = r.RelationshipTypeRowId)
order by IsGroup, RelationshipManagementGroupRowId, Parent, Child;
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('Get new rows', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
set @firstRun = 1;
set @deadlockRetries = @maxDeadlockCount;
while (@firstRun = 1 or @testRowCount > 0)
begin
BEGIN TRY
BEGIN TRANSACTION
set @firstRun = 0;
insert top(@batchSize) into sdk.Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy
select
u1.IsGroup as IsGroup,
u1.RelationshipManagementPackRowId as RelationshipManagementPackRowId,
u1.Parent as Parent,
u1.Child as Child
from @insertedRelationships u1
set @testRowCount = @@ROWCOUNT;
COMMIT TRANSACTION
set @deadlockRetries = @maxDeadlockCount;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION
IF ERROR_NUMBER() = 1205 and @deadlockRetries > 0
SET @deadlockretries = @deadlockretries - 1
ELSE
EXEC [SDK].[Microsoft_SQLServer_Visualization_Library_RethrowError]
END CATCH
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('Insert '+LTRIM(Str(@testRowCount, 38, 0))+' rows', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
end;
--parents branch
set @firstRun = 1;
set @deadlockRetries = @maxDeadlockCount;
while (@firstRun = 1 or @testRowCount > 0)
begin
BEGIN TRY
BEGIN TRANSACTION
set @firstRun = 0;
;with insertable as (
select distinct
u2.IsGroup,
u2.RelationshipManagementPackRowId as RelationshipManagementPackRowId,
h.Parent as Parent,
u2.Child as Child
from @insertedRelationships u2
inner join sdk.Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy h on h.Child = u2.Parent and h.IsGroup = u2.IsGroup
where not exists (
select NULL
from sdk.Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy h2
where u2.IsGroup = h2.IsGroup
and u2.RelationshipManagementPackRowId = h2.RelationshipManagementPackRowId
and h.Parent = h2.Parent
and u2.Child = h2.Child
)
)
insert top(@batchSize) into sdk.Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy
select i.IsGroup, i.RelationshipManagementPackRowId, i.Parent, i.Child from insertable i
set @testRowCount = @@ROWCOUNT;
COMMIT TRANSACTION
set @deadlockRetries = @maxDeadlockCount;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION
IF ERROR_NUMBER() = 1205 and @deadlockRetries > 0
SET @deadlockretries = @deadlockretries - 1
ELSE
EXEC [SDK].[Microsoft_SQLServer_Visualization_Library_RethrowError]
END CATCH
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('Insert '+LTRIM(Str(@testRowCount, 38, 0))+' parents', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
end;
--children branch
set @firstRun = 1;
set @deadlockRetries = @maxDeadlockCount;
while (@firstRun = 1 or @testRowCount > 0)
begin
BEGIN TRY
BEGIN TRANSACTION
set @firstRun = 0;
;with insertable as (
select distinct
h.IsGroup,
h.RelationshipManagementPackRowId as RelationshipManagementPackRowId,
u2.Parent as Parent,
h.Child as Child
from @insertedRelationships u2
inner join sdk.Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy h on u2.Child = h.Parent and u2.IsGroup = h.IsGroup
where not exists (
select NULL
from sdk.Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy h2
where h.IsGroup = h2.IsGroup
and h.RelationshipManagementPackRowId = h2.RelationshipManagementPackRowId
and u2.Parent = h2.Parent
and h.Child = h2.Child
)
)
insert top(@batchSize) into sdk.Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy
select i.IsGroup, i.RelationshipManagementPackRowId, i.Parent, i.Child from insertable i
set @testRowCount = @@ROWCOUNT;
COMMIT TRANSACTION
set @deadlockRetries = @maxDeadlockCount;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION
IF ERROR_NUMBER() = 1205 and @deadlockRetries > 0
SET @deadlockretries = @deadlockretries - 1
ELSE
EXEC [SDK].[Microsoft_SQLServer_Visualization_Library_RethrowError]
END CATCH
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('Insert '+LTRIM(Str(@testRowCount, 38, 0))+' children', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
end;
--children other parents branch
set @firstRun = 1;
set @deadlockRetries = @maxDeadlockCount;
while (@firstRun = 1 or @testRowCount > 0)
begin
BEGIN TRY
BEGIN TRANSACTION
set @firstRun = 0;
;with inserted as (
select
h.IsGroup,
h.RelationshipManagementPackRowId as RelationshipManagementPackRowId,
u2.Parent as Parent,
h.Child as Child
from @insertedRelationships u2
inner join sdk.Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy h on u2.Child = h.Parent and u2.IsGroup = h.IsGroup
),
insertable as (
select distinct
u2.IsGroup,
u2.RelationshipManagementPackRowId as RelationshipManagementPackRowId,
h.Parent as Parent,
u2.Child as Child
from inserted u2
inner join sdk.Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy h on h.Child = u2.Parent and h.IsGroup = u2.IsGroup
where not exists (
select NULL
from sdk.Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy h2
where u2.IsGroup = h2.IsGroup
and u2.RelationshipManagementPackRowId = h2.RelationshipManagementPackRowId
and h.Parent = h2.Parent
and u2.Child = h2.Child
)
)
insert top(@batchSize) into sdk.Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy
select i.IsGroup, i.RelationshipManagementPackRowId, i.Parent, i.Child from insertable i
set @testRowCount = @@ROWCOUNT;
COMMIT TRANSACTION
set @deadlockRetries = @maxDeadlockCount;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION
IF ERROR_NUMBER() = 1205 and @deadlockRetries > 0
SET @deadlockretries = @deadlockretries - 1
ELSE
EXEC [SDK].[Microsoft_SQLServer_Visualization_Library_RethrowError]
END CATCH
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('Insert '+LTRIM(Str(@testRowCount, 38, 0))+' other parents of children', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
end;
delete from @insertedRelationships;
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('Clean @insertedRelationships', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
set @StartTimeSegment = getdate()
END
update b
set FinishDate = GetUtcDate()
from sdk.Microsoft_SQLServer_Visualization_Library_Table_Batches b
inner join @CurrentBatch cb on b.BatchId = cb.BatchId;
delete from @CurrentBatch;
IF @profiling = 1
BEGIN
set @EndTimeSegment = getdate()
insert into @profilingdata values ('Update batch', DATEDIFF(MILLISECOND, @StartTimeSegment, @EndTimeSegment))
DECLARE @EndTime DateTime = getdate();
insert into @profilingdata values ('Total time', DATEDIFF(MILLISECOND, @StartTime, @EndTime))
select * from @profilingdata
delete from @profilingdata
END
END
END
GO
GRANT EXECUTE ON [SDK].[Microsoft_SQLServer_Visualization_Library_UpdateHierarchy] TO OpsMgrReader
GO
-- Create the stored procedure to generate an error using
-- RAISERROR. The original error information is used to
-- construct the msg_str for RAISERROR.
ALTER PROCEDURE [SDK].[Microsoft_SQLServer_Visualization_Library_RethrowError] AS
BEGIN
-- Return if there is no error information to retrieve.
IF ERROR_NUMBER() IS NULL
RETURN;
-- Assign variables to error-handling functions that
-- capture information for RAISERROR.
SELECT
@ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorLine = ERROR_LINE(),
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
-- Build the message string that will contain original
-- error information.
SELECT @ErrorMessage =
N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +
'Message: '+ ERROR_MESSAGE();
-- Raise an error: msg_str parameter of RAISERROR will contain
-- the original error information.
RAISERROR
(
@ErrorMessage,
@ErrorSeverity,
1,
@ErrorNumber, -- parameter: original error number.
@ErrorSeverity, -- parameter: original error severity.
@ErrorState, -- parameter: original error state.
@ErrorProcedure, -- parameter: original error procedure name.
@ErrorLine -- parameter: original error line number.
);
END
GO
GRANT EXECUTE ON [SDK].[Microsoft_SQLServer_Visualization_Library_RethrowError] TO OpsMgrReader
GO
Home
