AcctivateLogSyncChecker.sql

DECLARE @cmd NVARCHAR(1000),
        @BasePath NVARCHAR(255) = N'C:\ProgramData\Alterity\Acctivate\Log\WebStore\',
        @DateFolder NVARCHAR(10) = CONVERT(NVARCHAR(10), GETDATE(), 120), -- yyyy-MM-dd
        @Results TABLE (Result NVARCHAR(255)),
        @LatestSyncTime NVARCHAR(255),
        @LatestSyncDateTime DATETIME,
        @SyncStatus BIT

-- Construct the command to list the log directories for today's date
-- where 'DIR /A:D /B /O-D ' + @DateFolder: Lists all directories (/A:D) in bare format (/B), sorted by date in descending order (/O-D), 
-- in the folder named as the current date (@DateFolder, formatted as yyyy-MM-dd). 
SET @cmd = N'CD ' + @BasePath + ' && DIR /A:D /B /O-D ' + @DateFolder

-- Execute the command
INSERT INTO @Results (Result)
EXEC xp_cmdshell @cmd

-- Check if the command returned any results
IF EXISTS(SELECT 1 FROM @Results WHERE Result IS NOT NULL AND Result NOT LIKE '%File Not Found%')
BEGIN
    -- Get the latest sync time directory name
    SELECT TOP 1 @LatestSyncTime = Result FROM @Results WHERE Result IS NOT NULL ORDER BY Result DESC

    IF @LatestSyncTime IS NOT NULL
    BEGIN
        -- Convert the latest sync time directory name to DATETIME
        SET @LatestSyncDateTime = CONVERT(DATETIME, @DateFolder + ' ' + REPLACE(@LatestSyncTime, '-', ':') + ':00')

        -- Determine if the latest sync is within the expected time range (e.g., last 10 minutes)
        SET @SyncStatus = CASE WHEN @LatestSyncDateTime > DATEADD(MINUTE, -10, GETDATE()) THEN 1 ELSE 0 END
    END
    ELSE
    BEGIN
        -- Latest time directory not found, set status as failure
        SET @SyncStatus = 0
    END
END
ELSE
BEGIN
    -- No log directory for today or command failed, set status as failure
    SET @SyncStatus = 0
END

-- Insert the check record into the log table
INSERT INTO [dbo].[SyncCheckLog] (LatestSyncTime, SyncStatus)
VALUES (@LatestSyncDateTime, @SyncStatus)