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
/
25506
< prev
next >
Wrap
Text File
|
2010-06-15
|
2KB
|
54 lines
CREATE PROCEDURE [dbo].[ISA_spMalwareSitesRDL]
@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),
Incidents bigint
)
IF OBJECT_ID (@SummTableName, 'u') IS NOT NULL
BEGIN
DECLARE @strQuery varchar(8000)
SET @strQuery =
'SELECT
[Site],
SUM([MalwareIncidents]) AS Incidents
FROM ' + @SummTableName + '
WHERE ([date] >= ' + QUOTENAME(CONVERT(varchar, @FromDate, 126),CHAR(39)) + ' AND [date] <= ' + QUOTENAME(CONVERT(varchar, @ToDate, 126),CHAR(39)) + ')
GROUP BY [Site]
ORDER BY Incidents DESC
'
INSERT #Table1
EXEC(@strQuery)
END
DECLARE @TotalIncidents bigint
SET @TotalIncidents = (SELECT SUM([Incidents]) FROM #Table1)
IF (@TotalIncidents = 0)
SET @TotalIncidents = 1
SELECT TOP(@TopCount)
Site,
Incidents,
(CAST([Incidents] AS decimal) / @TotalIncidents) AS IncidentRatio
FROM #Table1
WHERE ([Incidents] > 0) AND
[Site] != N'{[23124]}'
UNION ALL
SELECT
N'{[23124]}' As Site,
SUM([Incidents]) AS Incidents,
(CAST(SUM([Incidents]) AS decimal) / @TotalIncidents) AS IncidentRatio
FROM #Table1 t1
WHERE NOT EXISTS
(SELECT * FROM
(SELECT TOP(@TopCount) *
FROM #Table1
WHERE [Site] != N'{[23124]}') t2
WHERE t1.Site = t2.Site)
END