| Description |
This article describes how to verify FortiSIEM retention policy details, such as creation date, last modified time, owner ID, status, and associated objects, by querying the FortiSIEM PostgreSQL database. This is useful when retention policies do not appear correctly in the GUI or when auditing configuration changes. |
| Solution |
Symptoms:
- Retention policy details are missing or unclear in the FortiSIEM GUI.
- Impossible to determine when a retention policy was created or last modified.
- Audit retention policy ownership or activation status.
- Requests for backend validation of retention policy configuration.
Background:
- FortiSIEM stores retention policy configuration details in the ph_retention_policy table within the phoenixdb PostgreSQL database.
- Timestamps in this table are stored in epoch milliseconds, which require conversion to a human-readable format.
Solution:
To retrieve retention policy details, including creation date, last modified time, owner ID, active status, user IDs, and associated devices/events, execute the following command on the FortiSIEM Supervisor node:
psql -U phoenix phoenixdb -c "select id, TO_CHAR(TO_TIMESTAMP(creation_time / 1000), 'DD/MM/YYYY')
as creation_time, TO_CHAR(TO_TIMESTAMP(last_modified_time / 1000), 'DD/MM/YYYY HH24:MI:SS') as last_modified_time,owner_id,active,custids,description,device_display_names,devices,
event_display_names from ph_retention_policy;"
Verification:
- Confirm the creation_time and last_modified_time values align with expected configuration changes.
- Verify the active column reflects the policy status shown in the GUI.
- Ensure the owner_id matches the expected user.
- Cross-check device and event mappings with the FortiSIEM GUI.
Notes:
- Database access should be performed only by authorized administrators.
- Timestamp values are converted from epoch milliseconds to a readable format.
- This query is read-only and does not modify any configuration.
|
