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

rader · ‎05-09-2024

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 · a week ago

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.

View solution in original post

rader · a month ago

It wouldn't have occurred to me to tie in the users to the report as I'm the only one that uses LS here. This gives me another thread to pull on.

Thank you for the tip @Mister_Nobody .

Mister_Nobody · a month ago

Try this report:

Select Top 1000000 tsysWebUsers.Username,
  tsysWebTabs.TabName,
  tsysWebControls.ControlName,
  tsysWebControls.Path
From tsysWebUsers
  Inner Join tsysWebTabs On tsysWebUsers.PageID = tsysWebTabs.PageID
  Inner Join tsysWebTabControls On tsysWebTabs.TabID = tsysWebTabControls.TabID
  Inner Join tsysWebControls On tsysWebTabControls.ControlID =
      tsysWebControls.ControlID

rader · a month ago

Pretty close. Just needs tweaking the actual report/chart titles. But yes, closer than what I've been able to dig out. I'll have time next week to really dig deeper into this.

You are a superstar @Mister_Nobody 😁

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

New to Lansweeper?

Try Lansweeper For Free