Advanced Report Usage Notes: Avoid Duplicating Identical Records

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
;