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