Symantec Endpoint Protection (SEP) Reporting: SQL Stored Procedure to Generate Monthly Reports

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]

Leave a Reply

Your email address will not be published. Required fields are marked *