It’s the security officers responsibility to overview the firm’s infrastructure risk exposure and trends in real time. Unfortunately, none of the industry leading security products has a feature to create a consolidated risk report that can help the top security officers to review and keep track with risk events.
As I’ve great exposure into SEP DB schema, I’ve developed a SQL query that generates a consolidated report in a high level format classifying the risk events into below categories:
[code language=”sql”]
if user name matches *admin*, report it as "Admin account access"
if user name matches "system", report it as "SYSTEM account access"
if file name matches "unavailable", report it as "unavailable"
if file name matches "*Program Files*|*C:/Winnt*", report it as "System Folders"
if file name matches "*Temporary Internet Files*|*Mozilla/Firefox/Cache*", report it as "Web browsing"
if file name matches "*[HNP]:*", report it as "Network drive"
if file name matches "*D:*", report it as "Optical removable drive"
if file name matches "*[^CDA]:*", report it as "USB removable drive"
if file name matches "*Local Settings/Temp*", report it as "User Profile Temp Folder"
if file name matches "*Documents and Settings*", report it as "Local User Profile"
if file name matches "*C:*", report it as "Local drive"
if NONE of the above conditions exists, report it as "-". This possibly indicates a scenario that isn’t covered above and should be checked further.
[/code]
SQL Stored procedure SEP_generate_monthly_reports:
[code language=”sql”]
CREATE PROC SEP_generate_monthly_reports
@SITE_GUID VARCHAR(100),
@NUM_OF_MONTHS INT
AS
BEGIN
SET NOCOUNT ON
—
SELECT
MAX(Convert(varchar(16), DATEADD(minute,DATEDIFF(minute,GETUTCDATE(),
GETDATE()),ALERTS.ALERTDATETIME), 120)) AS alertdatetime,
ACTUALACTION.ACTUALACTION AS Action_description,
ALERTS.ACTUALACTION_IDX AS action_id,
UPPER(SEM_COMPUTER.COMPUTER_NAME) as ComputerName,
VIRUS.VIRUSNAME,
SUM(ALERTS.NoOfViruses) AS ‘risk_count’,
Infection_method =
CASE
WHEN (SEM_CLIENT.USER_NAME LIKE N’%-admin%’ OR
ALERTS.FILEPATH LIKE N’%-admin%’)
THEN ‘Admin account access’
WHEN SEM_CLIENT.USER_NAME LIKE N’%system%’
THEN ‘SYSTEM account access’
WHEN ALERTS.FILEPATH LIKE N’%unavailable%’
THEN ‘Unavailable’
WHEN (ALERTS.FILEPATH LIKE N’%Program Files%’ OR
ALERTS.FILEPATH LIKE N’%C:\/Winnt%’)
THEN ‘System Folders’
WHEN (ALERTS.FILEPATH LIKE N’%Temporary Internet Files%’ OR
ALERTS.FILEPATH LIKE N’%Mozilla\/Firefox\/Cache%’)
THEN ‘Web browsing’
WHEN ALERTS.FILEPATH LIKE N’%[HNP]:%’
THEN ‘Network drive’
WHEN ALERTS.FILEPATH LIKE N’%D:%’
THEN ‘Optical removable drive’
WHEN ALERTS.FILEPATH LIKE N’%[^CDA]:%’
THEN ‘USB removable drive’
WHEN ALERTS.FILEPATH LIKE N’%Local Settings\/Temp%’
THEN ‘User Profile Temp Folder’
WHEN ALERTS.FILEPATH LIKE N’%Documents and Settings%’
THEN ‘Local User Profile’
WHEN ALERTS.FILEPATH LIKE N’%C:%’
THEN ‘Local drive’
ELSE ‘-‘
END
INTO
#tmp1
FROM
ACTUALACTION, ALERTS, SEM_COMPUTER, VIRUS, SEM_CLIENT
WHERE
(ALERTS.ALERTDATETIME > DATEADD(month, -@NUM_OF_MONTHS, getUTCdate()) AND
ALERTS.SITE_IDX = @SITE_GUID AND
ACTUALACTION.ACTUALACTION_IDX = ALERTS.ACTUALACTION_IDX AND
SEM_COMPUTER.COMPUTER_ID = ALERTS.COMPUTER_IDX AND
VIRUS.VIRUSNAME_IDX = ALERTS.VIRUSNAME_IDX AND
SEM_CLIENT.USER_NAME = ALERTS.USER_NAME)
GROUP BY
SEM_COMPUTER.COMPUTER_NAME,
SEM_CLIENT.USER_NAME,
VIRUS.VIRUSNAME,
ALERTS.FILEPATH,
ACTUALACTION.ACTUALACTION,
ALERTS.ACTUALACTION_IDX
ORDER BY
Action_description,
alertdatetime DESC,
SEM_COMPUTER.COMPUTER_NAME,
risk_count DESC
—
SELECT
alertdatetime,
action_id,
action_description,
computername,
virusname,
infection_method,
SUM(risk_count) AS rc,
SUBSTRING(alertdatetime,1,13) AS cnt
INTO
#tmp2
FROM
#tmp1
GROUP BY
alertdatetime,
action_description,
computername,
virusname,
infection_method,
action_id
ORDER BY
action_description,
alertdatetime DESC,
computername,
rc DESC
—
SELECT
MIN(alertdatetime) AS alert_datetime,
action_id,
action_description,
computername,
virusname,
infection_method,
SUM(rc) AS rc
INTO
#tmp3
FROM
#tmp2
GROUP BY
cnt,
action_description,
computername,
virusname,
infection_method,
action_id
ORDER BY
alert_datetime DESC,
action_description DESC,
computername,
rc DESC
—
SELECT
alert_datetime,
action_description,
computername,
virusname,
rc,
risk_severity =
CASE
WHEN (#tmp3.action_id NOT IN (1,3,5,6,19,200) AND rc IN (1,2))
THEN ‘Low’
WHEN (#tmp3.action_id NOT IN (1,3,5,6,19,200) AND rc IN (3,4,5))
THEN ‘Medium’
WHEN (#tmp3.action_id NOT IN (1,3,5,6,19,200) AND rc > 5)
THEN ‘High’
WHEN (#tmp3.action_id IN (1,3,5,6,19,200) AND rc <= 50 )
THEN ‘Low’
WHEN (#tmp3.action_id IN (1,3,5,6,19,200) AND rc > 50 )
THEN ‘High’
ELSE ‘-‘
END,
infection_method
FROM
#tmp3
GROUP BY
action_description,
computername,
virusname,
infection_method,
rc,
alert_datetime,
action_id
ORDER BY
action_description,
alert_datetime DESC,
computername,
rc DESC
END
[/code]
