Res.Microsoft.Windows.BranchCache.Report.IISBandwidthUsagePerfDataGet.Install (Resource)

Element properties:

Source Code:

<Resource ID="Res.Microsoft.Windows.BranchCache.Report.IISBandwidthUsagePerfDataGet.Install" Accessibility="Public" FileName="Microsoft.Windows.BranchCache.Report.IISBandwidthUsagePerfDataGet.Install.sql" HasNullStream="false"/>

File Content: Microsoft.Windows.BranchCache.Report.IISBandwidthUsagePerfDataGet.Install.sql

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[prc_Microsoft_Windows_BranchCache_HTTP_ComputePerfCounterValue]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)


DROP PROCEDURE [dbo].[prc_Microsoft_Windows_BranchCache_HTTP_ComputePerfCounterValue]


GO





IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[prc_Microsoft_Windows_BranchCache_HTTP_ComputePerfCounterDataSet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)


DROP PROCEDURE [dbo].[prc_Microsoft_Windows_BranchCache_HTTP_ComputePerfCounterDataSet]


GO





CREATE PROCEDURE [dbo].[prc_Microsoft_Windows_BranchCache_HTTP_ComputePerfCounterValue] (


  @start_time datetime,


  @end_time datetime,


  @managedentity_rowid int,


  @ignoreMachineRestart int)


AS





DECLARE @ExecError int


DECLARE @old_time DateTime


DECLARE @prev_time DateTime


DECLARE @next_time DateTime


DECLARE @protocol_name as char(256)


DECLARE @counter_name as char(256)





--DROP TABLE #BranchCacheIISCounterList


CREATE TABLE #BranchCacheIISCounterList(


    [ProtocolName] char(256),


    [CounterName] char(256)


)





INSERT INTO #BranchCacheIISCounterList VALUES ('HTTP.sys', 'Server Cache Miss Bytes')


INSERT INTO #BranchCacheIISCounterList VALUES ('HTTP.sys', 'Client Cache Miss Bytes')


INSERT INTO #BranchCacheIISCounterList VALUES ('HTTP.sys', 'Hash Bytes')


INSERT INTO #BranchCacheIISCounterList VALUES ('HTTP.sys', 'Projected Server Bytes Without Caching')





--SELECT *


--FROM #BranchCacheIISCounterList





DECLARE CounterList_Cursor CURSOR FOR


SELECT ProtocolName, CounterName FROM #BranchCacheIISCounterList





--DROP TABLE #BranchCacheSummary


CREATE TABLE #BranchCacheSummary(


    [DateTime] [datetime],


    [Path] [nvarchar] (max),


    [CounterName] [nvarchar](max),


    [CounterValue] [bigint]


)





--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)


			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 #BranchCacheIISCounterList as vBranchCacheIISCounterList


				ON vPerformanceRule.CounterName = vBranchCacheIISCounterList.CounterName


			  WHERE vPerf.ManagedEntityRowId = @managedentity_rowid


				and vPerf.DateTime >= @start_time 


				and vPerf.DateTime < @end_time


				and (vPerformanceRule.ObjectName IN ('BranchCache Kernel Mode'))


			  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 #BranchCacheIISCounterList as vBranchCacheIISCounterList


			ON vPerformanceRule.CounterName = vBranchCacheIISCounterList.CounterName


		  WHERE vPerf.ManagedEntityRowId = @managedentity_rowid


			and vPerf.DateTime >= @prev_time 


			and vPerf.DateTime < @next_time


			and (vPerformanceRule.ObjectName IN ('BranchCache Kernel Mode'))


		  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


--SELECT * FROM #BranchCachePerfSummary





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 #BranchCacheIISCounterList as vBranchCacheIISCounterList


    ON vPerformanceRule.CounterName = vBranchCacheIISCounterList.CounterName


  WHERE vPerf.ManagedEntityRowId = @managedentity_rowid


    and vPerf.DateTime >= @old_time 


    and vPerf.DateTime < @start_time


    and (vPerformanceRule.ObjectName IN ('BranchCache Kernel Mode'))





--SELECT * FROM #BranchCacheTempPerfData





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


--DEALLOCATE CounterList_Cursor;


--SELECT * FROM #BranchCachePerfSummary





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





--DROP TABLE #BranchCachePerfDataSet


CREATE TABLE #BranchCachePerfDataSet(


  [Path] [nvarchar] (max),


  [ProtocolName] [nvarchar](max),


  [BytesFromCacheCounterValue] [bigint],


  [BytesFromServerCounterValue] [bigint],


  [BandwidthSaving] [float]


)





-- =======================================


-- Formula for computing bandwidth saving:


--


-- Bandwidth Saving %     = Bytes_From_Cache / Total_Bytes


-- 


--   Bytes_From_Cache     = Total_Content_Size - [BranchCache_Overhead + Missing_Content]


--   Total_Content_Size   = 'Projected Server Bytes Without Branch Caching'


--   BranchCache_Overhead = 'Hash Bytes'


--   Missing_Content_Size = 'Server Cache Miss Bytes' + 'Client Cache Miss Bytes'


-- =======================================





DECLARE @path                 nvarchar(max)


DECLARE @content_bytes        bigint


DECLARE @hash_bytes           bigint


DECLARE @server_missing_bytes bigint


DECLARE @client_missing_bytes bigint


DECLARE @bandwidth_saving     float


DECLARE @bytes_from_server    bigint


DECLARE @bytes_from_cache     bigint





OPEN CounterList_Cursor;





FETCH NEXT FROM CounterList_Cursor INTO @protocol_name, @counter_name





-- The order of #BranchCacheIISCounterList table


--   'HTTP.sys', 'Server Cache Miss Bytes'


--   'HTTP.sys', 'Client Cache Miss Bytes'


--   'HTTP.sys', 'Hash Bytes'


--   'HTTP.sys', 'Projected Server Bytes Without Caching'





WHILE @@FETCH_STATUS = 0


  BEGIN


    -- Get the first one


    SET @path = (SELECT Path FROM #BranchCacheSummary WHERE CounterName = @counter_name)


    SET @server_missing_bytes = (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 @client_missing_bytes = (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 @hash_bytes = (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 @content_bytes = (SELECT CounterValue FROM #BranchCacheSummary WHERE CounterName = @counter_name)





    FETCH NEXT FROM CounterList_Cursor INTO @protocol_name, @counter_name





    IF @path IS NULL OR @server_missing_bytes IS NULL OR @client_missing_bytes IS NULL OR @hash_bytes IS NULL OR @content_bytes IS NULL


    BEGIN


        CONTINUE


    END    





    SET @bandwidth_saving = 0


    SET @bytes_from_server = @server_missing_bytes + @client_missing_bytes + @hash_bytes


    SET @bytes_from_cache = 0





    IF @content_bytes > 0 AND @content_bytes >= @bytes_from_server


    BEGIN


      SET @bytes_from_cache = @content_bytes - @bytes_from_server


      SET @bandwidth_saving = CONVERT(float,(@bytes_from_cache *100)) / CONVERT(float,@content_bytes)


    END





    INSERT INTO #BranchCachePerfDataSet


      VALUES (@path


             ,@protocol_name


             ,@bytes_from_cache


             ,@bytes_from_server


             ,@bandwidth_saving


             )


  END





CLOSE CounterList_Cursor;


DEALLOCATE CounterList_Cursor;





SELECT * FROM #BranchCachePerfDataSet





GO





CREATE PROCEDURE [dbo].[prc_Microsoft_Windows_BranchCache_HTTP_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





--DROP TABLE #BranchCachePerfDataSet


CREATE TABLE #BranchCachePerfDataSet(


    [Path] [nvarchar] (max),


    [ProtocolName] [nvarchar](max),


    [BytesFromCacheCounterValue] [bigint],


    [BytesFromServerCounterValue] [bigint],


    [BandwidthSaving] [float]


)





-- DROP TABLE #BranchCacheManagedEntity


CREATE TABLE #BranchCacheManagedEntity (ManagedEntityRowId int)





INSERT INTO #BranchCacheManagedEntity


EXECUTE @ExecError = [Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportObjectListParse]


@ObjectList = @ObjectList,


@StartDate = @StartDate_BaseValue,


@EndDate = @EndDate_BaseValue





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.IISNode')





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.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_HTTP_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;





DECLARE @bytes_fromcache bigint


DECLARE @bytes_fromserver bigint





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





SELECT * FROM #BranchCachePerfDataSet





GO