Skip to content

Instantly share code, notes, and snippets.

@santrancisco

santrancisco/alert.sql

Created March 24, 2025 07:42
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save santrancisco/66a61a434f10c27680f2f12f67a12a89 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save santrancisco/66a61a434f10c27680f2f12f67a12a89 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. santrancisco created this gist Mar 24, 2025.
    77 changes: 77 additions & 0 deletions alert.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,77 @@
    ---Create dns table

    CREATE TABLE new_dns(domain_name String, fetch_date DateTime) PARTITION BY toYYYYMM(fetch_date) ORDER BY domain_name

    ---Create interesting search table

    create table interesting_search (pattern String) order by pattern;


    ----Create mat view for alert
    CREATE MATERIALIZED VIEW clickhousealert_real
    ENGINE = URL('https://hooks.slack.com/services/xxxxxxx/xxxxxx/xxxxxxxxxx', 'JSONEachRow')
    AS
    SELECT
    if(
    length(domain_names) = 0,
    '',
    concat(
    'Domains:\n',
    arrayStringConcat(
    arrayMap((d, f) -> concat('- ', d, ' (', formatDateTime(f, '%Y-%m-%d'), ')'), domain_names, fetch_dates),
    '\n'
    )
    )
    ) AS text,
    'dns-alert' AS username,
    ':warning:' AS icon_emoji
    FROM (
    SELECT
    groupArray(domain_name) AS domain_names,
    groupArray(fetch_date) AS fetch_dates
    FROM new_dns
    WHERE arrayExists(
    p -> domain_name LIKE p,
    (SELECT groupArray(pattern) FROM interesting_search)
    )
    );





    CREATE MATERIALIZED VIEW clickhousealert_real
    ENGINE = URL('https://yourslack_web_hook', 'JSONEachRow')
    AS
    SELECT
    if(
    length(domain_names) = 0,
    '',
    concat(
    'Domains:\n',
    arrayStringConcat(
    arrayMap((d, f) -> concat('- ', d, ' (', formatDateTime(f, '%Y-%m-%d'), ')'), domain_names, fetch_dates),
    '\n'
    )
    )
    ) AS text,
    'dns-alert' AS username,
    ':warning:' AS icon_emoji
    FROM (
    SELECT
    groupArray(domain_name) AS domain_names,
    groupArray(fetch_date) AS fetch_dates
    FROM new_dns
    WHERE arrayExists(
    p -> domain_name LIKE p,
    (SELECT groupArray(pattern) FROM interesting_search)
    )
    );

    ---- Insert interesting patterns

    INSERT INTO interesting_search (pattern) VALUES ('%clickhouse%'),('%coinbase%'),('%altinity%');

    ---- INSERT data to our dns table

    INSERT INTO new_dns(domain_name) SETTINGS materialized_views_ignore_errors=1 SELECT * FROM url('https://raw.githubusercontent.com/romainmarcoux/malicious-domains/refs/heads/main/full-domains-ab.txt', 'CSV', 'domain_name String') WHERE domain_name NOT IN (SELECT domain_name FROM new_dns WHERE fetch_date>today()-INTERVAL 14 day); select count(*) from new_dns;