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.