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.

Difference between SWQL Studio and SWQL in EOC

The new EOC makes heavy use of SWQL to report on multiple Orion instances at once, but I've stumbled over what appears to be a major difference (of which I'm sure the issue is on the EOC side and have posted in their forum:

Using just a simple SWQL query, even against a single Orion from the EOC, it appears that there is 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 

Any thoughts on what could cause this difference in behavior?

  • Another strange difference is that FULL JOIN works for SWQL Studio, but for SWQL on EOC, FULL JOIN does not function.

    There's no simple way to look at what error the EOC is seeing...

  • The dual selection of value also fails when attempting to self-join to create columns (as PIVOT does not work with SWQL).

    This for example:

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

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

    where
    v.Node.MachineType like '%WINDOWS%'
    and v.VolumeType='Virtual Memory'
  • Curious, what are the Orion versions you have added as sites?

    Could you please enable verbose log level for SWIS and capture logs? (Ideally in SWQL Studio, it gathers only related log messages.) Thanks.

  • All sites are running Orion 2017.3.3 SP3.  A thread in the EOC is open too and they made some suggestions.  There's definitely differences between SWQL Studio and SWQL in EOC - the obvious being 'FULL JOIN' vs. 'JOIN'.  That's easily demonstrable.

    How would I go about gathering SWIS EOC logs?  You say ideally in SWQL Studio - but SWQL Studio functions exactly as it should from what I can see.

  • Usually, when I want to see what SWIS is doing when running a query, i.e. how it processes the query and executes, I change the log level (with LogAdjuster) for SWIS to TRACE at minimum on the machine where SWIS instance I will connect to lives.

    Then, when you execute a query in SWQL Studio, it will open a tab "Log" in the result area. The "Log" tab then contains log messages there are related to the given query execution.

    Note: in order to by-pass query plan cache, i.e. to see complete processing and execution, I usually add "WITH NOPLANCACHE" clause at the end of the query being tested.

  • Additional difference sighted between SWQL Studio and EOC - for some reason, any query in EOC to touch Orion.HardwareHealth.HardwareInfo returns invalid.

    I don't know why it would be the schema in one place and not the other - EOC results in this even when I limit to the Site that I'm running the SWQL Studio command on successfully.

  • HardwareHealth entities seem to be not federated, thus not available in EOC. What message does EOC SWIS report?

  • Perhaps this is where my confusion is - when writing SWQL for a report for EOC, you include the SWQL query and select which Orion instances you want to run on.  I'm working from an assumption that EOC is simply running on each of the Orion instances returning the results, running as a UNION between the results sets and then sorting.

    At this point, it is obvious that this is not the case, hence the requirements for joining on OrionSiteID and the lack of hardware health.

    I suppose my question now is that if it's not just UNIONing results from each Orion --- why not?

  • Internally during query execution time, it does "union" results from all Orions, however EOC SWIS is only able to query entity types it knows, so that it is able to compose the query plan for execution properly. EOC SWIS schema used for the an input query pre-processing and query plan preparation is just a subset of schemas of Orion instances it operates on.