set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER   PROCEDURE [dbo].[stp_SecurityAuditReport]

AS 

TRUNCATE TABLE SecurityEvents_Tmp

-- Parse Bank Number & UserName
INSERT INTO SecurityEvents_Tmp(DepartmentNumber, UserName, EventLog, RecordNumber, TimeGenerated, TimeWritten, EventID, EventType, EventTypeName, EventCategory, EventCategoryName, SourceName, Strings, ComputerName, SID, Message, Data)
SELECT 
DepartmentNumber = '001',
UserName = CASE
WHEN Strings LIKE '[0-9][0-9][0-9]%' 
THEN SUBSTRING(Strings,1,charindex('|',Strings,1)-1)
WHEN  Strings LIKE '-|[0-9][0-9][0-9]%' 
THEN SUBSTRING(Strings,3,charindex('|',Strings,3)-3)
WHEN Strings LIKE '-|[a-z]%' 
THEN SUBSTRING(Strings,3,charindex('|',Strings,3)-3)
WHEN Strings LIKE 'Account Unlocked.  |%' 
THEN SUBSTRING(Strings,21,charindex('|',Strings,21)-21)
ELSE SUBSTRING(Strings,1,charindex('|',Strings,1)-1) 
END,
Events.*
FROM Events 
JOIN EventsToLog on Events.EventID = EventsToLog.EventID
WHERE SID NOT LIKE 'S-%'


-- Update blank usernames
UPDATE SecurityEvents_Tmp SET UserName = 'NO USERNAME' 
WHERE UserName = '' OR UserName = '-'

-- Update DepartmentNumbers with zeros
UPDATE SecurityEvents_Tmp SET 
DepartmentNumber = CASE 
	WHEN UserName LIKE '[0-9][0-9][0-9][a-z]%' OR 
	UserName LIKE '[0-9][0-9][0-9]#%' OR 
	UserName LIKE '[0-9][0-9][0-9]$%' 
	THEN SUBSTRING(UserName,1,3)
     ELSE '001' END

DECLARE @TimeGenerated datetime
SELECT @TimeGenerated = TimeGenerated FROM SecurityEvents_TimeGenerated

DECLARE @TimeGeneratedEnd datetime
SELECT @TimeGeneratedEnd = TimeGeneratedEnd FROM SecurityEvents_TimeGenerated

SELECT DepartmentName = CASE WHEN b.DepartmentName IS NULL 
	THEN 'All Department' ELSE b.DepartmentName END,
	a.EventID,d.EventDescription,a.UserName, a.TimeGenerated,c.Email1,c.Email2,c.Email3,c.Email4
	FROM SecurityEvents_Tmp a
		LEFT JOIN DepartmentList b on a.DepartmentNumber = b.DepartmentNumber
		LEFT JOIN EmailToList c on b.DepartmentNumber = c.DepartmentNumber
			JOIN EventsToLog d on a.EventID = d.EventID
	WHERE b.Departmentnumber in (select Departmentnumber from Departmentlist)
		AND a.TimeGenerated BETWEEN @TimeGenerated  AND  @TimeGeneratedEnd 
	ORDER BY b.DepartmentNumber,a.EventID,a.TimeGenerated

	BEGIN
		SET @LeftSplit = @Delimiter + SUBSTRING(@List, @SplitStart, @SplitEnd - @SplitStart) + @Delimiter

		INSERT @ReturnTbl (OutParam)
		SELECT LTRIM(RTRIM(SUBSTRING(@LeftSplit, Number + 1,
	                    CHARINDEX(@Delimiter, @LeftSplit, Number + 1) - Number - 1)))
	                AS Value
		FROM   dbo.Numbers
		WHERE  Number <= LEN(@LeftSplit) - 1
			AND SUBSTRING(@LeftSplit, Number, 1) = @Delimiter
			AND SUBSTRING(@LeftSplit, Number + 1,
	                    	CHARINDEX(@Delimiter, @LeftSplit, Number + 1) - Number - 1) <> ''

		SET @SplitStart = @SplitEnd + 1
		SET @SplitEnd = @SplitEnd + 7998

		SELECT @SplitEnd = MAX(Number) + @SplitStart
		FROM dbo.Numbers
		WHERE (SUBSTRING(@List, Number + @SplitStart, 1) = @Delimiter
				OR Number+@SplitStart = DATALENGTH(@List) + 1)
			AND Number BETWEEN 1 AND @SplitEnd - @SplitStart
	END