2 Replies Latest reply on Oct 14, 2014 6:55 AM by madhavan

    "Complex" SWQL Query and Error

    LDave

      Hi all,

      I'm trying to create a table resource for displaying custom poller results using swql query.

       

      I've created the query in SQL, where it works without problems. When converted to SWQL though, it produces the error at the bottom of this post.

      The query (already translated with SWQL v3 schema names) is:

       

      select AP_Name,Switch,Port,Duplex,Speed

      from

      (   SELECT

          sts.RowID,sts.Status as AP_Name

          FROM

          Orion.NPM.CustomPollerStatus Sts inner join Orion.NPM.CustomPollerAssignment Ass on Sts.CustomPollerAssignmentID = Ass.CustomPollerAssignmentID

          inner join  Orion.NPM.CustomPollers CP on Ass.CustompollerID = CP.CustompollerID

          WHERE

          CP.CustompollerID like '7A4E4DAC-BF2B-4CA4-9DC3-40B6B00C5B5D')as tableAP

      left join

      (   SELECT

          sts.RowID,sts.Status as Switch

          FROM

          Orion.NPM.CustomPollerStatus Sts inner join Orion.NPM.CustomPollerAssignment Ass on Sts.CustomPollerAssignmentID = Ass.CustomPollerAssignmentID

          inner join  Orion.NPM.CustomPollers CP on Ass.CustompollerID = CP.CustompollerID

          WHERE

          CP.CustompollerID like 'a9f57ac8-efb6-4d8c-97ea-8de315e5c443')as tableSwitch

      On tableSwitch.RowID = TableAP.RowID

      left join

      (   SELECT

          sts.RowID,sts.Status as Port

          FROM

          Orion.NPM.CustomPollerStatus Sts inner join Orion.NPM.CustomPollerAssignment Ass on Sts.CustomPollerAssignmentID = Ass.CustomPollerAssignmentID

          inner join  Orion.NPM.CustomPollers CP on Ass.CustompollerID = CP.CustompollerID

          WHERE

          CP.CustompollerID like '166f2b25-ed1e-41ad-a79b-94f39711753e')as tablePort

       

      On TableAP.RowID = Tableport.RowID

       

      Left Join

       

      (   SELECT

          sts.RowID,sts.Status as Duplex

          FROM

          Orion.NPM.CustomPollerStatus Sts inner join Orion.NPM.CustomPollerAssignment Ass on Sts.CustomPollerAssignmentID = Ass.CustomPollerAssignmentID

          inner join  Orion.NPM.CustomPollers CP on Ass.CustompollerID = CP.CustompollerID

          WHERE

          CP.CustompollerID like '5504e8ad-9cf4-43a1-b2b8-7d4bdcd66c0b')as tableDuplex

       

      On TableAP.RowID = TableDuplex.RowID

       

      Left Join

       

      (   SELECT

          sts.RowID,sts.Status as Speed

          FROM

          Orion.NPM.CustomPollerStatus Sts inner join Orion.NPM.CustomPollerAssignment Ass on Sts.CustomPollerAssignmentID = Ass.CustomPollerAssignmentID

          inner join  Orion.NPM.CustomPollers CP on Ass.CustompollerID = CP.CustompollerID

          WHERE

          CP.CustompollerID like '4d888308-c447-474a-b257-c9b210ad918b')as tableSpeed

       

      On TableAP.RowID = TableSpeed.RowID

       

       

      Then I will filter the results in order to have details of a single AP in the AP page, but before I would like to have it working in this way.

      If I execute all single selects separately, all of them work.

      If I execute just the first part (getting the AP name with the external select that gathers from the tableAP) it works.

      Apparently what's not working is the join between the various "tables".

       

      The error I get is this:

      The communication object,

      System.ServiceModel.SecuritySessionClientSettings'1+ClientSecurityDuplexSessionChannel[System.ServiceModel.Channels.IDuplexSessionChannel], cannot be used for communication because it is in the faulted state.

       

      Any help will be appreciated.

       

      Thanks,

      Dave

       

       

      PS. I also ask if is there a possibility to define a table (like With Table as ( select blablabla) ) in order to avoid multiple joins for every "column" of the query below.

        • Re: "Complex" SWQL Query and Error
          LDave

          Apparently (until someone gives a better answer) the best way I've found to achieve this is to use report writer to create a report to be displayed with the "report from report writer resource".

          It can be filtered in the where part of the sql query with ${variable} macros. i.e. NodeID = ${NodeID} as stated here: SQL to limit "Report from Orion Report Writer" to Group members.

          It won't work running the report itself, but it will with the resource on the node page

          1 of 1 people found this helpful
            • Re: "Complex" SWQL Query and Error
              madhavan

              Hi,

               

              When a subquery is used in from or join clause the query alias and the alias used in the ON condition when joining should be the same (case sensitive).

               

              example :: The alias tableAP when used in join ON condition it is used as TableIP. This will be fixed in future releases.

               

              Let me know if this solves your issue.