FortiSIEM Working with the Report Server

Leave a reply
Working with the Report Server

This section contains information on AccelOps Report Server architecture, viewing and querying CMDB and Event data in contained in the Report Server databases, and database maintenance.

Report Server Architecture: phoenixdb and reportdb

Working with CMDB Data in AccelOps Report Server

Viewing phoenixdb Organization

Querying Incident Data in AccelOps Report Server

Reference: Attribute Columns in the ph_incident_view Table

Sample Incident Queries

Querying Other CMDB Tables in AccelOps Report Server

Querying Device Vendor and Model Distribution for Discovered Devices Querying Discovered Devices

Working with Event Data in AccelOps Report Server

Viewing reportdb Organization

Syncing an AccelOps Report with Report Server

Deleting a Report from AccelOps Report Server

Modifying an Existing Report in AccelOps Report Server

Report Server Architecture: phoenixdb and reportdb

AccelOps Report Server contains two databases:

phoenixdb

This database contains the entire AccelOps CMDB and is populated via asynchronous PostgreSQL replication (slony) in near-real time.

reportdb

This database contains the results of event queries.

Topics in this section describe how to view the tables in these databases, and how those tables are organized. For viewing the tables, we recommend using the pgAdmin PostgreSQL database utility, which you can download from the pgAdmin website.

Working with CMDB Data in AccelOps Report Server

Data from the AccelOps CMDB database is populated to the AccelOps Report Server and stored in the Report Server phoenixdb. This section contains information on how to view the organization of phoenixdb, and write queries against the data it contains.

Viewing phoenixdb Organization

Querying Incident Data in AccelOps Report Server

Reference: Attribute Columns in the ph_incident_view Table Sample Incident Queries

Querying Other CMDB Tables in AccelOps Report Server

Querying Device Vendor and Model Distribution for Discovered Devices Querying Discovered Devices

Viewing phoenixdb Organization

This database contains the contents of the entire AccelOps CMDB database, including incidents.

  1. In the pgAdmin utility, go to File > Add Server.
  2. In the New Server Registration dialog, enter connection details for AccelOps Report Server.

For Maintenance DB, select phoenixdb.

For Username and Password, use the read-only user name and password that you created when you provisioned the Report Server.

  1. Click OK.

When the connection to the AccelOps Report Server is established, phoenixdb will load in the Object browser. There are approximately

197 tables in phoenixdb, which are replicated from the AccelOps cluster.

  1. Select a table to view, then right-click to open the Options
  2. In the Options menu, select View Data, and then select an option for which rows you want to view.

For example, to view the contents of the ph_device table, which contains CMDB information about discovered devices, you would select and then right click on ph_device, then select View Data > View All Rows.

You can also use this method to examine Views and other objects in the phoenixdb database.

Querying Incident Data in AccelOps Report Server

There are two ways to look at the incident data inside AccelOps Report Server:

Incident Tables (ph_incident and ph_incident_detail)

Contains the incidents

Incident View (ph_incident_view)

This is a database view that adds other context to the incident tables by joining with other tables in the database. Added information includes location and business service. Some information is parsed out for easier query, such as host names and IP address fields from incident_source, and incident_target fields in ph_incident are parsed out as separate fields in ph_incident_view.

This topic describes how to view the data contained in Incident View.

  1. Follow the instructions in Viewing phoenixdb Organization to access the phoenixdb database in AccelOps Report Server.
  2. Go to Views > ph_incident_view > Columns to view the table columns.
  3. Go to Views > ph_incident_view > View Data > View Last 100 Rows to view the incidents.

 

Reference: Attribute Columns in the ph_incident_view Table

Column Name Format Description
incident_id integer Unique id for an incident
cust_org_id integer Customer Id (for AO-SP)
first_seen_time integer The time when the incident was first seen. The format is UNIX time but with milliseconds granularity. It is defined as the number of milliseconds that have elapsed since 00:00:00 Coordinated Universal Time

(UTC), Thursday, 1 January 1970    
last_seen_time integer The time when the incident was last seen. The format is UNIX time but with milliseconds granularity. It is defined as the number of milliseconds that have elapsed since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970
incident_et string Incident event type id e.g. PH_RULE_SERVER_HW_CRITICAL
incident_status integer 0: Active 1: Auto Cleared 2: Manually Cleared 3: System Cleared
incident_count integer The number of times this exact incident (with the same parameters: source, destination etc has happened)
biz_name string  Associated business service name
severity integer  Numerical severity of the incident – range 0-10
severity_cat string  Incident severity category: 0-4: LOW, 5-8: MEDIUM and 9-10: HIGH
orig_device_ip string  IP address of the device that reported the incident
ph_incident_category  string  Category of infrastructure affected by this incident: possible valies: Network, Server, Storage, Virtualization, Application, Internal
incident_src string Incident Source string formatted as a list of <Attribute>:Value; e.g.

srcIpAddr:10.1.1.1,srcName:JoeLaptop
src_ip_addr string Source IP parsed out from incident_src field
src_name string Source Name parsed out from incident_src field
src_device_location string (Geo) Location display name string for the object specified in incident_src
src_country string (Geo) Country name string for the object specified in incident_src
src_state string (Geo) State name for the object specified in incident_src
src_building string (Geo) Building name for the object specified in incident_src
src_floor string (Geo) Floor for the object specified in incident_src
src_latitude double (Geo) Latitude for the object specified in incident_src
src_longitude double (Geo) Latitude for the object specified in incident_src
incident_target string Incident Destination string formatted as a list of <Attribute>:Value; e.g.

“destIpAddr:10.1.1.1,destName:JoeLaptop” or “hostIpAddr:10.1.1.1,hostName:JoeLaptop”
dest_ip_addr string Destination IP parsed out from incident_target field
dest_name string Destination Name parsed out from incident_target field
dest_device_location string (Geo) Location display name string for the object specified in incident_target
dest_country string (Geo) Country name string for the object specified in incident_target
dest_state string (Geo) State name for the object specified in incident_target
dest_building string (Geo) Building name for the object specified in incident_target
dest_floor string (Geo) Floor for the object specified in incident_target
dest_latitude double (Geo) Latitude for the object specified in incident_target

 

dest_longitude double (Geo) Longitude for the object specified in incident_target
host_ip_addr string Host IP address parsed out from incident_target field
host_name string Host Name parsed out from incident_target field
host_device_location string (Geo) Location display name string for the object specified in incident_target – populated if incident_target contains hostIpAddr
host_country string (Geo) Country name string for the object specified in incident_target – populated if incident_target contains hostIpAddr
host_state string (Geo) State name for the object specified in incident_target – populated if incident_target contains hostIpAddr
host_building string (Geo) Building name for the object specified in incident_target – populated if incident_target contains hostIpAddr
host_floor string (Geo) Floor for the object specified in incident_target – populated if incident_target contains hostIpAddr
host_latitude double (Geo) Latitude for the object specified in incident_target – populated if incident_target contains hostIpAddr
host_longitude double (Geo) Longitude for the object specified in incident_target – populated if incident_target contains hostIpAddr
vm_name string VM Name if incident involves a Virtual machine – populated if incident_target contains vmName
user_attr string User name if incident involves user, i.e. incident_target contains user
target_user_attr string Target user name if incident involves user, i.e. incident_target contains targetUser
ldap_domain string Domain if incident involves user, i.e. incident_target contains domain
computer string Computer name incident_target contains computer
target_computer string Target Computer name incident_target contains targetComputer
incident_details string Incident Details containing evidence on why the incident triggered e.g. Triggered Event Count = 90 or AVG(CPUUtil) = 90 etc

Sample Incident Queries

Show Incident Categories with Severity and Frequency Occurrence Show Incident Location

Show Incident Categories with Severity and Frequency Occurrence

This query will show which parts of the infrastructure are triggering events.

  1. Follow the instructions in Viewing phoenixdb Organization to access the phoenixdb in AccelOps Report Server.
  2. Under Views, select ph_incident_view.
  3. In pgAdmin, click on the SQL icon in the menu bar to open the SQL query window.
  4. Enter this SQL query:

 

SELECTph_incident category, incident_et, severity_cat, src_ip_addr, host_name, FROMph_incident_view

GROUPBYph_incident category, incident_et, severity_cat, src_ip_addr, host_name

ORDERBYCOUNT(*) DESC;

COUNT(*)

  1. When the query executes, you will see a list of matching incidents in the Output Pane. Show Incident Location
  2. Follow the instructions in Viewing phoenixdb Organization to access the phoenixdb in Accelops Report Server.
  3. Under Views, select ph_incident_view.
  4. In pgAdmin, click on the SQL icon in the menu bar to open the SQL query window.
  5. Enter this SQL query:

 

COUNT(*)

SELECT host_device_location, severity_cat, ph_incident_category, FROM ph_incident_view

GROUP BY host_device_location, ph_incident_category, severity_cat

ORDER BY host_device_location ASC, severity_cat ASC COUNT

(*) DESC;

  1. When the query executes, you will see a list of incidents and their locations in the Output Pane.

Querying Other CMDB Tables in AccelOps Report Server

Querying Device Vendor and Model Distribution for Discovered Devices Querying Discovered Devices

Querying Device Vendor and Model Distribution for Discovered Devices

Querying Discovered Devices

Working with Event Data in AccelOps Report Server

Data from the AccelOps EventDB database is populated to the AccelOps Report Server and stored in the Report Server reportdb. This section contains information on how to view the organization of reportdb, and write queries against the data it contains.

Viewing reportdb Organization

Syncing an AccelOps Report with Report Server

Deleting a Report from AccelOps Report Server

Modifying an Existing Report in AccelOps Report Server

Viewing reportdb Organization

This database contains the reports that are synched from the AccelOps cluster.

  1. In the pgAdmin utility, go to File > Add Server.
  2. In the New Server Registration dialog, enter connection details for AccelOps Report Server.

For Maintenance DB, select reportdb.

For the Port enter 30000 (default port used for the reported).

For Username and Password, use the read-only user name and password that you created when you provisioned the Report Server.

  1. Click OK.

When the connection to the Report Server is established, reports will load in the Object browser.

  1. Select a table to view, then right-click to open the Options
  2. In the Options menu, select View Data, and then select an option for which rows you want to view.

Syncing an AccelOps Report with Report Server

  1. Log in to AccelOps.
  2. Go to Analytics > Reports.
  3. Select a report.

Any reports with a Sync checkbox can be synced. Run the report to make sure it contains some data.

  1. For each report you want to sync, select the Sync
  2. Click OK.
  3. After several minutes, follow the instructions in Viewing reportdb Organization to view the reportdb database.
  4. Under Tables, you should now see the synced reports.

Table Structure for Synced Reports

When you sync an AccelOps report to AccelOps Report Server, two pairs of tables are created in reportdb, one pair for each organization in the case of AO-SP. For each organization, multiple tables are created:

  1. A parent table containing data for all months: the table name is of the form <Report Name>_<ID>_<custId>
  2. A child table for the current month: <Report Name>_<ID>_<custId>_<yYYYYmMM> where YYYY is the year and MM is the month.

Queries should be written using the parent table. To see data in the parent table, follow the instructions in  Viewing reportdb Organization . The re portdb database fields are generated from the display fields in AccelOps report definitions. Only the field report_time is added to the Report Server table definitions to capture the time when the particular report is generated. For example, if you synced the report Network Devices by CPU, Memory, you would see these fields:

Field Description
report_time UNIX time at which the report is generated. Unix time (or POSIX time or Epoch time) is a system for describing instants in time, defined as the number of seconds that have elapsed since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970 not counting leap seconds.
hostName Host Name of the device for which CPU and memory are being measured
hostIpAddr Access IP of the device for which CPU and memory are being measured
AVG(cpuUtil) Average of all the CPU utilization metrics within the last 5 minutes ending with report_time
AVG(memUtil) Average of all the CPU utilization metrics within the last 5 minutes ending with report_time

Deleting a Report from AccelOps Report Server

  1. Log in to AccelOps.
  2. In Analytics > Reports > Synced Reports, select the report you want to delete.
  3. In the Sync Details dialog, clear the Sync option for the report, and then click OK.

The report will no longer be synced with Report Server. You can verify this by making sure the Sync option is not selected for the report on the Analytics > Reports > Synced Reports page. You can now delete the report from AccelOps Report Server.

  1. Log in to AccelOps Report Server via SSH and navigate to the directory /opt/phoenix/deployment/jumpbox.
  2. Run the py command, along with the table name and date as arguments, to delete the report.
  3. After you have deleted the table containing the report information, you will need to delete the parent table, which will now be empty of content, using the same py command.

Modifying an Existing Report in AccelOps Report Server

Suppose a system report is synced and exported to AccelOps Report Server. When you modify that report in AccelOps, you must rename it, at which point it becomes a user report. When you then sync that report for Accelops Report Server, a new table is created on the AccelOps Report Server.

Suppose now that you have a user-defined report that is already synced to the AccelOps Report Server, but you modify it inline in AccelOps, which means that you have changed the report conditions without changing the report name. This will cause a change in the table, but a new table will not be created. Here are some examples of inline modifications, and how they affect the structure of the table as well as the data collected in the table:

Modification Effect
GROUP BY field added The corresponding table has the new GROUP BY field, but only newer data populates the field
GROUP BY field removed There is no change in the corresponding table, and newer data does not populate the field
GROUP BY field changed For example, the field srclpAddr is changed to destlpAddr. Both fields are retained, but newer data populates d estlpAddr.
Aggregated fields added The corresponding table has the new field, but only newer data populate that field
Aggregated field removed There is no change in the corresponding table, and newer data does not populate the field
Aggregated Field

Changed

 For example, AVG(cpUutil) is changed to MAX(cpuUtil). Both fields are retained, but newer data populates MA X(cpuUtil).

 

 

This entry was posted in Administration Guides, FortiSIEM on by .

About Mike

Michael Pruett, CISSP has a wide range of cyber-security and network engineering expertise. The plethora of vendors that resell hardware but have zero engineering knowledge resulting in the wrong hardware or configuration being deployed is a major pet peeve of Michael's. This site was started in an effort to spread information while providing the option of quality consulting services at a much lower price than Fortinet Professional Services. Owns PacketLlama.Com (Fortinet Hardware Sales) and Office Of The CISO, LLC (Cybersecurity consulting firm).

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.