June 7, 2010 at 10:22 am
allenjasonbrown (6/5/2010)
Hi Wayne - I am trying to set up the other record types.Can you explain what this field does "Pos3-Pos2-15"
SubString(c1.Field9, Pos2 + 14, Pos3-Pos2-15 ----I understand this means start in position 2 plus 14. Which makes sense but I do not underatand that the Pos3-Pos2-15 is??
Thanks a ton!
Sorry that you had to wait for me... I spent a weekend away from the computer!!! 😀
For each of the desired strings (called "search strings"), we are getting the position that the specific search string starts at.
In the 2nd string, the value for that "field" starts at Pos2 + 14, which you correctly figured out.
Now, we need to see how many characters to get (which is the 3rd parameter to the SubString function). To do that, we get the next "field" (Pos3), subtract Pos2, and subtract the length of the Pos2 "search string", and subtract the space after it. Since the number of characters between the start of the "search string" and the start of it's data is 14 (for the second string), we end up with (Pos3-Pos2-14-1), or (Pos3-Pos2-15).
Did I explain this good enough to where you understand it, or do I need to go into this deeper?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 7, 2010 at 10:51 am
Hi Wayne -no worries, everyone needs weekends away from the pc for sanity reasons! That explanation makes perfect sense.
I posted this to another post but I duplicated a different record type but now am getting a substring error. Any thought?
Hi -
I am running this code and am receiving the error:
(28400 row(s) affected)
Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.
The statement has been terminated.
(28400 row(s) affected)
(0 row(s) affected)
';WITH CTE AS
(
SELECT Field9,
RowID,
Pos1 = CharIndex(''User Account Created'', Field9),
Pos2 = CharIndex(''Account Name'', Field9),
Pos3 = CharIndex(''Target Domain'', Field9),
Pos4 = CharIndex(''Target Account ID'', Field9),
Pos5 = CharIndex(''Caller User Name'', Field9),
Pos6 = CharIndex(''Caller Domain'', Field9),
Pos7 = CharIndex(''Caller Logon Id'', Field9)
FROM evCreateUser
)
UPDATE evCreateUser
SET [User Account Created] = SubString(c1.Field9, Pos1 + 22, Pos2-Pos1-23),
[Account Name] = SubString(c1.Field9, Pos2 + 14, Pos3-Pos2-15),
[Target Domain] = SubString(c1.Field9, Pos3 + 15, Pos4-Pos3-16),
[Target Account ID] = SubString(c1.Field9, Pos4 + 20, Pos5-Pos4-21),
[Caller User Name] = SubString(c1.Field9, Pos5 + 18, Pos6-Pos5-19),
[Caller Domain] = SubString(c1.Field9, Pos6 + 15, Pos7-Pos6-16),
[Caller Logon ID] = SubString(c1.Field9, Pos7 + 17, LEN(c1.Field9)-Pos7-18)
FROM evCreateUser c
JOIN CTE c1
ON c1.RowID = c.RowID
WHERE c1.Field9 LIKE ''%User Account Created%'''
The data looks like this:
28129,Security,Security,SUCCESS AUDIT,MIS631,6/1/2010 9:35:17 PM,624,mhigh\KILGORE,"User Account Created: New Account Name: higha New Domain: KILGORE New Account ID: %{S-1-5-21-2204003968-2505502765-350501314-1244} Caller User Name: mhigh Caller Domain: KILGORE Caller Logon ID: (0x0,0x2B554F) Privileges - Attributes: Sam Account Name: higha Display Name: - User Principal Name: higha Home Directory: - Home Drive: - Script Path: - Profile Path: - User Workstations: - Password Last Set: <never> Account Expires: <never> Primary Group ID: 513 AllowedToDelegateTo: - Old UAC Value: 0x0 New UAC Value: 0x15 User Account Control: Account Disabled 'Password Not Required' - Enabled 'Normal Account' - Enabled User Parameters: - Sid History: - Logon Hours: <value not set> "
This data seems to work fine with similar code:
28132,Security,Security,SUCCESS AUDIT,MIS631,6/1/2010 9:35:19 PM,626,mhigh\KILGORE,"User Account Enabled: Target Account Name: higha Target Domain: KILGORE Target Account ID: %{S-1-5-21-2204003968-2505502765-350501314-1244} Caller User Name: mhigh Caller Domain: KILGORE Caller Logon ID: (0x0,0x2B554F) "
Thanks in Advance....Allen
June 7, 2010 at 11:05 am
allenjasonbrown (6/5/2010)
Wayne --I am stuck!
Here is the code I am running:
.....
WIth the input being....
28259,Security,Security,SUCCESS AUDIT,MIS631,6/1/2010 9:53:11 PM,624,ekilgore\KILGORE,"User Account Created: New Account Name: rfitzbetter New Domain: KILGORE New Account ID: %{S-1-5-21-2204003968-2505502765-350501314-1245} Caller User Name: ekilgore Caller Domain: KILGORE Caller Logon ID: (0x0,0x3ACA73) Privileges - Attributes: Sam Account Name: rfitzbetter Display Name: - User Principal Name: rfitzbetter Home Directory: - Home Drive: - Script Path: - Profile Path: - User Workstations: - Password Last Set: <never> Account Expires: <never> Primary Group ID: 513 AllowedToDelegateTo: - Old UAC Value: 0x0 New UAC Value: 0x15 User Account Control: Account Disabled 'Password Not Required' - Enabled 'Normal Account' - Enabled User Parameters: - Sid History: - Logon Hours: <value not set> "
I am getting error:
(28400 row(s) affected)
Msg 8152, Level 16, State 10, Line 1
String or binary data would be truncated.
The statement has been terminated.
(0 row(s) affected)
This is when I run against the full file I uploaded yesterday......I am sure I did somethign stupid.......but I am frustrated.
THanks!!
Change the dynamic portion of the code to this:
declare @sql varchar(max)
set @sql =
';WITH CTE AS
(
SELECT Field9,
RowID,
Pos1 = CharIndex(''User Account Created'', Field9),
Pos2 = CharIndex(''Account Name'', Field9),
Pos3 = CharIndex(''New Domain'', Field9),
Pos4 = CharIndex(''New Account ID'', Field9),
Pos5 = CharIndex(''Caller User Name'', Field9),
Pos6 = CharIndex(''Caller Domain'', Field9),
Pos7 = CharIndex(''Caller Logon Id'', Field9) ,
Pos8 = CharIndex(''Privileges'', Field9)
FROM EVCreatedUsers
)
UPDATE EVCreatedUsers
SET [User Account Created] = SubString(c1.Field9, Pos1 + 21, Pos2-Pos1-22),
[Account Name] = SubString(c1.Field9, Pos2 + 14, Pos3-Pos2-15),
[New Domain] = SubString(c1.Field9, Pos3 + 12, Pos4-Pos3-13),
[New Account ID] = SubString(c1.Field9, Pos4 + 17, Pos5-Pos4-18),
[Caller User Name] = SubString(c1.Field9, Pos5 + 18, Pos6-Pos5-19),
[Caller Domain] = SubString(c1.Field9, Pos6 + 15, Pos7-Pos6-16),
--[Caller Logon ID] = SubString(c1.Field9, Pos7 + 17, LEN(c1.Field9)-Pos7-18)
[Caller Logon ID] = SubString(c1.Field9, Pos7 + 17, Pos8-Pos7-18)
FROM EVCreatedUsers c
JOIN CTE c1
ON c1.RowID = c.RowID
WHERE c1.Field9 LIKE ''%User Account Created%'''
-- run the dynamic sql
exec (@sql)
(when composing a post, see the "IFCode Shortcuts" to the right? Click the "code" = "sql" one, and put your code between them... that will prevent an endless post with a lot of code, put instead will show a scrollable code window.)
Explanation: you are getting the entire rest of the line for the "caller logon id". It doesn't look like you need all of this... just the stuff before the "priviledges" text. So I added an extra Pos8 to get that position, and used that to get the length. If you know that it will always be a fixed number of characters, you can also just use that.
Also, you had "FROM EVEnabledUsers " in the CTE... I changed this to "FROM EVCreatedUsers".
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 7, 2010 at 7:12 pm
Hi Wayne -
This is the full code with your changes
-- drop the table if it already exists
IF object_id('evCreateUser') IS NOT NULL DROP TABLE evCreateUser
-- create a temp table
CREATE TABLE evCreateUser(
Field1 VARCHAR(50),
Field2 VARCHAR(50),
Field3 VARCHAR(50),
Field4 VarChar(50),
Field5 VARCHAR(50),
Field6 VARCHAR(50),
Field7 VarChar(50),
Field8 VARCHAR(50),
Field9 VARCHAR(max)
)
-- perform the bulk insert
BULK
INSERT evCreateUser
FROM 'C:\Scripts\PSLogList\eventviewertest.csv'
WITH
(
FIELDTERMINATOR = ','
--ROWTERMINATOR = ''
)
-- add some new columns
ALTER TABLE evCreateUser ADD
[User Account Created] varchar(50),
[Account Name] varchar(50),
[Target Domain] varchar(50),
[Target Account ID] varchar(50),
[Caller User Name] varchar(50),
[Caller Domain] varchar(50),
[Caller Logon Id] varchar(50),
[RowID] INT NOT NULL IDENTITY
-- since the added columns will generate an error,
-- perform the update via dynamic sql.
declare @sql varchar(max)
set @sql =
';WITH CTE AS
(
SELECT Field9,
RowID,
Pos1 = CharIndex(''User Account Created'', Field9),
Pos2 = CharIndex(''Account Name'', Field9),
Pos3 = CharIndex(''Target Domain'', Field9),
Pos4 = CharIndex(''Target Account ID'', Field9),
Pos5 = CharIndex(''Caller User Name'', Field9),
Pos6 = CharIndex(''Caller Domain'', Field9),
Pos7 = CharIndex(''Caller Logon Id'', Field9) ,
Pos8 = CharIndex(''Privileges'', Field9)
FROM EVCreateUser
)
UPDATE EVCreateUser
SET [User Account Created] = SubString(c1.Field9, Pos1 + 21, Pos2-Pos1-22),
[Account Name] = SubString(c1.Field9, Pos2 + 14, Pos3-Pos2-15),
[Target Domain] = SubString(c1.Field9, Pos3 + 12, Pos4-Pos3-13),
[Target Account ID] = SubString(c1.Field9, Pos4 + 17, Pos5-Pos4-18),
[Caller User Name] = SubString(c1.Field9, Pos5 + 18, Pos6-Pos5-19),
[Caller Domain] = SubString(c1.Field9, Pos6 + 15, Pos7-Pos6-16),
--[Caller Logon ID] = SubString(c1.Field9, Pos7 + 17, LEN(c1.Field9)-Pos7-18)
[Caller Logon ID] = SubString(c1.Field9, Pos7 + 17, Pos8-Pos7-18)
FROM EVCreateUser c
JOIN CTE c1
ON c1.RowID = c.RowID
WHERE c1.Field9 LIKE ''%User Account Created%'''
-- run the dynamic sql
exec (@sql)
-- show the results
SELECT * FROM evCreateUser
I am still getting errror:
(28400 row(s) affected)
Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.
The statement has been terminated.
(28400 row(s) affected)
(0 row(s) affected)
When I run it against the full file I posted previously....any thoughts?
Thanks again for all your help
June 8, 2010 at 9:03 am
For rows where Field9 contains "User Account Created", there are no "Target Domain" or "Target Account ID" "columns" - they are "New Domain" and "New Account ID". Change those strings in the dynamic sql (Pos3, Pos4), and it should work.
BTW, when testing one of the types of Field9, just put a:
select * from evCreateUsers where Field9 like '%User Account Created%' (substitute the appropriate phrase) to see the rows you need to work with. From there, you should be able to figure out what is going on.
Another trick: after the "with CTE AS ()", change the "UPDATE" statement to a select statement to see what it is returning.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 5 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply