The Fake Logtable

This time just a short and easy one. Imagine you load FTP logs or any other kind of log file into a table.

For this purpose we introduce a fake ftp log like this:

DECLARE @LogFile TABLE (	
        id			INT IDENTITY PRIMARY KEY,
	TransactionId	INT NOT NULL,
	Operation		NVARCHAR(100) NOT NULL,
	[Filename]		NVARCHAR(250) NULL
);

INSERT INTO @LogFile 
VALUES 
	(1, 'connecting', NULL),
	(1, 'connection acquiered', NULL),
	(1, 'filetransfer', 'file1A.txt'),
	(1, 'filetransfer', 'file1B.txt'),
	(1, 'filetransfer', 'file1C.txt'),
	(1, 'filetransfer', 'file1D.txt'),
	(1, 'successful', NULL),
	(1, 'connection closed', NULL),
	(2, 'connecting', NULL),
	(2, 'connection acquiered', NULL),
	(2, 'filetransfer', 'file2A.txt'),
	(2, 'filetransfer', 'file2B.txt'),
	(2, 'failure', NULL),
	(2, 'connection closed', NULL),
	(3, 'connecting', NULL),
	(3, 'connection acquiered', NULL),
	(3, 'filetransfer', 'file3A.txt'),
	(3, 'filetransfer', 'file3B.txt'),
	(3, 'filetransfer', 'file3C.txt'),
	(3, 'successful', NULL),
	(3, 'connection closed', NULL)
;

 If we select we get a transaction id and some fake commands:

Pivot and filtering

So now we want to identify the valid transactions i.e. transactions with a connection acquiered, at least one filetransfer, a successful message and eventually a connection closed message.

Because we don’t need to know what files were sent we have to create a common table expression (CTE) that filters only columns that we need in the pivot process.

The pivot counts the rows for each possible Operation like this:

WITH PivotPrep AS (
	SELECT TransactionId, Operation
	FROM @LogFile
)
SELECT * 
FROM PivotPrep
PIVOT (COUNT(Operation) 
		FOR [Operation] 
		IN ([connection acquiered],[filetransfer],[successful],[connection closed])
	  ) AS x; 

Result:

So now we have everything in place. Let’s just add the correct where clauses

WITH PivotPrep AS (
	SELECT TransactionId, Operation
	FROM @LogFile
)
SELECT TransactionId
FROM PivotPrep
PIVOT (COUNT(Operation) 
		FOR [Operation] 
		IN ([connection acquiered],[filetransfer],[successful],[connection closed])
	  ) AS x 
WHERE	x.[connection acquiered] = 1 
AND		x.filetransfer >= 1
AND		x.successful = 1
AND		x.[connection closed] = 1;

All files transferred

Now lets find out what files have been sent only considering valid transactions. 

This is easy now since we just have to transform the query above to another CTE and join it with the logfile table. Since we only want to know what files have been transferred we just add a where clause and we’re done.

 

WITH PivotPrep AS (
	SELECT TransactionId, Operation
	FROM @LogFile
), ValidTransactions AS (
	SELECT TransactionId
	FROM PivotPrep
	PIVOT (COUNT(Operation) 
			FOR [Operation] 
			IN ([connection acquiered],[filetransfer],[successful],[connection closed])
		  ) AS x 
	WHERE	x.[connection acquiered] = 1 
	AND		x.filetransfer >= 1
	AND		x.successful = 1
	AND		x.[connection closed] = 1
)
SELECT DISTINCT b.Filename
FROM ValidTransactions AS a
JOIN @LogFile AS b
	ON a.TransactionId = b.TransactionId
WHERE b.Operation = 'filetransfer'

Result