User Profile
gabrielsroka
Frequent Visitor
Joined 7 years ago
User Widgets
Contributions
Re: API calls with Pagination
Hi Anonymous I spent some more time and I got a basic Custom Data Connector to work. It needs more work, but this should get you started. This works with Power BI Desktop (as of now). Excel support may be added by Microsoft in the future. 1. In your Documents folder, create a folder called "Power BI Desktop\Custom Connectors" (mine already existed). 2. Download the code from https://github.com/gabrielsroka/gabrielsroka.github.io/blob/master/Okta.pq to Okta.pq and save it in the "Custom Connectors" folder. 3. In Power BI Desktop, go to File | Options and settings | Options. 4. Go the Security tab. 5. Under Data Extensions, select Allow any extension to load without validation or warning. 6. Restart Power BI Desktop See https://github.com/Microsoft/DataConnectors for more info. One thing I found by researching and experimenting (that I didn't see in the documentation) is you can use the Okta.pq file, you don't have to create a .mez file (which is a Zip file). You can even edit the Okta.pq file while it's loaded in Power BI Desktop -- this makes development much easier.22KViews1like3CommentsRe: API calls with Pagination
Hi Anonymous Here's some proof-of-concept code that might help. It doesn't use the HTTP response headers for pagination (since they're only available in a custom Data Connector such as https://github.com/gabrielsroka/gabrielsroka.github.io/blob/master/Okta.pq), so it makes repeated calls to /logs. It works, but would need more work to make it respect rate limits and to wait, maybe using Function.InvokeAfter(), etc. See the comments and variables below for more info. let startDate = #date(2018, 11, 1), // Change this. baseUrl = "https://XXXXXXXXXXXXXXXXXXXXX.oktapreview.com", // Change this. token = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX", // Change this. iterations = 2, // Max rate limit for "/logs" is 120 calls per minute. limit = 2, // For "/logs", this must between 1 and 1000. Set it lower while debugging. logsUrl = orgUrl & "/api/v1/logs", headers = [ Authorization = "SSWS " & token, Accept = "application/json", #"Content-Type" = "application/json", #"User-Agent" = "excel-power-query/2016 Windows/10.0.17134.345" ], iso = "s", // Fetch 1 day at a time. If you have more than 1000 events per day, you'll // have to use a more granular time-window, eg, 1 hr at a time, or 1 minute at a time. GetByDateRange = (i) => let since = Date.ToText(startDate + #duration(i, 0, 0, 0), iso), until = Date.ToText(startDate + #duration(i + 1, 0, 0, 0), iso), query = [limit = Text.From(limit), since = since, until = until], logs = Json.Document(Web.Contents(logsUrl, [Headers = headers, Query = query])) in logs, GeneratedList = List.Generate( () => [i = 0, res = GetByDateRange(i)], each [i] < iterations, each [i = [i] + 1, res = GetByDateRange(i)], each [res]), table1 = Table.FromList(GeneratedList, Splitter.SplitByNothing(), {"log"}), table2 = Table.ExpandListColumn(table1, "log"), fieldNames = {"displayMessage", "eventType", "published", "severity"}, Logs = Table.ExpandRecordColumn(table2, "log", fieldNames) in Logs22KViews0likes4CommentsRe: API calls with Pagination
Hi Anonymous, Okta's API returns an HTTP response header called "Link" which contains a URL for the next page. It doesn't seem to be currently possible for Power Query to look at the HTTP "Link" response header. I tried the code below, and it does return some HTTP response headers, but not the "Link" header. response = Web.Contents(url, [Headers = [Authorization = "SSWS " & token]]), headers = Value.Metadata(response)[Headers] One quick workaround is to use a PowerShell script like: https://github.com/mbegan/Okta-Scripts/blob/master/saveEventLogs.md to export logs to a JSON file which can be consumed by Power Query. An alternative is to write a custom Data Connector since that has the ability to see all HTTP response headers, eg: https://github.com/gabrielsroka/gabrielsroka.github.io/blob/master/Okta.pq22KViews0likes5CommentsRe: how to add "rel=next" in power query(pagination)?
Hi srpandya95 Okta's API returns an HTTP response header called "Link" which contains a URL for the next page. It doesn't seem to be currently possible for Power Query to look at the HTTP response headers (although it does support HTTP request headers, but that's a different story). One quick workaround is to use a PowerShell script like: https://github.com/mbegan/Okta-Scripts/blob/master/saveEventLogs.md to export logs to a JSON file which can be consumed by Power Query. An alternative would be to write a custom connector. Hopefully Microsoft will add the ability to use HTTP response headers in a future release.6.6KViews0likes1Comment
Data Privacy
Microsoft Fabric Community and Privacy
To learn more about how we manage your data, please review the Microsoft Fabric Community Data Privacy guide.