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:

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.  

SQL Stored procedure SEP_generate_monthly_reports:

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