As a PowerBI administrator, it's always difficult to monitor the on-premises gateways within our organization, especically when the number of gateways has been growing rapidly. Today I'm going to explain how you can effecitivley administrate and monitor those gateways in PowerBI itself. Along with that, I'm going to give lots of Tips & Tricks.
The below Visio Pic is the Architecture of our Entier Gateway monitoring System.
PowerBI Service Account
In order to begin the first most step is to have a Service Account with a Pro License, here we are going to call some API's. So instead of using your persona account, you should have a service account with a pro license.
Once you have a service account you should be add these service account to all your Gateway's as a Administrator.
Tips:Instead of adding the service account directorly to the gateway admin's try to create a Distribution List (Group) and add the Service account to the DL, for ease of management.
In order to call the API's we also need to have a Application Id (Client ID) for that you need the below API permissions.
Now we have the Service account and Application id with these we are ready to generate the Bearer token and call the API.
Bearer Token using PowerShell
We are going to automating the part of generating the bearer token to call the API. So for this the best way is to use PowerShell, we can also use PBI M-query to generate the Bearer but in that case we need to provide the service account user name and password with in the report which is a kind of unsecure where we expose the username and password with-in the report.
The below PowerShell script I've used to generate the Bearer token and save it in a Text File
Tips:I've used a command [Net.ServicePointManager] :: SecurityProtocol = [Net.SecurityProtocolType]::Tls12 which will enforce to use the TLS12 if you don't put this in some of the old machines it will fail as those are using the old TLS Version.
Inorder to get the current gateway status we need to call another api called Gateways - Get Gateway unforunately this API call we need to for each and every gateways seperately and not as a bulk operation.
Let's continue our coding, the below code will call the Get Gateway API for each and every gateway id which we obtained from the previous result
Tips : In order to identify the gateway environment like Production/non-production, you can specify in the gatewayDepartment while configuring the gateway itself, which will be used for us to create filters in our report.
Now after doing some cleanup in the modeling with the below code
Now I'm going to group them based on the data. I've named this query as ClusterGateways because this information is on the cluster level. Then I've created another query with the Reference of the ClusterGateways named as Gateways because this is going to be the tell how many nested/child clusteres available with-in the main Cluster Table
Now Lets me do some modeling in the Gateways with the below code.