Custom Report/Custom Data Collection Idea

[DavidWiseman]

Collection

DBA Dash Currently supports custom checks and custom performance counters. It doesn't support the collection of data in some arbitrary format.

Why might this be useful?

The DBA Dash agent is great for central data collection - allowing data to be collected even in cases without direct connectivity between environments. It might be useful to store some custom data in the DBA Dash repository for reporting alongside the standard data that is collected.

How?

Storing custom SQL to be executed in the agent config file could be a security concern. The App could allow the configuration of a stored procedure to be executed which must already exist in the database the agent connects to. A safe name prefx like "DBADash_" could also be required.
So the config would store the name of the proc, plus a cron schedule
DBADash_MyCustomData1, 0 0 0 1/1 * ? *
DBADash_MyCustomData2, 0 0/10 * ? * * *

In the repository DB, the user would create procs to import this data. A custom schema would be used that would be excluded from the dacpac deployments (I assume this is possible)
UserCollection.MyCustomData1_Upd
UserCollection.MyCustomData2_Upd

The user would also create an associated user-defined table type with the correct schema to match the data collection which is passed as an input into the stored proc along with the InstanceID parameter. Exactly like the existing collections work. Tables can also be created to store the data in this custom schema.

Report

It would also be useful to have a way to display this custom data inside the DBA Dash GUI. Users might also want to create some custom reports based on the existing data collected.

A custom user report schema could be used. A stored procedure is created in this schema and the application could look for eligible procedures when it starts.
e.g.
CustomReport.ServerInfo
CustomReport.DatabaseInfo

The app would add a Custom Reports node in the tree, and add a node for each report that you click on to display the data. The ServerInfo report might have a @InstanceIDs parameter which the application can pass in - and it knows to display this report at the root level.
The DatabaseInfo report would have a @databaseid parameter to the app knows to display the DatabaseInfo report at the database level.
Some procs might have additional parameters that need values supplied from the user. This starts to get more complicated but some parameter prompts might be possible.

In terms of displaying the data, it would just output everything returned from the proc to a grid. It might also be possible to create some chart reports with data returned in some expected format.

Example

You could configure DBA Dash to run something like sp_BlitzIndex on a daily basis to get index recommendations. In the GUI as you are looking at the performance of an Instance or DB, you will be able to click and see the latest index recommendations. There could be an argument for DBA Dash doing index recommendations natively - but the custom collection/reporting could be used for many things.

Next Steps

This is just an idea at this stage and it's open to feedback.