This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Error when running report for accounts with Account Limitations - Group by Group

I am getting a syntax error for a custom report where the user account is limited to 4 groups/subgroups that contain the letters 'DD'.  If I remove the Account Limitations Group by Group, the report runs fine.  I even removed the alias name for the Nodes file, but I still get the same error.  Any ideas?

Error:
System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near the keyword 'WHERE'. Incorrect syntax near 'Nodes'. 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() 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, SqlConnection connection, SqlTransaction transaction, CommandBehavior behavior) 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()

from a custom sql report:
SELECT 
   ApplicationName
, acp.ApplicationID
, ComponentName
, case a.Unmanaged when 'False' then '' else 'Unmanaged' end as Unmanaged
/*   , ace.Disabled */
, ComponentStatus
, NodeName
, ard.NodeId as ArdNodeId
, 'Role:  ' + d.System_Role as System_Role
, PrimaryAppSupportEmail
, ard.ComponentType
, DisplayType
, case when ProcessName > '' then ProcessName else acd.Value end as 'Process / Url'
, [Threshold-CPU-Critical]
, [Threshold-CPU-Warning]
, [Threshold-PhysicalMemory-Critical]
, [Threshold-PhysicalMemory-Warning]
, [Threshold-VirtualMemory-Critical]
, [Threshold-VirtualMemory-Warning]
, [Threshold-ResponseTime-Critical]
, [Threshold-ResponseTime-Warning]
, [Threshold-Statistic-Critical]
, [Threshold-Statistic-Warning]
, TimeStamp
, Nodes.NodeId

FROM APM_AlertsAndReportsData ard
left join Nodes on Nodes.NodeId = ard.NodeId
left join IPAM_Node ipn on ipn.IPAddress = Nodes.IP_Address
left join IPAM_NodeAttrData d on ipn.IPNodeId = d.IPNodeId
left join APM_ApplicationCustomProperties acp on acp.ApplicationId = ard.ApplicationId
left join APM_Application a on a.Id = ard.ApplicationId
left join APM_ComponentDetails acd on acd.ApplicationId = ard.ApplicationId and acd.ID = ard.ComponentID and acd.[Key] = 'Url'
left join APM_ComponentExt ace on ace.ApplicationId = ard.ApplicationId and ace.ID = ard.ComponentID

where ard.NodeName in (select GroupMemberFullName from Containers_AlertsAndReportsData car where car.GroupName like '%DD%')
  AND ace.Disabled = 0

order by NodeName, ApplicationName, ComponentName

  • I figured it out.  I had to comment out the WHERE and ORDER BY clause and incorporate the logic in the JOINS.  I may have been able to nest this within a select * from (..... ) too, but I didn't try it.

    p.s.  I assume SolarWinds is trying to append a WHERE clause at the end of the SQL statement to incorporate the account limitations.

    The sql low looks like:

    ...

    FROM APM_AlertsAndReportsData ard

    join Nodes on Nodes.NodeId = ard.NodeId and ard.NodeName in (select GroupMemberFullName from Containers_AlertsAndReportsData car where car.GroupName like '%DD%')  

    left join IPAM_Node ipn on ipn.IPAddress = Nodes.IP_Address

    left join IPAM_NodeAttrData d on ipn.IPNodeId = d.IPNodeId

    left join APM_ApplicationCustomProperties acp on acp.ApplicationId = ard.ApplicationId

    left join APM_Application a on a.Id = ard.ApplicationId

    left join APM_ComponentDetails acd on acd.ApplicationId = ard.ApplicationId and acd.ID = ard.ComponentID and acd.[Key] = 'Url'

    join APM_ComponentExt ace on ace.ApplicationId = ard.ApplicationId and ace.ID = ard.ComponentID AND ace.Disabled = 0