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
/
25514
< prev
next >
Wrap
Text File
|
2010-06-15
|
2KB
|
71 lines
CREATE PROCEDURE [dbo].[ISA_spMalwareSitesDurationRDL]
@FromDate datetime,
@ToDate datetime,
@ReportType varchar(10),
@TopCount int
AS
BEGIN
DECLARE @SummTableName varchar(100)
SET @SummTableName = dbo.fnGetSummaryTableName('tblSiteSummary', @FromDate, @ToDate, @ReportType)
CREATE TABLE #Table1
(
Site varchar(2048),
AverageMalwareInspectionDuration bigint,
Requests bigint
)
CREATE TABLE #Table2
(
Site varchar(2048),
AverageMalwareInspectionDuration bigint,
Requests bigint
)
IF OBJECT_ID (@SummTableName, 'u') IS NOT NULL
BEGIN
DECLARE @strQuery varchar(8000)
SET @strQuery =
'SELECT
[Site] AS Site,
SUM([MalwareInspectionDuration]) AS AverageMalwareInspectionDuration,
SUM([Requests]) AS Requests
FROM ' + @SummTableName + '
WHERE ([date] >= ' + QUOTENAME(CONVERT(varchar, @FromDate, 126),CHAR(39)) + ' AND [date] <= ' + QUOTENAME(CONVERT(varchar, @ToDate, 126),CHAR(39)) + ')
AND ([MalwareInspectionDuration] > 0)
GROUP BY [Site]
'
INSERT #Table2
EXEC(@strQuery)
END
INSERT INTO #Table1
SELECT *
FROM #Table2
WHERE [Requests] > 0
ORDER BY CAST([AverageMalwareInspectionDuration] AS decimal)/[Requests] DESC
DECLARE @TotalRequests bigint
SET @TotalRequests = (SELECT SUM([Requests]) FROM #Table1)
IF (@TotalRequests = 0)
SET @TotalRequests = 1
SELECT TOP(@TopCount)
Site,
CAST([AverageMalwareInspectionDuration] AS decimal)/[Requests] AS InspectionDuration,
[Requests] AS Requests,
(CAST([Requests] AS decimal) / @TotalRequests) AS RequestRatio
FROM #Table1
WHERE [Site] != N'{[23124]}'
UNION ALL
SELECT
N'{[23124]}' As Site,
CASE
WHEN (SUM(Requests) > 0) THEN CAST(SUM([AverageMalwareInspectionDuration]) AS decimal)/SUM([Requests])
ELSE 0
END AS InspectionDuration,
SUM([Requests]),
(CAST(SUM([Requests]) AS decimal) / @TotalRequests) AS RequestRatio
FROM #Table1 t1
WHERE NOT EXISTS
(SELECT * FROM
(SELECT TOP(@TopCount) *
FROM #Table1
WHERE [Site] != N'{[23124]}') t2
WHERE t1.Site = t2.Site)
END