IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[prc_Microsoft_Windows_BranchCache_ComputePerfCounterDataSet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[prc_Microsoft_Windows_BranchCache_ComputePerfCounterDataSet]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[prc_Microsoft_Windows_BranchCache_ComputePerfCounterValue]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[prc_Microsoft_Windows_BranchCache_ComputePerfCounterValue]
GO
INSERT INTO #BranchCacheClientCounterList VALUES ('SMB', 'SMB: Bytes from cache')
INSERT INTO #BranchCacheClientCounterList VALUES ('SMB', 'SMB: Bytes from server')
INSERT INTO #BranchCacheClientCounterList VALUES ('BITS', 'BITS: Bytes from cache')
INSERT INTO #BranchCacheClientCounterList VALUES ('BITS', 'BITS: Bytes from server')
INSERT INTO #BranchCacheClientCounterList VALUES ('HTTP', 'WININET: Bytes from cache')
INSERT INTO #BranchCacheClientCounterList VALUES ('HTTP', 'WININET: Bytes from server')
INSERT INTO #BranchCacheClientCounterList VALUES ('HTTP', 'WINHTTP: Bytes from cache')
INSERT INTO #BranchCacheClientCounterList VALUES ('HTTP', 'WINHTTP: Bytes from server')
INSERT INTO #BranchCacheClientCounterList VALUES ('Other', 'Other: Bytes from cache')
INSERT INTO #BranchCacheClientCounterList VALUES ('Other', 'Other: Bytes from server')
DECLARE CounterList_Cursor CURSOR FOR
SELECT ProtocolName, CounterName FROM #BranchCacheClientCounterList
--DROP TABLE #BranchCachePerfSummary
CREATE TABLE #BranchCachePerfSummary(
[DateTime] [datetime] NOT NULL,
[Path] [nvarchar] (max),
[CounterName] [nvarchar](max) NOT NULL,
[CounterValue] [bigint] NOT NULL
)
CREATE TABLE #BranchCacheTempPerfData(
[DateTime] [datetime] NOT NULL,
[Path] [nvarchar] (max),
[CounterName] [nvarchar](max) NOT NULL,
[CounterValue] [bigint] NOT NULL
)
IF @ignoreMachineRestart = 1
BEGIN
INSERT INTO #BranchCachePerfSummary
([DateTime]
,[Path]
,[CounterName]
,[CounterValue]
)
SELECT
MAX(vPerf.DateTime),
vManagedEntity.Path,
vPerformanceRule.CounterName,
MAX(vPerf.Maxvalue)
FROM [OperationsManagerDW].[Perf].[vPerfHourly] AS vPerf
INNER JOIN [OperationsManagerDW].[dbo].PerformanceRuleInstance as vPerformanceRuleInstance
ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId
INNER JOIN [OperationsManagerDW].[dbo].PerformanceRule as vPerformanceRule
ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId
INNER JOIN [OperationsManagerDW].[dbo].ManagedEntity as vManagedEntity
ON vPerf.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId
INNER JOIN #BranchCacheClientCounterList as vBranchCacheClientCounterList
ON vPerformanceRule.CounterName = vBranchCacheClientCounterList.CounterName
WHERE vPerf.ManagedEntityRowId = @managedentity_rowid
and vPerf.DateTime >= @start_time
and vPerf.DateTime < @end_time
and (vPerformanceRule.ObjectName IN ('BranchCache'))
GROUP BY vManagedEntity.Path, vPerformanceRule.CounterName
END
ELSE
BEGIN
DECLARE Event_Cursor CURSOR FOR
SELECT
MAX(Evt.DateTime)
FROM Event.vEvent(NoLock) Evt
Inner Join EventPublisher(NoLock) Pub
On Evt.EventPublisherRowId = Pub.EventPublisherRowId
Inner Join EventChannel(NoLock) Chl
On Evt.EventChannelRowId = Chl.EventChannelRowId
Inner Join EventLoggingComputer(NoLock) Cmp
On Evt.LoggingComputerRowId = Cmp.EventLoggingComputerRowId
Inner Join ManagedEntity(NoLock) as vManagedEntity
On vManagedEntity.path LIKE Cmp.ComputerName
WHERE vManagedEntity.ManagedEntityRowId = @managedentity_rowid
and Chl.EventChannelTitle = 'Microsoft-Windows-BranchCache/Operational'
and Pub.EventPublisherName = 'Microsoft-Windows-BranchCache'
and Evt.EventNumber = 1
and Evt.DateTime > @start_time
and Evt.DateTime < @end_time
GROUP BY Evt.DateTime
ORDER BY Evt.DateTime ASC
OPEN Event_Cursor;
FETCH NEXT FROM Event_Cursor INTO @next_time;
DECLARE @compute_once int
SET @compute_once = 1
SET @prev_time = @start_time
WHILE @@FETCH_STATUS = 0 OR @prev_time < @end_time
BEGIN
IF @@FETCH_STATUS <> 0
BEGIN
IF @compute_once = 0
BREAK
SET @compute_once = 0
SET @next_time = @end_time
END
INSERT INTO #BranchCachePerfSummary
([DateTime]
,[Path]
,[CounterName]
,[CounterValue]
)
SELECT
MAX(vPerf.DateTime),
vManagedEntity.Path,
vPerformanceRule.CounterName,
MAX(vPerf.Maxvalue)
FROM [OperationsManagerDW].[Perf].[vPerfHourly] AS vPerf
INNER JOIN [OperationsManagerDW].[dbo].PerformanceRuleInstance as vPerformanceRuleInstance
ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId
INNER JOIN [OperationsManagerDW].[dbo].PerformanceRule as vPerformanceRule
ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId
INNER JOIN [OperationsManagerDW].[dbo].ManagedEntity as vManagedEntity
ON vPerf.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId
INNER JOIN #BranchCacheClientCounterList as vBranchCacheClientCounterList
ON vPerformanceRule.CounterName = vBranchCacheClientCounterList.CounterName
WHERE vPerf.ManagedEntityRowId = @managedentity_rowid
and vPerf.DateTime >= @prev_time
and vPerf.DateTime < @next_time
and (vPerformanceRule.ObjectName IN ('BranchCache'))
GROUP BY vManagedEntity.Path, vPerformanceRule.CounterName
SET @prev_time = @next_time
FETCH NEXT FROM Event_Cursor INTO @next_time;
END
CLOSE Event_Cursor;
DEALLOCATE Event_Cursor;
END
SET @old_time = DATEADD(DAY, -7, @start_time)
INSERT INTO #BranchCacheTempPerfData
([DateTime]
,[Path]
,[CounterName]
,[CounterValue]
)
SELECT
vPerf.DateTime,
vManagedEntity.Path,
vPerformanceRule.CounterName,
vPerf.Maxvalue
FROM [OperationsManagerDW].[Perf].[vPerfHourly] AS vPerf
INNER JOIN [OperationsManagerDW].[dbo].PerformanceRuleInstance as vPerformanceRuleInstance
ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId
INNER JOIN [OperationsManagerDW].[dbo].PerformanceRule as vPerformanceRule
ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId
INNER JOIN [OperationsManagerDW].[dbo].ManagedEntity as vManagedEntity
ON vPerf.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId
INNER JOIN #BranchCacheClientCounterList as vBranchCacheClientCounterList
ON vPerformanceRule.CounterName = vBranchCacheClientCounterList.CounterName
WHERE vPerf.ManagedEntityRowId = @managedentity_rowid
and vPerf.DateTime >= @old_time
and vPerf.DateTime < @start_time
and (vPerformanceRule.ObjectName IN ('BranchCache'))
IF @@ROWCOUNT > 0
BEGIN
OPEN CounterList_Cursor;
FETCH NEXT FROM CounterList_Cursor INTO @protocol_name, @counter_name;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #BranchCachePerfSummary
([DateTime]
,[Path]
,[CounterName]
,[CounterValue]
)
SELECT TOP 1
DateTime,
Path,
CounterName,
-CounterValue
FROM #BranchCacheTempPerfData AS vPerfData
WHERE (vPerfData.CounterName IN (@counter_name))
ORDER BY vPerfData.DateTime DESC
FETCH NEXT FROM CounterList_Cursor INTO @protocol_name, @counter_name;
END
CLOSE CounterList_Cursor;
END
INSERT INTO #BranchCacheSummary
SELECT MAX(DateTime) as DateTime, Path, CounterName, SUM(CounterValue) AS CounterValue FROM #BranchCachePerfSummary GROUP BY Path, CounterName
UPDATE #BranchCacheSummary SET CounterValue = 0 WHERE CounterValue < 0
FETCH NEXT FROM CounterList_Cursor INTO @protocol_name, @counter_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @path = (SELECT Path FROM #BranchCacheSummary WHERE CounterName = @counter_name)
SET @bytes_fromcache = (SELECT CounterValue FROM #BranchCacheSummary WHERE CounterName = @counter_name)
SET @current_protocol_name = @protocol_name
FETCH NEXT FROM CounterList_Cursor INTO @protocol_name, @counter_name
IF @@FETCH_STATUS != 0 OR @current_protocol_name != @protocol_name
BREAK
SET @bytes_fromserver = (SELECT CounterValue FROM #BranchCacheSummary WHERE CounterName = @counter_name)
FETCH NEXT FROM CounterList_Cursor INTO @protocol_name, @counter_name
IF @path IS NULL OR @bytes_fromcache IS NULL OR @bytes_fromserver IS NULL
BEGIN
CONTINUE
END
SET @bandwidth_saving = 0
IF (@bytes_fromcache + @bytes_fromserver) > 0
BEGIN
SET @bandwidth_saving = CONVERT(float,(@bytes_fromcache * 100)) / CONVERT(float, (@bytes_fromcache + @bytes_fromserver))
END
INSERT INTO #BranchCachePerfDataSet VALUES (@path, @current_protocol_name, @bytes_fromcache, @bytes_fromserver, @bandwidth_saving)
END
CLOSE CounterList_Cursor;
DEALLOCATE CounterList_Cursor;
SELECT * FROM #BranchCachePerfDataSet
GO
CREATE PROCEDURE [dbo].[prc_Microsoft_Windows_BranchCache_ComputePerfCounterDataSet] (
@StartDate_BaseValue datetime,
@EndDate_BaseValue datetime,
@ObjectList xml,
@HostedCacheOnly int)
AS
DECLARE @ExecError int
DECLARE @managedentity_rowid int
DECLARE @temp_managedentity_rowid int
DECLARE @ignoreMachineRestart int
SET @temp_managedentity_rowid = 0
SET @ignoreMachineRestart = 1
IF @HostedCacheOnly = 0
BEGIN
DECLARE ManagedEntity_Cursor CURSOR FOR
SELECT vManagedEntity.ManagedEntityRowId
FROM [OperationsManagerDW].[dbo].ManagedEntity as vManagedEntity
INNER JOIN #BranchCacheManagedEntity as vManagedEntityParam
ON vManagedEntity.ManagedEntityRowId = vManagedEntityParam.ManagedEntityRowId
INNER JOIN [OperationsManagerDW].[dbo].ManagedEntityType as vManagedEntityType
ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityType.ManagedEntityTypeRowId
WHERE vManagedEntityType.ManagedEntityTypeSystemName IN ('Microsoft.Windows.BranchCache.DistributedCacheClientNode',
'Microsoft.Windows.BranchCache.HostedCacheClientNode')
END
ELSE
BEGIN
DECLARE ManagedEntity_Cursor CURSOR FOR
SELECT vManagedEntity.ManagedEntityRowId
FROM [OperationsManagerDW].[dbo].ManagedEntity as vManagedEntity
INNER JOIN #BranchCacheManagedEntity as vManagedEntityParam
ON vManagedEntity.ManagedEntityRowId = vManagedEntityParam.ManagedEntityRowId
INNER JOIN [OperationsManagerDW].[dbo].ManagedEntityType as vManagedEntityType
ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityType.ManagedEntityTypeRowId
WHERE vManagedEntityType.ManagedEntityTypeSystemName IN ('Microsoft.Windows.BranchCache.HostedCacheServerNode')
END
OPEN ManagedEntity_Cursor;
FETCH NEXT FROM ManagedEntity_Cursor INTO @managedentity_rowid;
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @temp_managedentity_rowid = vManagedEntity.ManagedEntityRowId
FROM [OperationsManagerDW].[dbo].ManagedEntity as vManagedEntity
INNER JOIN [OperationsManagerDW].[dbo].ManagedEntityType as vManagedEntityType
ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityType.ManagedEntityTypeRowId
WHERE vManagedEntityType.ManagedEntityTypeSystemName IN ('Microsoft.Windows.BranchCache.Windows7',
'Microsoft.Windows.BranchCache.Windows7Server')
AND vManagedEntity.Path = (
SELECT vManagedEntity.Path
FROM [OperationsManagerDW].[dbo].ManagedEntity as vManagedEntity
WHERE vManagedEntity.ManagedEntityRowId = @managedentity_rowid
)
IF @temp_managedentity_rowid <> 0
SET @ignoreMachineRestart = 0
INSERT INTO #BranchCachePerfDataSet
EXECUTE [dbo].[prc_Microsoft_Windows_BranchCache_ComputePerfCounterValue] @start_time = @StartDate_BaseValue, @end_time = @EndDate_BaseValue, @managedentity_rowid = @managedentity_rowid, @ignoreMachineRestart = @ignoreMachineRestart
FETCH NEXT FROM ManagedEntity_Cursor INTO @managedentity_rowid;
SET @temp_managedentity_rowid = 0
SET @ignoreMachineRestart = 1
END
CLOSE ManagedEntity_Cursor;
DEALLOCATE ManagedEntity_Cursor;
SET @bytes_fromcache = (SELECT SUM(BytesFromCacheCounterValue) FROM #BranchCachePerfDataSet)
SET @bytes_fromserver = (SELECT SUM(BytesFromServerCounterValue) FROM #BranchCachePerfDataSet)
IF @bytes_fromcache = 0 AND @bytes_fromserver = 0
BEGIN
DELETE FROM #BranchCachePerfDataSet
END
Home