Find who deleted or cancelled a Test, Sample, or Batch

Applies to: All QM STARLIMS environments with audit trail enabled — SQL Server and Oracle

Use the SQL queries in this article to identify which user deleted or cancelled a batch, sample, or test, and when the action occurred. All queries run against the AUDITTRL table.

Prerequisites

• Read access to the STARLIMS application database
• Audit trail must be enabled — deletions are only recorded if auditing was active at the time of the event

Important:  Out of the box, STARLIMS does not record audit trail events for actions taken before a batch is started. Only post-start deletion events are captured in AUDITTRL.

Query 1 — Find deleted batches

Returns all delete events recorded against the Batches table:

SQL Server (MSSQL)

SELECT *
FROM   AUDITTRL
WHERE  EVENT_TYPE = 'Delete'
AND    TABLENAME  = 'Batches'

Oracle

SELECT *
FROM   AUDITTRL
WHERE  UPPER(EVENT_TYPE) = 'DELETE'
AND    UPPER(TABLENAME)  = 'BATCHES'

Note:  Oracle string comparisons are case-sensitive. UPPER() is applied to both the column and the literal to guarantee a match regardless of how STARLIMS stored the value.

To narrow results to a specific date range, use the platform-appropriate syntax:

SQL Server (MSSQL)

SELECT *
FROM   AUDITTRL
WHERE  EVENT_TYPE = 'Delete'
AND    TABLENAME  = 'Batches'
AND    AUDIT_DT  >= '2026-01-01'
AND    AUDIT_DT  <  '2026-01-01'

Oracle

SELECT *

FROM   AUDITTRL
WHERE  UPPER(EVENT_TYPE) = 'DELETE'
AND    UPPER(TABLENAME)  = 'BATCHES'
AND    AUDIT_DT  >= TO_DATE('2026-01-01', 'YYYY-MM-DD')
AND    AUDIT_DT  <  TO_DATE('2026-01-01', 'YYYY-MM-DD')

Note:  Oracle string comparisons are case-sensitive. UPPER() is applied to both the column and the literal to guarantee a match regardless of how STARLIMS stored the value.

Query 2 — Find deletions and cancellations across Batches, Samples, and Tests

Includes both hard deletes and cancellation events (CancelBatch, CancelTest, CancelSample, CancelFolder):

SQL Server (MSSQL)

SELECT *
FROM   AUDITTRL
WHERE  (    EVENT_TYPE = 'Delete'
        AND TABLENAME  = 'Batches')
   OR  EVENTCODE IN (
       'CancelTest',
       'CancelBatch',
       'CancelFolder',
       'CancelSample'
       )

Oracle

SELECT *
FROM   AUDITTRL
WHERE  (    UPPER(EVENT_TYPE) = 'DELETE'
        AND UPPER(TABLENAME)  = 'BATCHES')
   OR  UPPER(EVENTCODE) IN (
       'CANCELTEST',
       'CANCELBATCH',
       'CANCELFOLDER',
       'CANCELSAMPLE'
       )

Note:  Oracle string comparisons are case-sensitive. UPPER() is applied to both the column and the literal to guarantee a match regardless of how STARLIMS stored the value.

Key fields in AUDITTRL

Joining to related tables

Use ORIGINAL_ORIGREC to link the audit record back to the source data. Because the batch or sample has been deleted, the record no longer exists in the live table — use ROW_DATA (XML) to recover field values, or join to ORDERS/ORDTASK/RESULTS only if the parent records still exist.

Example — retrieve the username and timestamp for each deleted batch:

SQL Server (MSSQL)

SELECT APP_USERNAME,
       AUDIT_DT,
       ORIGINAL_ORIGREC  AS DeletedBatchOrigrec,
       ROW_DATA          AS DeletedRowXML
FROM   AUDITTRL
WHERE  EVENT_TYPE = 'Delete'
AND    TABLENAME  = 'Batches'
ORDER  BY AUDIT_DT DESC

Oracle

SELECT APP_USERNAME,
       AUDIT_DT,
       ORIGINAL_ORIGREC  AS DeletedBatchOrigrec,
       ROW_DATA          AS DeletedRowXML
FROM   AUDITTRL
WHERE  UPPER(EVENT_TYPE) = 'DELETE'
AND    UPPER(TABLENAME)  = 'BATCHES'
ORDER  BY AUDIT_DT DESC

Note:  Oracle string comparisons are case-sensitive. UPPER() is applied to both the column and the literal to guarantee a match regardless of how STARLIMS stored the value.

Note:  The ROW_DATA XML column contains all field values from the deleted row. Parse it using XML functions in SQL Server (e.g. CAST(ROW_DATA AS XML).value(...)) or XMLType in Oracle to extract specific fields such as FOLDERNO or ORDNO without needing a JOIN.

Pending verification

Important:  The exact casing of EVENT_TYPE, TABLENAME, and EVENTCODE values stored in AUDITTRL has not been verified against a live system. Run the diagnostic queries below before publishing to confirm the correct values for your installation:

-- Check actual EVENT_TYPE values stored
SELECT DISTINCT EVENT_TYPE FROM AUDITTRL ORDER BY EVENT_TYPE

-- Check TABLENAME and EVENTCODE values for batch/cancel events
SELECT DISTINCT TABLENAME, EVENT_TYPE, EVENTCODE
FROM   AUDITTRL
WHERE  UPPER(TABLENAME) LIKE '%ATCH%'
   OR  UPPER(EVENTCODE) LIKE '%ANCEL%'
ORDER  BY TABLENAME

Note:  SQL Server is usually case-insensitive (depends on collation), so the SQL Server queries may work regardless of casing. On Oracle, the UPPER() wrappers in this document ensure they work regardless of how the values were stored.

Notes

Note:  Audit trail coverage depends on which events are configured in Administration > Enterprise Settings > Audit Trail. If deletions are not appearing in AUDITTRL, verify that Delete events are enabled for the relevant tables.

Note:  For pre-batch-start events, consider adding custom audit trail entries via SSL if your compliance requirements demand them.