PostgreSQL - Plochy SQL model¶

Schema¶

CREATE TABLE alerts(
    ID text,
    DetectTime timestamp,
    Source_min inet[],
    Source_max inet[],
    Target_min inet[],
    Target_max inet[],
    Category text[],
    Node_Name text[],
    Source_Port integer[],
    Target_Port integer[],
    alert jsonb
);

Schema MentatPG:

CREATE TABLE alerts(
    ID text,
    DetectTime timestamp,
    Source_IP iprange[],
    Target_IP iprange[],
    Category text[],
    Detector text[],
    Protocol text[],
    Source_Port integer[],
    Target_Port integer[],
    ResolvedAbuses text[],
    alert jsonb
);

Import:

\copy alerts from 'mentat-alerts-psql_flat.csv' with csv escape '\' quote '`';

kde 'mentat-alerts-psql_flat.csv' je výstup me2pg_sql_flat.py

Dotaz¶

\set source_min_q '\'45.58.108.0\'::inet'
\set source_max_q '\'45.58.108.255\'::inet'
\set target_min_q '\'195.113.252.0\'::inet'
\set target_max_q '\'195.113.252.255\'::inet'
\set category_q '\'{\"Recon.Scanning\"}\''
\set port_q '\'{56371}\''

SELECT alert FROM alerts
    WHERE (
        ((:source_min_q <= ANY(source_min)) AND (:source_max_q >= ANY(source_min))) AND ((:source_min_q <= ANY(source_max)) AND (:source_max_q >= ANY(source_max)))
        AND
        ((:target_min_q <= ANY(target_min)) AND (:target_max_q >= ANY(target_min))) AND ((:target_min_q <= ANY(target_max)) AND (:target_max_q >= ANY(target_max)))
        AND
        category @> :category_q
        AND
        (source_port @> :port_q OR target_port @> :port_q)
    )
    ORDER BY detecttime;