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:

  1: if user name matches *admin*, report it as "Admin account access" 
  2: if user name matches "system", report it as "SYSTEM account access" 
  3: if file name matches "unavailable", report it as "unavailable" 
  4: if file name matches "*Program Files*|*C:/Winnt*", report it as "System Folders" 
  5: if file name matches "*Temporary Internet Files*|*Mozilla/Firefox/Cache*", report it as "Web browsing" 
  6: if file name matches "*[HNP]:*", report it as "Network drive" 
  7: if file name matches "*D:*", report it as "Optical removable drive" 
  8: if file name matches "*[^CDA]:*", report it as "USB removable drive" 
  9: if file name matches "*Local Settings/Temp*", report it as "User Profile Temp Folder" 
 10: if file name matches "*Documents and Settings*", report it as "Local User Profile" 
 11: if file name matches "*C:*", report it as "Local drive" 
 12: 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:

  1: CREATE PROC SEP_generate_monthly_reports
  2: @SITE_GUID VARCHAR(100),
  3: @NUM_OF_MONTHS INT
  4: 
  5: AS
  6: BEGIN
  7: SET NOCOUNT ON
  8: 
  9: -- 
 10:     SELECT
 11:         MAX(Convert(varchar(16), DATEADD(minute,DATEDIFF(minute,GETUTCDATE(),
 12:             GETDATE()),ALERTS.ALERTDATETIME), 120)) AS alertdatetime,
 13:         ACTUALACTION.ACTUALACTION AS Action_description,
 14: 		ALERTS.ACTUALACTION_IDX AS action_id,
 15:         UPPER(SEM_COMPUTER.COMPUTER_NAME) as ComputerName,
 16:         VIRUS.VIRUSNAME,
 17:         SUM(ALERTS.NoOfViruses) AS 'risk_count',
 18:         Infection_method =
 19:             CASE
 20:                 WHEN (SEM_CLIENT.USER_NAME LIKE N'%-admin%' OR
 21:                     ALERTS.FILEPATH LIKE N'%-admin%')
 22:                 THEN 'Admin account access'
 23: 
 24:                 WHEN SEM_CLIENT.USER_NAME LIKE N'%system%'
 25:                 THEN 'SYSTEM account access'
 26: 
 27:                 WHEN ALERTS.FILEPATH LIKE N'%unavailable%'
 28:                 THEN 'Unavailable'
 29: 
 30:                 WHEN (ALERTS.FILEPATH LIKE N'%Program Files%' OR
 31:                     ALERTS.FILEPATH LIKE N'%C:\/Winnt%')
 32:                 THEN 'System Folders'
 33: 
 34:                 WHEN (ALERTS.FILEPATH LIKE N'%Temporary Internet Files%' OR
 35:                     ALERTS.FILEPATH LIKE N'%Mozilla\/Firefox\/Cache%')
 36:                 THEN 'Web browsing'
 37: 
 38:                 WHEN ALERTS.FILEPATH LIKE N'%[HNP]:%'
 39:                 THEN 'Network drive'
 40: 
 41:                 WHEN ALERTS.FILEPATH LIKE N'%D:%'
 42:                 THEN 'Optical removable drive'
 43: 
 44:                 WHEN ALERTS.FILEPATH LIKE N'%[^CDA]:%'
 45:                 THEN 'USB removable drive'
 46: 
 47:                 WHEN ALERTS.FILEPATH LIKE N'%Local Settings\/Temp%'
 48:                 THEN 'User Profile Temp Folder'
 49: 
 50:                 WHEN ALERTS.FILEPATH LIKE N'%Documents and Settings%'
 51:                 THEN 'Local User Profile'
 52: 
 53:                 WHEN ALERTS.FILEPATH LIKE N'%C:%'
 54:                 THEN 'Local drive'
 55: 
 56:                 ELSE '-'
 57:             END
 58: 	INTO
 59: 		#tmp1
 60:     FROM
 61:         ACTUALACTION, ALERTS, SEM_COMPUTER, VIRUS, SEM_CLIENT
 62:     WHERE
 63:         (ALERTS.ALERTDATETIME > DATEADD(month, -@NUM_OF_MONTHS, getUTCdate()) AND
 64:         ALERTS.SITE_IDX = @SITE_GUID AND
 65:         ACTUALACTION.ACTUALACTION_IDX = ALERTS.ACTUALACTION_IDX AND
 66:         SEM_COMPUTER.COMPUTER_ID = ALERTS.COMPUTER_IDX AND
 67:         VIRUS.VIRUSNAME_IDX = ALERTS.VIRUSNAME_IDX AND
 68:         SEM_CLIENT.USER_NAME = ALERTS.USER_NAME)
 69:     GROUP BY
 70:         SEM_COMPUTER.COMPUTER_NAME,
 71: 		SEM_CLIENT.USER_NAME,
 72:         VIRUS.VIRUSNAME,
 73: 		ALERTS.FILEPATH,
 74: 		ACTUALACTION.ACTUALACTION,
 75: 		ALERTS.ACTUALACTION_IDX
 76:     ORDER BY
 77:         Action_description,
 78: 		alertdatetime DESC,
 79:         SEM_COMPUTER.COMPUTER_NAME,
 80: 		risk_count DESC
 81: 
 82: 
 83:  --
 84:     SELECT
 85:         alertdatetime,
 86: 		action_id,
 87:         action_description,
 88:         computername,
 89:         virusname,
 90:         infection_method,
 91:         SUM(risk_count) AS rc,
 92:         SUBSTRING(alertdatetime,1,13) AS cnt
 93:     INTO
 94:         #tmp2
 95:     FROM
 96:         #tmp1
 97:     GROUP BY
 98:         alertdatetime,
 99:         action_description,
100:         computername, 
101:         virusname,
102:         infection_method,
103: 		action_id
104:     ORDER BY
105:         action_description,
106:         alertdatetime DESC,
107:         computername,
108:         rc DESC
109: 
110: --
111: 	SELECT
112: 		MIN(alertdatetime) AS alert_datetime,
113: 		action_id,
114: 		action_description,
115: 		computername,
116: 		virusname,
117: 		infection_method,
118: 		SUM(rc) AS rc
119: 	INTO
120: 		#tmp3
121: 	FROM
122: 		#tmp2
123: 	GROUP BY 
124: 		cnt,
125: 		action_description,
126: 		computername,
127: 		virusname,
128: 		infection_method,
129: 		action_id
130: 	ORDER BY
131: 		alert_datetime DESC,
132: 		action_description DESC,
133: 		computername,
134: 		rc DESC
135: 
136: --
137: 	SELECT
138: 		alert_datetime,
139: 		action_description,
140: 		computername,
141: 		virusname,
142: 		rc,
143: 		risk_severity =
144: 			CASE
145: 				WHEN (#tmp3.action_id NOT IN (1,3,5,6,19,200) AND rc IN (1,2))
146: 				THEN 'Low'
147: 
148: 				WHEN (#tmp3.action_id NOT IN (1,3,5,6,19,200) AND rc IN (3,4,5))
149: 				THEN 'Medium'
150: 
151: 				WHEN (#tmp3.action_id NOT IN (1,3,5,6,19,200) AND rc > 5)
152: 				THEN 'High'
153: 
154: 				WHEN (#tmp3.action_id IN (1,3,5,6,19,200) AND rc <= 50 )
155: 				THEN 'Low'
156: 
157: 				WHEN (#tmp3.action_id IN (1,3,5,6,19,200) AND rc > 50 )
158: 				THEN 'High'
159: 
160: 				ELSE '-'
161: 			END,
162: 		infection_method
163: 	FROM
164: 		#tmp3    
165: 	GROUP BY
166: 		action_description,
167: 		computername,
168: 		virusname,
169: 		infection_method,
170: 		rc,
171: 		alert_datetime,
172: 		action_id
173: 	ORDER BY
174: 		action_description,
175: 		alert_datetime DESC,
176: 		computername,
177: 		rc DESC
178: END
179: 
180: