ePO Web API : remote executeQuery | joinTables

[mr54]

Hello,

I'm currently trying to execute queries with ePO's API, and it's working pretty well, if I don't try to join tables.

For example, I would get Client Events with a task. The SQL code is :

select [EPOProductEvents].[ReceivedUTC], [EPOProductEvents].[TVDEventID], [EPOEventFilterDesc].[Name], [EPOProductEvents].[TVDSeverity], [EPOProductEvents].[IPV6], [EPOProductEvents].[AutoID] from [EPOProductEvents] left join [EPOEventFilterDesc] on [EPOProductEvents].[TVDEventID] = [EPOEventFilterDesc].[EventId] and (EPOEventFilterDesc.Language='0409') where ( [EPOProductEvents].[ReceivedUTC] between DATEADD(HOUR, DATEDIFF(HOUR, 0, GETUTCDATE())-1, 0) and DATEADD(HOUR, DATEDIFF(HOUR, 0, GETUTCDATE()), 0) )

My target is : EPOProductEvent and I want to join this table with EPOEventFilterDesc to get some others attributes, but it doesn't work. Each time I precise the "joinTables" argument, I have some errors.

Except that  if I don't specify "joinTables", I have a result, with some empty attributes but with joinTables, it doesn't work anymore.

query = {
   'target':'EPOProductEvents',
   'select':'(select (top 10) EPOProductEvents.ReceivedUTC EPOProductEvents.TVDEventID EPOProductEvents.TVDSeverity EPOProductEvents.AutoID EPOProductEvents.IPV6 EPOEventFilterDesc.Name EPOEventFilterDesc.Name)',
   'joinTables':'EPOEventFilterDesc'
}

According to this guide : https://docs.mcafee.com/bundle/epolicy-orchestrator-web-api-reference-guide/page/GUID-F8BA4F50-D930-... , the method should work.

I try to precise the join attributes on 'where' cond, but same result.

Does anyone know how to do this?

Thanks in advance.

mr

[cdinet]

Create the same query in epo console, save it, then view query syntax.  That might show you what it would expect to see.

[mr54]

What do you meen by "query syntax" ? Because the first code I wrote was the SQL export of my query from epo.

select [EPOProductEvents].[ReceivedUTC], [EPOProductEvents].[TVDEventID], [EPOEventFilterDesc].[Name], [EPOProductEvents].[TVDSeverity], [EPOProductEvents].[IPV6], [EPOProductEvents].[AutoID] from [EPOProductEvents] left join [EPOEventFilterDesc] on [EPOProductEvents].[TVDEventID] = [EPOEventFilterDesc].[EventId] and (EPOEventFilterDesc.Language='0409') where ( [EPOProductEvents].[ReceivedUTC] between DATEADD(HOUR, DATEDIFF(HOUR, 0, GETUTCDATE())-1, 0) and DATEADD(HOUR, DATEDIFF(HOUR, 0, GETUTCDATE()), 0) ) order by [EPOProductEvents].[ReceivedUTC] asc

 I aslo have the xml code if I export the query :

<?xml version="1.0"?>
-<list id="1">
-<query id="2">
<dictionary id="3"/>
<name>MR</name>
<description>client test</description>
<target>EPOProductEvents</target>
<table-uri>query:table?orion.table.order=asc&orion.table.columns=EPOProductEvents.ReceivedUTC%3AEPOProductEvents.TVDEventID%3AEPOEventFilterDesc.Name%3AEPOProductEvents.TVDSeverity%3AEPOProductEvents.IPV6&orion.table.order.by=EPOProductEvents.ReceivedUTC</table-uri>
<condition-uri>query:condition?orion.condition.sexp=%28+where+%28+newerThanFull+EPOProductEvents.ReceivedUTC+3600000++%29+%29</condition-uri>
<summary-uri>query:summary?orion.query.type=table.table&orion.sum.query=false</summary-uri>
</query>
</list>

 No one tells me how to join tables if I'm not mistaken.