Audit SQL Script Handbook

Audit SQL Script Handbook

This handbook provides starter SQL scripts for common audit tests. Each script includes the audit purpose, risks addressed, and notes on how to adapt it for different systems. The examples are written in standard SQL and may require syntax adjustments for specific databases (Oracle, SQL Server, MySQL, PostgreSQL).

Duplicate Transactions

SQL Script:

SELECT InvoiceNumber, COUNT(*) AS NumOccurrences
FROM AccountsPayable
GROUP BY InvoiceNumber
HAVING COUNT(*) > 1;

Purpose: Identify duplicate invoices or payments that may indicate control failure or fraud.

Risk Addressed: Duplicate payments, fraud, or poor vendor master data management.

Adaptation Notes: Adjust table and column names; may also use DISTINCT or window functions in some RDBMS.

Missing Records (Completeness)

SQL Script:

SELECT o.OrderID
FROM Orders o
LEFT JOIN Invoices i ON o.OrderID = i.OrderID
WHERE i.OrderID IS NULL;

Purpose: Detect orders that were never invoiced (potential revenue leakage).

Risk Addressed: Revenue underreporting or incomplete records.

Adaptation Notes: Change join conditions based on business process (e.g., shipments vs. invoices).

Recalculation Test (Accuracy)

SQL Script:

SELECT LoanID, Principal, InterestRate, TermMonths,
       ROUND(Principal * InterestRate / 12, 2) AS ExpectedMonthlyInterest,
       ActualMonthlyInterest
FROM LoanTable
WHERE ROUND(Principal * InterestRate / 12, 2) <> ActualMonthlyInterest;

Purpose: Recalculate loan interest and flag mismatches with system-calculated amounts.

Risk Addressed: Incorrect calculations could lead to misstated revenue or balances.

Adaptation Notes: Adjust formula logic for different products or calculation bases.

Outlier Transactions

SQL Script:

SELECT *
FROM Payments
WHERE Amount > 100000
   OR Amount < 0
   OR PaymentDate > GETDATE();

Purpose: Identify unusual amounts, negative transactions, or future-dated activity.

Risk Addressed: Fraudulent or erroneous postings.

Adaptation Notes: Replace GETDATE() with CURRENT_DATE or SYSDATE depending on DBMS.

User Access Review

SQL Script:

SELECT u.UserID, u.Department, a.Role
FROM Users u
JOIN AccessRights a ON u.UserID = a.UserID
LEFT JOIN HR_Employees h ON u.UserID = h.EmployeeID
WHERE h.EmployeeID IS NULL;

Purpose: Detect active accounts with no matching employee (potential 'ghost users').

Risk Addressed: Unauthorized access and weak identity management.

Adaptation Notes: Align with HR and access system tables; may require multiple joins.

Segregation of Duties (SOD) Conflict

SQL Script:

SELECT UserID
FROM AccessRights
WHERE Role IN ('CreateVendor', 'ApprovePayment')
GROUP BY UserID
HAVING COUNT(DISTINCT Role) > 1;

Purpose: Identify users with conflicting roles (e.g., vendor creation and payment approval).

Risk Addressed: Fraud risk due to inadequate segregation of duties.

Adaptation Notes: Update role names according to your system's access structure.

Population Reconciliation

SQL Script:

SELECT SUM(SubLedger.Amount) AS SubLedgerTotal,
       (SELECT SUM(GL.Amount) FROM GeneralLedger GL WHERE GL.Account = 'Revenue') AS GLTotal
FROM SubLedger
HAVING SUM(SubLedger.Amount) <> (SELECT SUM(GL.Amount) FROM GeneralLedger GL WHERE GL.Account = 'Revenue');

Purpose: Confirm that subledger totals reconcile to the general ledger.

Risk Addressed: Misstated balances and reconciliation gaps.

Adaptation Notes: Change account type (e.g., expenses, receivables) depending on audit focus.

Benford’s Law (First-Digit Test)

SQL Script:

SELECT LEFT(CAST(Amount AS VARCHAR), 1) AS FirstDigit,
       COUNT(*) * 1.0 / (SELECT COUNT(*) FROM Transactions) AS ActualFrequency
FROM Transactions
WHERE Amount > 0
GROUP BY LEFT(CAST(Amount AS VARCHAR), 1)
ORDER BY FirstDigit;

Purpose: Spot unusual first-digit distributions in large datasets (fraud red flag).

Risk Addressed: Fraudulent manipulation of amounts.

Adaptation Notes: Use SUBSTRING or equivalent if LEFT is not supported.

Timeliness of Recording

SQL Script:

SELECT t.TransactionID, t.TransactionDate, t.RecordedDate,
       DATEDIFF(DAY, t.TransactionDate, t.RecordedDate) AS DaysToRecord
FROM Transactions t
WHERE DATEDIFF(DAY, t.TransactionDate, t.RecordedDate) > 5;

Purpose: Check if transactions are being recorded promptly.

Risk Addressed: Delayed recognition may cause reporting inaccuracies.

Adaptation Notes: Replace DATEDIFF syntax with database-specific date functions.

Inactive User Accounts

SQL Script:

SELECT u.UserID, u.LastLoginDate
FROM Users u
WHERE DATEDIFF(DAY, u.LastLoginDate, GETDATE()) > 90
  AND u.IsActive = 1;

Purpose: Identify accounts active in the system but unused for 90+ days (security risk).

Risk Addressed: Dormant accounts may be exploited for unauthorized access.

Adaptation Notes: Adjust inactivity threshold (e.g., 60 or 120 days).