FortiSIEM Creating and Managing Workbooks

Leave a reply
Creating and Managing Workbooks

This section contains information on using Visual Analytics Desktop to create sheets and workbooks that are based on AccelOps reports, and then publishing them for others to use.

Viewing Workbooks

Creating and Publishing Workbooks

Creating a Single Sheet Workbook

Creating a Multiple Sheet Workbook

Using AccelOps Workbooks with Tableau Visual Analytics Desktop and Server Adding Users to Workbooks

Viewing Workbooks
  1. Log in to Visual Analytics Server.
  2. Click the Content tab and select Workbooks.
  3. Click on a workbook.

The workbook along with the various worksheets are displayed.

  1. Select a workbook or worksheet.
  2. You will be prompted for credentials that will allow the workbook or worksheet to access database information. Enter the Admin credential that you used to set up Accelops Report Server and click OK.
  3. When your credential is accepted, the chart associated with the selected workbook or worksheet will be displayed.
Creating and Publishing Workbooks

Workbooks are collections of AccelOps reports that have been synced to AccelOps Report Server, and which are then the basis for charts and dashboards that can be published to Visual Analytics Server for access by other users. Information in this section describes how to create single and multiple sheets of report information, and then make them accessible to other users.

Creating a Single Sheet Workbook

Creating a Multiple Sheet Workbook

Using AccelOps Workbooks with Tableau Visual Analytics Desktop and Server

Creating a Single Sheet Workbook

These instructions demonstrate how to create a single-sheet workbook that will chart the CPU and memory utilization trend for various servers. This example uses the Servers by CPU, Memory report and its associated table, but any report with a table in the reportdb database can also be used. The Tableau Desktop online Help also contains extensive information about building sheets and workbooks with the Tableau Desktop editor, which powers the AccelOps Visual Analytics Desktop.

Prerequisites

Procedure

Create the Sheet

Create the Workbook

Publish the Workbook

Prerequisites

Follow the instructions in Syncing an AccelOps Report with Report Server to sync the report you want to use for your worksheet.

You will need to know the name of the parent table for your synced report. Follow the instructions in Viewing reportdb Organization to find the table that corresponds to your report.

Procedure

Create the Sheet

  1. Launch Tableau Visual Analytics Desktop.
  2. Connect to AccelOps Report Server with the Username and Password that you used during Report Server installation. For Database, enter reportdb. For Port, enter 30000.

Connecting to Port 30000

It’s important to make sure you enter the correct port to connect to the reportdb database. If you leave this option blank you will connect to the default PostgreSQL database port, which will connect you with phoenixdb instead of reportdb. For more information about the databases contained in Report Server, see Report Server Architecture: phoenixdb and reportdb.

  1. Under Tables, select the parent table for your report.

For the steps following, we will use the Servers by CPU, Memory table and its associated columns.

  1. Drag the table to the View pane and click Update Now.

The data in the table will load into the pane below. Note that the table columns match closely to the Report Display Columns in AccelOps.

  1. For Connection, select Live.
  2. Click Go to Worksheet.

In the worksheet view you will see that a set of Dimensions and Measures are populated for the table.

  1. Under Measures, select Report Time and drag it to the Dimensions section to create Report Time as a calculated measurement.
  2. Under Dimensions, right-click on Report Time to edit the calculation formula and convert it to a human-readable format from UNIX time. The formula should look like DATEADD(‘second’,INT([Report Time]),#1969-12-31 16:00:00#)
  3. Drag Report Time from Dimensions to Columns.
  4. Under Columns, right-click on Report Time and select Exact Date.

You should now see dates and time increments in your chart as the X-axis.

  1. Under Measures, select and drag AVG(cpuUtil) and AVG(memUtil) to Rows.
  2. Set the aggregation of both AVG(cpuUtil) and AVG(memUtil) to AVG. For example, AVG(AVG(cpuUtil)) and AVG(AVG(memUtil)).

You should now see both measures on the Y-axis of your chart.

  1. Under Dimensions, drag Host Name to the Color section under Marks. Each host will be assigned a color and added to the chart.
  2. Change the chart display name for AVG(cpuUtil) and AVG(memUtil) by clicking on each in the Y-axis to launch the Edit Y-Axis dialog.

You can now edit the Title and Range, as well as other attributes, for each measure.

  1. Under Data, click on the data source to open the Options menu, then click Refresh.
  2. Rename the sheet by clicking on the data source to open the Options menu, then select Rename and enter a new name.

Your sheet is now complete. Hover your mouse over a trend line to view information about a specific host.

Create the Workbook

  1. Click the Dashboard tab on the bottom of the Sheet editor to open the Dashboard
  2. Under Dashboard, select an appropriate Size and screen resolution.
  3. Under Dashboard, select the sheet and drag it into the display pane.
  4. Open the Dashboard options menu and select Rename.

Change the name of the dashboard from Server CPU/Memory Trend to Server Performance.

  1. In the File menu, select Save. Publish the Workbook
  2. In the Server menu, select Sign In…
  3. Enter the IP address and port number for the Visual Analytics Server.
  4. Enter the Username and Password for the Visual Analytics Server admin user, and then click Sign In.
  5. In the Server menu, select Publish Workbook.
  6. Enter attributes for the workbook, such the associated Project, Name, View Permissions, and Views to Share.

See Adding Users to Workbooks for more information about user permissions for workbooks.

  1. Click Publish.

Creating a Multiple Sheet Workbook

These instructions demonstrate how to create a multiple-sheet workbook that will contain a set of charts related to Network Health. This example uses the Network Devices by Ping RTT, Network Interfaces By Utilization, and Network Devices By CPU, Memory reports, but any report with an associated table and views in the reportdb database could be used. The Tableau Desktop online Help also contains extensive information about building sheets and workbooks with the Tableau Desktop editor, which powers the AccelOps Visual Analytics Desktop.

Prerequisites

Procedure

Create a View

Create a Workbook that Uses the View

Create the Workbook

Publish the Workbook

Prerequisites

Follow the instructions in Syncing an AccelOps Report with Report Server to sync the reports you want to use for your worksheet. You will need to know the name of the parent table for your synced reports. Follow the instructions in Viewing reportdb Organization to find the table that corresponds to your report.

Procedure

Create a View

Each report you want to include in your workbook corresponds to a table in the AccelOps reportdb. These tables need to be joined to cross-link the information that will appear in your workbook. In the case of a Network Health workbook that includes the sheets Network Devices by Ping RTT, Network Interfaces By Utilization, and Network Devices By CPU, Memory, the joining keys are host name and time.

  1. Follow the instructions in Viewing reportdb Organization to find the parent tables for the reports you want to join.

For each report there is one parent table and multiple child tables containing data for a particular month.

  1. Create a SQL statement in pgAdmin to join the tables.

In this example data is captured for one day. This enables quick generation of the data visualization.

SELECT cpu.report_time, cpu.”hostName”, cpu.”hostIpAddr”, cpu.”AVG(cpuUtil)”, cpu.”AVG(memUtil)”,        uptime.”SUM(sysDownTime)”, uptime.”AVG(avgDurationMSec)”, uptime.”LAST(sysUpTime)”,        uptime.”SUM(pollIntv)”, util.”intfName”, util.”intfAlias”,        util.”AVG(inIntfUtil)” AS “totalAvgInIntfUtil”, util.”AVG(outIntfUtil)” AS “totalAvgOutIntfUtil”,        util.”AVG(recvBitsPerSec)” AS “totalAvgRecvBitsPerSec”,        util.”AVG(sentBitsPerSec)” AS “totalAvgSentBitsPerSec”,        util.”AVG(outQLen)”, util.”AVG(intfSpeed64)”

FROM “Network Devices By CPU, Memory_1278492569_1” cpu,

“Network Devices by Ping RTT_2021056235_1” uptime,

“Network Interfaces By Utilization_382117475_1″ util

 

WHERE ((cpu.report_time * 1000)::double precision * ’00:00:00.001′::interval + ‘1969-12-31 16:00:00-08’::timestamp with time zone) >= (now() – 1::double precision * ‘1 day’::interval)      AND ((uptime.report_time * 1000)::double precision * ’00:00:00.001′::interval + ‘1969-12-31 16:00:00-08’::timestamp with time zone) >= (now() – 1::double precision * ‘1 day’::interval)      AND ((util.report_time * 1000)::double precision * ’00:00:00.001′::interval + ‘1969-12-31 16:00:00-08’::timestamp with time zone) >= (now() – 1::double precision * ‘1 day’::interval)      AND cpu.report_time = uptime.report_time AND cpu.”hostName” = uptime.”hostName” AND uptime.report_time = util.report_time AND uptime.”hostName” = util.”hostName”;

 

  1. Click the Play icon in pgAdmin to execute the query.

Make sure the output pane contains data that is the result of the query execution.

  1. Modify the SQL statement to create a view.

Add this command at the top of the SQL statement:

Add this command at the bottom of the SQL statement:

Your complete SQL statement should look like this:

CREATE OR REPLACE VIEW ph_network_health_view AS

SELECT cpu.report_time, cpu.”hostName”, cpu.”hostIpAddr”, cpu.”AVG(cpuUtil)”, cpu.”AVG(memUtil)”,        uptime.”SUM(sysDownTime)”, uptime.”AVG(avgDurationMSec)”, uptime.”LAST(sysUpTime)”,        uptime.”SUM(pollIntv)”, util.”intfName”, util.”intfAlias”,        util.”AVG(inIntfUtil)” AS “totalAvgInIntfUtil”, util.”AVG(outIntfUtil)” AS “totalAvgOutIntfUtil”,        util.”AVG(recvBitsPerSec)” AS “totalAvgRecvBitsPerSec”,        util.”AVG(sentBitsPerSec)” AS “totalAvgSentBitsPerSec”,        util.”AVG(outQLen)”, util.”AVG(intfSpeed64)”

FROM “Network Devices By CPU, Memory_1278492569_1” cpu,

“Network Devices by Ping RTT_2021056235_1” uptime,

“Network Interfaces By Utilization_382117475_1″ util

 

WHERE ((cpu.report_time * 1000)::double precision * ’00:00:00.001′::interval + ‘1969-12-31 16:00:00-08’::timestamp with time zone) >= (now() – 1::double precision * ‘1 day’::interval)      AND ((uptime.report_time * 1000)::double precision * ’00:00:00.001′::interval + ‘1969-12-31 16:00:00-08’::timestamp with time zone) >= (now() – 1::double precision * ‘1 day’::interval)      AND ((util.report_time * 1000)::double precision * ’00:00:00.001′::interval + ‘1969-12-31 16:00:00-08’::timestamp with time zone) >= (now() – 1::double precision * ‘1 day’::interval)      AND cpu.report_time = uptime.report_time AND cpu.”hostName” = uptime.”hostName” AND uptime.report_time = util.report_time AND uptime.”hostName” = util.”hostName”;

grant select on ph_network_health_view TO public;

 

  1. In pgAdmin, click the Play icon to execute the statement.
  2. Using pgAdmin, navigate to the Views and make sure the ph_network_health_view has been created.
  3. Right-click on ph_network_health_view to open the Options menu, then select View Data > View Last 100 Rows to make sure the view contains data.

Create a Workbook that Uses the View

  1. Launch AccelOps Visual Analytics Desktop.
  2. Connect to AccelOps Report Server with the Username and Password that you used during Report Server installation. For Database, enter reportdb. For Port, enter 30000.
  3. Under Tables, enter the name of the view you created in the search box to locate the view.

 

  1. Drag the view into the Join pane and click Update Now. The data in the view will load into the pane below.

 

  1. For Connection, select Live.
  2. Click Go to Worksheet.

In the worksheet view you will see that a set of Dimensions and Measures are populated for the view.

An example worksheet showing CPU and Memory Utilization with several Dimensions and Measures populated from the original table.

 

  1. For each report in your workbook you can now create an individual sheet, as described in Creating a Single Sheet Workbook.

Create the Workbook

  1. Click the Dashboard tab on the bottom of the Sheet editor to open the Dashboard
  2. Drag each sheet you’ve created into the Join

 

An example of three worksheets loaded into the Dashboard Join pane.

  1. Under Dashboard, select an appropriate Size and screen resolution.
  2. Open the Dashboard Options menu and select Rename.
  3. In the File menu, select Save. Publish the Workbook
  4. In the Server menu, select Sign In…
  5. Enter the IP address and port number for the Visual Analytics Server.
  6. Enter the Username and Password for the Visual Analytics Server admin user, and then click Sign In.
  7. In the Server menu, select Publish Workbook.
  8. Enter attributes for the workbook, such the associated Project, Name, View Permissions, and Views to Share.

See Adding Users to Workbooks for more information about user permissions for workbooks.

 

  1. Click Publish.

 

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.