Create Custom Widget in SQL Server Operations Studio


By:   |   Updated: 2018-05-31   |   Comments   |   Related: More > SQL Server Management Studio

Problem

In this article, I am going to explain how to add a custom widget to SQL Server Operations Studio.

Solution

SQL Server Operations Studio is a cross platform, open source database management tool. It is platform independent hence it can be installed on Windows, Linux and macOS. It can be used to manage SQL Server running on any OS platform. We can also manage Azure SQL databases and SQL Azure SQL Data warehouses. It is not a replacement of SQL Server Management Studio, but it provides more facilities and configuration options. You can view query results in various formats like JSON, CSV and Excel. It also provides more advanced coding features like T-SQL code formatting, auto-suggestions, etc.

You can download SQL Server Operations Studio preview from this location.

On GitbHub, a few extensions have been developed which can be integrated with SQL Server Operations Studio that helps DBAs manage databases more efficiently. If you do not want to integrate these extensions, we can create our own custom widgets and place on the dashboard which can make database monitoring more efficient.

In this tip, I am going to demonstrate how to create a custom insight widget to display the top 10 largest tables of a database on the SQL Server Operations Studio dashboard.

For this demo, I have developed a script which will give me the list of the top 10 largest tables of any database. I am going to use “AdventureWorks2014” database. By executing the following script, you can get list of the top 10 largest tables for a database.

Use AdventureWorks2014
Go

select Top 10 object_schema_name(object_id) + '.' + object_name(object_id) as 'Table Name'
   , Convert(Numeric(10,2),(sum( case 
   when index_id<2 then in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count 
   else lob_used_page_count + row_overflow_used_page_count 
   end )*8)) as 'Table Size in MB'
from sys.dm_db_partition_stats
where object_id > 1024 and  object_name(object_id) in (select table_name from INFORMATION_SCHEMA.TABLES)
group by object_id
order by 
sum( case 
     when index_id<2 then in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count 
     else lob_used_page_count + row_overflow_used_page_count 
    end )*8
desc			

Create custom widget in SQL Operations Studio

First, we will save the above query to a file. I copied the above query to my desktop. Now let's create a custom widget to show the results of the query. To do that, open the query editor, paste the above query and execute it. Now in the results window, click on the “View as Chart” option which is on the right side of the query result window as shown in the below image:

query results

The entire result set will be presented as a chart in a separate tab named “Chart viewer”. Click on Create Insight option which is on the top left of the chart window as shown in the below image:

query results as chart

The create insight option will create JSON code which will be used to configure the user defined settings in the SQL Server Operations Studio dashboard.

When I click on “Create Insight”, the below code will be generated.

{
    "name": "My-Widget",
    "gridItemConfig": {
        "sizex": 2,
        "sizey": 1
    },
    "widget": {
        "insights-widget": {
            "type": {
                "horizontalBar": {
                    "dataDirection": "vertical",
                    "dataType": "number",
                    "legendPosition": "none",
                    "labelFirstColumn": false,
                    "columnsAsLabels": false
                }
            },
            "queryFile": "SQLQuery1"
        }
    }
} 			

Now, we will make two changes to the above JSON script.

  1. Change the widget name from “My-Widget” to “Top 10 Large tables”.
  2. Replace the “SQLQuery1” with the fully qualified name of SQL Script, saved on the Desktop.
"queryFile": "C:\\Users\\Administrator\\Desktop\\SOS_Query\\PersonTables.sql"			

The updated code will look like below:

{
    "name": "Top 10 Large tables",
    "gridItemConfig": {
        "sizex": 2,
        "sizey": 1
    },
    "widget": {
        "insights-widget": {
            "type": {
                "horizontalBar": {
                    "dataDirection": "vertical",
                    "dataType": "number",
                    "legendPosition": "none",
                    "labelFirstColumn": false,
                    "columnsAsLabels": false
                }
            },
            "queryFile": "C:\\Users\\Administrator\\Desktop\\SOS_Query\\PersonTables.sql"
        }
    }
}			

Now we will add the above JSON code in the user defined settings. To do that, press [Ctrl]+[,]. Type “dashboard” in the search settings window as shown and click on “Edit” for “dashboard.database.widgets” as shown in the below image:

custom widget in sql operations studio

Copy and paste the above configuration JSON code into “dashboard.database.widgets.  The entire code is shown below:

    "dashboard.database.widgets": [
 
        {
    "name": "Top 10 Large Tables",
    "gridItemConfig": {
        "sizex": 2,
        "sizey": 1
    },
    "widget": {
        "insights-widget": {
            "type": {
                "horizontalBar": {
                    "dataDirection": "vertical",
                    "dataType": "number",
                    "legendPosition": "none",
                    "labelFirstColumn": false,
                    "columnsAsLabels": false
                }
            },
            "queryFile": "C:\\Users\\Administrator\\Desktop\\SOS_Query\\PersonTables.sql"
        }
    }
},
 
        {
            "name": "Tasks",
            "gridItemConfig": {
                "sizex": 1,
                "sizey": 1
            },
            "widget": {
                "tasks-widget": [
                    {
                        "name": "backup",
                        "when": "!mssql:iscloud"
                    },
                    {
                        "name": "restore",
                        "when": "!mssql:iscloud"
                    },
                    "configureDashboard",
                    "newQuery"
                ]
            }
        },
        {
            "gridItemConfig": {
                "sizex": 1,
                "sizey": 2
            },
            "widget": {
                "explorer-widget": {}
            }
        }
    ],
			

Save and close the user settings.

Now to view the dashboard, right click on the “AdventureWorks2014” database and select “Manage” as shown in the below image.

view dashboard

As the above query is executing the first time, the widget will take a while to load. Once it’s loaded, the entire dashboard will look like the below image.

database dashboard

Summary

In this tip, I demonstrated how can we create a custom widget and show it in SQL Server Operations Studio.

Next Steps


Last Updated: 2018-05-31


get scripts

next tip button



About the author
MSSQLTips author Nisarg Upadhyay Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional with more than 5 years of experience.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools