June 2, 2010 at 7:57 pm
Hi All - I have a .csv file from event viewer that I am trying to import into a sql table. The data looks like this:
28262,Security,Security,SUCCESS AUDIT,Test631,6/1/2010 9:53:13 PM,626,bkilgore\KILGORE,"User Account Enabled: Target Account Name: rfitzbetter Target Domain: KILGORE Target Account ID: %{S-1-5-21-2204003968-2505502765-350501314-1245} Caller User Name: ekilgore Caller Domain: KILGORE Caller Logon ID: (0x0,0x3ACA73) "
As you can see, the file at the beginning looks fine BUT starting with (,"User Account Enabled) there are no more commas and all that data is in one field. I need to break up the entire record.
For Example:
Target Account Name: rfitzbetter - I would like to have 'rfitzbetter' spearated out into a field labeled "Target Account Name"
Target Domain: KILGORE - I would like to have 'Kilgore' separated out into a field labeled "Target Domain".
I can set up the table ahead of time (EventID, EventNAme, ........ CallerUserName, Caller Domain, CallerLogon) but would still need to parce out the data and obviously just basing it on a comma delimited will not work.
Can anyone PLEASE HELP!!!
June 2, 2010 at 8:53 pm
This solution is based on using a DelimitedSplit function, which is this (This function uses an in-line tally table. A persistent tally table will be more efficient. See the article referenced in the remarks of the code):
IF OBJECT_ID('dbo.DelimitedSplit') IS NOT NULL DROP FUNCTION dbo.DelimitedSplit
GO
CREATE FUNCTION [dbo].[DelimitedSplit] (
@list varchar(max),
@Delimiter char(1)
)
RETURNS TABLE
AS
RETURN
-- first, need to break down into separate items. See Jeff Moden's article:
-- The "Numbers" or "Tally" Table: What it is and how it replaces a loop.
-- at http://www.sqlservercentral.com/articles/T-SQL/62867/
-- for how a tally table can split strings apart.
WITH Tens (N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ),
Thousands (N) AS (SELECT 1 FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3),
Millions (N) AS (SELECT 1 FROM Thousands t1 CROSS JOIN Thousands t2),
Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Millions),
ItemSplit (ItemOrder, Item) AS (
SELECT N,
RTRIM(LTRIM(SUBSTRING(@Delimiter + @list + @Delimiter,N+1,
CHARINDEX(@Delimiter,@Delimiter + @list + @Delimiter,N+1)-N-1)))
FROM Tally
WHERE N < LEN(@Delimiter + @list + @Delimiter)
AND SUBSTRING(@Delimiter + @list + @Delimiter,N,1) = @Delimiter
)
SELECT ItemID = ROW_NUMBER() OVER (ORDER BY ItemOrder),
Item
FROM ItemSplit
GO
Now, let's get everything ripped apart...
-- See how you start off by actually creating a table and then
-- inserting the data into it? Your doing this makes it a lot easier
-- for all of us volunteers to help you. So, help us help you.
-- See http://www.sqlservercentral.com/articles/Best+Practices/61537/
-- for more details on how to do all of this.
DECLARE @test-2 TABLE (
ID int IDENTITY,
Col1 varchar(max)
)
INSERT INTO @test-2
SELECT '28262,Security,Security,SUCCESS AUDIT,Test631,6/1/2010 9:53:13 PM,626,bkilgore\KILGORE,"User Account Enabled: Target Account Name: rfitzbetter Target Domain: KILGORE Target Account ID: %{S-1-5-21-2204003968-2505502765-350501314-1245} Caller User Name: ekilgore Caller Domain: KILGORE Caller Logon ID: (0x0,0x3ACA73) "'
;WITH CTE AS
(
-- first, break apart the two different parts of the string.
SELECT Part1 = left(Col1, CharIndex('"', Col1)-1),
Part2 = SubString(Col1, CharIndex('"', Col1), len(Col1))
FROM @test-2
), CTE2 AS
(
-- get the starting position of each of these strings.
SELECT Part1, Part2,
Pos1 = CharIndex('User Account Enabled', Part2),
Pos2 = CharIndex('Account Name', Part2),
Pos3 = CharIndex('Target Domain', Part2),
Pos4 = CharIndex('Target Account ID', Part2),
Pos5 = CharIndex('Caller User Name', Part2),
Pos6 = CharIndex('Caller Domain', Part2),
Pos7 = CharIndex('Caller Logon Id', Part2)
FROM CTE
), CTE3 AS
(
-- use the delimited split function to rip apart the first part of the string.
SELECT Col1 = MAX(CASE WHEN rs.ItemID = 1 THEN Item ELSE NULL END),
Col2 = MAX(CASE WHEN rs.ItemID = 2 THEN Item ELSE NULL END),
Col3 = MAX(CASE WHEN rs.ItemID = 3 THEN Item ELSE NULL END),
Col4 = MAX(CASE WHEN rs.ItemID = 4 THEN Item ELSE NULL END),
Col5 = MAX(CASE WHEN rs.ItemID = 5 THEN Item ELSE NULL END),
Col6 = MAX(CASE WHEN rs.ItemID = 6 THEN Item ELSE NULL END),
Col7 = MAX(CASE WHEN rs.ItemID = 7 THEN Item ELSE NULL END),
Col8 = MAX(CASE WHEN rs.ItemID = 8 THEN Item ELSE NULL END),
Part1
FROM CTE
CROSS APPLY DelimitedSplit(Part1, ',') rs
GROUP BY CTE.Part1
)
-- and finally, put all of the output back together.
SELECT c1.Part1, c1.Part2, c3.Col1, c3.Col2, c3.Col3, c3.Col4, c3.Col5, c3.Col6, c3.Col7, c3.Col8,
'User Account Enabled' = SubString(c1.Part2, Pos1 + 22, Pos2-Pos1-23),
'Account Name' = SubString(c1.Part2, Pos2 + 14, Pos3-Pos2-15),
'Target Domain' = SubString(c1.Part2, Pos3 + 15, Pos4-Pos3-16),
'Target Account ID' = SubString(c1.Part2, Pos4 + 20, Pos5-Pos4-21),
'Caller User Name' = SubString(c1.Part2, Pos5 + 18, Pos6-Pos5-19),
'Caller Domain' = SubString(c1.Part2, Pos6 + 15, Pos7-Pos6-16),
'Caller Logon ID' = SubString(c1.Part2, Pos7 + 17, LEN(Part2)-Pos7-18)
FROM CTE2 c1
JOIN CTE3 c3
ON c3.Part1 = c1.Part1
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 3, 2010 at 5:15 pm
Thank you so much Wayne.....and thank you for the references to the articles. I am new to SQL server so I will certainly take a look.
Also, can I chnage the select to actually select either from the .csv file itself or I have already coded to import the .csv file into a table with 9 fields.
This is the code I am importing the table with:
USE TestDB
GO
CREATE TABLE CSVTest
(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))
GO
BULK
INSERT CSVTest
FROM 'C:\Scripts\eventviewersave.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
--Check the content of the table.
SELECT *
FROM CSVTest
GO
Thanks!!
June 3, 2010 at 5:49 pm
Okay, with all of this new information, you won't need the DelimitedSplit function.
Change to a temp table, and then use this code:
-- drop the table if it already exists
IF object_id('tempdb..#CSVTest') IS NOT NULL DROP TABLE #CSVTest
-- create a temp table
CREATE TABLE #CSVTest(
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 #CSVTest
FROM 'C:\Scripts\eventviewersave.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
-- add some new columns
ALTER TABLE #CSVTest ADD
[User Account Enabled] 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 Enabled'', 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 #CSVTest
)
UPDATE #CSVTest
SET [User Account Enabled] = 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 #CSVTest c
JOIN CTE c1
ON c1.RowID = c.RowID'
-- run the dynamic sql
exec (@sql)
-- show the results
SELECT * FROM #CSVTest
-- do what you need to with the results
-- drop the table
IF object_id('tempdb..#CSVTest') IS NOT NULL DROP TABLE #CSVTest
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 3, 2010 at 7:07 pm
Hi Wayne - Thanks Again......although I am receiving this error:
(1 row(s) affected)
Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.
The statement has been terminated.
(1 row(s) affected)
(it appears to insert 1 row......with the first 8 fields look ok and the field 9 (which is the long string) looks to contain the entire file(all the records)......then all the new fields are null with the excpetion of the rownum which =1)
June 3, 2010 at 7:17 pm
Provide additional rows of data - especially the ones that aren't loading.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 3, 2010 at 7:47 pm
Hi Wayne - I think I have attached a file which is the top xxx number of records that I cut and pasted into a txt doc
June 3, 2010 at 8:04 pm
Of the 2942 lines in that file, there are only 3 where Field9 starts with "User Account Enabled". Do you only want to run this for just those lines?
If so, replace the last line of the dynamic sql with these two lines:
ON c1.RowID = c.RowID
WHERE c1.Field9 LIKE ''%User Account Enabled%'''
Also, for the BULK INSERT, you need to remove the row terminator clause.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 3, 2010 at 9:12 pm
Thanks Wayne....I really appreciate all your help. It would be nice if I could somehow dynamically perform this for all the record types but I will alter the values for the ones I need to import...thanks.
June 3, 2010 at 9:27 pm
If you have all of the record types, we can probably work something out. 🙂
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 3, 2010 at 9:52 pm
Hey Wayne - Based on the 3 digit code in Field7........ I think I am trying to import:
528, 529, 531, 535, 539, 576, 608, 609, 624, 625, 626, 628, 630, 632, 633, 634, 644
with the 600's being the most important. They could all go in separate tables if need be? Thanks for any help you continue to provide!!
June 3, 2010 at 10:05 pm
If you have separate tables for each of the Field7 #'s, then you can:
1. get rid of adding the extra columns to #csvtest.
2. get rid of the dynamic sql.
3. duplicate the CTE code for each Field7 value, and just insert directly into each table for the appropriate Field7 value.
You should be able to just clone what is already there. If you run into any problems, ask away.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 5, 2010 at 9:35 pm
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!
June 5, 2010 at 11:01 pm
Wayne --
I am stuck!
Here is the code I am running:
-- drop the table if it already exists
IF object_id('EVCreatedUsers') IS NOT NULL DROP TABLE EVCreatedUsers
-- create a temp table
CREATE TABLE EVCreatedUsers(
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 EVCreatedUsers
FROM 'C:\Scripts\psloglist\eventviewer1.csv'
WITH
(
FIELDTERMINATOR = ','
--ROWTERMINATOR = ''
)
-- add some new columns
ALTER TABLE EVCreatedUsers ADD
[User Account Created] varchar(50),
[Account Name] varchar(50),
[New Domain] varchar(50),
[New 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(''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)
FROM EVEnabledUsers
)
UPDATE EVCreatedUsers
SET [User Account Created] = SubString(c1.Field9, Pos1 + 22, Pos2-Pos1-23),
[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)
FROM EVCreatedUsers 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 EVCreatedUsers
where is not null
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!!
June 6, 2010 at 1:25 pm
Hi Wayne -
I also get an error about length passed to substring too long? COuld it be that for a different record type the records are longer?
I was also trying to use 642, User account changed
Really Stuck!!! Help...thanks.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply