Интеграции со сторонними системами

-- SELECT
--     COUNT(*)::int
-- FROM preferences.collectors
-- WHERE preferences.collectors.enabled = True

-- SELECT
--     COUNT(*)::int
--          FROM corrdisp.realtime_correlators
-- WHERE corrdisp.realtime_correlators.active = True

-- SELECT
--     id,
--     directive_id,
--     assigned_to,
--     is_retro,
--     initial_time,
--     registration_time,
--     close_time,
--     status,
--     severity,
--     event_doc_keys,
--     gossopka_sending_status,
--     security_label,
--     tenant_id,
--     correlator_ids,
--     recommendation,
--     has_errors,
--     updated_at,
--     comments,
--     status_reason,
--     histories,
--     gossopka_incident_type,
--     gossopka_incident_id,
--     response_stage,
--     description,
--     asset_ips,
--     threats_array,
--     gossopka_incident_category
-- FROM incman.incidents
-- WHERE id = 16

-- SELECT
--     id,
-- -- directives START
--     directive_id,
--     name,
--     directive_severity,
--     directive_assigned_to,
--     reaction_script_filename,
--     reaction_is_enabled,
--     created_at,
--     directive_updated_at,
--     aggregate_in,
--     aggregated_min_count,
--     group_id,
--     gossopka,
--     directive_recommendation,
--     aggregate_greedily_count,
--     directive_gossopka_incident_type,
--     rule,
--     deleted_at,
--     risk_score,
--     author,
--     reference_url,
--     note,
--     timestamp_override,
--     rule_edit_state,
--     produce_incident,
--     produce_aggregated_event,
--     store_keys_in_event,
--     directive_threats_array,
--     directive_gossopka_incident_category,
-- --     END
--     assigned_to,
--     is_retro,
--     initial_time,
--     registration_time,
--     close_time,
--     status,
--     severity,
--     event_doc_keys,
--     gossopka_sending_status,
--     security_label,
--     tenant_id,
--     correlator_ids,
--     recommendation,
--     has_errors,
--     updated_at,
--     comments,
--     status_reason,
--     histories,
--     gossopka_incident_type,
--     gossopka_incident_id,
--     response_stage,
--     description,
--     asset_ips,
--     threats_array,
--     gossopka_incident_category
-- FROM incman.incidents
--     JOIN (
--         SELECT
--             id as dir_id,
--             name,
--             severity as directive_severity,
--             assigned_to as directive_assigned_to,
--             reaction_script_filename,
--             reaction_is_enabled,
--             created_at,
--             updated_at as directive_updated_at,
--             aggregate_in,
--             aggregated_min_count,
--             group_id,
--             gossopka,
--             recommendation as directive_recommendation,
--             aggregate_greedily_count,
--             gossopka_incident_type as directive_gossopka_incident_type,
--             rule,
--             deleted_at,
--             risk_score,
--             author,
--             reference_url,
--             note,
--             timestamp_override,
--             rule_edit_state,
--             produce_incident,
--             produce_aggregated_event,
--             store_keys_in_event,
--             threats_array as directive_threats_array,
--             gossopka_incident_category as directive_gossopka_incident_category
--         FROM corrdisp.directives
--     ) AS directive
--     ON directive_id = directive.dir_id
-- WHERE incman.incidents.id = 16

-- SELECT
--     COUNT(*)
-- FROM komrad_events.events
-- WHERE dateDiff('second', events.w_time, now()) <= 1

-- SELECT
--     COUNT(*) / (1 * 60 * 60)
-- FROM komrad_events.events
-- WHERE dateDiff('second', events.w_time, now()) <= 1 * 60 * 60

SELECT
    'total_events_count_by_collector_type_last_24h' AS name,
    count()  as value,
    'count events by collector type last 24 hours' AS help,
    map('collector_type', collector_type) AS labels,
    'counter' AS type
FROM events
WHERE key_time >= timestamp_sub(HOUR, 24, now())
GROUP BY collector_type

SELECT
    'total_events_count_by_collector_type_last_hour' AS name,
    count()  as value,
    'count events by collector type last hour' AS help,
    map('collector_type', collector_type) AS labels,
    'counter' AS type
FROM events
WHERE key_time >= timestamp_sub(HOUR, 1, now())
GROUP BY collector_type

SELECT * FROM  komrad_events.view_widgets FORMAT Prometheus

CREATE OR REPLACE VIEW komrad_events.view_widgets
            (
             `name` String,
             `value` Float64,
             `help` String,
             `labels` Map(String, String),
             `type` String
                ) AS
SELECT names as name, value, help, labels, type from (
                                                         SELECT
                                                             'total_events_count' AS names,
                                                             total_rows  as value,
                                                             'total IS events' AS help,
                                                             map('total_events_count', '') AS labels,
                                                             'counter' AS type
                                                         FROM system.tables
                                                         WHERE database = 'komrad_events' AND tables.name = 'events'
                                                         )
UNION ALL
SELECT
    'total_events_count_by_collector_type_last_24h' AS name,
    count()  as value,
    'count events by collector type last 24 hours' AS help,
    map('collector_type', collector_type) AS labels,
    'counter' AS type
FROM events
WHERE key_time >= timestamp_sub(HOUR, 24, now())
GROUP BY collector_type
UNION ALL
SELECT
    'total_events_count_by_collector_type_last_hour' AS name,
    count()  as value,
    'count events by collector type last hour' AS help,
    map('collector_type', collector_type) AS labels,
    'counter' AS type
FROM events
WHERE key_time >= timestamp_sub(HOUR, 1, now())
GROUP BY collector_type
UNION ALL
SELECT
    'asset_ip_count' AS name,
    count()  as value,
    'top asset_ips count last 24 hours' AS help,
    map('asset_ip',  arrayJoin(asset_ips)) AS labels,
    'counter' AS type
FROM events
WHERE key_time >= timestamp_sub(HOUR, 24, now())
GROUP BY arrayJoin(asset_ips)
ORDER BY value DESC
LIMIT 10