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 Product Specialist or TPS to assist
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
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Feedback 利用規約 プライバシー Cookie Preference Center