[SOLVED] Parse table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

[SOLVED] Parse table

Author

Topic

SparkByte
Yak Posting Veteran

60 Posts

Posted - 2006-12-01 : 00:22:53

Everyone here has been so helpful! Hopefully someone can help me figure this out.

I have created a multi-part solution for creating network access secuity reports and would like to modify the primary stored procedure that parses the main table to get the user name and department number. I have to admit I got some help from a co-worker to get it working the 1st time. (She is not available now to help.

)

This is the stored procedure.

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO



ALTER    PROCEDURE dbo.stp_SecurityAuditReport

AS 

TRUNCATE TABLE SecurityEvents_Tmp

-- Parse Branch 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,
SecurityEvents.*
FROM SecurityEvents 
JOIN EventsToLog on SecurityEvents.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


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Now I have figured out how to use a string parser, thanks to this forum!

declare @Strings as varchar(255)
declare @LogonName as varchar(255)
declare @DomainName as varchar(255)
declare @Message as VarChar(255)
declare @MessageVar as VarChar(255)


select @Strings = Strings from SecurityEvents where eventid = '592'

Select @LogonName = value from dbo.fn_Split(@Strings,'|') where idx = 3
Select @DomainName = value from dbo.fn_Split(@Strings,'|') where idx = 4


Select @LogonName,@DomainName

Output:
jfuhrman QLICHE

What I would like to do is some how incorporate the string splitter into the stored procedure so that I can pull some additional information from the strings column value.
Currently I am only making use of the name value and also want to make use of the domain value.

Anyway got any ideas??

Thank You,

John Fuhrman
http://www.titangs.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources