On premise gateway logs - Dataset and Gateway connecton

[BarGraph2020]

Hello,

We have a Power BI On Premise gateway server. I can loginto the server and run the Gateway Performance Monitoring Pbit file (Monitor and optimize on-premises data gateway performance | Microsoft Docs) which gave me information such as datasource type, the query executed, excution length. What I need is the Dataset name and who configured the scheduled refresh. 

When I look Gateway performance template report and notice some long running queries I want to inform the user of the dataset but I cant find that information. I may be missing something. The fields does not have any  Dataset or user names. I thought this would be easy information to find but found out its not.

How do you admins maintain /monitor your on premise gateway performance.

Thanks,

[Anonymous]

@lbendlin Asking nearly a year later 🙂 Do you know if there is now a way to find out dataset and workspac information from the gateway logs? I do not have the Power BI admin role so I cannot use the Power BI service logs via the REST API. 

[lbendlin]

Sort of. There is a slightly modernized template file you can try on your gateway cluster members.  I found it to be rather lacking and have implemented my own process based on the same files. The hard part is to make the gateway service commit the logs to disk for a better near real time experience. After all these years we still don't have anything close to real time.

Monitor and optimize on-premises data gateway performance | Microsoft Learn

[Anonymous]

Thanks for the quick reply.  I have tried this already.  While it does contain the datasource type and the datasource information (e.g. ODBC and dsn=foo), it does not have the Dataset or Workspace info which is what I am looking for.  I'm an admin for the gateways but not Power BI Service.  The REST APIs need you to be the Service admin for sending requests to the group endpoints. 😞  Thanks though! 

[lbendlin]

It won't have the dataset information as that is not something the gateway needs to know. All it needs is the data source and the credentials, and it gets these from the load balancer when it gets the job assignment.

Look into correlating  with the system logs via the query ID and Request ID.

[v-luwang-msft · Accepted solution]

Hi  @BarGraph2020 ,

Refer the article ,in the below,it tells:

Long-running queries might require additional modification on your data source or further optimization of the query itself. This could be either for Power BI refreshes or for direct database queries, like Power BI DirectQuery, Power Apps, or Azure Logic Apps.

By default, the gateway performs basic logging. If you're investigating slow-performing queries, in addition to using the performance monitoring feature, you can temporarily enable Additional logging to gather additional log information. To do this, in the on-premises data gateway app select Diagnostics > Additional logging.

through the operation according article ,we could get the below（Data set IDs with slow refresh efficiency）:

QueryAdditionalInformation is: {
  "Application": "Dataset-Premium",
  "ObjectId": "6de5b524-8a04-4578-961d-e65b2bf3dcd4",
  "ApplicationContext": "{\"DatasetId\":\"6de5b524-8a04-4578-961d-ej67gdf3dcd4\",\"Sources\":[{\"ReportId\":\"e0cec7bc-f53d-4174-b551-678656fba\"}]}"
}.

 According dataset id ,how to find dataset ,refer:https://www.cathrinewilhelmsen.net/how-to-find-dataset-id-power-bi/ 

And you could download the file from the following articles,The author shares his experience in using it, which can be learned from：

https://insightsquest.com/2016/08/08/monitor-on-premises-data-gateways/ 

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[lbendlin]

1. The gateway cluster members have no idea who requested the dataset refresh. This is all managed by the Power BI service.  Use the Power BI service audit logs to retrieve that information

2. We are monitoring gateway CPU usage, memory and disk health.  We found that the gateway logs are mostly useless, even with that template, and with additional logging enabled. Microsoft uses internal tools to analyze the gateway logs that they ask you to send them, but they won't make these tools available to us.

This is a moderately busy gateway with ten cluster members. A simple Powershell script runs every five minutes and pushes rows into a hybrid streaming dataset.