Data export

If necessary, you can create your own reports and export data in any format — CSV, JSON, or TabSeparated — without additional tools.

Data is stored in four main logs:

qoestor.fullflow – full netflow log, default retention period: 2 hours

qoestor.clicksteam – full clickstream log, default retention period: 2 hours

qoestor.fullflow_agg – pre-aggregated netflow log, default retention period: 14 days

qoestor.clicksteam_agg – pre-aggregated clickstream log, default retention period: 14 days

The command format is as follows:

clickhouse-client --database=qoestor --query="your SQL here"

By default, data is exported in TabSeparated format.

Example: The client requested a connection log with a specific host in CSV format.

clickhouse-client --database=qoestor --query="select * from fullflow prewhere flow_start_date = '2018-10-04' where (source_ipv4 = '10.64.66.100' or destination_ipv4 = '10.64.66.100') and host = 'google.com' ORDER BY flow_start_time limit 10 format CSV"

For detailed information on ClickHouse SQL, see https://clickhouse.com/docs/ru/sql-reference/statements/select.

Exporting large QoE reports via CLI

Data export is performed using the fastor-report-cli script.

The script and the following commands must be executed on the QoE master server.

Example usage:

Create a folder for storing and editing SQL queries:

mkdir -p /tmp/reports_sql

Create a folder for storing report results:

mkdir -p /tmp/reports_results

Copy a prepared SQL query template into the folder created in step 1:

cp /var/qoestor/backend/app_bash/export/reports_cli/top_hosts_ips.sql /tmp/reports_sql

In this example, the query for TOP IP hosts — top_hosts_ips.sql — is copied.

Edit the query: set the period and add filters.

Execute the query on all nodes with the command

fastor-report-cli -r top_hosts_ips.sql -d /tmp/reports_results -w /tmp/reports_sql

where

-w /tmp/reports_sql — working directory containing your queries

-r top_hosts_ips.sql — your query file

-d /tmp/reports_results — directory where report files will be saved

To cancel a query:

Press Ctrl-Z

Check in the GUI whether the query remains in the processes list:

Warning! These queries have no time limit. If you do not verify that a query has completed, it may continue running indefinitely.