topic Re: Cortex XDR - Endpoint/Incident API Limit (100) - PowerBI Query in Cortex XDR Discussions

Cortex XDR - Endpoint/Incident API Limit (100) - PowerBI Query

brownchris — Thu, 03 Mar 2022 17:23:47 GMT

I am attempting to pull in endpoint/incident data using the appropriate API in PowerBI. However, there's a limit of 100 . I tried adding a separate custom column anticipated that my total number of incidents would be let's say "x" value, but that just repeats the already pulled 100 rows.

See a copy of the Query I am using below:

let
body = "{ ""request_data"": {}}",
GetJson = Web.Contents("[URL_GOES_HERE].xdr.us.paloaltonetworks.com/public_api/v1/incidents/get_incidents/",
[
Headers = [#"Content-Type" = "application/json",
#"x-xdr-auth-id" = "[Auth_ID_GOES_HERE]",
#"Authorization" = "API_Key_GOES_HERE"
],
Content = Text.ToBinary(body)
]
),
FormatAsJson = Json.Document(GetJson),
#"Converted to Table" = Record.ToTable(FormatAsJson),
Value = #"Converted to Table"{0}[Value],
Incidents = Value[incidents],
Result = Table.FromRecords(Incidents),
#"Changed Type" = Table.TransformColumnTypes(Result,{{"creation_time", Int16.Type}})
in
#"Changed Type"

Any help in correcting my logic will be greatly appreciated

Re: Cortex XDR - Endpoint/Incident API Limit (100) - PowerBI Query

bbarmanroy — Fri, 04 Mar 2022 02:05:56 GMT

Hi @brownchris your analysis is correct. By default, the query returns 100 results. This is explained in the API description page.
https://docs.paloaltonetworks.com/cortex/cortex-xdr/cortex-xdr-api/cortex-xdr-apis/incident-management/get-incidents.html

Now as we look further down the page, we notice the details of a successful response. It contains three fields:

total number of results
the number of results returned in the response
the results itself (the count of results is the same as Step 2).

Assuming you need to retrieve 160 incidents (i.e., more than 100), you'll need to make 2 calls at a minimum. The first call will retrieve the first 100 results, the next one will retrieve the remaining 60 results. This is how the response will look like:

First call
total_count = 160

result_count = 100

incidents = {[100 incidents]}

Second call
total_count = 160

result_count = 60

incidents = {[60 incidents]}

The way to do that is by modifying the search_from and search_to fields in the request_data parameter in successive calls. This will return the results corresponding to the offsets that you're requesting for.

Here is an example for retrieving larger datasets using the get_endpoints API. I have used Python as the scripting language, but you can use any language/script or querying tool that can make POST calls.

Here, you can see the total_count value as 113, and the result_count value as 12. The way I am able to retrieve those specific entries is by changing the values of the search_from and search_to fields. As this is an example, I have hard-coded the values. In your case, you should programmatically change the values of the two fields by incrementing the values accordingly using counters and loops.

Let us know how it goes.

Re: Cortex XDR - Endpoint/Incident API Limit (100) - PowerBI Query

brownchris — Tue, 08 Mar 2022 18:24:00 GMT

Thank you @bbarmanroy, really appreciate your response. The challenge I am finding PowerBI doesn't like when I declare the search parameters. I get a bad request error when I attempt to add the "search_from" & "search_to" parameters.

Re: Cortex XDR - Endpoint/Incident API Limit (100) - PowerBI Query

bbarmanroy — Wed, 09 Mar 2022 01:17:48 GMT

Hi @brownchris I don't think I see the parameters in the 'body' variable that you have defined.
You might want to try wrap 'body' in 'Json.FromValue()' or check in PowerBI forums as this seems to be related to PowerBI and not Cortex XDR APIs.

Re: Cortex XDR - Endpoint/Incident API Limit (100) - PowerBI Query

MichaelEdwards2 — Tue, 21 Jun 2022 17:01:50 GMT

Hi Chris, Did you ever get this resolved?, I am also finding the same challenge. I have tried different combinations for the data search strings and it is not accepting on each.

Re: Cortex XDR - Endpoint/Incident API Limit (100) - PowerBI Query

SilviuMihailDascalu — Tue, 21 Jun 2022 17:14:02 GMT

@MichaelEdwards2 - can you paste the request that are you executing against the API? also what are the errors that are you getting? may I also ask if you have tried these requests with curl and see if they're working before implementing them in PowerBI?

Re: Cortex XDR - Endpoint/Incident API Limit (100) - PowerBI Query

brownchris — Tue, 21 Jun 2022 17:15:44 GMT

@MichaelEdwards2 I did get it to work for endpoints, in my code I used the /get_endpoint that returned the maximum result set size is 100. So I used /get_endpoints instead. Try that and let me know.

I do ran into another problem however, the date fields are coming over as a date, and converting them leads to an error.

Re: Cortex XDR - Endpoint/Incident API Limit (100) - PowerBI Query

MichaelEdwards2 — Tue, 21 Jun 2022 17:21:44 GMT

I dont get any error with the below, it just does not limit the results (.i.e get 100 results, instead of 10)

let
body = "{ ""request_data"": {}}",
GetJson =Web.Contents("https://api-mycompany.xdr.eu.paloaltonetworks.com/public_api/v1/incidents/get_incidents/",
[
Query = [
search_from = "0",
search_to = "10"
],
Headers=[#"Content-Type"="application/json",
#"x-xdr-auth-id"="34",
Authorization="My secret Key"
],
Content = Text.ToBinary(body)
]
),
FormatAsJson = Json.Document(GetJson),
#"Converted to Table" = Record.ToTable(FormatAsJson),
Value = #"Converted to Table"{0}[Value],
Incidents = Value[incidents],
Result = Table.FromRecords(Incidents),
#"Changed Type" = Table.TransformColumnTypes(Result,{{"creation_time", Int16.Type}})
in
#"Changed Type"

Re: Cortex XDR - Endpoint/Incident API Limit (100) - PowerBI Query

SilviuMihailDascalu — Tue, 21 Jun 2022 17:39:41 GMT

@MichaelEdwards2 - can you make sure you add the both properties search_from and search_to into the body variable? These two properties should be part of the request_data element.

Change

body = "{ ""request_data"": {}}",

body = {"request_data": {"search_from":0,"search_to":10}}

Try this and let me know if it works.

Re: Cortex XDR - Endpoint/Incident API Limit (100) - PowerBI Query

MichaelEdwards2 — Tue, 21 Jun 2022 18:59:22 GMT

Hi Chris, The "get_endpoints" api is only related to endpoint data like OS, version etc and not really what Im after. I need to create a monthly report for incidents and the "Get_incidents" api seems to be the logical one to use. When you say you had issues with the dates, do you mean the epoch format ?I

I used the below query to resolve this if this helps ?

#"Added Custom" = Table.AddColumn(#"Extracted Values2", "Unix Epoch Time", each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, +1, 0, [Column1.last_seen]/1000)),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Unix Epoch Time", "Last_Seem_UK_Time"}}),

Re: Cortex XDR - Endpoint/Incident API Limit (100) - PowerBI Query

MichaelEdwards2 — Tue, 21 Jun 2022 19:02:59 GMT

Thanks for the response, unfortuntly I get the below response. I did try changing the ":" to a "=", but it did not work.

Expression.SyntaxError: Token Comma expected.

0001:= let
0002:body = {"request_data": {"search_from":0,"search_to":10}}

-----> ^

Re: Cortex XDR - Endpoint/Incident API Limit (100) - PowerBI Query

brownchris — Thu, 30 Jun 2022 16:07:52 GMT

Yes this function worked for the time.

For the Incidents the reply should be returning a list to get all values, but instead it is returning a record and I am not able to find a way around that as yet.

Did you get it to work?

Re: Cortex XDR - Endpoint/Incident API Limit (100) - PowerBI Query

brownchris — Thu, 30 Jun 2022 16:39:34 GMT

Yes, this fixed the time for me.

For the incidents call, the reply is returning a record and to get all incidents it should be a list and I haven't found a way to do that as yet.

Have you had any success?