10 Replies Latest reply on Jun 6, 2019 2:59 PM by dan jagnow

    Differences in SWQL in EOC?

    mwb

      I'm the most interested in EOC to do unified reporting across different Orion instances.  I tend to fall back on the raw queries to get to the data I want and while the lack of inclusion of SQL along with SWQL is frustrating, I understand it from a design perspective.

       

      What I've been struggling with is where the SWQL in EOC seems to function differently than the SWQL elsewhere.  For instance, I have a query for a report that runs fine in SWQL Studio, runs fine on an individual Orion instance and then does nothing except kick out "Query is not valid" on EOC.  The query is below and you'll note that there is only one custom property that definitely exists across my instances.  Everything else should be a function of SWQL that would work on both the individual Orions and EOC.

       

      Futhermore, since EOC runs the SWQL against the other instances directly, there's not really a good way like SWQL Studio to run the query on the EOC and get some more pertinent error information.

       

      I'd welcome any thoughts anyone can give.  I've tripped over a number of EOC SWQL items - this is just one obvious example.

       

      select n.Caption, IP_Address,
      TOSTRING(vm.VolumeSize/1024/1024/1024) + ' GB' as VirtualMemory,
      TOSTRING (ram.VolumeSize/1024/1024) + ' MB' as RAM,
      TOSTRING((vm.VolumeSize-ram.VolumeSize)/1024/1024) + ' MB' as PageFile,
      TOSTRING(((vm.VolumeSize-ram.VolumeSize)-ram.VolumeSize)/1024/1024/1024) + ' GB' as PageFileDiff
      from Orion.Nodes n
      
      FULL JOIN (select NodeID, VolumeSize from Orion.Volumes
      where VolumeType='Virtual Memory')
      vm
      on vm.NodeID=n.NodeID
      
      FULL JOIN (select NodeID, VolumeSize from Orion.Volumes
      where VolumeType='RAM')
      ram
      on ram.NodeID=n.NodeID
      
      where n.CustomProperties.Department='Physician Services'
      and MachineType like '%WINDOWS%'
      
      and ((vm.VolumeSize-ram.VolumeSize)-ram.VolumeSize)/1024/1024/1024<1
      
      order by ((vm.VolumeSize-ram.VolumeSize)-ram.VolumeSize)/1024/1024/1024
        • Re: Differences in SWQL in EOC?
          mwb

          It appears to be something with FULL JOIN versus JOIN - but again only on SWQL on EOC.

          I would welcome any thoughts.


          • Re: Differences in SWQL in EOC?
            mwb

            And even with this simple SWQL query running against one node, I'm returning TWO results on EOC set to query against just one Orion, instead of the one that returns on the SWQL Studio on the Orion instance itself.  WHY?

             

            select n.Caption, IP_Address, 
            TOSTRING (ram.VolumeSize/1024/1024/1024) + ' GB' as RAM,  ram.VolumeType as RAMT
            from Orion.Nodes n 
             
            JOIN (select NodeID, VolumeSize, VolumeType from Orion.Volumes 
            where VolumeType='RAM') 
            ram 
            on ram.NodeID=n.NodeID 
             
            where n.CustomProperties.Department='Physician Services' 
            and MachineType like '%WINDOWS%' 
            and n.Caption=
              • Re: Differences in SWQL in EOC?
                mwb

                Oddly the nested filter is not working in SWQL - when I return:

                 

                JOIN (select NodeID, VolumeSize, VolumeType from Orion.Volumes  
                where VolumeType='RAM') 

                It is not in fact filtering the sub-query based on the VolumeType.  It's returning both the entry for the RAM and a boot volume C: - despite C: being "fixed disk" in the database for that VolumeID.

                 

                Further looking at the subquery and the returned VolumeID, it appears that the VolumeID is not only not VolumeType='RAM', but also belongs to a different NodeID entirely, which means something weird is going on with the JOIN as well.

              • Re: Differences in SWQL in EOC?
                mwb

                There also appears to be an error(?) where two values with the same column name cannot be selected, even if they are renamed:

                 

                select NodeID, v.Node.Caption, VolumeID, v.Caption, VolumeType, VolumeSize  
                from Orion.Volumes v

                 

                This does not work on the EOC - it returns the last "caption" value twice.  However, this works correctly in the SWQL Studio.

                  • Re: Differences in SWQL in EOC?
                    jblankjblank

                    mwb I will have a look at your examples and check with the engineering team to see where we can fill in some of the gaps and perhaps make improvements.  Thank you for providing all the details you have so far.  

                    • Re: Differences in SWQL in EOC?
                      jblankjblank

                      mwb Here are a few suggestions:

                       

                      1) Avoid missing table aliases in select statement: "select NodeID, v.Node.Caption from Orion.Volumes v" would be rewritten to "select v.NodeID, v.Node.Caption from Orion.Volumes v"

                       

                      2) Avoid duplicated column names in select statement: "select v.Node.Caption, v.Caption from Orion.Volumes v" would be rewritten to  "select v.Node.Caption as NodeCaption, v.Caption as VolumeCaption from Orion.Volumes v"

                       

                      3) To avoid data duplication when an entity with the same ID is present in multiple remote Orion Instances (Orion Sites), always use InstanceSiteId in JOIN statements. To explain, here is a simplified version of one your provided queries:

                       

                      select n.Caption, TOSTRING (ram.VolumeSize/1024/1024/1024) + ' GB' as RAM,  ram.VolumeType as RAMT 

                      from Orion.Nodes n   

                      JOIN (select NodeID, VolumeSize, VolumeType from Orion.Volumes where VolumeType='RAM') ram  

                      on ram.NodeID=n.NodeID  

                      where n.MachineType like '%WINDOWS%'

                       

                      When executing it over a single instance of Orion Site, it returns a correct result. But when executing the query from EOC when multiple Orion Sites are attached to it, EOC will return duplicated data. To avoid that data duplication, add InstanceSiteId into select statement of inner query (so that each Node is identified by both NodeID and InstaceSiteId) and ram.InstaceSiteId=n.InstaceSiteId condition to JOIN statement to avoid cross-site joins.

                       

                      Another improvement to distinguish from which Orion Site data comes from, would be adding Site Name (n.OrionSite.Name) to the outer select statement.

                       

                      The complete query would look as follows:

                       

                      select n.Caption, TOSTRING (ram.VolumeSize/1024/1024/1024) + ' GB' as RAM,  ram.VolumeType as RAMT, n.OrionSite.Name as OrionName

                      from Orion.Nodes n   

                      JOIN (select NodeID, InstanceSiteId, VolumeSize, VolumeType from Orion.Volumes where VolumeType='RAM') ram  

                      on ram.NodeID=n.NodeID and ram.InstanceSiteId=n.InstanceSiteId   

                      where n.MachineType like '%WINDOWS%'