Transpose fields from one column of data

[giuval45]

Hi,

I have a data set extracted from Active Directory with Logoff information of the users:

The format of this file is:

"This event is generated when a logon session is destroyed. It may be positively correlated with a logon event using the Logon ID value. Logon IDs are only unique between reboots on the same computer."""
"Information,13/06/2019 00.00.20,Microsoft-Windows-Security-Auditing,4634,Logoff,""An account was logged off."

Subject:
" Security ID: SYSTEM"
" Account Name: SYSTEM"
" Account Domain: NT AUTHORITY"
" Logon ID: 0x2BBE225A6"

"Logon Type: 9"

"This event is generated when a logon session is destroyed. It may be positively correlated with a logon event using the Logon ID value. Logon IDs are only unique between reboots on the same computer."""
"Information,13/06/2019 00.00.20,Microsoft-Windows-Security-Auditing,4634,Logoff,""An account was logged off."

Subject:
" Security ID: SYSTEM"
" Account Name: SYSTEM"
" Account Domain: NT AUTHORITY"
" Logon ID: 0x2BBE229D2"

"Logon Type: 9

repeated for each gorup of records (more than 500.000 rows, for only one day...), as per the attached sample (the original is .csv, but is not possible to upload it...):

I need to obtain these fields in a flat file :

TimeStamp, SecurityID, AccountName, AccountDomain, LogonID, LogonType

13/06/2019 00.00.20,SYSTEM,SYSTEM,NT AUTHORITY,0x2BBE225A6,9

13/06/2019 00.00.20,SYSTEM,SYSTEM,NT AUTHORITY,0x2BBE229D2,9

in order to monitor all the accesses by the users.

I tried with Transpose but this move ALL the rows to one line. What is needed is a Transpose limited to 8 rows each time a sort of a "reversed Crosstab", in order to create the a/m columns of fields, with the data, for each group of Records.

Many thanks for your help.

Best regards,

Giulio

[Frank_Hartmann · Accepted solution]

There might be better solutions for your requirement like mine, but give it a try:

Load:
LOAD RowNo()-3 as RowNum,
	 textbetween(A,'Logon ID:','') as 	[Logon ID]

FROM
[C:\Users\admin\Downloads\Export-Test.xlsx]
(ooxml, no labels, table is Foglio2) Where A<>'';

left join

LOAD RowNo() as RowNum,
	 textbetween(A,'Security ID:','') as 	[Security ID]

FROM
[C:\Users\admin\Downloads\Export-Test.xlsx]
(ooxml, no labels, table is Foglio2) Where A<>'';

left join

LOAD RowNo()+2 as RowNum,
	 textbetween(A,'Information,',',Microsoft') as 	[Timestamp]

FROM
[C:\Users\admin\Downloads\Export-Test.xlsx]
(ooxml, no labels, table is Foglio2) Where A<>'';

left join

LOAD RowNo()-2 as RowNum,
	 textbetween(A,'Account Domain:','') as 	[Account Domain]

FROM
[C:\Users\admin\Downloads\Export-Test.xlsx]
(ooxml, no labels, table is Foglio2) Where A<>'';

left join

LOAD RowNo()-1 as RowNum,
	 textbetween(A,'Account Name:','') as 	[Account Name]

FROM
[C:\Users\admin\Downloads\Export-Test.xlsx]
(ooxml, no labels, table is Foglio2) Where A<>'';

left join

LOAD RowNo()-4 as RowNum,
	 textbetween(A,'Logon Type:','') as 	[Logon Type]

FROM
[C:\Users\admin\Downloads\Export-Test.xlsx]
(ooxml, no labels, table is Foglio2) Where A<>'';

NoConcatenate

Final:
Load * Resident Load Where Timestamp<>Null(); DROP Table Load; DROP Field RowNum;

[giuval45]

Hi Frank,

really many thanks...

Your solution is very simple, fast and "efficient"... 🙂

Waiting for a reply from the Community, I worked on it and I obtained the same result with 12 (TWELVE!) ETL passages, some quite complex and using, at the end, also a Generic Load... 😞

Just to confirm that to connect two points, a part a straight line, there are several way, some very cumbersone... like mine...

So I really appreciated the simplicity and effectiveness of your script.

Best regards,

Giulio