Splitting Strings from Event Log

[sparkbyte]

I everyone, I have been on the admin side of IT for the past 20 years and recently started to do some scripting (VBscript) and a little SQL.

I have developed a solution to meet the needs of some federal auditors, but not really met my needs yet.
What I have done is this.

I use MS Logparser to go out to 64 servers and copy the event logs into a DB on a SQL 2000 Ent. Server.

On the SQL server I have one StoredProcedure that parses out information from the security event log DB and put that info into a temp DB.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER   PROCEDURE [dbo].[stp_SecurityAuditReport]

AS 

TRUNCATE TABLE SecurityEvents_Tmp

-- Parse Department 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 Department Numbers 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

As you can see, we use 3 didgit numeric prefixes on all Departmental employee accounts. This is later used to produce departmenntal user audit reports.

I then have this script in a DTS that exports the report to an excel spreadsheet. (All works well for this purpose!)

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

This combination of utils and scripts does very good for producing generic security reports for branch officers.

But now I am getting requests to justify/explain what is in these reports. The problem I have is that the information needed to delve further into the event logs is in a field called Strings. This field not only changes in length and the amount of fields within this string, but the information in this field changes depending on the type of event record it came from.


This is the Strings field from a failed logon (529)
200jenil|DOMAIN|10|User32 |Negotiate|SERVER|SERVER$|DOMAIN|(0x0,0x3E7)|6920|-|10.190.12.10|48397

And this is from Event ID 642 which was an account being created.
-|381$cmiller|DOMAIN|%{S-1-5-21-3554868564-134719009-1577582102-7972}|Jmotta|DOMAIN|(0x0,0x58F635E)|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|%%1792|-|-

Now, my script does a good job of getting the first user name out but as in the 642 event the second users name would be useful as well. This is the person that created/modified the user account.

So what I was hoping was that I could use a function (or whatever) to automaticaly split the Strings value into it's individual components and put them into an auto-sizing temp table as something like Field1, Field2, Field3, and so on until the end of string.

I could then use a case to get the information needed.

HELP PLEASE!!


Thanks

John Fuhrman
Titan Global Services

http://www.titangs.com

 

[dbomrrsm]

If you captured the event number could you build case statements depending on the event number to split the string down depending on the rules of outputting the error message.

Again my two peneth.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[alicorn2]

Have a look at

http://forge.mysql.com/snippets/view.php?id=4

-- it's for mysql but you could probably use the same logic to make code that works for you.