Looking to create a report to list all the dashboards and widgets assigned.

[rader]

I'm looking to create a report to list all the dashboards and the widgets on them.

I've found some of the information in tsysWebTabs, tsysreportpages and tsysreports, and have a basic linking of the dashboard names to the tabs assigned to them, but I can't find the widget tables to link these together.

Here's what I've got so far:

Select Top 1000000 tsysWebTabs.TabID,
  tsysWebTabs.TabName,
  tsysreportpages.HomepageQuery,
  tsysreports.Reporttitle,
  tsysreports.Reportquery
From tsysWebTabs
  Inner Join tsysreportpages On tsysWebTabs.PageID = tsysreportpages.PageID
  Inner Join tsysreports On
      tsysreports.Reportquery = tsysreportpages.HomepageQuery
Order By tsysWebTabs.TabID

Some of the TabID's line up with the dashboard names, like Main page shown here, and some don't, like #2 Servers, which I don't have on my dashboard. The HomepageQuery and the Reporttitle are wrong too.

Is there a DB table for the Widgets?

Anyone else come across a solution for this?

[rader · Accepted solution]

Final version for me. I'm posting two sets of code for anyone to use. First will be the full debug version that allows me to see the trees in the forest. Second is just the cleaned-up version of the forest.

Looking at some of the results from the tsysWebTabControls.State field, it's apparent that some of the built-in widgets show some of the config options as on the events widgets, and most show window sizes, and digging deeper into the tables I was able to find menu placements (row, column info). But given my limited SQL skills, I'm at an end for this report. Maybe someone else can enhance it if you want.

Debug version:

Select Top 1000000 tsysWebUsers.Username As Agent,
  tsysWebTabs.TabName As [Dashboard Menu Name],
  tsysWebControls.ControlName As [Widget Type],
  tsysreports.Reporttitle As [Report Title],
  tsysWebControls.Category As [1: Stock, 2: Helpdesk, 3: Cust],
  tsysWebTabs.GlobalTabs As [Shared Menu],
  tsysWebControls.Path As Path,
  tsysreports.Reportquery As [Report Query Name],
  tsysWebTabControls.State As [HTML Query Statement]
From tsysWebTabs
  Inner Join tsysWebUsers On tsysWebUsers.PageID = tsysWebTabs.PageID
  Inner Join tsysWebTabControls On tsysWebTabs.TabID = tsysWebTabControls.TabID
  Inner Join tsysWebControls On tsysWebControls.ControlID =
      tsysWebTabControls.ControlID
  Left Join tsysreports On
      tsysreports.Reportquery = SubString(Cast(tsysWebTabControls.State As
      nvarchar(max)), CharIndex('web', tsysWebTabControls.State), 40)
Order By Agent,
  tsysWebTabs.TabID

Cleaned up:

Select Top 1000000 tsysWebUsers.Username As Agent,
  tsysWebTabs.TabName As [Dashboard Name],
  tsysWebControls.ControlName As [Widget Type],
  tsysreports.Reporttitle As [Report Title],
  tsysWebControls.Category As [1: Stock, 2: Helpdesk, 3: Cust],
  tsysWebTabs.GlobalTabs As [Shared Menu]
From tsysWebTabs
  Inner Join tsysWebUsers On tsysWebUsers.PageID = tsysWebTabs.PageID
  Inner Join tsysWebTabControls On tsysWebTabs.TabID = tsysWebTabControls.TabID
  Inner Join tsysWebControls On tsysWebControls.ControlID =
      tsysWebTabControls.ControlID
  Left Join tsysreports On
      tsysreports.Reportquery = SubString(Cast(tsysWebTabControls.State As
      nvarchar(max)), CharIndex('web', tsysWebTabControls.State), 40)
Order By Agent,
  tsysWebTabs.TabID

Thanks to @Mister_Nobody for the user tips. It really helped me understand the relationship in the data.

Enjoy. 

[Mister_Nobody]

  Case tsysWebControls.Category
    When 1 Then 'Stock'
    When 2 Then 'Helpdesk'
    When 3 Then 'Custom'
  End As Category,

[rader]

Ooo, I like that. 🙂

Thank you.

Select Top 1000000 tsysWebUsers.Username As Agent,
  tsysWebTabs.TabName As [Dashboard Name],
  tsysWebControls.ControlName As [Widget Type],
  tsysreports.Reporttitle As [Report Title],
  Case tsysWebControls.Category
    When 1 Then 'Stock'
    When 2 Then 'Helpdesk'
    When 3 Then 'Custom'
  End As Category,
  tsysWebTabs.GlobalTabs As [Shared Menu]
From tsysWebTabs
  Inner Join tsysWebUsers On tsysWebUsers.PageID = tsysWebTabs.PageID
  Inner Join tsysWebTabControls On tsysWebTabs.TabID = tsysWebTabControls.TabID
  Inner Join tsysWebControls On tsysWebControls.ControlID =
      tsysWebTabControls.ControlID
  Left Join tsysreports On
      tsysreports.Reportquery = SubString(Cast(tsysWebTabControls.State As
      nvarchar(max)), CharIndex('web', tsysWebTabControls.State), 40)
Order By Agent,
  tsysWebTabs.TabID

[Mister_Nobody]

Report shows reports by don't show widgets...

Select Top 1000000 tsysWebUsers.Username As Agent,
  tsysWebTabs.TabName As [Dashboard Menu Name],
  tsysWebControls.ControlName As [Widget Type],
  tsysreports.Reporttitle,
  tsysreports.Reportquery,
  tsysWebTabs.GlobalTabs
From tsysWebTabs
  Inner Join tsysWebUsers On tsysWebUsers.PageID = tsysWebTabs.PageID
  Inner Join tsysWebTabControls On tsysWebTabs.TabID = tsysWebTabControls.TabID
  Inner Join tsysWebControls On tsysWebControls.ControlID =
      tsysWebTabControls.ControlID
  Left Join tsysreports On
      tsysreports.Reportquery = SubString(Cast(tsysWebTabControls.State As
      nvarchar(max)), CharIndex('web', tsysWebTabControls.State), 40)
Order By Agent,
  [Dashboard Menu Name]

How filter only user-custom dashboards with reports and widgets?

[rader]

Made it a little further in case anyone is interested. The primary challenge to creating this report is that documentation to the tables I'm digging through is non-existent, at least that I can find.

At this point the report only pulls the custom reports/charts from all the dashboard tabs. Here's the latest sample from my dashboards.

Really large image to show the query statement

The challenge was to extract the HTML Query Statement's report name (custom reports seem to start with 'web50rep' and are 40 characters long. Using the SubString (CAST... to pull the report name out of tsysWebTabControls.State, then match it to the tsysreports.ReportQuery field in an unrelated database, then display the Reporttitle to get the real-world name of the report and/or chart for display. The SubString(CAST) was necessary to have two different data types (ntype -> nvarchar) match string values.

Now the only thing I'm struggling with is how to get the build reports to display on the same report. Their in the tsysWebControls database under 'web40rep...', category type 1. All the custom reports above are category type 3, and HelpDesk reports are category type 2, fyi.

Select Top 1000000 tsysWebUsers.Username As Agent,
  tsysWebTabs.TabID As [Dashboard ID],
  tsysWebTabs.TabName As [Dashboard Menu Name],
  tsysWebTabControls.ControlID As [Widget ID],
  tsysWebControls.ControlName As [Widget Type],
  tsysreports.Reporttitle As [Report Title],
  tsysWebTabControls.State As [HTML Query Statement],
  tsysreports.Reportquery As [Report Query Name]
From tsysWebTabs
  Inner Join tsysWebUsers On tsysWebUsers.PageID = tsysWebTabs.PageID
  Inner Join tsysWebTabControls On tsysWebTabs.TabID = tsysWebTabControls.TabID
  Inner Join tsysWebControls On tsysWebControls.ControlID =
      tsysWebTabControls.ControlID,
  tsysreports
Where tsysreports.Reportquery = SubString(Cast(tsysWebTabControls.State As
  nvarchar(max)), CharIndex('web', tsysWebTabControls.State), 40)
Order By Agent,
  [Dashboard ID]

This is at least one way to ensure you have a report of all your custom reports should you have to rebuild your dashboards.

[Mister_Nobody]

I think your query has too much debug info.

I have simplified your report for my LS:

Select Top 1000000 tsysWebUsers.Username,
  tsysWebTabs.TabName,
  tsysreports.Reporttitle,
  tsysreports.Reportquery,
  tsysWebTabs.GlobalTabs
From tsysWebTabs
  Inner Join tsysWebUsers On tsysWebUsers.PageID = tsysWebTabs.PageID
  Inner Join tsysWebTabControls On tsysWebTabs.TabID = tsysWebTabControls.TabID
  Inner Join tsysWebControls On tsysWebControls.ControlID =
      tsysWebTabControls.ControlID
  Inner Join tsysreports On
      tsysreports.Reportquery = SubString(Cast(tsysWebTabControls.State As
      nvarchar(max)), CharIndex('web', tsysWebTabControls.State), 40)
Order By tsysWebUsers.Username,
  tsysWebTabs.TabName

[rader]

It's intentional for now. Making sure I see the trees in the forest as it were. Later I intend to remove the debugging data.

Nice clean report you did there. What's the reason for the Global Tabs?

[Mister_Nobody]

We have common custom dashboards - charts, events. So I want to see them. 

[Mister_Nobody]

Users can customize their tabs so you have to add column for user's info:

Select Top 1000000 tsysWebTabs.TabID,
tsysWebTabs.TabName,
tsysreportpages.HomepageQuery,
tsysreports.Reporttitle,
tsysreports.Reportquery,
tsysWebUsers.Username
From tsysWebTabs
Inner Join tsysreportpages On tsysWebTabs.PageID = tsysreportpages.PageID
Inner Join tsysreports On
tsysreports.Reportquery = tsysreportpages.HomepageQuery
Inner Join tsysWebUsers On tsysWebUsers.PageID = tsysWebTabs.PageID
Order By tsysWebTabs.TabID