1 Reply Latest reply on Jun 27, 2014 8:40 AM by marunderwood

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

    marunderwood

      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

        • Re: Error when running report for accounts with Account Limitations - Group by Group
          marunderwood

          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