I posted about a similar query and problem back in August. I appreciate everyone who tried to help (zackm, ctlswadmin, dnerdahl), though I never did find a solution.
After another request for a report, I'm trying again, though I've narrowed the issue down a bit.
The current query, which executes flawlessly in SWQL Studio, is:
SELECT
N.NodeID AS Node_UniqueID, N.CoreNodeID AS Node_ID, N.AgentIP AS IP_Address, N.NodeCaption AS "Node_Name",
CA.ConfigType AS Config_Type, CA.DownloadTime AS Download_Time, CA.AttemptedDownloadTIme AS Last_Successful_Download_Time,
SI.StatusName as "Status"
FROM
NCM.Nodes N INNER JOIN Cirrus.ConfigArchive CA ON N.NodeID = CA.NodeID
INNER JOIN Orion.StatusInfo SI ON N.Status = SI.StatusID
WHERE
(CA.ConfigID = (SELECT TOP 1 CAA.ConfigID
FROM Cirrus.ConfigArchive CAA
WHERE CAA.NodeID = N.NodeID
ORDER BY CAA.DownloadTime DESC)
AND CA.AttemptedDownloadTime < ADDDAY(-1, GETDATE())
ORDER BY CA.AttemptedDownloadTime
Essentially, I am looking for NCM nodes where a nightly config backup has failed by pulling the date of the most recently downloaded configuration and showing only those which have not been downloaded in the past 24 hours, along with a node status that may show why (e.g., node is down or unmanaged), so my customer can investigate why NCM is unable to connect and pull configs from nodes of interest.
As with my August issue, that query works as-is in SWQL Studio, but clicking the "Preview Results" button in Report Writer only gets a "Query is not valid" error.
The issue appears to be with the nested SELECT clause. If I remove it so the query's WHERE clause becomes
WHERE
CA.AttemptedDownloadTime < ADDDAY(-1, GETDATE())
then Report Writer previews the not-so-useful results.
I am stumped as to whether this will work at all in ReportWriter. Any assistance in getting Report Writer to accept the supposedly valid SWQL query would be appreciated. If you have some other way to pull the date and time of only the most recently downloaded config besides the Report Writer-offending subquery, I'm all ears.