tem_wafw00f

CREATE VIEW tem_wafw00f AS
WITH RECURSIVE split_lines AS (
    SELECT
        wafWoof.uniform_resource_id,
        t.tenant_id,
        t.tenant_name,
        ts.ur_ingest_session_id,
        CASE
            WHEN instr(block_content, char(10)) > 0 THEN substr(block_content, 1, instr(block_content, char(10)) - 1)
            ELSE block_content
        END AS line,
        CASE
            WHEN instr(block_content, char(10)) > 0 THEN substr(block_content, instr(block_content, char(10)) + 1)
            ELSE ''
        END AS rest,
        block_content
    FROM tem_wafw00f_original_txt wafWoof
    INNER JOIN uniform_resource ur ON wafWoof.uniform_resource_id = ur.uniform_resource_id
    INNER JOIN tem_tenant t ON t.device_id = ur.device_id
    INNER JOIN tem_session ts ON ur.device_id = ts.device_id

    UNION ALL

    SELECT
        uniform_resource_id,
        tenant_id,
        tenant_name,
        ur_ingest_session_id,
        CASE
            WHEN instr(rest, char(10)) > 0 THEN substr(rest, 1, instr(rest, char(10)) - 1)
            ELSE rest
        END AS line,
        CASE
            WHEN instr(rest, char(10)) > 0 THEN substr(rest, instr(rest, char(10)) + 1)
            ELSE ''
        END AS rest,
        block_content
    FROM split_lines
    WHERE rest <> ''
)
SELECT DISTINCT
    uniform_resource_id,
    tenant_id,
    tenant_name,
    ur_ingest_session_id,
    -- Extract host from "https://..."
    substr(
        line,
        instr(line, 'https://') + 8,
        CASE
            WHEN instr(substr(line, instr(line, 'https://') + 8), '/') > 0
            THEN instr(substr(line, instr(line, 'https://') + 8), '/') - 1
            ELSE length(substr(line, instr(line, 'https://') + 8))
        END
    ) AS host,
    block_content
FROM split_lines
WHERE line LIKE '[*] Checking %'
ORDER BY host