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.

Differences in SWQL in EOC?

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
Parents
  • 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=
Reply
  • 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=
Children
  • 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.