How to resolve Purge job issues caused by incorrect timestamps or Table 0 errors (4370530)

A customer is having issues with the purge jobs due to errors about the date timestamp and/or Table 0, and they have provided the results of the queries from the following KB:

https://support.quest.com/change-auditor/kb/4348955/internal-cannot-find-table-0-error-is-reported-in-purge-job-results-with-a-sql-error-the-conversion-of-a-datetimeoffset-data-type-to-a-datetime-data-type-resulted-in-an-out-of-range-value-4348955

What are the next steps to resolve the issue?

NOTE: If you are not familiar with correcting this issue previously, please do not run through these steps, please reach out to the SQL team and request them to perform those, also always backup the database previously.  

1. At the start : Check the AuditEvents table for errors, then fix them (using Query 1 or Query 1 Opt to find the errors and Fix 1 to change the timestamps)
2. Does the purge job now work?
3. If no, it's time to look at the SubsystemID and SeverityID index tables (IX):
   • Examine the Severity table for "broken" dates (Using Query 2)
   • Examine the Subsystem table for "broken" dates (Using Query 3)
   • Heal the two tables (Using Fix 2 and Fix 3)
4. Does the purge job now work?
5. If no, look for orphaned records:
   • Examine the Severity table for "Orphaned" Records (Using Query 4 and 5)
   • Examine the Subsystem table for "Orphaned" Records (Using Query 6 and 7)
   • Delete the orphaned records in the two tables (Using Fix 4 and Fix 5)
6. Does the purge job now work?
7. If no:
   • Start applying step 3 to other IX tables
     OR
   • SQL Profile trace the purge job to check and see what tables are involved in that customer's custom purge job

Query 1:
Use this query to better find older events, (optionally) filtered by facility:
SELECT _id,_date_hh,EventID,SubsystemID,FacilityID,TimeBatched,TimeDetected,TimeReceived
FROM AuditEvent
WHERE (_date_hh < '2010-12-31 23:59:59.0000000 +00:00')

Query 1 Opt:
(Optional) You can use this to filter on specific facilities to narrow down results if necessary
SELECT _id,_date_hh,EventID,SubsystemID,FacilityID,TimeBatched,TimeDetected,TimeReceived
FROM AuditEvent
WHERE ((FacilityID=102) or (FacilityID=106)) AND (_date_hh < '2010-12-31 23:59:59.0000000 +00:00')

Adding CSV headers if needed:
Prepend the resulting CSV from the customer with the following two lines:
sep=;
_id;_date_hh;EventID;SubsystemID;FacilityID;TimeBatched;TimeDetected;TimeReceived

Fix 1:
Fix any events that have corrupted date values:
UPDATE AuditEvent
SET _date_hh = '2022-12-15 12:31:18.9440683 +00:00',TimeDetected = '2022-12-15 12:31:18.9440683 +00:00'
WHERE EventID = '747CED01-DE32-3B57-6136-221AEDE8C2E0'
 
Query 2:
Search the AuditEvent_IX_SeverityID table for entries where the _date_hh value does not match the events in the AuditEvent table
SELECT ais._id AS AIS_id, ais._date_hh as AIS_date_hh, ae._id as AE_id, ae._date_hh as AE_date_hh
FROM AuditEvent_IX_SeverityID as ais
INNER JOIN AuditEvent AS ae on ae._id = ais._id
WHERE ae._date_hh <> ais._date_hh

Adding CSV headers if needed:
Prepend the resulting CSV from the customer with the following two lines:
sep=;
AIS_id;AIS_date;AE_id;AE_date;
 
Query 3:
Search the AuditEvent_IX_SubsystemID table for entries where the _date_hh value does not match the events in the AuditEvent table
SELECT ais._id AS AIS_id, ais._date_hh as AIS_date_hh, ae._id as AE_id, ae._date_hh as AE_date_hh
FROM AuditEvent_IX_SubsystemID as ais
INNER JOIN AuditEvent AS ae on ae._id = ais._id
WHERE ae._date_hh <> ais._date_hh

Adding CSV headers if needed:
Prepend the resulting CSV from the customer with the following two lines:
sep=;
AIS_id;AIS_date;AE_id;AE_date

Fix 2:
Set the _date_hh value in the AuditEvent_IX_SeverityID table to be the same as that of the AuditEvents table where the "_id" value matches
UPDATE AuditEvent_IX_SeverityID
SET _date_hh = ae._date_hh
FROM AuditEvent_IX_SeverityID AS ais
INNER JOIN AuditEvent AS ae on ae._id = ais._id
WHERE ae._date_hh <> ais._date_hh
 
Fix 3:
Set the _date_hh value in the AuditEvent_IX_SubsystemID table to be the same as that of the AuditEvents table where the "_id" value matches
UPDATE AuditEvent_IX_SubsystemID
SET _date_hh = ae._date_hh
FROM AuditEvent_IX_SubsystemID AS ais
INNER JOIN AuditEvent AS ae on ae._id = ais._id
WHERE ae._date_hh <> ais._date_hh
 

Query 4:
Check the severity table for events that exist and may be malformed that do not have an "event" associated in the AuditEvent table
SELECT ais._id as SevID_ID, ais._date_hh as SevID_DATE, ae._id as AE_ID, ae._date_hh as SevID_DATE
FROM AuditEvent_IX_SeverityID as ais
LEFT Join AuditEvent as ae on ais._id = ae._id
WHERE ae._id is NULL

Adding CSV headers if needed:
Prepend the resulting CSV from the customer with the following two lines:
sep=;
AIS_id;AIS_date;AE_id;AE_date;

Query 5:
SELECT *
FROM AuditEvent_IX_SeverityID
WHERE _date_hh < '2015-12-31 23:59:59.0000000 +00:00'

Query 6:
Check the subsystem table for events that exist and may be malformed that do not have an "event" associated in the AuditEvent table
SELECT ais._id as SubID_ID, ais._date_hh as SubID_DATE, ae._id as AE_ID, ae._date_hh as AE_DATE
FROM AuditEvent_IX_SubsystemID as ais
LEFT Join AuditEvent as ae on ais._id = ae._id
WHERE ae._id is NULL

Adding CSV headers if needed:
Prepend the resulting CSV from the customer with the following two lines:
sep=;
AIS_id;AIS_date;AE_id;AE_date;

Query 7:
SELECT *
FROM AuditEvent_IX_ SubsystemID
Where _date_hh < '2015-12-31 23:59:59.0000000 +00:00'
 
Fix 4: 
Delete entries from the severity table where no _id exists in the AuditEvent table
DELETE ais from AuditEvent_IX_SeverityID as ais
LEFT JOIN Auditevent as ae on ae._id = ais._id
WHERE ae._id is Null

Fix 5:
Delete entries from the subsystem table where no _id exists in the AuditEvent table
DELETE ais from AuditEvent_IX_SubsystemID as ais
LEFT JOIN Auditevent as ae on ae._id = ais._id
WHERE ae._id is Null