cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Aniketos
New Member

Using a loop to pull more than 100 items from API

I am attempting to connect to Palo Alto's Cortex XDR to pull events into Power BI for analysis on Mean Time to Resolution, totals per analyst, etc. 

I am able to authenticate and pull the last 100 events. According to the documentation, there is no way to pull more than 100 per query. 

This is the query being ran. 

 

 

let
    body = "{ ""request_data"": {}}",
    GetJson = Web.Contents("https://api-{company}.xdr.us.paloaltonetworks.com/public_api/v1/incidents/get_incidents/",
        [  
            Headers = [#"Content-Type" = "application/json",
                #"x-xdr-auth-id" = "{API ID}",
                #"Authorization" = "{API 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)
in
    Result

 

 

 When looking at the documentation, I notice it has a way to query specific ranges of incidents (not exceeding 100) and attempted to plug that in but was not able to pull the designated events. 

 

Is there a way to loop the API pull to ingest all the events without knowing the total (will be ever increasing)?

1 ACCEPTED SOLUTION
mahoneypat
Super User IV
Super User IV

Please check out this video for how to do this.

Power BI - Tales From The Front - REST APIs - YouTube

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

3 REPLIES 3
v-yingjl
Community Support
Community Support

Hi @Aniketos ,

You can try to pagination/paginate in the loop query, please refer:

  1. how to create a query that paginates? 
  2. Pagination and DO/WHILE in Power BI / Power Query 

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

mahoneypat
Super User IV
Super User IV

Please check out this video for how to do this.

Power BI - Tales From The Front - REST APIs - YouTube

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

lbendlin
Super User III
Super User III

Per the documentation you have to use the Offset parameter for your result pagination

 

You have two options

1. pre-create a list of offsets  and then call the API for each row in the list until the returned row count is smaller than 100

2. use a recursive query that calls itself as long as the returned row count is 100, and that collects all the results along the way

 

Option 1 is easier on the Power Query resources but a bit tricky to terminate correctly. Option 2 is more accurate but requires much more resources  (like a snake that gets longer with each bite)

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors