Base de données interne

Log Sources Stats

psql -U qradar -c "COPY (SELECT sensordevice.devicename AS Name, sensordevice.hostname AS Identifier, sensordevice.devicedescription AS Description, to_char(to_timestamp(sensordevice.creationdate/1000), 'DD-MM-YYYY HH24:MI:SS') AS Creation, to_char(to_timestamp(sensordevice.timestamp_last_seen/1000), 'DD-MM-YYYY HH24:MI:SS') AS LastSeen, sensordevicetype.devicetypename AS Type, sensordevice.deviceenabled AS Activated, sensordevice.autodiscovered AS Autodiscovered, sensordevice.eps60s AS eps, sensordevice.coalesce_events AS Coalesce, sensorprotocolstatusseverity.description AS Status FROM sensordevice LEFT JOIN sensordevicetype ON sensordevice.devicetypeid = sensordevicetype.id LEFT JOIN sensorprotocolstatus ON sensordevice.status_record = sensorprotocolstatus.id LEFT JOIN sensorprotocolstatusseverity ON sensorprotocolstatus.current_state = sensorprotocolstatusseverity.severity) TO '/tmp/export-all-logsources-status.csv' WITH CSV DELIMITER ',' HEADER;"

Log Sources Dupes TODO: RENAME

psql -U qradar -c "SELECT id FROM sensordevice WHERE devicename IN (SELECT devicename FROM sensordevice GROUP BY devicename HAVING COUNT(sensordevice.id) > 1) AND deviceenabled = False;"

Log Sources Disable all Coalescing

psql -U qradar -c "UPDATE sensordevice SET coalesce_events = false WHERE id IN (SELECT id FROM sensordevice WHERE coalesce_events = true AND deviceenabled = true);"

WinCollect Sources

psql -U qradar -c "COPY (SELECT ale_client.name AS WinCollect, ale_client.hostname AS WinCollectHostname, ale_client.version AS WinCollectVersion, ale_client.os_version AS WinCollectOSVersion, sensordevice.hostname AS LogSourceHostname, sensordevice.devicename AS LogSourceDevice, sensordevice.devicedescription AS LogSourceDescription FROM ale_sensor_device_mapping LEFT JOIN ale_client ON ale_sensor_device_mapping.ale_client_id = ale_client.id LEFT JOIN sensordevice ON ale_sensor_device_mapping.sensor_device_id = sensordevice.id) TO '/tmp/export-wincollect-logsources.csv' WITH CSV DELIMITER ',' HEADER;"

Rules / Searches dependancies

psql -U qradar -c "SELECT id, rule_data as \"data\" FROM custom_rule WHERE CAST(rule_data AS varchar) ILIKE '%<SEARCH TERM>%' UNION ALL SELECT id, query as \"data\" FROM saved_search WHERE CAST(query AS varchar) ILIKE '%<SEARCH TERM>%'" 

Propriétés non visibles / non regex

psql -U qradar -c "SELECT id, propertyname FROM ariel_aql_property WHERE id NOT IN (SELECT ap_id FROM ariel_property_expression) UNION ALL SELECT id, propertyname FROM ariel_regex_property WHERE id NOT IN (SELECT ap_id FROM ariel_property_expression) UNION ALL SELECT id, propertyname FROM ariel_calculated_property WHERE id NOT IN (SELECT ap_id FROM ariel_property_expression);"

Custom property from regex

escape \ with 3x\ : \d = \\\\d

psql -U qradar -c "SELECT ariel_property_expression.id, ariel_regex_property.propertyname, ariel_property_expression.regex FROM ariel_property_expression LEFT JOIN ariel_regex_property ON ariel_regex_property.id = ariel_property_expression.ap_id WHERE regex ILIKE '%<SEARCH TERM>%';"

Custom property from name

psql -U qradar -c "SELECT ariel_property_expression.id, ariel_regex_property.propertyname, ariel_property_expression.regex FROM ariel_property_expression LEFT JOIN ariel_regex_property ON ariel_regex_property.id = ariel_property_expression.ap_id WHERE ariel_regex_property.propertyname ILIKE '%<SEARCH TERM>%';"

Get PostgreSQL database size

psql -U qradar -c "SELECT current_database();"
psql -U qradar -c "SELECT pg_size_pretty( pg_database_size('qradar') );"