IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ComtradeMPXAXD_SiteAvailability_DataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[ComtradeMPXAXD_SiteAvailability_DataGet] AS RETURN 1')
END
GO
-- get sites
create table #Sites (Site_RowId int, Site_Name nvarchar(max) COLLATE database_default)
insert into #Sites
Select meSite.ManagedEntityRowId Site_RowId, meSite.DisplayName Site_Name
from vManagedEntity meSite
inner join vManagedEntityManagementGroup mg on mg.ManagedEntityRowId = meSite.ManagedEntityRowId and mg.ToDateTime is null
inner join vManagedEntityType metSite on metSite.ManagedEntityTypeRowId = meSite.ManagedEntityTypeRowId AND metSite.ManagedEntityTypeSystemName = 'ComTrade.Citrix.XenDesktop.Site' AND meSite.ManagedEntityRowId IN (select * from #ManagedEntitySite)
inner join vManagementPack mp on metSite.ManagementPackRowId = mp.ManagementPackRowId and mp.ManagementPackSystemName IN ('Comtrade.Citrix.XenApp.And.XenDesktop')
-- get sites' related hypervisor connections
create table #Connections (Connection_RowId int, Connection_Name nvarchar(max) COLLATE database_default, Site_RowId int, Site_Name nvarchar(max) COLLATE database_default)
insert into #Connections
Select meConnection.ManagedEntityRowId Connection_RowId, meConnection.DisplayName Connection_Name,
meSite.ManagedEntityRowId Site_RowId, meSite.DisplayName Site_Name
from vManagedEntity meSite
inner join vManagedEntityManagementGroup mg on mg.ManagedEntityRowId = meSite.ManagedEntityRowId and mg.ToDateTime is null
inner join vManagedEntityType metSite on metSite.ManagedEntityTypeRowId = meSite.ManagedEntityTypeRowId AND metSite.ManagedEntityTypeSystemName = 'ComTrade.Citrix.XenDesktop.Site' AND meSite.ManagedEntityRowId IN (select * from #ManagedEntitySite)
inner join vRelationship re1 on re1.SourceManagedEntityRowId = meSite.ManagedEntityRowId
inner join vRelationshipType ret1 on ret1.RelationshipTypeRowId = re1.RelationshipTypeRowId AND ret1.RelationshipTypeSystemName = 'ComTrade.Citrix.XenDesktop.Site.Contains.SiteProxy'
inner join vManagedEntity me1 on me1.ManagedEntityRowId = re1.TargetManagedEntityRowId
inner join vManagedEntityType met1 on met1.ManagedEntityTypeRowId = me1.ManagedEntityTypeRowId AND met1.ManagedEntityTypeSystemName = 'ComTrade.Citrix.XenDesktop.SiteDataCollector'
inner join vRelationship re2 on re2.SourceManagedEntityRowId = me1.ManagedEntityRowId
inner join vRelationshipType ret2 on ret2.RelationshipTypeRowId = re2.RelationshipTypeRowId AND ret2.RelationshipTypeSystemName = 'ComTrade.Citrix.XenDesktop.SiteDataCollector.Hosts.HostConnectionContainer'
inner join vManagedEntity me2 on me2.ManagedEntityRowId = re2.TargetManagedEntityRowId
inner join vManagedEntityType met2 on met2.ManagedEntityTypeRowId = me2.ManagedEntityTypeRowId AND met2.ManagedEntityTypeSystemName = 'ComTrade.Citrix.XenDesktop.HostConnections.Container'
inner join vRelationship re3 on re3.SourceManagedEntityRowId = me2.ManagedEntityRowId
inner join vRelationshipType ret3 on ret3.RelationshipTypeRowId = re3.RelationshipTypeRowId AND ret3.RelationshipTypeSystemName = 'ComTrade.Citrix.XenDesktop.HostConnectionContainer.Hosts.HostConnection'
inner join vManagedEntity meConnection on meConnection.ManagedEntityRowId = re3.TargetManagedEntityRowId
inner join vManagedEntityType metConnection on metConnection.ManagedEntityTypeRowId = meConnection.ManagedEntityTypeRowId AND metConnection.ManagedEntityTypeSystemName IN ('ComTrade.Citrix.XenDesktop.HostConnection')
inner join vManagementPack mp on metConnection.ManagementPackRowId = mp.ManagementPackRowId and mp.ManagementPackSystemName IN ('Comtrade.Citrix.XenApp.And.XenDesktop')
-- get zone's related hypervisor connections (for XAXD 7.7 and MPXAXD 3.14)
create table #ConnectionsZone (Connection_RowId int, Connection_Name nvarchar(max) COLLATE database_default, Site_RowId int, Site_Name nvarchar(max) COLLATE database_default)
insert into #ConnectionsZone
Select meConnection.ManagedEntityRowId Connection_RowId, meConnection.DisplayName Connection_Name,
meSite.ManagedEntityRowId Site_RowId, meSite.DisplayName Site_Name
from vManagedEntity meSite
inner join vManagedEntityManagementGroup mg on mg.ManagedEntityRowId = meSite.ManagedEntityRowId and mg.ToDateTime is null
inner join vManagedEntityType metSite on metSite.ManagedEntityTypeRowId = meSite.ManagedEntityTypeRowId AND metSite.ManagedEntityTypeSystemName = 'ComTrade.Citrix.XenDesktop.Site' AND meSite.ManagedEntityRowId IN (select * from #ManagedEntitySite)
inner join vRelationship re1 on re1.SourceManagedEntityRowId = meSite.ManagedEntityRowId
inner join vRelationshipType ret1 on ret1.RelationshipTypeRowId = re1.RelationshipTypeRowId AND ret1.RelationshipTypeSystemName = 'Citrix.XenDesktop.Site.Contains.ZonesContainer'
inner join vManagedEntity me1 on me1.ManagedEntityRowId = re1.TargetManagedEntityRowId
inner join vManagedEntityType met1 on met1.ManagedEntityTypeRowId = me1.ManagedEntityTypeRowId AND met1.ManagedEntityTypeSystemName = 'Citrix.XenDesktop.Zones.Container'
inner join vRelationship re2 on re2.SourceManagedEntityRowId = me1.ManagedEntityRowId
inner join vRelationshipType ret2 on ret2.RelationshipTypeRowId = re2.RelationshipTypeRowId AND ret2.RelationshipTypeSystemName = 'Citrix.XenDesktop.ZonesContainer.Contains.Zone'
inner join vManagedEntity me2 on me2.ManagedEntityRowId = re2.TargetManagedEntityRowId
inner join vManagedEntityType met2 on met2.ManagedEntityTypeRowId = me2.ManagedEntityTypeRowId AND met2.ManagedEntityTypeSystemName like 'Citrix.XenDesktop.Zone.%'
inner join vRelationship re3 on re3.SourceManagedEntityRowId = me2.ManagedEntityRowId
inner join vRelationshipType ret3 on ret3.RelationshipTypeRowId = re3.RelationshipTypeRowId AND ret3.RelationshipTypeSystemName = 'Citrix.XenDesktop.Zone.Contains.HostConnection.Container'
inner join vManagedEntity me3 on me3.ManagedEntityRowId = re3.TargetManagedEntityRowId
inner join vManagedEntityType met3 on met3.ManagedEntityTypeRowId = me3.ManagedEntityTypeRowId AND met3.ManagedEntityTypeSystemName = 'Citrix.XenDesktop.Zone.HypervisorConnection.Container'
inner join vRelationship re4 on re4.SourceManagedEntityRowId = me3.ManagedEntityRowId
inner join vRelationshipType ret4 on ret4.RelationshipTypeRowId = re4.RelationshipTypeRowId AND ret4.RelationshipTypeSystemName = 'Citrix.XenDesktop.Zone.HostConnection.Container.Contains.HostConnection'
inner join vManagedEntity meConnection on meConnection.ManagedEntityRowId = re4.TargetManagedEntityRowId
inner join vManagedEntityType metConnection on metConnection.ManagedEntityTypeRowId = meConnection.ManagedEntityTypeRowId AND metConnection.ManagedEntityTypeSystemName IN ('Citrix.XenDesktop.HostConnection.Zone')
inner join vManagementPack mp on metConnection.ManagementPackRowId = mp.ManagementPackRowId and mp.ManagementPackSystemName IN ('Comtrade.Citrix.XenApp.And.XenDesktop')
--get all monitor instance IDs (for sites, services and connections)
--monitor types: 1-site availability, 2-services, 3-hypervisor connections
CREATE TABLE #ManagedEntityMonitors (MEMonitorRowId int, MESiteRowId int, MonitorType int, SiteName nvarchar(max) COLLATE database_default, ObjectDisplayName nvarchar(max) COLLATE database_default)
INSERT INTO #ManagedEntityMonitors
SELECT VMeMon.ManagedEntityMonitorRowId ,MES.Site_RowId, case when VMon.MonitorSystemName = @SiteMonitorSystemName then 1 else 2 end, MES.Site_Name,
case when VMon.MonitorSystemName = @SiteMonitorSystemName then MES.Site_Name else SS.ServiceName end
FROM vMonitor as VMon INNER JOIN
vManagedEntityMonitor as VMeMon ON VMon.MonitorRowId = VMeMon.MonitorRowId INNER JOIN
#Sites as MES ON MES.Site_RowId = VMeMon.ManagedEntityRowId INNER JOIN
vManagedEntity as MESRV ON MES.Site_RowId = MESRV.ManagedEntityRowId INNER JOIN
@SiteServicesAndAggregate as SS ON SS.MonitorId = VMon.MonitorSystemName
INSERT INTO #ManagedEntityMonitors
SELECT VMeMon.ManagedEntityMonitorRowId ,MES.Site_RowId, 3, MES.Site_Name, MES.Connection_Name
FROM vMonitor as VMon
INNER JOIN vManagedEntityMonitor as VMeMon ON VMon.MonitorRowId = VMeMon.MonitorRowId
INNER JOIN #Connections as MES ON MES.Connection_RowId = VMeMon.ManagedEntityRowId
INNER JOIN vManagedEntity as MESRV ON MES.Connection_RowId = MESRV.ManagedEntityRowId
WHERE VMon.MonitorSystemName IN (@HypervisorConnectionMonitorSystemName, @ZoneHypervisorConnectionMonitorSystemName)
-- Add new Zone Hypervisor connection
INSERT INTO #ManagedEntityMonitors
SELECT VMeMon.ManagedEntityMonitorRowId ,MES.Site_RowId, 3, MES.Site_Name, MES.Connection_Name
FROM vMonitor as VMon
INNER JOIN vManagedEntityMonitor as VMeMon ON VMon.MonitorRowId = VMeMon.MonitorRowId
INNER JOIN #ConnectionsZone as MES ON MES.Connection_RowId = VMeMon.ManagedEntityRowId
INNER JOIN vManagedEntity as MESRV ON MES.Connection_RowId = MESRV.ManagedEntityRowId
WHERE VMon.MonitorSystemName IN (@HypervisorConnectionMonitorSystemName, @ZoneHypervisorConnectionMonitorSystemName)
CREATE TABLE #ManagedEntityMonitorsFinal (MEMonitorRowId int, MESiteRowId int, MonitorType int, SiteName nvarchar(max) COLLATE database_default, ObjectDisplayName nvarchar(max) COLLATE database_default)
INSERT INTO #ManagedEntityMonitorsFinal
select max(vMon.MEMonitorRowId), vMon.MESiteRowId, vMon.MonitorType, vMon.SiteName, vMon.ObjectDisplayName
from #ManagedEntityMonitors vMon
group by vMon.MESiteRowId, vMon.ObjectDisplayName, vMon.MonitorType, vMon.SiteName
INSERT INTO #StateTable
SELECT DATEADD(minute, @GMTDiff, vState.DateTime) as "DateTime", vState.IntervalDurationMilliseconds, vState.InWhiteStateMilliseconds,
vState.InGreenStateMilliseconds, vState.InYellowStateMilliseconds,
vState.InRedStateMilliseconds, vState.InDisabledStateMilliseconds,
vState.InPlannedMaintenanceMilliseconds, vState.InUnplannedMaintenanceMilliseconds,
vState.HealthServiceUnavailableMilliseconds,
MEMS.MEMonitorRowId, MEMS.MESiteRowId, MEMS.MonitorType, MEMS.SiteName, MEMS.ObjectDisplayName
FROM vStateHourlyFull as vState INNER JOIN
#ManagedEntityMonitorsFinal as MEMS ON MEMS.MEMonitorRowId = vState.ManagedEntityMonitorRowId
WHERE
(vState.DateTime >= DATEADD(hh, DATEPART(hh, @StartDate_BaseValue_UTC), convert(varchar(8), @StartDate_BaseValue_UTC, 112))) AND
(vState.DateTime < DATEADD(hh, DATEPART(hh, @EndDate_BaseValue_UTC), convert(varchar(8), @EndDate_BaseValue_UTC, 112))) AND
(vState.Date BETWEEN DATEADD(day, -1, @StartDate_BaseValue_UTC) AND DATEADD(day, 1, @EndDate_BaseValue_UTC))
and vState.[DateTime] - CAST(FLOOR(CAST(vState.[DateTime] AS float)) AS datetime) >= DATEADD(minute, -@GMTDiff, @BH_start) --start hours
and vState.[DateTime] - CAST(FLOOR(CAST(vState.[DateTime] AS float)) AS datetime) < DATEADD(minute, -@GMTDiff, @BH_end) --end hours
and ( (DATEPART(WEEKDAY, DATEADD(minute, @GMTDiff, vState.[DateTime])) = 1 and @BusinessDay1 = 1) --monday
OR (DATEPART(WEEKDAY, DATEADD(minute, @GMTDiff, vState.[DateTime])) = 2 and @BusinessDay2 = 1) --tuesday
OR (DATEPART(WEEKDAY, DATEADD(minute, @GMTDiff, vState.[DateTime])) = 3 and @BusinessDay3 = 1) --wednesday
OR (DATEPART(WEEKDAY, DATEADD(minute, @GMTDiff, vState.[DateTime])) = 4 and @BusinessDay4 = 1) --thursday
OR (DATEPART(WEEKDAY, DATEADD(minute, @GMTDiff, vState.[DateTime])) = 5 and @BusinessDay5 = 1) --friday
OR (DATEPART(WEEKDAY, DATEADD(minute, @GMTDiff, vState.[DateTime])) = 6 and @BusinessDay6 = 1) --saturday
OR (DATEPART(WEEKDAY, DATEADD(minute, @GMTDiff, vState.[DateTime])) = 7 and @BusinessDay7 = 1)) --sunday
AND --SAMO V ODSEKIH
vState.DateTime BETWEEN DATEADD(minute, -@GMTDiff, @StartDate_BaseValue) AND DATEADD(minute, -@GMTDiff, @EndDate_BaseValue)
END
ELSE
BEGIN
INSERT INTO #StateTable
SELECT DATEADD(minute, @GMTDiff, vState.DateTime) as "DateTime", vState.IntervalDurationMilliseconds, vState.InWhiteStateMilliseconds,
vState.InGreenStateMilliseconds, vState.InYellowStateMilliseconds,
vState.InRedStateMilliseconds, vState.InDisabledStateMilliseconds,
vState.InPlannedMaintenanceMilliseconds, vState.InUnplannedMaintenanceMilliseconds,
vState.HealthServiceUnavailableMilliseconds,
MEMS.MEMonitorRowId, MEMS.MESiteRowId, MEMS.MonitorType, MEMS.SiteName, MEMS.ObjectDisplayName
FROM vStateHourlyFull as vState INNER JOIN
#ManagedEntityMonitorsFinal as MEMS ON MEMS.MEMonitorRowId = vState.ManagedEntityMonitorRowId
WHERE
(vState.DateTime >= DATEADD(hh, DATEPART(hh, @StartDate_BaseValue_UTC), convert(varchar(8), @StartDate_BaseValue_UTC, 112))) AND
(vState.DateTime < DATEADD(hh, DATEPART(hh, @EndDate_BaseValue_UTC), convert(varchar(8), @EndDate_BaseValue_UTC, 112))) AND
(vState.Date BETWEEN DATEADD(day, -1, @StartDate_BaseValue_UTC) AND DATEADD(day, 1, @EndDate_BaseValue_UTC))
AND --SAMO V ODSEKIH
vState.DateTime BETWEEN DATEADD(minute, -@GMTDiff, @StartDate_BaseValue) AND DATEADD(minute, -@GMTDiff, @EndDate_BaseValue)
-- DATA AGGREGATION
IF (@AggregationType = 0 OR @AggregationType = -1) --HOURLY or RAW
BEGIN
INSERT INTO #StateTableFinal
SELECT A."DateTime", A.IntervalDurationMilliseconds, A.InWhiteStateMilliseconds, A.InGreenStateMilliseconds, A.InYellowStateMilliseconds, A.InRedStateMilliseconds,
A.InDisabledStateMilliseconds, A.InPlannedMaintenanceMilliseconds, A.InUnplannedMaintenanceMilliseconds, A.HealthServiceUnavailableMilliseconds,
A.MEMonitorRowId, A.MESiteRowId, A.MonitorType, A.SiteName, A.ObjectDisplayName
FROM #StateTable A
END
IF @AggregationType = 1 --DAILY
BEGIN
INSERT INTO #StateTableFinal
SELECT convert(datetime,CONVERT(varchar,DATEPART(month, A.[DateTime]))+'/'+CONVERT(varchar,DATEPART(day, A.[DateTime]))+'/'+CONVERT(varchar,DATEPART(yyyy, A.[DateTime]))),
SUM(cast (A.IntervalDurationMilliseconds as bigint)), SUM(cast (A.InWhiteStateMilliseconds as bigint)), SUM(cast (A.InGreenStateMilliseconds as bigint)), SUM(cast (A.InYellowStateMilliseconds as bigint)), SUM(cast (A.InRedStateMilliseconds as bigint)),
SUM(cast (A.InDisabledStateMilliseconds as bigint)), SUM(cast (A.InPlannedMaintenanceMilliseconds as bigint)), SUM(cast (A.InUnplannedMaintenanceMilliseconds as bigint)), SUM(cast (A.HealthServiceUnavailableMilliseconds as bigint)),
A.MEMonitorRowId, A.MESiteRowId, A.MonitorType, A.SiteName, A.ObjectDisplayName
FROM #StateTable A
GROUP BY DATEPART(yyyy, A.[DateTime]), DATEPART(month, A.[DateTime]), DATEPART(day, A.[DateTime]),
A.MEMonitorRowId, A.MESiteRowId, A.MonitorType, A.SiteName, A.ObjectDisplayName
END
IF @AggregationType = 2 --MONTHLY
BEGIN
INSERT INTO #StateTableFinal
SELECT convert(datetime,CONVERT(varchar,DATEPART(month, A.[DateTime]))+'/1/'+CONVERT(varchar,DATEPART(yyyy, A.[DateTime]))),
SUM(cast (A.IntervalDurationMilliseconds as bigint)), SUM(cast (A.InWhiteStateMilliseconds as bigint)), SUM(cast (A.InGreenStateMilliseconds as bigint)), SUM(cast (A.InYellowStateMilliseconds as bigint)), SUM(cast (A.InRedStateMilliseconds as bigint)),
SUM(cast (A.InDisabledStateMilliseconds as bigint)), SUM(cast (A.InPlannedMaintenanceMilliseconds as bigint)), SUM(cast (A.InUnplannedMaintenanceMilliseconds as bigint)), SUM(cast (A.HealthServiceUnavailableMilliseconds as bigint)),
A.MEMonitorRowId, A.MESiteRowId, A.MonitorType, A.SiteName, A.ObjectDisplayName
FROM #StateTable A
GROUP BY DATEPART(yyyy, A.[DateTime]), DATEPART(month, A.[DateTime]),
A.MEMonitorRowId, A.MESiteRowId, A.MonitorType, A.SiteName, A.ObjectDisplayName
END
IF @AggregationType = 3 --YEARLY
BEGIN
INSERT INTO #StateTableFinal
SELECT convert(datetime,'1/1/'+CONVERT(varchar,DATEPART(yyyy, A.[DateTime]))),
SUM(cast (A.IntervalDurationMilliseconds as bigint)), SUM(cast (A.InWhiteStateMilliseconds as bigint)), SUM(cast (A.InGreenStateMilliseconds as bigint)), SUM(cast (A.InYellowStateMilliseconds as bigint)), SUM(cast (A.InRedStateMilliseconds as bigint)),
SUM(cast (A.InDisabledStateMilliseconds as bigint)), SUM(cast (A.InPlannedMaintenanceMilliseconds as bigint)), SUM(cast (A.InUnplannedMaintenanceMilliseconds as bigint)), SUM(cast (A.HealthServiceUnavailableMilliseconds as bigint)),
A.MEMonitorRowId, A.MESiteRowId, A.MonitorType, A.SiteName, A.ObjectDisplayName
FROM #StateTable A
GROUP BY DATEPART(yyyy, A.[DateTime]),
A.MEMonitorRowId, A.MESiteRowId, A.MonitorType, A.SiteName, A.ObjectDisplayName
END
select * from #StateTableFinal s
order by s.DateTime asc
DROP TABLE #ManagedEntitySite
DROP TABLE #Sites
DROP TABLE #Connections
DROP TABLE #StateTable
DROP TABLE #StateTableFinal
DROP TABLE #ManagedEntityMonitors
DROP TABLE #ManagedEntityMonitorsFinal
DROP TABLE #ConnectionsZone
Home