topic Re: how to extract DMV data from multiple warehouses/lakehouses? in Data Warehouse

how to extract DMV data from multiple warehouses/lakehouses?

Jeanxyz — Fri, 05 Sep 2025 08:10:42 GMT

Just out of curiosity, I'm wondering if it's possible to create a DMV query that loops over all the lakehouses and warehouses across multiple workspaces? In this way, a Fabric admin can monitor queries in one place.

Below is my DMV, it extracts queries run in stock_lakehouse during the past hour. I want to avoid inputting the lakehouse name in a hard code, is it possible to get a lot of all the workspaces, then a list of lakehouses and warehouses for each workspace?

SELECT * FROM stock_lakehouse.queryinsights.exec_requests_history WHERE start_time >= DATEADD(MINUTE, -60, GETUTCDATE())

Re: how to extract DMV data from multiple warehouses/lakehouses?

v-echaithra — Fri, 05 Sep 2025 11:45:09 GMT

Hi Jeanxyz ,

Thank you for reaching out to Microsoft Community.

Currently, Microsoft Fabric does not support running a single DMV query that loops over all lakehouses and warehouses across different workspaces. There is no built-in or native "cross-workspace" DMV querying mechanism available. However, it is possible to build an automated, orchestrated solution using tools like Microsoft Fabric APIs, Azure Functions, or Data Factory pipelines to dynamically collect this information.

The process begins by retrieving a list of all workspaces. To do this, you'll need admin permissions and can use endpoints such as admin/workspaces from the Power BI REST API or Microsoft Graph API. Once you have the workspaces, you can loop through each one and identify the Fabric items within them. Specifically, you should look for items of type "Lakehouse" and "Warehouse" in each workspace. These can be found using Fabric APIs or Graph API beta endpoints.

After identifying the lakehouses and warehouses, you can run the appropriate DMV queries for each. This can be done using a Fabric Notebook, Data Pipeline, or Azure Function. For example, you might run a query like:

SELECT * FROM <lakehouse_or_warehouse>.queryinsights.exec_requests_history
WHERE start_time >= DATEADD(MINUTE, -60, GETUTCDATE())

The results from each query should then be aggregated and stored in a central location, such as a designated lakehouse or warehouse (e.g., admin_monitoring_lakehouse). This centralized data can then be used for reporting or monitoring via Power BI dashboards or other Fabric tools.

Re: how to extract DMV data from multiple warehouses/lakehouses?

v-echaithra — Mon, 08 Sep 2025 09:15:55 GMT

Hi Jeanxyz ,

We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.

Best Regards,
Chaithra E.

Re: how to extract DMV data from multiple warehouses/lakehouses?

Jeanxyz — Mon, 08 Sep 2025 09:35:31 GMT

It looks one can get a list of items in fabric workspace via sempy packages. I will try this in notebook when I get some time this weekend. Will post here if I get good news.

https://learn.microsoft.com/en-us/python/api/semantic-link-sempy/sempy.fabric?view=semantic-link-python

Re: how to extract DMV data from multiple warehouses/lakehouses?

spaceman127 — Mon, 08 Sep 2025 11:07:30 GMT

Hi Jeanxyz ,

i have a script.

This will display all items in the respective workspaces.

I use this often in customer projects and it helps enorm.

# 1) Install Package Semantic Link %pip install --upgrade semantic-link-sempy -q # 2) Imports the Libraries import sempy.fabric as fabric import pandas as pd from IPython.display import display, HTML def summarize_workspace_items(full_display: bool = True): # 3) Get Workspaces from the Tenant workspaces = fabric.list_workspaces() workspaces = workspaces.query("Type == 'Workspace'") # only normal workspaces, not the own workspace rows = [] for ws_id, ws_name in zip(workspaces["Id"], workspaces["Name"]): items = fabric.list_items(workspace=ws_id) if items.empty: continue # Automatically determine all item types for item_type in items["Type"].unique(): subset = items[items["Type"] == item_type].copy() if subset.empty: continue subset["workspace_name"] = ws_name subset["Item Type"] = item_type subset["item_id"] = subset["Id"] subset["item_name"] = subset.get("Name", subset.get("displayName", subset.get("Display Name"))) rows.append(subset[["item_id", "item_name", "workspace_name", "Item Type"]]) # 4) Combine all rows if rows: df = pd.concat(rows, ignore_index=True) else: df = pd.DataFrame(columns=["item_id", "item_name", "workspace_name", "Item Type"]) # 5) Sort the DataFrame df = df.sort_values(["workspace_name", "item_name"]).reset_index(drop=True) # 6) Display the full table if requested / Its besser to show if full_display: pd.set_option("display.max_rows", None) pd.set_option("display.max_columns", None) pd.set_option("display.width", 200) display(HTML(df.to_html(max_rows=None, max_cols=None))) return df # Execute the function and get the DataFrame df_items = summarize_workspace_items()

i Hope that help you.

Best regards

Re: how to extract DMV data from multiple warehouses/lakehouses?

Jeanxyz — Tue, 09 Sep 2025 14:58:42 GMT

Thanks a lot, it works. I also managed to extract queries from warehouses/lakehouses across workspaces thanks to the item extraction function.