Can't find correct config backup log for generate backup status report

lykt

Before upgrading our NCM from 2020.2 to 2024.1. our backup report used to be like below.

Afterward we found report wasn't updating at all and the run time in 2nd column stuck at 2/9/2024. After investigate I found the old db table NCM_JobLogs is empty now and those logs should be stored in a new location.

At first I checked this similar name table and eventually write a SQL through ChatGPT to extract log successfully.

WITH LatestRunDate AS (
    SELECT MAX(CAST(RunDate AS DATE)) AS LatestDate
    FROM dbo.NCM_JobLogsNodes
    WHERE JobLog LIKE 'ERROR:%'
)
SELECT
    CAST(j.RunDate AS DATE) AS RunDate,
    CASE
        WHEN CHARINDEX('(', j.JobLog) > 0 AND CHARINDEX(')', j.JobLog) > CHARINDEX('(', j.JobLog)
        THEN SUBSTRING(j.JobLog, CHARINDEX('(', j.JobLog) + 1, CHARINDEX(')', j.JobLog) - CHARINDEX('(', j.JobLog) - 1)
        ELSE NULL
    END AS IPAddress,
    CASE
        WHEN CHARINDEX('(', j.JobLog) > 0 AND CHARINDEX(')', j.JobLog) > CHARINDEX('(', j.JobLog)
        THEN LTRIM(RTRIM(REPLACE(SUBSTRING(j.JobLog, CHARINDEX('___', j.JobLog) + 3, CHARINDEX(' (', j.JobLog) - CHARINDEX('___', j.JobLog) - 3), '_', '')))
        ELSE NULL
    END AS Hostname,
    e.JobLog AS [Error Statement]
FROM
    dbo.NCM_JobLogsNodes j
OUTER APPLY (
    SELECT TOP 1 JobLog
    FROM dbo.NCM_JobLogsNodes
    WHERE JobId = j.JobId AND NodeID = j.NodeID AND JobLog LIKE 'ERROR:%'
    ORDER BY RunDate DESC
) e
WHERE
    CAST(j.RunDate AS DATE) = (SELECT LatestDate FROM LatestRunDate)
    AND (j.JobLog LIKE '%(%):%' OR e.JobLog IS NOT NULL)
    AND j.JobLog NOT LIKE 'ERROR:%'
    AND CHARINDEX('(', j.JobLog) > 0
    AND CHARINDEX(')', j.JobLog) > CHARINDEX('(', j.JobLog)
ORDER BY j.RunDate DESC

result of the code:

However I found the "error node" in report seems backed up successfully in fact. I could find most of those nodes latest backup config in NCM gui. May I know if I select correct table in db to extract log? Or if the DB table name is not default? Want to recreate a report for backup config job status. Please kindly guide me if any clue could provide. Thanks.

stuartd

It looks to me as if you are running a SQL query?

The advice from Solarwinds has always been (where feasible) to use a SWQL query instead using the API. Their reasoning being is that the DB Schema can and does change, but the API (ie using SWQL) will just reference the new or renamed tables. At least, that's my understanding.

That said, I don't have an answer for your issue, but I can point you at the SWQL based report we run: Backup Status by last backup date - Errors Only  

Full disclosure. I uploaded the linked report but it was a combination of an EMEA based Solarwinds engineer and other scripts cobbled together. It essentially shows us the nodes that failed to backup and what their last (actual) error message was. There is a couple of custom properties in it that you will likely need to rem out. HTH