Splitting Strings

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 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


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!!



Thank You,

John Fuhrman
http://www.titangs.com