Integridad
Integrity
1. Detectar Saltos en Secuencias (Gaps)
1. Detect Gaps in Sequences
Audita facturas o comprobantes perdidos buscando números que faltan en una secuencia correlativa.
Audits lost invoices or receipts by finding missing numbers in a consecutive sequence.
Ejemplo baseBase example
WITH Secuencia AS (
SELECT
Invoice_ID,
LEAD(Invoice_ID) OVER (ORDER BY Invoice_ID) AS Next_ID
FROM dbo.Invoices
)
SELECT
Invoice_ID + 1 AS Missing_Start,
Next_ID - 1 AS Missing_End
FROM Secuencia
WHERE Next_ID - Invoice_ID > 1;
Lógica
Logic
2. Incoherencias Cronológicas
2. Chronological Inconsistencies
Encuentra registros imposibles, como contratos que terminan antes de empezar o altas post-mortem.
Finds impossible records, such as contracts ending before they begin or post-mortem entries.
Ejemplo baseBase example
SELECT
Contract_ID,
Start_Date,
End_Date
FROM dbo.Contracts
WHERE End_Date < Start_Date;
Seguridad
Security
3. Segregación de Funciones (SoD)
3. Segregation of Duties (SoD)
Detecta usuarios que tienen permisos contradictorios (ej. quien crea el pago también lo aprueba).
Detects users with conflicting permissions (e.g., the one who creates the payment also approves it).
Ejemplo baseBase example
SELECT
User_ID
FROM security.UserRoles
WHERE Role_Name IN ('Payment_Maker', 'Payment_Checker')
GROUP BY User_ID
HAVING COUNT(DISTINCT Role_Name) = 2;
Lógica Cruzada
Cross-Field Logic
4. Estados Contradictorios
4. Contradictory Statuses
Encuentra empleados marcados como inactivos pero que siguen cobrando, o sin fecha de cese.
Finds employees marked as inactive but who are still on payroll, or lack a termination date.
Ejemplo baseBase example
SELECT
Employee_ID,
Status,
Termination_Date,
Payroll_Active
FROM dbo.EmployeeMaster
WHERE Status = 'Inactive'
AND (Termination_Date IS NULL OR Payroll_Active = 1);
Descuadre
Imbalance
5. Auditoría de Asientos Contables
5. Accounting Entry Audit
Verifica que en el libro mayor todo asiento contable sume cero (Débitos = Créditos).
Verifies that in the general ledger every entry nets to zero (Debits = Credits).
Ejemplo baseBase example
SELECT
Journal_ID,
SUM(Debit_Amount) AS Total_Debit,
SUM(Credit_Amount) AS Total_Credit,
SUM(Debit_Amount) - SUM(Credit_Amount) AS Variance
FROM finance.GeneralLedger
GROUP BY Journal_ID
HAVING SUM(Debit_Amount) - SUM(Credit_Amount) <> 0;
Fraude
Fraud
6. Transacciones Duplicadas Rápidas
6. Rapid Duplicate Transactions
Detecta si se procesó un doble cobro o pago accidental al mismo proveedor en el mismo minuto.
Detects if an accidental double charge or payment was made to the same vendor within the same minute.
Ejemplo baseBase example
SELECT
Vendor_ID,
Amount,
CAST(Transaction_Time AS smalldatetime) AS Minute_Block,
COUNT(*) AS Occurrences
FROM dbo.Payments
GROUP BY Vendor_ID, Amount, CAST(Transaction_Time AS smalldatetime)
HAVING COUNT(*) > 1;
Cuentas
Accounts
7. Cuentas Zombie (Inactivas)
7. Zombie (Dormant) Accounts
Identifica usuarios con acceso activo al sistema que no se han logueado en más de 90 días.
Identifies users with active system access who haven't logged in for over 90 days.
Ejemplo baseBase example
SELECT
User_ID,
Last_Login_Date
FROM security.SystemUsers
WHERE Is_Active = 1
AND Last_Login_Date < DATEADD(day, -90, GETDATE());
Precios
Pricing
8. Discrepancia de Precios Maestros
8. Master Price Discrepancies
Cruza lo facturado contra el catálogo oficial para detectar manipulaciones manuales de precio.
Cross-checks billed amounts against the official catalog to detect manual price manipulation.
Ejemplo baseBase example
SELECT
t.Order_ID,
t.Product_ID,
t.Unit_Price_Charged,
m.Official_Unit_Price
FROM sales.OrderLines t
JOIN master.Products m ON t.Product_ID = m.Product_ID
WHERE t.Unit_Price_Charged <> m.Official_Unit_Price;
Formato
Format
9. Longitud de Documentos Inválida
9. Invalid Document Length
Detecta números de seguro social, pasaportes o DNIs que no cumplen con los caracteres exigidos.
Detects SSNs, passports, or IDs that do not meet the required character length.
Ejemplo baseBase example
SELECT
Employee_ID,
National_ID
FROM dbo.EmployeeMaster
WHERE LEN(LTRIM(RTRIM(National_ID))) NOT IN (9, 11);
Caracteres
Characters
10. Detectar Basura en Texto
10. Detect Garbage in Text
Encuentra nombres que contienen símbolos inyectados o caracteres no alfanuméricos.
Finds names containing injected symbols or non-alphanumeric characters.
Ejemplo baseBase example
SELECT
Customer_ID,
Customer_Name
FROM dbo.Customers
WHERE Customer_Name LIKE '%[^a-zA-Z0-9 -]%';
Solapamiento
Overlapping
11. Fechas Históricas Solapadas (SCD)
11. Overlapping Historical Dates (SCD)
Verifica que no existan dos versiones del mismo registro activas en el mismo rango de tiempo.
Verifies that two active versions of the same record don't exist in the same time range.
Ejemplo baseBase example
SELECT
a.Record_ID, a.Start_Date, a.End_Date,
b.Start_Date AS Conflict_Start
FROM audit.EmployeeHistory a
JOIN audit.EmployeeHistory b
ON a.Employee_ID = b.Employee_ID AND a.Record_ID <> b.Record_ID
WHERE a.Start_Date <= ISNULL(b.End_Date, '2099-12-31')
AND ISNULL(a.End_Date, '2099-12-31') >= b.Start_Date;
Fuerza Bruta
Brute Force
12. Alerta de Intentos Fallidos
12. Failed Login Alert
Busca comportamientos de fuerza bruta: más de 5 inicios de sesión fallidos en los últimos 15 minutos.
Looks for brute force behaviors: over 5 failed login attempts in the last 15 minutes.
Ejemplo baseBase example
SELECT
Username,
COUNT(*) AS Failed_Attempts
FROM security.LoginLogs
WHERE Status = 'Failed'
AND Attempt_Time >= DATEADD(minute, -15, GETDATE())
GROUP BY Username
HAVING COUNT(*) > 5;
Anomalías
Anomalies
13. Fechas de Nacimiento Ilógicas
13. Illogical Birth Dates
Encuentra registros basura en campos de fecha (menores de 18 o mayores de 100 años).
Finds garbage records in date fields (under 18 or over 100 years old).
Ejemplo baseBase example
SELECT
Customer_ID,
Date_Of_Birth,
DATEDIFF(year, Date_Of_Birth, GETDATE()) AS Age
FROM dbo.Customers
WHERE DATEDIFF(year, Date_Of_Birth, GETDATE()) NOT BETWEEN 18 AND 100;
Emails
Emails
14. Estructuras de Email Inválidas
14. Invalid Email Structures
Filtra correos que no cumplen el patrón básico de texto + @ + dominio + . + extensión.
Filters emails that do not match the basic pattern of text + @ + domain + . + extension.
Ejemplo baseBase example
SELECT
Employee_ID,
Email
FROM dbo.EmployeeMaster
WHERE Email NOT LIKE '%_@__%.__%';
SLA
SLA
15. Auditoría de Datos Estancados
15. Stale Data Audit
Verifica si tablas críticas (ej. tipos de cambio) no se han actualizado hoy, indicando un fallo en el ETL.
Verifies if critical tables (e.g., exchange rates) haven't updated today, indicating an ETL failure.
Ejemplo baseBase example
SELECT
Table_Name,
Last_Update_Time
FROM sys.SystemStatus
WHERE Category = 'Critical_Finance'
AND Last_Update_Time < CAST(GETDATE() AS DATE);
Red
Network
16. Múltiples IPs por Usuario
16. Multiple IPs per User
Detecta usuarios que están accediendo desde demasiadas redes distintas en un mismo día (posible cuenta comprometida).
Detects users accessing from too many different networks on the same day (possible compromised account).
Ejemplo baseBase example
SELECT
User_ID,
CAST(Login_Time AS DATE) AS Log_Date,
COUNT(DISTINCT IP_Address) AS Unique_IPs
FROM security.LoginLogs
GROUP BY User_ID, CAST(Login_Time AS DATE)
HAVING COUNT(DISTINCT IP_Address) >= 3;
Duplicados Ocultos
Hidden Duplicates
17. Filas Idénticas (Excepto Llave Primaria)
17. Identical Rows (Except Primary Key)
Encuentra registros que se cargaron dos veces. Tienen diferente ID autonumérico, pero los mismos datos de negocio.
Finds records that were loaded twice. They have different auto IDs, but identical business data.
Ejemplo baseBase example
SELECT
Transaction_Date,
Amount,
Account_ID,
COUNT(*) AS Identical_Rows
FROM dbo.Transactions
GROUP BY Transaction_Date, Amount, Account_ID
HAVING COUNT(*) > 1;
Borrados
Deletions
18. Auditoría de Registros Eliminados
18. Deleted Records Audit
Usa la tabla de logs para ver quién borró información crítica durante el fin de semana.
Uses the log table to see who deleted critical information over the weekend.
Ejemplo baseBase example
SELECT
Audit_ID,
User_ID,
Table_Name,
Action_Time
FROM audit.DatabaseLog
WHERE Action_Type = 'DELETE'
AND Table_Name = 'FinanceMaster'
AND DATEPART(weekday, Action_Time) IN (1, 7);
Extremos
Extremes
19. Límites Duros (Outliers)
19. Hard Limits (Outliers)
Un control básico anti-fraude o anti-error (fat finger) para transacciones inusualmente grandes.
A basic anti-fraud or anti-error (fat finger) control for unusually large transactions.
Ejemplo baseBase example
SELECT
Transaction_ID,
User_ID,
Amount
FROM dbo.WireTransfers
WHERE Amount > 1000000; -- Hardcoded threshold for review
Fin de Semana
Weekends
20. Actividad No Autorizada en Fin de Semana
20. Unauthorized Weekend Activity
Busca movimientos en módulos donde la operatoria solo debería ocurrir de lunes a viernes.
Looks for movements in modules where operations should only occur Monday through Friday.
Ejemplo baseBase example
SELECT
Adjustment_ID,
Employee_ID,
Adjustment_Date
FROM inventory.Adjustments
WHERE DATEPART(dw, Adjustment_Date) IN (1, 7); -- Sunday (1) or Saturday (7)