Appendix / Advanced Report Usage Notes |
Console's security filtering prioritizes speed over the cost of potentially returning duplicate records. It is up to the report writer to remove duplicate records returned when querying report tables directly.
For example, a user unaware of Console internals might expect the following to always return no more than a single record:
SELECT ts.* FROM $TBL_TRANSFER_SESSIONS ts WHERE ts.session_id='ed0a9b4039bb40dfa86690ff7e1f6fa2' ;
However, depending on the user's group memberships and permissions, the above could return multiple identical records. To correct this, use SELECT DISTINCT. For example:
SELECT DISTINCT ts.* FROM $TBL_TRANSFER_SESSIONS ts WHERE ts.session_id='ed0a9b4039bb40dfa86690ff7e1f6fa2' ;
Be aware that this means you cannot directly perform aggregate computations--such as SUM, AVERAGE, or COUNT--on the reporting tables. For example, in the following, total_bytes_transferred could count some sessions multiple times:
SELECT DISTINCT ts.contact , SUM(ts.bytes_transferred) AS total_bytes_transferred FROM $TBL_TRANSFER_SESSIONS ts WHERE ... ;
Instead, first extract just the data of interest to a temporary table, then summarize from there:
# Create holding table for filtered raw data CREATE TABLE $TMP_FILTERED_TRANSFER_SESSIONS ( `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY , `contact` VARCHAR(255) , `bytes_transferred` BIGINT(20) ); # Extract relevant data (very important to include ts.id) INSERT INTO $TMP_FILTERED_TRANSFER_SESSIONS SELECT DISTINCT ts.id , ts.contact , ts.bytes_transferred FROM $TBL_TRANSFER_SESSIONS ts WHERE ts.started_at < '$REPORT_PERIOD_END' AND ( ts.stopped_at >= '$REPORT_PERIOD_START' OR ts.stopped_at IS NULL ); # Summarize by contact CREATE TABLE $FINAL_RESULT_TABLE SELECT fts.contact , SUM(fts.bytes_transferred) AS total_bytes_transferred FROM $TMP_FILTERED_TRANSFER_SESSIONS fts GROUP BY fts.contact ORDER BY fts.contact ;