Analyse FastStats Service log with DuckDB queries

readme.md

Copy logfile or log bak file into memory and then query on it

When using DuckDB CLI you can directly create an in-memory database with .open :memory:

CREATE TABLE log as 
WITH raw_log
AS (
	SELECT * exclude("Thread")
		,SPLIT("Thread", ',') AS ThreadArr
	FROM read_csv("C:\Users\flo\Downloads\fs_handel_log.txt", delim = '\t', quote = '', ESCAPE = '', columns = { 'Type' : 'VARCHAR', 'Thread' : 'VARCHAR', 'System' : 'VARCHAR', Timestamp: 'TIMESTAMP', 'Heap' : 'HUGEINT', 'Memory' : 'HUGEINT', 'Message' : 'VARCHAR', 'guid' : 'VARCHAR' }, header = false, null_padding = true, strict_mode = false, store_rejects = true, timestampformat = '%d/%m/%Y %H:%M:%S.%f')
	),
  parsed_log AS (
  SELECT *
		,ThreadArr [1] AS Thread
		,ThreadArr [2] AS ThreadNo
		,ThreadArr [3] AS SystemName
		,ThreadArr [4] AS JobId
		,ThreadArr [5] AS JobType
		,ThreadArr [6] AS UserId
	FROM raw_log
  ),
 final_log as (
 SELECT coalesce(System, SystemName) AS System
	,* exclude("System", "SystemName", ThreadArr)
FROM parsed_log
 )

SELECT * FROM final_log;

Then you can query on it

SELECT * FROM log;

To evaluate logged queries and their time and records, use an approach like this

Then for example you can query for expensive queries that are logged

CREATE VIEW sqllog as
SELECT *
	,epoch(STRPTIME(CONCAT (
				'1970-01-01 '
				,REGEXP_EXTRACT(Message, 'took ([0-9]+:[0-9]+:[0-9]+\.[0-9]+) to execute', 1)
				), '%Y-%m-%d %H:%M:%S.%n')) AS exectime
	,coalesce(nullif(replace(REGEXP_EXTRACT(Message, 'and affected (.*?) row', 1),'.',''),''),'0')::BIGINT as records
FROM log
WHERE Message LIKE '%took%to execute%'
ORDER BY exectime DESC;

To select for a specific time range use

select * from log where Timestamp between '2025-11-12 15:30:00' and '2025-11-12 16:00:00';

or for the last n hours

Select * from log where Timestamp >= CURRENT_TIMESTAMP - INTERVAL '24 hours' order by timestamp;