Backend postgresql queries for incident investigation
DescriptionA list of useful PostgreSQL queries to investigate FortiSIEM incidents, including incident counts, detailed information, clearance status, noisy incident types, and incidents without matching rule records.ScopeFortiSIEM.SolutionExecute the following commands on the Supervisor to investigate incidents in the phoenixdb database.Count Incidents by Time Period:Replace 10 days with the required period, such as 30 days or 45 days.psql -U phoenix -d phoenixdb -c "SELECT count(*) AS incident_count FROM ph_incident WHERE creation_time > (extract(epoch FROM now() - interval '10 days') * 1000);"Retrieve Incident Details:psql -U phoenix -d phoenixdb -c "SELECT id, incident_title, incident_et, severity_cat, incident_detail, incident_detail_json, to_timestamp(creation_time/1000) AS created_at FROM ph_incident WHERE creation_time > (extract(epoch FROM now() - interval '10 days') * 1000) ORDER BY creation_time DESC LIMIT 50;"Save the output to a file by appending > /tmp/incidents_l
