0 Replies Latest reply on Apr 24, 2012 12:06 PM by eddiec

    Is there a way to do an outer join via the API syntax

      I am trying to write an API query  that will report back all of the machines that have not completed  a virus scan for whatever reason. I'm having a bit of trouble figuring out how to do this via the API.


      In SQL I would use an outer join to filter all the columns that have successfully completed, and then just pull the results with an absence of data like so:


      SELECT l.NodeName AS machineName, b.NodeName AS groupName FROM EPOLeafNode l

      INNER JOIN dbo.EPOBranchNode b ON b.AutoID = l.ParentID

      LEFT OUTER JOIN EPOEvents e ON e.AgentGUID = l.AgentGUID

      AND e.ThreatEventID = 1203

      WHERE e.ThreatEventID IS NULL


      But using the API I can not figure out the syntax to do the same thing. I tried passing this nonsensical query to core.executeQuery:


      target=EPOEvents&select=(select EPOLeafNode.NodeName)&where=(where (and (eq EPOEvents.ThreatEventID 1203) (isBlank EPOEvents.ThreatEventID)))


      But all it does is return an empty set, which is correct, since i'm asking it to give me records where a value is actually 2 different values. So how should I be structuring my API query to give me results similiar to my SQL query. I'm trying to be good and only use the API.