Drug Diversion Surveillance Without Expensive Soft…

Most healthcare organizations already have the data they need to run meaningful diversion surveillance. Automated dispensing cabinets, pharmacy information systems, and electronic medication administration records generate transaction logs that — with the right analytical logic applied — surface the patterns that distinguish diversion from normal practice variation. This playbook shows how to extract those signals using SQL against the data structures common to most enterprise pharmacy and ADC systems.

This is a practical, institution-agnostic framework. The schema used in the examples below is generic — your actual table names and column structures will differ, but the analytical logic translates directly. Before building production queries, work with your informatics team to map these patterns to your specific data sources. No commercial software license is required to implement this approach.

What Surveillance Is Looking For

Diversion surveillance examines medication movement transactions to find patterns that are inconsistent with legitimate clinical activity. The four primary transaction types that generate useful surveillance signals are:

Dispense records: What was dispensed, to which patient, by which provider, at what time, from which cabinet or pharmacy location
Waste records: What was wasted, how much, who documented the waste, who witnessed it, and whether the waste amount is consistent with the dispensed dose for the documented patient
Override events: Controlled substances removed from an ADC outside the normal dispense workflow — typically before a medication order has been verified — recorded by user and time
Count discrepancies: Differences between the expected quantity on hand (based on transaction records) and the quantity found during a physical count, by drug, cabinet, and time of count

Surveillance is not triggered by any single transaction — it is triggered by patterns. A single override event, a single waste discrepancy, or a single elevated waste ratio is rarely meaningful in isolation. The analytical goal is to identify users, shifts, or locations where these events cluster in a way that is statistically inconsistent with peer behavior or the organization's own baseline.

Schema Assumptions for the Examples Below

The queries in this playbook assume the following generic table structure. Adapt field names and joins to your actual data warehouse or ADC export format:

-- Controlled substance dispense events
dispenses(patient_id, drug, dose, ts, user_id, cabinet_id)

-- Waste documentation events
waste(patient_id, drug, amount, ts, user_id, witness_id, witnessed)

-- ADC override events (dispense outside normal order workflow)
overrides(ts, user_id, cabinet_id, drug, amount)

-- Physical count events
counts(ts, drug, cabinet_id, expected, actual, user_id)

Where ts is a timestamp column, user_id identifies the staff member, and witnessed is a boolean indicating whether a witness was documented at the time of the waste event.

Query 1: Waste Ratio Per User (90-Day Window)

This query calculates each user's waste rate — the proportion of all dispensed controlled substance units that were subsequently documented as wasted — over the prior 90 days. Users with waste rates significantly above the cohort average warrant a second look, particularly when the drug involved is a high-abuse-potential opioid.

SELECT
    d.user_id,
    d.drug,
    SUM(d.dose)                          AS total_dispensed,
    COALESCE(SUM(w.amount), 0)           AS total_wasted,
    ROUND(
        100.0 * COALESCE(SUM(w.amount), 0)
             / NULLIF(SUM(d.dose), 0),
        1
    )                                    AS waste_pct
FROM dispenses d
LEFT JOIN waste w
    ON  w.user_id   = d.user_id
    AND w.drug      = d.drug
    AND w.patient_id = d.patient_id
    AND w.ts BETWEEN d.ts AND d.ts + INTERVAL '8 hours'
WHERE d.ts >= CURRENT_DATE - INTERVAL '90 days'
  AND d.drug IN (
      'morphine','hydromorphone','fentanyl',
      'oxycodone','hydrocodone','lorazepam','midazolam'
  )
GROUP BY d.user_id, d.drug
HAVING SUM(d.dose) > 0
ORDER BY waste_pct DESC;

What to look for: Users whose waste percentage is more than two standard deviations above the mean for their peer group (same unit, same drug). A 70% waste rate when the unit average is 30% is a signal. Pair this with a review of whether the waste events had valid witnesses (witnessed = true). High waste rates combined with unwitnessed waste is a compound red flag.

Query 2: Override Frequency by User and Time of Day

Override events represent controlled substance access outside the normal order-verification workflow. Some overrides are clinically appropriate — emergency situations where waiting for order verification would harm the patient. But a pattern of overrides concentrated in specific users, particularly during off-peak hours, is a consistent diversion signal.

SELECT
    user_id,
    drug,
    EXTRACT(HOUR FROM ts)                AS hour_of_day,
    COUNT(*)                             AS override_count,
    COUNT(*) FILTER (
        WHERE EXTRACT(HOUR FROM ts) BETWEEN 0 AND 5
    )                                    AS overnight_overrides
FROM overrides
WHERE ts >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY user_id, drug, hour_of_day
ORDER BY override_count DESC;

What to look for: Users with override counts significantly higher than peers in the same role and unit. Pay particular attention to overrides clustered between midnight and 6 a.m., when supervisor presence is reduced and patient activity is lower. Override frequency that spikes at shift transitions — the first or last hour of a shift — can indicate grab-and-go behavior. Cross-reference high-override users against their waste documentation to see whether overridden quantities appear in subsequent waste records.

Query 3: Count Discrepancies by Shift

Count discrepancies — where the physical count does not match the expected quantity based on transaction records — are among the most direct indicators of diversion. Aggregating discrepancies by shift, rather than just by drug or cabinet, helps identify whether losses are concentrated in specific coverage windows.

SELECT
    cabinet_id,
    drug,
    CASE
        WHEN EXTRACT(HOUR FROM ts) BETWEEN 7  AND 14 THEN 'Day (07-15)'
        WHEN EXTRACT(HOUR FROM ts) BETWEEN 15 AND 22 THEN 'Evening (15-23)'
        ELSE                                               'Night (23-07)'
    END                                  AS shift,
    COUNT(*)                             AS count_events,
    SUM(expected - actual)               AS total_variance,
    AVG(expected - actual)               AS avg_variance_per_count
FROM counts
WHERE ts >= CURRENT_DATE - INTERVAL '90 days'
  AND expected <> actual
GROUP BY cabinet_id, drug, shift
ORDER BY total_variance DESC;

What to look for: A shift-specific pattern where variances are consistently positive (physical count lower than expected) indicates loss during that shift. A single drug with recurring positive variances in the same shift — particularly if that shift is associated with a small number of staff — narrows the investigation focus significantly. Negative variances (physical count higher than expected) can indicate documentation errors or, less commonly, diversion from a different cabinet with return to this one.

Query 4: Combined Outlier Score by User

Individual metrics are useful; combining them into a single composite signal per user is more powerful for prioritizing investigation resources. This query ranks users by a weighted combination of their waste ratio outlier status, override frequency outlier status, and discrepancy association rate.

WITH waste_stats AS (
    SELECT
        user_id,
        AVG(waste_pct)  AS avg_waste_pct,
        STDDEV(waste_pct) AS sd_waste_pct
    FROM (
        SELECT
            d.user_id,
            ROUND(100.0 * COALESCE(SUM(w.amount),0)
                       / NULLIF(SUM(d.dose),0), 1) AS waste_pct
        FROM dispenses d
        LEFT JOIN waste w
            ON w.user_id = d.user_id AND w.drug = d.drug
            AND w.ts BETWEEN d.ts AND d.ts + INTERVAL '8 hours'
        WHERE d.ts >= CURRENT_DATE - INTERVAL '90 days'
        GROUP BY d.user_id, d.drug
    ) sub
    GROUP BY user_id
),
override_stats AS (
    SELECT user_id, COUNT(*) AS override_count
    FROM overrides
    WHERE ts >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY user_id
),
discrepancy_stats AS (
    SELECT user_id, COUNT(*) AS discrepancy_count
    FROM counts
    WHERE ts >= CURRENT_DATE - INTERVAL '90 days'
      AND expected <> actual
    GROUP BY user_id
)
SELECT
    w.user_id,
    ROUND(w.avg_waste_pct, 1)            AS avg_waste_pct,
    COALESCE(o.override_count, 0)        AS override_count,
    COALESCE(d.discrepancy_count, 0)     AS discrepancy_count,
    ROUND(
        (COALESCE(w.avg_waste_pct, 0) / 100.0)
        + (COALESCE(o.override_count, 0) / 50.0)
        + (COALESCE(d.discrepancy_count, 0) / 10.0)
    , 2)                                 AS composite_risk_score
FROM waste_stats w
LEFT JOIN override_stats o  ON o.user_id = w.user_id
LEFT JOIN discrepancy_stats d ON d.user_id = w.user_id
ORDER BY composite_risk_score DESC
LIMIT 20;

What to look for: The top of this ranked list represents users whose combined behavioral profile deviates most from baseline across three independent dimensions. Users who appear in the top 10 across all three individual metrics — and therefore rank highest on the composite — should be the first candidates for a manual case review. Adjust the weighting coefficients in the composite score formula to reflect the relative importance your program assigns to each signal type.

Interpreting Results and Escalating Signals

SQL query results are signals, not conclusions. A high waste ratio, an elevated override count, and recurring shift-specific discrepancies together create a credible pattern that warrants investigation — but they do not establish diversion as a fact. Legitimate clinical differences (a provider who works primarily in high-acuity areas, a unit with a particular patient population) can produce metrics that appear aberrant without reflecting any misconduct.

The appropriate response to a credible surveillance signal is to escalate to the diversion prevention officer for a structured case review — not to confront the provider, alter access, or initiate disciplinary action. The case review should gather additional context (patient acuity, shift composition, procedure volume), look for corroborating signals (witness verification failures, camera review, patient interview), and apply the investigation protocol before any conclusions are drawn.

Document the surveillance finding, the escalation, and all subsequent review steps. That documentation becomes the evidentiary record if the investigation concludes that diversion occurred and DEA notification is required. For guidance on what constitutes a reportable significant loss, see the 15 red flags page and the analytics dashboard guide.

Operationalizing the Playbook

Running these queries manually each month is a starting point, but sustainable surveillance requires automation. Once your query logic is validated against your actual data schema, consider scheduling the queries as database jobs or integrating them into a reporting tool your compliance team already uses. A monthly review cadence is the minimum; weekly is appropriate for high-volume facilities or high-risk areas like procedural suites and ICUs.

Build a simple dashboard that displays the top outliers by user, shift, and drug each period. Even a spreadsheet that populates from a scheduled query export is sufficient to support a consistent review cycle. The goal is to make the surveillance review a routine operational activity rather than an ad hoc response to a complaint or a loss that has already become large.

For a structured framework of what metrics to track and how to present them to leadership, see the analytics dashboard guide. For a comprehensive list of behavioral and transactional red flags that complement quantitative surveillance, see the 15 red flags of drug diversion. If you're weighing whether to build this in-house or buy a packaged tool, see our drug diversion monitoring software buyer's guide for how these queries map to commercial ADC analytics, EHR surveillance, and anomaly-detection categories.

Centralizing diversion monitoring is also a session topic at the IHFDA 11th Annual Conference (Sep 28–29, 2026) — see our guide for the agenda and live registration deadline.

Drug Diversion Surveillance Without Expensive Software: A SQL + Analytics Playbook