Query Works in SWQL Studio But Not in Report Writer

I have been working on a report to pull the download times for the most recent downloaded running config for devices managed by NCM.

I managed to come up with the following query, which worked in SWQL Studio:

SELECT

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"

FROM

Cirrus.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.ConfigType = 'Running'))

ORDER BY

N.NodeCaption

The query, however, returns "Query is not valid" when pasting it into the "Advanced Database Query" selector in the Report Writer and clicking "Preview Results."

Does anyone have an idea why the query works in SWQL Studio, but fails when I try using it in a report?

Find more posts tagged with

network performance reports

npm report

npm reports

Accepted answers

All comments

zackm

Try removing the ORDER BY at the bottom; also, you might need to wrap your aliases in brackets [ ] instead of quotes " "

mjperkins

I tried both suggestions. Unfortunately, neither worked. Thanks for trying!

ctlswadmin

What version of NCM are you on?

Tried writing the query on NCM 7.4.1, so my tables are different.

dnerdahl

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.

SELECT
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"
FROM

Report writer, when using SWQL, doesn't like it when you do that. Try removing them?

SELECT
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
FROM

ctlswadmin

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

WHERE

(

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'))

ORDER BY

nn.NodeCaption

mjperkins

I tried pulling the quotes when I tried replacing the quotes with brackets (suggested earlier in this discussion). It did not work.

mjperkins

NCM 7.3.1

mjperkins

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.

ctlswadmin

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 ?

ctlswadmin

Run this query on the Solarwinds Database Manager share your output please.

SELECT t.name AS table_name,

SCHEMA_NAME(schema_id) AS schema_name,

c.name AS column_name

FROM sys.tables AS t

INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID

WHERE c.name LIKE '%NodeCaption%'

ORDER BY schema_name, table_name;

modot

I don't know much about the syntax for these things, but should "IP_Addess" be "IP_Address"?

mesverrum

I sometime wonder how I get anything done...

Run this inside a Custom Query resource instead of a custom table resource and your life will be much easier. Unless you want to keep this report as an excel, only custom tables can be exported to excel but honestly this type of report doesn't NEED excel...

SELECT np.Nodes.Caption

,np.Nodes.DetailsUrl as [_linkfor_Caption]

,'/netperfmon/images/vendors/' + np.Nodes.VendorIcon as [_iconfor_Caption]

, LoginStatus as [Latest Login Status]

,max(downloadtime) as [Last Backup]

, ConfigType

, case

when max(downloadtime) is null then '/Orion/images/StatusIcons/Small-Down.gif'

when daydiff(max(downloadtime),getdate())>7 THEN '/Orion/images/StatusIcons/Small-Critical.gif'

when daydiff(max(downloadtime),getdate())>2 THEN '/Orion/images/StatusIcons/Small-Warning.gif'

else '/Orion/images/StatusIcons/Small-Up.gif'

end as [_iconfor_Last Backup]

FROM NCM.NodeProperties np

left join NCM.ConfigArchive ca on ca.NodeID=np.NodeID

--where (np.Nodes.Caption like '%${SEARCH_STRING}%' or configtype like '%${SEARCH_STRING}%' or loginstatus like '%${SEARCH_STRING}%')

group by np.Nodes.Caption, np.Nodes.DetailsUrl, np.Nodes.VendorIcon, configtype, LoginStatus

order by Caption, configtype