IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[prc_Microsoft_Windows_BranchCache_SMBCSC__ComputePerfCounterValue]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[prc_Microsoft_Windows_BranchCache_SMBCSC__ComputePerfCounterValue]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[prc_Microsoft_Windows_BranchCache_SMBCSC_ComputePerfCounterDataSet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[prc_Microsoft_Windows_BranchCache_SMBCSC_ComputePerfCounterDataSet]
GO
--DROP TABLE #BranchCachePerfSummary
CREATE TABLE #BranchCachePerfSummary(
[DateTime] [datetime] NOT NULL,
[Path] [nvarchar] (max),
[CounterName] [nvarchar](max) NOT NULL,
[CounterValue] [bigint] NOT NULL
)
--DROP TABLE #BranchCacheTempPerfData
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)
MAX(vPerf.Maxvalue) - MIN(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 #BranchCacheSMBCounterList as vBranchCacheSMBCounterList
ON vPerformanceRule.CounterName = vBranchCacheSMBCounterList.CounterName
WHERE vPerf.ManagedEntityRowId = @managedentity_rowid
and vPerf.DateTime >= @start_time
and vPerf.DateTime < @end_time
and (vPerformanceRule.ObjectName IN ('Client Side Caching'))
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-OfflineFiles/Operational'
and Pub.EventPublisherName = 'Microsoft-Windows-OfflineFiles'
and Evt.EventNumber = 1
and Evt.DateTime > @start_time
and Evt.DateTime < @end_time
GROUP BY Evt.DateTime
ORDER BY Evt.DateTime ASC
-- Checkpoint 02
-- Display the events within the time range (without the cursor)
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)
MAX(vPerf.Maxvalue) - MIN(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 #BranchCacheSMBCounterList as vBranchCacheSMBCounterList
ON vPerformanceRule.CounterName = vBranchCacheSMBCounterList.CounterName
WHERE vPerf.ManagedEntityRowId = @managedentity_rowid
and vPerf.DateTime >= @prev_time
and vPerf.DateTime < @next_time
and (vPerformanceRule.ObjectName IN ('Client Side Caching'))
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
-- Checkpoint 03 Display the result counter values within each period
--SELECT * FROM #BranchCachePerfSummary ORDER BY CounterName
-- Bypass the part of getting the highest value from the previous 7 days
-- Not needed as the script is getting the difference for every period
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
-- Checkpoint 04 Sum up the counter values & set negative numbers to 0
--SELECT * FROM #BranchCacheSummary
FETCH NEXT FROM CounterList_Cursor INTO @protocol_name, @counter_name
-- The order of #BranchCacheSMBCounterList table
-- 'SMB CSC', 'Application Bytes Read From Cache')
-- 'SMB CSC', 'Application Bytes Read From Server')
-- 'SMB CSC', 'Application Bytes Read From Server (Not Cached)')
-- 'SMB CSC', 'SMB BranchCache Hash Bytes Received')
-- 'SMB CSC', 'SMB BranchCache Bytes Requested From Server')
-- 'SMB CSC', 'SMB BranchCache Bytes Received')
WHILE @@FETCH_STATUS = 0
BEGIN
-- Get the first one
SET @path = (SELECT Path FROM #BranchCacheSummary WHERE CounterName = @counter_name)
SET @app_read_from_cache = (SELECT CounterValue FROM #BranchCacheSummary WHERE CounterName = @counter_name)
-- Get the second one
FETCH NEXT FROM CounterList_Cursor INTO @protocol_name, @counter_name
IF @@FETCH_STATUS != 0
BREAK
SET @app_read_from_server = (SELECT CounterValue FROM #BranchCacheSummary WHERE CounterName = @counter_name)
-- Get the third one
FETCH NEXT FROM CounterList_Cursor INTO @protocol_name, @counter_name
IF @@FETCH_STATUS != 0
BREAK
SET @app_read_from_server_nc = (SELECT CounterValue FROM #BranchCacheSummary WHERE CounterName = @counter_name)
-- Get the forth one
FETCH NEXT FROM CounterList_Cursor INTO @protocol_name, @counter_name
IF @@FETCH_STATUS != 0
BREAK
SET @smb_bc_hash_received = (SELECT CounterValue FROM #BranchCacheSummary WHERE CounterName = @counter_name)
-- Get the fifth one
FETCH NEXT FROM CounterList_Cursor INTO @protocol_name, @counter_name
IF @@FETCH_STATUS != 0
BREAK
SET @smb_bc_req_from_server = (SELECT CounterValue FROM #BranchCacheSummary WHERE CounterName = @counter_name)
-- Get the sixth one
FETCH NEXT FROM CounterList_Cursor INTO @protocol_name, @counter_name
IF @@FETCH_STATUS != 0
BREAK
SET @smb_bc_bytes_received = (SELECT CounterValue FROM #BranchCacheSummary WHERE CounterName = @counter_name)
FETCH NEXT FROM CounterList_Cursor INTO @protocol_name, @counter_name
IF @path IS NULL OR @app_read_from_cache IS NULL OR @app_read_from_server IS NULL OR @app_read_from_server_nc IS NULL OR @smb_bc_hash_received IS NULL OR @smb_bc_req_from_server IS NULL OR @smb_bc_bytes_received IS NULL
BEGIN
CONTINUE
END
SET @bandwidth_saving = 0
SET @bytes_from_cache = 0
SET @total_needed = @app_read_from_cache + @app_read_from_server
SET @bytes_from_server = @app_read_from_server + @smb_bc_hash_received + @smb_bc_req_from_server
IF @total_needed > 0 AND @total_needed >= @bytes_from_server
BEGIN
SET @bytes_from_cache = @total_needed - @bytes_from_server
SET @bandwidth_saving = CONVERT(float,@bytes_from_cache*100) / CONVERT(float,@total_needed)
END
INSERT INTO #BranchCacheSMBDataSet
VALUES (@path
,@protocol_name
,@bytes_from_cache
,@bytes_from_server
,@bandwidth_saving
)
END
CLOSE CounterList_Cursor;
DEALLOCATE CounterList_Cursor;
SELECT * FROM #BranchCacheSMBDataSet
GO
CREATE PROCEDURE [dbo].[prc_Microsoft_Windows_BranchCache_SMBCSC_ComputePerfCounterDataSet] (
@StartDate_BaseValue datetime,
@EndDate_BaseValue datetime,
@ObjectList xml)
AS
DECLARE @ExecError int
DECLARE @managedentity_rowid int
DECLARE @temp_managedentity_rowid int
DECLARE @ignoreMachineRestart int
SET @temp_managedentity_rowid = 0
SET @ignoreMachineRestart = 1
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')
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_SMBCSC__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