Please include Gateway Data Source Status information in the "Gateway Data Sources" source

[lbendlin]

Would be nice if you could include the data source status in the report - that allows me to chase after expired credentials or borked sources

[migueesc123]

hey! do you happen to know in which endpoint that data should be coming from? @lbendlin

[lbendlin]

This is the call https://docs.microsoft.com/en-us/rest/api/power-bi/gateways/getdatasourcestatus

Note that the call returns a non-conformant 200 response (with empty body). Meaning the "connection is ok" result will give you an error, and a "connection is not ok" result will give you a proper message. I can explain more if needed. Here is the current Power Query I am using.

(GatewayId as text, ConnectionId as text) => let md = Value.Metadata(Web.Contents("https://api.powerbi.com/v1.0/myorg/gateways/" & GatewayId & "/datasources/" & ConnectionId & "/status", [Headers=[Authorization=Authorization],ManualStatusHandling = {400}])) in if md[Response.Status] = 200 then true else false

[klinejordan]

That call is indeed special in that it only returns for data sources with errors. I've gotten it to work in a Logic Apps solution to dump all the GET commands to json files in blob storage (I don't really use this custom connector anymore unfortunately) but that's an interesting technique for handling the 400 errors in Power Query.

[migueesc123]

what made you stop using the connector @klinejordan ?

[klinejordan]

I needed this data dumped to a location that wasn't just a Power BI dataset because I needed extensibility to integrate with other sources, and since custom connectors can't be used in dataflows I ended up just writing Logic Apps with HTTP requests that do the same thing but dump to blob storage and from there I can use it where ever. I was also able to get the GetDatasetDatasources commands to work across my entire tenant in a Logic App (it takes about 12 hours) but Power BI couldn't handle that.

AdminDatasetLogicAppsTemplates.zip

[klinejordan]

To be clear I still use the connector for ad hoc stuff because its easier, but for the mission critical reporting I need refreshed every day or multiple times a day, I use Logic Apps.

[lbendlin]

That call is indeed special in that it only returns for data sources with errors. I've gotten it to work in a Logic Apps solution to dump all the GET commands to json files in blob storage (I don't really use this custom connector anymore unfortunately) but that's an interesting technique for handling the 400 errors in Power Query.

All credits go to @ImkeF - she pushed me to that version.

[ImkeF]

Thanks @lbendlin , yes, I've described the useful metadata that comes with the Web.Contents function here: https://www.thebiccountant.com/2020/09/19/retrieve-header-fields-like-response-status-power-bi-and-power-query/

[migueesc123]

Hey! I've carefully reviewed this and I don't think this would be a good fit for the connector even if we leave this data as a new "table" column with some level of lazy evaluation. The best approach would be to use the GETData function included in this connector in combination with the datasets table that the connector provides.

Nevertheless, thanks for the suggestion and for making me look into this!

[lbendlin]

@migueesc123 I would love to use GETData but in its current form it expects a well formed JSON output which this particular function doesn't deliver. Would it at least be possible to add a GETRawData function that only pulls the web contents, and allows for custom 400 handling?

GETRawData = (optional path as text) => let source = Web.Contents("https://api.powerbi.com/v1.0/myorg", [RelativePath = path,ManualStatusHandling = {400}]) in source;

[migueesc123]

Hey! Great contribution @lbendlin ! Could you please create a PR for it and add it where you see fit?

[lbendlin]

@migueesc123 I tried to create a PR but the site doesn't allow me to create a topic branch?

I added the above code to the Power Query code and it seems to work in my desktop. Didn't even have to restart Power BI, so I assume the connector code is re-read at runtime?

[klinejordan]

I have the hood open on a PR to add in Gateway Data Source Status as a separate table since it will slow down the performance of Gateway Data Sources significantly. In my tenant where we have about a thousand gateway connections it takes about 10-15 minutes to complete. This is analagous to the time it takes the Manage Gateways page in the service to fully open for me, even though that page can do concurrent requests for many data sources at a time while Power BI iterates one at a time.

I'll add the GetRawData function above as well.

[klinejordan]

PR submitted I just want @migueesc123 to take a look. I added a few other things as well.

[klinejordan]

Updated .mez available - @lbendlin can you check it out?

[lbendlin]

@klinejordan tested GatewayDataSourceStatus with somewhat mixed results. Out of my 288 connections I know that 53 are "bad". The new version of the connector only indicates 25 of them. Not sure if that is caused by timeouts or by unexpected return format.

It seems to be especially missing the SQL and Windows errors some of the time. The gateway admin page lists these errors for the connections missed by the connector: "Logging into Sql server failed. Possible reasons for this error include an invalid authentication mode, a missing login in Sql server, a login from an untrusted domain or a problem contacting the domain controller for the authentication request." "The on-premises data gateway's service account failed to impersonate the user."

The GETRawData option seems to catch all the bad connections reliably, but of course it doesn't provide the (very useful) additional error information, it only returns 200 or 400 via the Value.MetaData()[Response.Status] . That is acceptable in my scenario but it would certainly be nice to supplement it with the extended error information. I'll walk through your code some more to see if I can figure out where GatewayDataSourceStatus breaks down.

Edit: I think this is caused by the gateway not returning any details. Here is an example for a "bad" connection that GatewayDataSourceStatus is missing (return code is 400):

{ "error": { "code": "DM_GWPipeline_Gateway_ImpersonationError", "pbi.error": { "code": "DM_GWPipeline_Gateway_ImpersonationError", "parameters": {}, "details": [], "exceptionCulprit": 1 } } }

For comparison here is another 400 that is correctly reported:

{ "error": { "code": "DM_GWPipeline_Gateway_InvalidConnectionCredentials", "pbi.error": { "code": "DM_GWPipeline_Gateway_InvalidConnectionCredentials", "parameters": {}, "details": [ { "code": "DM_ErrorDetailNameCode_UnderlyingErrorCode", "detail": { "type": 1, "value": "-2146232060" } }, { "code": "DM_ErrorDetailNameCode_UnderlyingErrorMessage", "detail": { "type": 1, "value": "Login failed for user 'xxx'." } }, { "code": "DM_ErrorDetailNameCode_UnderlyingHResult", "detail": { "type": 1, "value": "-2146232060" } }, { "code": "DM_ErrorDetailNameCode_UnderlyingNativeErrorCode", "detail": { "type": 1, "value": "18456" } } ], "exceptionCulprit": 1 } } }

[migueesc123]

hey! @lbendlin unfortunately we've identified that this and other endpoints are not good candidates for a custom connector, so we have no plans to reliably try and get that data inside of the custom connector.

Nevertheless, this repo does accept new contributors in case you want to add new features or new functions to it. In case you'd like to fork it and create your own version of the connector, that's also possible as this repo is under the MIT license.

[lbendlin]

@migueesc123 you already helped me enough by enabling the GETRawData option.

I ended up modifying GatewayDataSourceStatus thusly:

let Source = PowerBIRESTAPI.Navigation(), Gateways = Source{[Key="Gateways"]}[Data], GatewayDataSources = Gateways{[Key="GatewayDataSources"]}[Data],

"Added Custom" = Table.AddColumn(GatewayDataSources , "Status", each try Json.Document(PowerBIRESTAPI.GETRawData("/gateways/"&[Gateway ID]&"/datasources/"&[Gateway Datasource ID]&"/status"),65001) otherwise null),

#"Added Custom1" = Table.AddColumn(#"Added Custom", "Reason", each try List.Select([Status][error][pbi.error][details], each _[code] ="DM_ErrorDetailNameCode_UnderlyingErrorMessage"){0}[detail][value] otherwise null)

in

"Added Custom1"

I do have a couple of other ideas for the connector, so if you could add me to the contributors then I might be able to bring these in.

Thank you again!

[migueesc123]

Hey @lbendlin You first need to fork the repo, make the changes on that repo and then do a PR against the migueesc123/PowerBIRESTAPI master branch.

Are you having issues creating a PR? I just gave it a try with a different user and it appears to be working as intended: https://github.com/migueesc123/PowerBIRESTAPI/pull/62

[lbendlin]

@migueesc123 I think I submitted my (first ever!) pull request. Please let me know if I did do anything wrong.

[migueesc123]

good job, @lbendlin ! I can see the PR. I've asked @klinejordan to check it out. I won't be able to check anything for the next 3 months or so due to other life-changing commitments.