Building multiple insight widgets

Introduction

In this session, you will learn to build your own extensions for Azure Data Studio. Azure Data Studio is a data management tool that enables you to work with SQL Server, Azure SQL DB and SQL DW from Windows, macOS and Linux. In addition to providing core functionality like a rich T-SQL editing experience, we also provide an Extension Marketplace where the community can contribute their own Extensions so that anyone can use the extension.

These extensions are generally lighted up through our Dashboard, where users can quickly monitor and troubleshoot their databases.

dash

Users can build their own Insight Widgets as shown in the image above, which are powered by T-SQL queries. This allows a user to see a visualization of the common queries they run day to day.

Over the course of this session, you will:

Build your first sqlops extension using a Visual Studio Code environment.
Use an Extension generator to help write an extension
Create an Insight Widget extension
Create an extension using Typescript

Prerequisites

All prerequisites are already install on machine. As a summary, here is what is pre-installed:

Azure Data Studio
Visual Studio Code
Git
Node.JS, >= 8.9.1, < 9.0.0
Yarn, follow the installation guide
Python, at least version 2.7 (version 3 is not supported)
Generator-sqlops
SQL Server 2017 developer edition
Visual Studio Code extensions
- Azure Data Studio debug
- mssql extension
- Debugger for Chrome
npm packages
- npm install -g vsce

Create an insight widget

Open Azure Data Studio.
Click new connection.
Select Localhost from Saved Locations and click Connect.
Click arrow next to localhost under Servers, then click arrow by databases, then right click AdventureWorks2014 and click New Query

Copy and paste this snippet

-- Get the space used by table TableName
SELECT TOP 10 tabl.name AS table_name,
--SUM(PART.rows) AS rows_count,
SUM(ALOC.total_pages) AS total_pages,
SUM(ALOC.used_pages) AS used_pages,
SUM(ALOC.data_pages) AS data_pages,
(SUM(ALOC.total_pages)*8/1024) AS total_space_MB,
(SUM(ALOC.used_pages)*8/1024) AS used_space_MB,
(SUM(ALOC.data_pages)*8/1024) AS data_space_MB
FROM sys.tables AS TABL
INNER JOIN sys.indexes AS INDX
ON TABL.object_id = INDX.object_id
INNER JOIN sys.partitions AS PART
ON INDX.object_id = PART.object_id
AND INDX.index_id = PART.index_id
INNER JOIN sys.allocation_units AS ALOC
ON PART.partition_id = ALOC.container_id
--WHERE TABL.name LIKE '%TableName%'
AND INDX.object_id > 255
AND INDX.index_id <= 1
GROUP BY TABL.name, 
INDX.object_id,
INDX.index_id,
INDX.name
ORDER BY --Object_Name(INDX.object_id),
(SUM(ALOC.total_pages)*8/1024) DESC
GO

Click File->Save As:
```
C:\Users\LabUser\.sqlops\sample.sql
```
Click Run.
Click View as Chart Icon on right of Results window.
Click Create Insight.
Edit name as 'Table Spaces'
Edit queryfile as 'C:\Users\LabUser\.sqlops\sample.sql'
Click bottom-left gear icon, then Settings

Under User Settings, find the last ']'. Add a comma and new line, then copy and paste your insight widget:

"dashboard.database.widgets": [
    {
        "name": "Table Spaces",
        "gridItemConfig": {
            "sizex": 2,
            "sizey": 1
        },
        "widget": {
            "insights-widget": {
                "type": {
                    "horizontalBar": {
                        "dataDirection": "vertical",
                        "dataType": "number",
                        "legendPosition": "none",
                        "labelFirstColumn": false,
                        "columnsAsLabels": false
                    }
                },
                "queryFile": "C:\\Users\\LabUser\\.sqlops\\sample.sql"
            }
        }
    }
]

Hit Ctrl+S to save your settings file
Right click AdventureWorks2014 under servers and click Manage. You should now be able to see the table spaces widget!

Create an insight extension

Open Visual Studio Code from the bottom task bar.
If the terminal is not already open, Hit Ctrl+` to open the Integrated Terminal Note: This is not the apostrophe, this is the grave accent below the ESC key.
Type yo sqlops and hit Enter. This opens the Azure Data Studio Extension generator. This allows you to quickly get started on building an extension of your choice.
To start off, use arrow keys and hit enter on New Dashboard Insight.
Follow these instructions:
- Add a full dashboard tab? Y
- What's the name of your extension? sample
- What's the identifier of the extension? hit enter
- What's the description of your extension? building a sample extension
- What's your publisher name? MSBuild
  - The publish name must be set, we recommend MSBuild but you can use any publisher name.
You have now created a very simple extension. Now let's edit it.
Type cd sample into the terminal and hit enter.
Type code . into the terminal and hit enter. This opens a new VS Code window, and shows the folder contents of what is currently in the Sample directory.
Click sql and open query.sql to see our target insight extension.
Select and delete what is currently in query.sql.

Paste the following T-SQL:

-- Get the space used by table TableName
SELECT TOP 10 tabl.name AS table_name,
--SUM(PART.rows) AS rows_count,
SUM(ALOC.total_pages) AS total_pages,
SUM(ALOC.used_pages) AS used_pages,
SUM(ALOC.data_pages) AS data_pages,
(SUM(ALOC.total_pages)*8/1024) AS total_space_MB,
(SUM(ALOC.used_pages)*8/1024) AS used_space_MB,
(SUM(ALOC.data_pages)*8/1024) AS data_space_MB
FROM sys.tables AS TABL
INNER JOIN sys.indexes AS INDX
ON TABL.object_id = INDX.object_id
INNER JOIN sys.partitions AS PART
ON INDX.object_id = PART.object_id
AND INDX.index_id = PART.index_id
INNER JOIN sys.allocation_units AS ALOC
ON PART.partition_id = ALOC.container_id
--WHERE TABL.name LIKE '%TableName%'
AND INDX.object_id > 255
AND INDX.index_id <= 1
GROUP BY TABL.name, 
INDX.object_id,
INDX.index_id,
INDX.name
ORDER BY --Object_Name(INDX.object_id),
(SUM(ALOC.total_pages)*8/1024) DESC
GO

Open README.md and delete the contents of the readme. Paste the following:
```
Sample insight widget extension.
```
Click File->Save All.
Hit CTRL+` to open the integrated terminal. Type 'vsce package' to package your extension. Type 'Y' and enter when prompted if you would like to continue without the repository field.
Use “Ctrl + C” to copy the file location of the vsix package from the terminal.
Open Azure Data Studio. Click File, then click Install Extension from VSIX package.
Paste directory link of extension and click Install
Click Reload Now on bottom right.
Click on arrow next to Localhost, arrow next to Databases, then right click AdventureWorks2014 and click Manage
On the line next to Home, click Sample to see your sample extension.
Let's try adding multiple insight widgets. Go back to Visual Studio Code and add a new file under sql folder called query2.sql

Paste the following query.

Top Active Connections

SELECT count(session_id) as [Active Sessions]
FROM sys.dm_exec_sessions
WHERE status = 'running'

Now, go to package.json file so that we can create a new container for our widget. Find dashboard.insights.

Type or paste the following below the existing insight json content:

{
    "id": "sample2.insight",
    "contrib": {
        "queryFile":"./sql/query2.sql",
        "type": {    
            "count": {
                "dataDirection": "vertical",
                "dataType": "number",
                "legendPosition": "none",
                "labelFirstColumn": false
            }
        }
    }
}

Find widgets-container. Type or paste the following:

{
    "name": "sample2",
    "gridItemConfig": {
        "sizex": 2,
        "sizey": 1
    },
    "widget": {
        "sample2.insight": {}
    }
}

Repeat Steps 13-19 and remember to Save All.

Using what you have learned, create two additional insight widgets using the following queries.

DB Size

with fs
as
(
    select database_id, type, size * 8.0 / 1024 size
    from sys.master_files
)
select 
    name,
    (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,
    (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB
from sys.databases db
where database_id > 4

All DB Space Used

declare @dbsize table
(Dbname nvarchar(128),
    file_Size_MB decimal(20,2)default (0),
    Space_Used_MB decimal(20,2)default (0),
    Free_Space_MB decimal(20,2) default (0))
insert into @dbsize
    (Dbname,file_Size_MB,Space_Used_MB,Free_Space_MB)
exec sp_msforeachdb
'use [?];
  select DB_NAME() AS DbName,
sum(size)/128.0 AS File_Size_MB,
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB,
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS Free_Space_MB
from sys.database_files  where type=0 group by type'

-------------------log size--------------------------------------

declare @logsize table
(Dbname nvarchar(128),
    Log_File_Size_MB decimal(20,2)default (0),
    log_Space_Used_MB decimal(20,2)default (0),
    log_Free_Space_MB decimal(20,2)default (0))
insert into @logsize
    (Dbname,Log_File_Size_MB,log_Space_Used_MB,log_Free_Space_MB)
exec sp_msforeachdb
'use [?];
  select DB_NAME() AS DbName,
sum(size)/128.0 AS Log_File_Size_MB,
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as log_Space_Used_MB,
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS log_Free_Space_MB
from sys.database_files  where type=1 group by type'
--------------------------------database free size
declare @dbfreesize table
(name nvarchar(128),
    database_size varchar(50),
    Freespace varchar(50)default (0.00))
insert into @dbfreesize
    (name,database_size,Freespace)
exec sp_msforeachdb
'use [?];SELECT database_name = db_name()
    ,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize) + convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2) + ''MB'')
    ,''unallocated space'' = ltrim(str((
                CASE
                    WHEN dbsize >= reservedpages
                        THEN (convert(DECIMAL(15, 2), dbsize) - convert(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576
                    ELSE 0
                    END
                ), 15, 2) + '' MB'')
FROM (
    SELECT dbsize = sum(convert(BIGINT, CASE
                    WHEN type = 0
                        THEN size
                    ELSE 0
                    END))
        ,logsize = sum(convert(BIGINT, CASE
                    WHEN type <> 0
                        THEN size
                    ELSE 0
                    END))
    FROM sys.database_files
) AS files
,(
    SELECT reservedpages = sum(a.total_pages)
        ,usedpages = sum(a.used_pages)
        ,pages = sum(CASE
                WHEN it.internal_type IN (
                        202
                        ,204
                        ,211
                        ,212
                        ,213
                        ,214
                        ,215
                        ,216
                        )
                    THEN 0
                WHEN a.type <> 1
                    THEN a.used_pages
                WHEN p.index_id < 2
                    THEN a.data_pages
                ELSE 0
                END)
    FROM sys.partitions p
    INNER JOIN sys.allocation_units a
        ON p.partition_id = a.container_id
    LEFT JOIN sys.internal_tables it
        ON p.object_id = it.object_id
) AS partitions'
-----------------------------------
select TOP 10
    d.Dbname,
    --(file_size_mb + log_file_size_mb) as DBsize,
    --d.file_Size_MB,
    d.Space_Used_MB,
    --d.Free_Space_MB,
    --l.Log_File_Size_MB,
    l.log_Space_Used_MB--,
    --l.log_Free_Space_MB,
    --fs.Freespace as DB_Freespace
from @dbsize d join @logsize l on d.Dbname=l.Dbname join @dbfreesize fs on d.Dbname=fs.name
order by d.Space_Used_MB DESC

extensions

Create an extension using Typescript

Hit Ctrl+` to open the Integrated Terminal Note: This is not the apostrophe, this is the grave accent below the ESC key.
Type yo sqlops and hit Enter. This opens the Azure Data Studio Extension generator.
To start off, use arrow keys and hit enter on New Extension (Typescript)
Follow these instructions:
- What's the name of your extension? TypeSample
- What's the identifier of the extension? hit enter
- What's the description of your extension? TypeScript extension
- What's your publisher name? msbuild
- Enable stricter Typescript checking? Y
- Setup linting using 'tslint'? Y
- Initialize a git repository? n
After npm install finishes, switch to the typescript directory using cd typescript
Open Integrated Terminal with Ctrl+` and run npm run compile
Open the project explorer by entering code . You should see a new out folder.
Right click on the out folder and create a new folder called sql. This is where we will store .sql files to power our insight widgets.
Right click on the sql folder and click New File. Name it query.sql
Open query.sql and paste the following familiar sql query:

Paste the following T-SQL:

-- Get the space used by table TableName
SELECT TOP 10 tabl.name AS table_name,
--SUM(PART.rows) AS rows_count,
SUM(ALOC.total_pages) AS total_pages,
SUM(ALOC.used_pages) AS used_pages,
SUM(ALOC.data_pages) AS data_pages,
(SUM(ALOC.total_pages)*8/1024) AS total_space_MB,
(SUM(ALOC.used_pages)*8/1024) AS used_space_MB,
(SUM(ALOC.data_pages)*8/1024) AS data_space_MB
FROM sys.tables AS TABL
INNER JOIN sys.indexes AS INDX
ON TABL.object_id = INDX.object_id
INNER JOIN sys.partitions AS PART
ON INDX.object_id = PART.object_id
AND INDX.index_id = PART.index_id
INNER JOIN sys.allocation_units AS ALOC
ON PART.partition_id = ALOC.container_id
--WHERE TABL.name LIKE '%TableName%'
AND INDX.object_id > 255
AND INDX.index_id <= 1
GROUP BY TABL.name, 
INDX.object_id,
INDX.index_id,
INDX.name
ORDER BY --Object_Name(INDX.object_id),
(SUM(ALOC.total_pages)*8/1024) DESC
GO

Save this query with Ctrl+S.
In package.json, find activationEvents and remove the contents in that section (two lines). Type "*" in activationEvents section.

In package.json Add command to contributes.commands. Note: Ensure to add a comma at } bracket before.

    {
    "command": "TypeInsight.simple.install",
    "title": "TypeInsight: install"
    }

In package.json, paste or type this snippet below in Contributes section.

,
    "dashboard.tabs": [
    {
    "id": "TypeInsight-simple",
    "title": "TypeInsight",
    "description": "Extension for checking who is active.",
    "container": {
        "nav-section": [
        {
            "id": "TypeInsight_insights",
            "title": "Insights",
            "container": {
            "TypeInsight-simple-insights": {}
            }
        },
        {
            "id": "TypeInsight_documentation",
            "title": "Documentation",
            "container": {
            "TypeInsight-simple-insights3": {}
            }
        }
        ]
    }
    }
],
"dashboard.containers": [
    {
    "id": "TypeInsight-simple-insights",
    "container": {
        "widgets-container": [
        
        {
            "name": "Top 10 CPU Usage",
            "gridItemConfig": {
            "sizex": 2,
            "sizey": 1
            },
            "widget": {
            "TypeInsight-simple-CPU-usage": {}
            }
        }
        ]
    }
    },
    {
    "id": "TypeInsight-simple-insights3",
    "container": {
        "widgets-container": [
        {
            "name": "Tasks",
            "widget": {
            "tasks-widget": [
                "TypeInsight.simple.install"
            ]
            }
        }
        ]
    }
    }
],
"dashboard.insights": [
    {
    "id": "TypeInsight-simple-CPU-usage",
    "contrib": {
        "type": {
        "bar": {
            "dataDirection": "vertical",
            "dataType": "number",
            "legendPosition": "none",
            "labelFirstColumn": false,
            "columnsAsLabels": true,
            "showTopNData": 10
        }
        },
        "queryFile": "./out/sql/query.sql"
    }
    }
]

Dropdown src and open extension.ts, add the following line to the end of export function activate to activate method.
```
sqlops.tasks.registerTask('TypeInsight.simple.install', e => vscode.window.showInformationMessage('test!'));
```
Run npm run compile
Remove everything from readme.md and save. (“File>Save All” if you have not saved the other files already)
Hit CTRL+` to open the integrated terminal. Type 'vsce package' to package your extension. Type 'Y' and enter when prompted if you would like to continue without the repository field.
Copy the directory link of your extension.
Open Azure Data Studio. Click File, then click Install Extension from VSIX package.
Paste directory link of extension and click Install
Click Reload Now on bottom right.
Click on arrow next to Localhost, arrow next to Databases, then right click AdventureWorks2014 and click Manage
On the line next to Home, click TypeSample to see your sample extension.

Next Steps

Thank you for attending this Microsoft Build session. Now that you have learned to build your own Azure Data Studio extensions, we encourage you to continue to build extensions and contribute to our Extensions Marketplace.

To learn to build your own extensions:

Get the prerequisites
Follow along on our Github Wiki Get Started
Read these blog posts:
- https://medium.com/@kevcunnane/writing-a-sql-operations-studio-extension-in-15-minutes-7dfd24a74dfe
- https://medium.com/@kevcunnane/publishing-an-extension-for-sql-operations-studio-f5a5b323c13b

Want to learn more about Azure Data Studio?

Leave a star on our Github
Download Azure Data Studio
Report Issues or suggest Feature Requests on our Github Issues page
Follow us on Twitter @sqlopsstudio

Documentation

Contributing

Tool Services

SQL Tools Service

Extensibility Reference

Project Management

Release Process/Issue Tracking
- Performance Issues

Provide feedback

Saved searches

Use saved searches to filter your results more quickly