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

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

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

L1 Bithead

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

1 accepted solution

Accepted Solutions

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"}}),

 

 

 

View solution in original post

12 REPLIES 12

L5 Sessionator

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-manageme...

bbarmanroy_0-1646357889134.png

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

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

bbarmanroy_1-1646358257711.png



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.

bbarmanroy_5-1646359492595.png

 



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.

bbarmanroy_4-1646359128641.png

 

Let us know how it goes.

 

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.

 

brownchris_0-1646763809616.png

 

L5 Sessionator

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. 

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.

@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?

Silviu-Mihail Dascalu

@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.

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"

@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"": {}}",

to

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

 

Try this and let me know if it works.

 

 

Silviu-Mihail Dascalu

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"}}),

 

 

 

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}}

----->                                      ^

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?

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?

  • 1 accepted solution
  • 8386 Views
  • 12 replies
  • 0 Likes
Like what you see?

Show your appreciation!

Click Like if a post is helpful to you or if you just want to show your support.

Click Accept as Solution to acknowledge that the answer to your question has been provided.

The button appears next to the replies on topics you’ve started. The member who gave the solution and all future visitors to this topic will appreciate it!

These simple actions take just seconds of your time, but go a long way in showing appreciation for community members and the LIVEcommunity as a whole!

The LIVEcommunity thanks you for your participation!