Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Creating custom insight widgets in SQL Operations Studio


By:   |   Read Comments   |   Related Tips: More > SQL Server Operations Studio

Attend this free live MSSQLTips webcast

Tips and Tricks to Growing Your SQL Server Estate from SMB to Enterprise Scale
Thursday, June 28, 2018 - click here to learn more


Problem

In my previous tips SQL Operations Studio Installation and Overview, SQL Operations Studio - Query Editor and Source Control and Customizing Dashboards in SQL Operations Studio, we learned about the new tool SQL Operations Studio and some of its features. In this tip, we will see how to create custom widgets along with different extensions.

Solution

Graphical representation of data helps visualize data quickly and effectively. SQL Operations Studio widgets represent data in graphical formats to visualize data.  SQL Operations Studio provides some useful built-in widgets like:

  • Table-space-DB-insight
  • Query-data-store-DB-insight
  • Backup-history-server-insight
  • All-database-size-server-insight

Apart from the built-in widgets, we can also create custom insight widgets using a T-SQL script. Let's explore how to create and configure a custom insight widget.

Add a custom database insight widget to the SQL Operations Studio dashboard

Custom insight widgets can be the following:

  • Insight widget: The widget that appears in the dashboard, this widget uses a T-SQL query to return the data in the widget. This is mandatory to configure the widget.
  • Insight widget details: If we want to view further details about the data, we need to configure using the insight widget details page query.

First, we have to configure the insight widget query in SQL Operations Studio. When we open up the dashboard page, it executes the saved query in the background and displays the results as per the configuration as a chart, text, image, etc. We can also configure the details page to provide additional details when required.

In my demonstration, I will be using the below query to show the count of indexes having average fragmentation greater than 30 percent.  This query will return a count of indexes.

SELECT count(*) as CountOfAvg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(N'AdventureWorks2017'), NULL, NULL, NULL, 'SAMPLED')
where avg_fragmentation_in_percent > 30			

Create Custom Insight Widget in SQL Operations Studio

Step 1 - Launch SQL Operations Studio and connect to the database.

Step 2 - Right click on the connection and launch New Query

new query

Step 3 - Copy or write the query then execute it to verify the output of the query we want to display.

sql query

Step 4 - In the results pane, towards the right, click View as Chart to create the insight.

sql query

Step 5 - Once we click on View as Chart, it opens a new tab Chart Viewer. We can customize the chart as needed.

We have multiple options to display data such as:

  • Bar
  • Count
  • Table
  • Pie
  • Doughnut
  • Horizontal bar
  • Line
  • Scatter
  • Time series
chart viewer

For example, I want to display the result as a table in the insight widget, so I will select Table as the chart type.

sql query

Step 6 - In the Chart Viewer, we get these options:

  • Save as image
  • Copy image
  • Create insight
chart viewer

Click Create Insight to generate the JSON and it should show the below JSON code to create the insight.

insigt

This is the JSON code we need to update.

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

The details of the JSON fields are as shown below:

Property Description
Name The title of the widget
gridItemConfig sizex and sizey specify the dimensions of the widget on the dashboard. We need to change these dimensions in order to adjust the other widgets also.
widget The actual widget configuration
type Chart type
  • Bar
  • Count
  • Table
  • Pie
  • Doughnut
  • Horizontal bar
  • Line
  • Scatter
  • Time series
datadirection Horizontal or vertical
dataType Data type of the output

Now, save the query to a folder with an appropriate name, for example, I saved the query as C:\mssqltips\SQLOPS\IndexFragmentation.sql

Step 7 - Now we need to open User Settings and paste the code into the database dashboard widget. To open User Settings, go to View > Command Palette > Preferences: Open User Settings as shown below.

index fragementation
open user settings

This opens up the settings.json file, so we can customize the dashboard widget.

default settings

In the User Settings search box, type database to locate a database widget. Below is how it looks.

sql query

Step 8 - Now, in the dashboard.database.widgets, edit the JSON with the corresponding entries for the widget.

Before doing so, make the required changes as shown below in the insight code generated in step 6.

{
            "name": "Top Index Fragmentation count", -- Modify the name of the widget
            "gridItemConfig": {
                "sizex": 2,
                "sizey": 1
            },
            "widget": {
                "insights-widget": {
                    "type": {
                        "table": {
                            "dataDirection": "vertical",
                            "dataType": "number",
                            "legendPosition": "none",
                            "labelFirstColumn": false
                        }
                    },
                    "queryFile": "C:\\mssqltips\\SQLOPS\\IndexFragmentation.sql"   --Specify the actual location
                }
            }
        }
			
database

Step 9 - Now save the user setting using Ctrl+S.

Step 10 - Launch the database dashboard by right-clicking on the database and selecting Manage.

manage

Step 11 - We can see the configured widget 'Top Index Fragmentation Count'

database size

In the above example, we have can see the count of indexes where the avg_fragmentation_in_percent > 30, but we do not see the details. Now we will show how to include the details.

Create Custom Insight Widget with Details in SQL Operations Studio

Step 1 - Open a new query editor and write the code and execute it as follows.

SELECT OBJECT_NAME(OBJECT_ID) as ObjectName, index_id,index_type_desc,index_level, 
avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count 
FROM sys.dm_db_index_physical_stats(DB_ID(N'AdventureWorks2017'), NULL, NULL, NULL, 'SAMPLED') 
where avg_fragmentation_in_percent > 30 
ORDER BY avg_fragmentation_in_percent DESC 
index fragementation

Step 2 - Save the query, for example, I save it as C:\mssqltips\SQLOPS\IndexFragmentationDetails.sql

windows

Step 3 - Now follow steps 8 and 9 from above and go to dashboard.database.widgets.

Step 4 - In dashboard.database.widgets, go to our previously configured insight.

"name": "Top Index Fragmentation count",			
database

Step 5 - Now add the below-highlighted fields to add the details insight.

  • Query File: Provide the query file location
  • Label: Label in the details widget, provide the column name from the query
  • Value: Value of the corresponding field in the query output.
"widget": {
                "insights-widget": {
                    "type": {
                        "table": {
                            "dataDirection": "vertical",
                            "dataType": "number",
                            "legendPosition": "none",
                            "labelFirstColumn": false
                        }
                    },--Specify the actual location
                    "queryFile": "C:\\mssqltips\\SQLOPS\\IndexFragmentation.sql",
                    "details": {
                        "queryFile": "C:\\mssqltips\\SQLOPS\\IndexFragmentationDetails.sql",
                        "label": "ObjectName",
                        "value": "avg_fragmentation_in_percent"
                }
            }
        },		

Step 6 - Now launch the database dashboard. We can see the option Show Details to see the detail page.

show details

Step 7 - Click on show details and it opens a new window with the details.

employee pay history

This window has two boxes such as:

  • Items: Output in the format of columns we specified in the configuration. For example, in my case it shows the object name and its corresponding avg_fragmentation_in_details value.
  • ItemValue: Details (query output) of the item selected in the Items Box.
Next Steps


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Rajendra Gupta Rajendra is a Consultant DBA with 9+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

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.



    



Learn more about SQL Server tools