1. ToolsNet 8
2. Configuration
3. Working with the Database
4. ToolsNet Archive Installer
5. Controller Clock Issues and Archiving

Controller Clock Issues and Archiving

Results stored in ToolsNet include a timestamp provided by the tightening controller. If the controller's internal clock is incorrect because of a depleted Real Time Clock (RTC) battery, a failed Network Time Protocol (NTP) synchronization, manual misconfiguration, or clock drift, results may be stored with wrong timestamp. Wrong timestamp can cause a result to be missing from the correct archive or to be archived to the wrong archive database.

Common symptoms:

• Results appear with dates far in the past (for example, 1970 or 2000).

• Results appear with dates far in the future.

• The total number of archived results does not match the number of results in the production database.

Detecting the Issue

Before you run the archiving job, verify that no results have incorrect timestamps. Run this query against the ToolsNet database to identify suspicious results:

Results with timestamps more than 1 day in the future

SELECT
    r.ID,
    r.ResultDateTime,
    r.ResultInsertDateTime,
    CASE
        WHEN u.MasterUnitID IS NOT NULL
            AND mu.Name IS NOT NULL
        THEN CONCAT(mu.Name, ' - ', u.Name)
        ELSE u.Name
    END AS ControllerName,
    u.IPAddress,
    p.Name AS ProgramName,
    r.UnifiedResultStatusTypeID AS ResultStatus
FROM AtlasCopco_ToolsNet_Database.ACDC.Result AS r
LEFT JOIN AtlasCopco_ToolsNet_Database.ACDC.Unit AS u
    ON r.UnitID = u.ID
LEFT JOIN AtlasCopco_ToolsNet_Database.ACDC.Unit AS mu
    ON u.MasterUnitID = mu.ID
LEFT JOIN AtlasCopco_ToolsNet_Database.ACDC.Program AS p
    ON r.ProgramID = p.ID
WHERE r.ResultDateTime > DATEADD(DAY, 1, GETUTCDATE())
ORDER BY
    r.ResultInsertDateTime DESC;

Results with timestamps suspiciously old (before ToolsNet deployment, adjust year)

SELECT
    r.ID,
    r.ResultDateTime,
    r.ResultInsertDateTime,
    CASE
        WHEN u.MasterUnitID IS NOT NULL
            AND mu.Name IS NOT NULL
        THEN CONCAT(mu.Name, ' - ', u.Name)
        ELSE u.Name
    END AS ControllerName,
    u.IPAddress,
    p.Name AS ProgramName,
    r.UnifiedResultStatusTypeID AS ResultStatus
FROM AtlasCopco_ToolsNet_Database.ACDC.Result AS r
LEFT JOIN AtlasCopco_ToolsNet_Database.ACDC.Unit AS u
    ON r.UnitID = u.ID
LEFT JOIN AtlasCopco_ToolsNet_Database.ACDC.Unit AS mu
    ON u.MasterUnitID = mu.ID
LEFT JOIN AtlasCopco_ToolsNet_Database.ACDC.Program AS p
    ON r.ProgramID = p.ID
WHERE
    r.ResultDateTime < (
        SELECT TOP (1)
            TimeStamp
        FROM AtlasCopco_ToolsNet_Database.ToolsNet.SystemInfoLog
    )
    AND r.ResultDateTime < (
        SELECT TOP (1)
            ResultInsertDateTime
        FROM AtlasCopco_ToolsNet_Database.ACDC.Result
        ORDER BY
            ID
    )
ORDER BY
    r.ResultInsertDateTime ASC;

UnitID, name and total results where the year does not match

SELECT
    r.UnitID,
    CASE
        WHEN u.MasterUnitID IS NOT NULL AND mu.Name IS NOT NULL
            THEN CONCAT(mu.Name, ' - ', u.Name)
        ELSE u.Name
    END AS ControllerName,
    u.IPAddress,
    COUNT(*) AS MismatchedYearCount
FROM AtlasCopco_ToolsNet_Database.ACDC.Result r
LEFT JOIN AtlasCopco_ToolsNet_Database.ACDC.Unit u
    ON r.UnitID = u.ID
LEFT JOIN AtlasCopco_ToolsNet_Database.ACDC.Unit mu
    ON u.MasterUnitID = mu.ID
LEFT JOIN AtlasCopco_ToolsNet_Database.ACDC.Program p
    ON r.ProgramID = p.ID
WHERE DATEPART(YEAR, r.ResultDateTime) <> DATEPART(YEAR, r.ResultInsertDateTime)
GROUP BY
    r.UnitID,
    CASE
        WHEN u.MasterUnitID IS NOT NULL AND mu.Name IS NOT NULL
            THEN CONCAT(mu.Name, ' - ', u.Name)
        ELSE u.Name
    END,
    u.IPAddress
ORDER BY MismatchedYearCount DESC;

If the query returns results, record the ControllerName and IP Address, and investigate the controller clock before running the archiving job.

Impact on Archiving

The archiving process selects results using the Time Stamp column. If a controller sends results with incorrect timestamp, archiving behavior changes as described below.

Recommended Actions Before Archiving

• Check the controller RTC battery and replace it if depleted.

• Make sure the controller is configured to sync time with a reliable NTP server and that sync is active.

• Correct any manual time misconfiguration on the controller.

• Do not run the archiving job for controllers that have submitted results with incorrect timestamps.

• For manual updates to timestamps in ToolsNet, open a QCM support ticket.

• If a large volume of results has incorrect timestamps and archiving cannot proceed safely, inform the ACDC/ToolsNet support team.

ToolsNet 8 will not detect or correct incorrect controller timestamps automatically. This is by design. Result timestamp integrity is the responsibility of the controller.

Recommended Actions After Archiving

Run a query for the specified date range (2024-01-01 to 2025-01-01) to find production results that were not archived. The query should:

• Compare each production result ID (Result.ID) with the archive reference (Archive.dbo.Result.ResultID).

• Return only records with no matching archive entry.

• Include unit and controller details (show MasterUnitName - UnitName when applicable), the unit IP address, and the full result record to support investigation of missing archive records.

SELECT
    u.ID AS UnitID,
    CASE
        WHEN u.MasterUnitID IS NOT NULL AND mu.Name IS NOT NULL
            THEN CONCAT(mu.Name, ' - ', u.Name)
        ELSE u.Name
    END AS ControllerName,
    u.IPAddress AS IPaddress,
    r.*
FROM AtlasCopco_ToolsNet_Database.ACDC.Result r
INNER JOIN AtlasCopco_ToolsNet_Database.ACDC.Unit u
    ON r.UnitID = u.ID
LEFT JOIN AtlasCopco_ToolsNet_Database.ACDC.Unit mu
    ON u.MasterUnitID = mu.ID
LEFT JOIN Archive.dbo.Result arch
    ON r.ID = arch.ResultID
WHERE
    r.ResultDateTime >= '2024-01-01'
    AND r.ResultDateTime < '2025-01-01'
    AND arch.ResultID IS NULL
ORDER BY
    ControllerName,
    r.ResultDateTime DESC;

In Archive.dbo.Result , ensure that Archive is replaced with your environment’s archive database name. For example, if your archive database is named ToolsNetArchive, update it to ToolsNetArchive.dbo.Result.