Try removing the ORDER BY at the bottom; also, you might need to wrap your aliases in brackets [ ] instead of quotes " "
I tried both suggestions. Unfortunately, neither worked. Thanks for trying!
What version of NCM are you on?
Tried writing the query on NCM 7.4.1, so my tables are different.
I don't have NCM to work with here... However, a quick syntax observation.
In your code bit you have your column AS statements with quotation marks around them.
N.CoreNodeID AS "Node_ID", N.AgentIP AS "IP_Addess", N.NodeCaption AS "Node_Name",
CA.DownloadTime AS "Download_Time", CA.LastAttemptedDownloadTIme AS "Last_Successful_Download_Time",
SI.StatusName as "Status"
Report writer, when using SWQL, doesn't like it when you do that. Try removing them?
N.CoreNodeID AS Node_ID, N.AgentIP AS IP_Addess, N.NodeCaption AS Node_Name,
CA.DownloadTime AS Download_Time", CA.LastAttemptedDownloadTIme AS Last_Successful_Download_Time,
SI.StatusName as Status
Try the query below. If it doesn't work, change the font in bold to AttemptedDownloadTime
If that still doesn't work share your version of NCM.
SELECT nn.CoreNodeID AS 'Node ID', nn.AgentIP AS 'IP Address',nn.NodeCaption AS 'Node Name',
ca.DownloadTime AS 'Download Time', CA.LastAttemptedDownloadTIme AS 'Last Successful Download Time',
si.StatusName AS 'Status'
From NCM_Nodes nn
JOIN NCM_ConfigArchive ca
ON nn.NodeID = ca.NodeID
JOIN StatusInfo si
ON nn.Status = si.StatusId
ca.ConfigID = (SELECT TOP 1 ca.ConfigID
From NCM_ConfigArchive ca
JOIN NCM_Nodes nn
ON ca.NodeID = nn.NodeID
ORDER BY ca.DownloadTime DESC) AND
(ca.ConfigType = 'Running'))
ctlswadmin, I don't think that query can work. The tables "NCM_..." in my system do not appear in SWQL Studio, but only in the (SQL) Database Manager, and there is no "NCM_Nodes" table in either place. Thanks for the suggestion though.
As I replied to another comment, I'm currently running NCM 7.3.1. I don't know whether that might help.
I believe that the NCM db was merged with NPM after 7.3. Do you know what the table name is where ncm node information is stored ?
In SQL, the basic NCM information is in NCM_NodesProperties. In SWQL, Cirrus.Nodes and NCM.Nodes both seem to have some data.
I tried pulling the quotes when I tried replacing the quotes with brackets (suggested earlier in this discussion). It did not work.