Grafana custom datasource to use mssql

Question

I want to create a Grafana plugin that uses MSSQL databases. I know there is a builtin MSSQL datasource plugin, but that requires the user to type entire SQL queries. I want to create a custom datasource that allows the user to select a name from a list, and the plugin will create the corresponding query for that. Therefore I want to build a full custom query builder (which is part of the datasource plugin).

I started with the simple data source plugin [https://github.com/grafana/simple-datasource/tree/master][1], and I am able to compile it and it is visible in Grafana.

I thought it would be easy if my datasource extends the existing MSSQL plugin.

My testDatasource looks like this:

async testDatasource() {
 return getBackendSrv()
  .datasourceRequest({
    url: '/api/tsdb/query',
    method: 'POST',
    data: {
      from: '5m',
      to: 'now',
      queries: [
        {
          refId: 'A',
          intervalMs: 1,
          maxDataPoints: 1,
          datasourceId: this.id,
          rawSql: 'SELECT 1',
          format: 'table',
        },
      ],
    },
  })
  .then((res: any) => {
    return { status: 'success', message: 'Database Connection OK' };
  })
  .catch((err: any) => {
    if (err.data && err.data.message) {
      return { status: 'error', message: err.data.message };
    } else {
      return { status: 'error', message: err.status };
    }
  });

and everytime I test it I get an error in the log error="Could not find executor for data source type: my-mssql-datasource" remote_addr=[::1]

I have checked the sources on github for this error and I can find that the Grafana code in grafana/pkg/tsdb/query_endpoint.go cannot find my-mssql-datasource name in its registered plugins.

Is there a way I can use the MSSQL backend handling that already exists in Grafana ? If so, any guides or hints that show how to do that ? I have tried to follow the Grafana documentation, but it does not help me with this.

For example: Can I add something to my datasourceRequest that makes Grafana to execute my queries against MSSQL ? Or can I register my datasource so it uses MSSQL ?

EDIT As @Jan Garaj mentioned in the comments it can be done with dashboard variables. However the dashboard variables are used for an entire dashboard. If I want to have 2 panels with different signals, that can be chosen by the user, I cannot use dashboard variables. Then I need something like panel-variables. Because it then is per panel, I wanted to do this with a query builder. [1]: https://github.com/grafana/simple-datasource/tree/master

Answer 1

I know this is an old question, but I've also had this same issue and can't quite figure out how to access Grafana's backend implementation of MSSQL. Scouring the github code for the built-in MSSQL plugin doesn't give any clues as to how it does it - all I can ascertain is that the backend requires rawSql, from, to, datasourceId, format and refId fields in the JSON sent to /api/tsdb/query. From my other research, it appears as though the securedJSON object fields (that contain a MSSSQL username and password, for example) may be appended to the original JSON object on the server side. It may be surmised that the backend then recognises that the datasourceId is an instance of a MSSQL datasource, and converts all the information into a SQL Server connection string and/or query and connects to the database through a Named Pipe or other method.

There doesn't appear to be any documentation to facilitate a third-party plugin connecting to the MSSQL backend, and my efforts to understand how their built-in datasources are bound to that backend have been fruitless.

I can suggest another approach, however. It should be possible to create a datasource plugin that interfaces with an in-built MSSQL datasource instance. In this regard, your datasource plugin essentially acts as a proxy between the user and the MSSQL datasource. This requires adding a standard MSSQL datasource as one normally would, and then pointing your new datasource plugin to the newly created MSSQL datasource. Your new datasource can have a nice UI for the user to select certain columns, options etc, and the plugin can then create a dynamic query, which is then sent to the MSSQL datasource.

So, you just need to figure out the datasourceId of the MSSQL datasource, shown as datasourceId-of-MSSQL-datasource in the snippet below.

    async testDatasource() {
     return getBackendSrv()
      .datasourceRequest({
        url: '/api/tsdb/query',
        method: 'POST',
        data: {
          from: '5m',
          to: 'now',
          queries: [
            {
              refId: 'A',
              intervalMs: 1,
              maxDataPoints: 1,
              datasourceId: 'datasourceId-of-MSSQL-datasource',
              rawSql: 'SELECT 1',
              format: 'table',
            },
          ],
        },
      })
      .then((res: any) => {
        return { status: 'success', message: 'Database Connection OK' };
      })
      .catch((err: any) => {
        if (err.data && err.data.message) {
          return { status: 'error', message: err.data.message };
        } else {
          return { status: 'error', message: err.status };
        }
      });

I've tried this approach on my local Grafana instance with success.