- Access exclusive content
- Connect with peers
- Share your expertise
- Find support resources
03-03-2022 09:23 AM
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
06-21-2022 11:59 AM
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"}}),
03-03-2022 06:05 PM
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...
Now as we look further down the page, we notice the details of a successful response. It contains three fields:
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.
03-08-2022 10:24 AM
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.
03-08-2022 05:17 PM
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.
06-21-2022 10:01 AM
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.
06-21-2022 10:14 AM
@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?
06-21-2022 10:15 AM
@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.
06-21-2022 10:21 AM
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"
06-21-2022 10:39 AM
@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.
06-21-2022 11:59 AM
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"}}),
06-21-2022 12:02 PM
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}}
-----> ^
06-30-2022 09:07 AM
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?
06-30-2022 09:39 AM
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?
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!