home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
moodle.waes.ac.uk
/
moodle.waes.ac.uk.zip
/
moodle.waes.ac.uk
/
TMG
/
SP1-TMG-KB981324-AMD64-ENU.msp
/
PCW_CAB_SHFx2
/
F2143_msfpcui.dll
/
BINARY
/
25582
< prev
next >
Wrap
Text File
|
2010-06-15
|
3KB
|
78 lines
CREATE PROCEDURE [dbo].[ISA_spIpsTopProtocolsRDL]
@FromDate datetime,
@ToDate datetime,
@ReportType varchar(10),
@TopCount int,
@SortOrder varchar(100)
AS
BEGIN
DECLARE @SummTableName varchar(100)
SET @SummTableName = dbo.fnGetSummaryTableName('tblIpsSummary', @FromDate, @ToDate, @ReportType)
DECLARE @OrderColumn varchar(100)
SET @OrderColumn =
CASE
WHEN @SortOrder = 'Requests' THEN 'Requests'
ELSE 'TotalBytes'
END;
CREATE TABLE #Table1
(
[SignatureProtocol] nvarchar(4000),
[Requests] bigint,
[TotalBytes] bigint
)
IF OBJECT_ID (@SummTableName, 'u') IS NOT NULL
BEGIN
DECLARE @strQuery varchar(8000)
SET @strQuery =
'SELECT
[SignatureProtocol] AS [SignatureProtocol],
SUM([Requests]) AS [Requests],
SUM([TotalBytes]) AS [TotalBytes]
FROM
(SELECT
o.[SignatureName] AS [SignatureName],
oo.[SignatureProtocol] AS [SignatureProtocol],
o.[Incidents] AS [Requests],
o.[TotalBytes] AS [TotalBytes]
FROM ' + @SummTableName + ' AS o
LEFT JOIN [tblIpsMetadata] AS oo
ON o.[SignatureName] = oo.[SignatureName]
WHERE
([Date] >= ' + QUOTENAME(CONVERT(varchar, @FromDate, 126),CHAR(39)) + ' AND [Date] <= ' + QUOTENAME(CONVERT(varchar, @ToDate, 126),CHAR(39)) + ')) as tbl
GROUP BY
[SignatureProtocol]
ORDER BY ' + QUOTENAME(@OrderColumn) + ' DESC;
'
INSERT #Table1
EXEC(@strQuery)
END
DECLARE @TotalBytes bigint
SET @TotalBytes = (SELECT SUM([TotalBytes]) FROM #Table1)
IF (@TotalBytes = 0)
SET @TotalBytes = 1
DECLARE @TotalIncidents bigint
SET @TotalIncidents = (SELECT SUM([Requests]) FROM #Table1)
IF (@TotalIncidents = 0)
SET @TotalIncidents = 1
SELECT TOP(@TopCount)
[SignatureProtocol] AS [SignatureProtocol],
[TotalBytes] AS [TotalBytes],
(CAST([TotalBytes] AS decimal) / @TotalBytes) AS [TotalBytesRatio],
[Requests] AS [Requests],
(CAST([Requests] AS decimal) / @TotalIncidents) AS [IncidentRatio]
FROM #Table1
UNION ALL
SELECT
N'{[23124]}' As [SignatureProtocol],
SUM([TotalBytes]) AS [TotalBytes],
(CAST(SUM([TotalBytes]) AS decimal) / @TotalBytes) AS [TotalBytesRatio],
SUM([Requests]) AS [Requests],
(CAST(SUM([Requests]) AS decimal) / @TotalIncidents) AS [IncidentRatio]
FROM #Table1 t1
WHERE NOT EXISTS
(SELECT * FROM
(SELECT TOP(@TopCount) *
FROM #Table1) t2
WHERE t1.[SignatureProtocol] = t2.[SignatureProtocol])
END