cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
rader
Champion Sweeper III

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.

Dashboard Charts & Reports — Mozilla Firefox.png

Is there a DB table for the Widgets?

Anyone else come across a solution for this?

 

1 ACCEPTED SOLUTION
rader
Champion Sweeper III

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

12 REPLIES 12
rader
Champion Sweeper III

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
Honored Sweeper II
  Case tsysWebControls.Category
    When 1 Then 'Stock'
    When 2 Then 'Helpdesk'
    When 3 Then 'Custom'
  End As Category,
rader
Champion Sweeper III

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
Honored Sweeper II

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
Champion Sweeper III

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 statementReally 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.

dashboard table view.png

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
Honored Sweeper II

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
Champion Sweeper III

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
Honored Sweeper II

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

Mister_Nobody
Honored Sweeper II

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