Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)?
Solved! Go to Solution.
Please check out this video for how to do this.
Power BI - Tales From The Front - REST APIs - YouTube
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi @Anonymous ,
You can try to pagination/paginate in the loop query, please refer:
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.
Please check out this video for how to do this.
Power BI - Tales From The Front - REST APIs - YouTube
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Working for me with this tutorial and only the following one funtion with absolute URL :
let
Source = Json.Document(Web.Contents("https://api.giphy.com/v1/gifs/search?api_key=jO78X8NofWbkPPHJnNCPZpOhsdCh9oNJ&q=hamburger&limit=25&offset=0&rating=g&lang=en")),
pagination = Source[pagination],
total_count = pagination[total_count],
Custom1 = List.Numbers(0, total_count/50, 50),
#"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Json.Document(Web.Contents("https://api.giphy.com/v1/gifs/search?api_key=jO78X8NofWbkPPHJnNCPZpOhsdCh9oNJ&q=hamburger&limit=25&offset=0&rating=g&lang=en"&[Column1]))),
#"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"data"}, {"data"}),
#"Expanded data" = Table.ExpandListColumn(#"Expanded Custom", "data"),
#"Expanded data1" = Table.ExpandRecordColumn(#"Expanded data", "data", {"title", "images"}, {"title", "images"}),
#"Expanded images" = Table.ExpandRecordColumn(#"Expanded data1", "images", {"downsized"}, {"downsized"}),
#"Expanded downsized" = Table.ExpandRecordColumn(#"Expanded images", "downsized", {"url"}, {"url"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded downsized",{{"title", type text}, {"url", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Column1"})
in
#"Removed Columns"
You may not want to post API keys here.
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |