Cortex XDR Query API into power BI

[neskiz]

Good afternoon and hope I can get some help on this. I am looking to connect Power BI to Cortex API to pull in data for reporting. Cortex has documentation showing that you can use the Start an XQL query to query data from a API call. The below script is what I am using which can work up to the queryId in step 1. From step 2, I get an error and cannot get the return I need. I have also included the resource link for Cortex documentation. 

Get XQL Query Quota • Cortex XDR REST API • Palo Alto Networks documentation portal

// XQL query to fetch endpoint data
XqlQuery = "dataset = endpoints | fields endpoint_id, endpoint_name, ip_address, last_seen, first_seen, endpoint_status",

// Step 1: Get the query ID
StartResponse = try Json.Document(Web.Contents(
BaseUrl & "/public_api/v1/xql/start_xql_query/",
[
Headers = [#"Content-Type"="application/json", #"x-xdr-auth-id"=ApiId, #"Authorization"=ApiKey],
Content = Text.ToBinary("{ ""request_data"": { ""query"": """ & XqlQuery & """ } }")
]
)) otherwise error "Failed to start XQL query. Check API credentials or URL.",
QueryId = if Value.Is(StartResponse, type text) then StartResponse else StartResponse[reply],

// Step 2: Get query results using streaming endpoint
ResultsResponse = try Web.Contents(
BaseUrl & "/public_api/v1/xql/get_query_results_stream/",
[
Headers = [#"Content-Type"="application/json", #"x-xdr-auth-id"=ApiId, #"Authorization"=ApiKey, #"Accept-Encoding"="gzip"],
Content = Text.ToBinary("{ ""request_data"": { ""query_id"": """ & QueryId & """, ""format"": ""json"" } }")
]
) otherwise error "Failed to retrieve query results. QueryId: " & QueryId,

// Decompress with fallback
DecompressedResults = try Binary.Decompress(ResultsResponse, Compression.GZip) otherwise ResultsResponse,
JsonResults = Json.Document(DecompressedResults),
Results = JsonResults[results],

// Step 3: Convert results to a table
TableFromResults = Table.FromList(Results, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ExpandedResults = Table.ExpandRecordColumn(TableFromResults, "Column1", {"endpoint_id", "endpoint_name", "ip_address", "last_seen", "first_seen", "endpoint_status"})
in
ExpandedResults

[v-nmadadi-msft]

Hi @neskiz,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the community members for the issue worked. If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Thanks and regards

[v-nmadadi-msft]

Hi @neskiz,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If our responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-nmadadi-msft]

Hi @neskiz,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[neskiz]

Thank you for your help ZhangKun. Can I get some help from you as to how I go about this adjustment? not sure I understand how I can achieve this. 

[ZhangKun]

Since I haven't used this product, I can only give you some hints from the content of the documentation:

Start an XQL query. There is nothing wrong with your code at this step.

Provide the query ID (query_id) obtained in the previous step to get_query_results. Pay attention to the parameter named pending_flag, because it determines whether to block (the server returns the result after successful execution. But if the blocking time is too long, the request will time out) or asynchronous (if this method is used, it is necessary to loop to determine whether the XQL query has been completed).
If the number of results is less than 1000, you don't need to do anything extra. But if it is more than 1000, you need to get the stream_id returned by get_query_results, and then you need to pass the stream_id to get_query_results_stream.
I don't see in the documentation how get_query_results returns the stream_id, you'll need to test this yourself.

Call get_query_results_stream normally and get the remaining results.

[ZhangKun · Accepted solution]

You can't call "Get XQL Query Results Stream API" directly.

Pay attention to this part of the document.

Running XQL Query APIs 

Run the following APIs to call an XQL query:

• Start an XQL Query—Run an XQL query. Response returns a unique execution ID used to retrieve the results by the Get XQL Query Results API.
• Get XQL Query Results—Retrieve XQL query results. API displays up to 1,000 results. If query generated more than 1,000 results, the response returns a unique stream ID used to retrieve additional results by the Get XQL Query Results Stream API.
• Get XQL Query Results Stream—Retrieve XQL query with more than 1,000 results.