Problems with SQL-based reports and web results

Hi all.

I'm getting more adventurous with NPM, and have started investigating the use of SQL in reports. I am comfortable with my knowledge of SQL, but I can do what I need to do and I am by no means an SQL guru! I have found a couple of online documents and sites that have given me enough of an idea to start moving around in the database, so I thought I'd just start with something simple and work up from there.

The problem I am having is getting the report to run in the browser interface. The initial query I have is to help me identify interfaces that I don't want monitored (QoS, Miniport, etc), so I can easily identify them and decide whether to remove them. The query I have is:

select Nodes.NodeID, Nodes.SysName, Interfaces.InterfaceName, Interfaces.InterfaceID

from Nodes

join Interfaces on Nodes.NodeID = Interfaces.NodeID

where Interfaces.InterfaceName like '%Miniport%' or

Interfaces.InterfaceName like '%QoS%' or

Interfaces.InterfaceName like '%WFP%' or

Interfaces.InterfaceName like '%river%'

order by Nodes.SysName

This code works when previewing the report in Report Viewer and in MS SQL Management Studio, but when I try to view it in the browser interface, I get an error message:

System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at SolarWinds.Orion.Common.SqlHelper.ExecuteReader(SqlCommand command) at SolarWinds.Orion.Web.Reporting.OrionReport.QueryData(String query, String orderBy, String grouping, String filter, Dictionary`2 macroContext) at SolarWinds.Orion.Web.Reporting.ReportRunner.WorkerProc()

Through other SQL queries, I have determined that the browser struggles with queries based on the Nodes table, but only when there is an additional criteria in place (WHERE clauses, ORDER BY commands, etc). For example:

select Nodes.NodeID, Nodes.SysName from Nodes

and

select Interfaces.InterfaceName, Interfaces.InterfaceID

from Interfaces

where Interfaces.InterfaceName like '%Miniport%' or

Interfaces.InterfaceName like '%QoS%' or

Interfaces.InterfaceName like '%WFP%' or

Interfaces.InterfaceName like '%river%'

work in the browser, but

select Nodes.NodeID, Nodes.SysName from Nodes order by Nodes.SysName

does not - same error message as above ... and yes, it is reporting a problem near the keyword 'WHERE'. In all cases, the quesries work fine in MS SQL Management Studio and when previewing the report in the Report Writer.

Has anyone else seen this behaviour, and if so, how did you resolve it? Is there something I should be aware of when SQL is presented through the browser? I am familiar enough with SQL to know there are different flavours around, so if there is an issue with the syntax please let me know!

Find more posts tagged with

web_console

sql

report

Accepted answers

All comments

netlogix

I have had an issue with report writer and custom properties and you had to have the custom property editor updated the report writer schema. Open Custom Properties Editor, right click in toolbar ribbon, customize, Commands tab, properties section, bottom, drag and drop "Update Report Schemas" onto ribbon. Close and click on it. Might help, might not, but its one of the easy things I try when reports are acting weird.

Also, you might try deleting and re-creating the report (though you may have already tried that).

jsbajada

Hi netlogix.

I added that menu item, clicked on it, and after a few seconds it popped up a window advising it had been done. I went back to the browser and refreshed, but no luck. I tried a couple of different methods (closing and re-opening the browser, closing Report Writer, etc), but it just didn't want to play nice - no change to the error I described above.

And yes, I had tried re-creating the report - even after updating the report schema.

Any other ideas?

netlogix

I copied and pasted you SQL into a report for me and it works, I also added Interfaces.InterfaceName like '%loop% (for loopback adapters) - I am out or ideas. Anyone else? SW staff?

jsbajada

Thanks for the loopback criteria - I completely forgot about those!

This morning I faced the "An unsecured or incorrectly secured fault was received from the other party" error as outlined (and resolved by) this article: http://thwack.solarwinds.com/thread/41896

I'm wondering if there could be some IIS or account setting that is stopping this query running as a report? As mentioned, it works everywhere except in the browser, so I'm wondering if I shoudl focus on that?