Multiple Grafana datasources data transformation

Grafana Dashboards
,
1

Hello all,

I am using Grafana 8.3.4 on AKS with Helm charts deployment. I want to deploy a new Dashboard, containing GeoMap coordinates of MS SQL datasource. First step is done - I managed to present a new MS SQL datasource and configured Latitude and Longitude. Now I am trying to extend a little bit and to create a second Dashboard, containing some datasource logic - I have another database which I added as a second datasource, containing Client Id`s which are currently active. This client Ids could be retrieved in the first database as well.

I want to achieve Mixed datasourcing with transformation to show only active Clients on the dashboard. I already tried with Merge and Outer join but I couldn`t gain any success. To be more clear, here are the SQL queries:

SELECT ClientId as ClientId FROM [User].[Clients] where Timestamp >= CAST(GETDATE() AS DATE) AND FinishedAt IS NULL

This is running against the first database and returns me a list of active clients at the moment - ClientIds

And this one is running against the second database:

select cast(Latitude as decimal(10, 6)) as Latitude, cast(Longitude as decimal(10, 6)) as Longitude, ClientId as ClientId from [DB].[Coordinates]

The above query returns me a list with Latitude, Longitude and ClientId. The ClientId which is the same client Id as the first SQL query if it`s in the list.

I want to use Transformation to receive the data in the following format:

Latitude, Longitude and (optional) ClientId ONLY from the first query. All of the another ClientIds and Lat/Longs should be stripped.

The question is, how to perform a transformation to receive a list with Latitude and Longitude (optional ClientId) with only the clientId from the first query (the active client) ?

Sorry if became too complicated, hope it is still clear to understand and thanks in advance!

Regards,
Stefan Lazarov

2

Welcome @stefcholazarov

Are the two datasources on separate servers?

3

Hi @yosiasz

No, they are on the same server, different databases.

Thanks!

4

Why not do a cross database join on clientId?

5

I am not sure how to do it in Grafana? When I am using – Mixed – datasource I can query only one database at a time. Can you guide me if you have something in mind ?

Thank you!

6

Actually what I wan to achieve is like “if” condition - if clientId is presented in the first query, do show me Lat/Long only for it and omit the others.

thanks!

7 
select cast(co.Latitude as decimal(10, 6)) as Latitude,
cast(co.Longitude as decimal(10, 6)) as Longitude,
 cl.ClientId as ClientId 
from [DB].[Coordinates] co
Join [User].[Clients] cl 
On co.ClientId = cl.clientId
where cl.Timestamp >= CAST(GETDATE() AS DATE) 
AND cl.FinishedAt IS NULL
8

Thank you so much ! Sorry for the dump question, but how can I use it in Grafana ? I have separate queries for each database ?

9

And one more thing I forgot to mention - this is Azure SQL Database, it that mean, I have to use " Azure Elastic Query" instead of your query, @yosiasz ?

Thank you!

10

I thought you said it was ms sql?

11

My mistake, sorry. By MS SQL I mean - I am using Azure SQL Database with non-managed SQL Server instance. Everything is on the cloud, apologies.

1 Like
12

Which data source are you using to connect?

13

I am using Microsoft SQL Server as a datasource provider. If I can get somehow the clientId from the first query and use it as a variable for the second query? Is there such an option to accomplish it ?

1 Like
14

And also - can I use for example “Outer join” and then some additional logic ?

Thank you !

15

Yes, create a variable then use it as a drop down to filter by clientId

Read the documentation on grafana variables. Then after reading docu and trying it out and if you are still stuck post back here

16

Thank you for the help! I did exactly what you said:

image
image1263Ă—558 109 KB

My question is - is there an option to have all of the ids selected all the time ? Thanks!

17

Select all and then when you save your dashboard, do this:
image

18

Thanks! But my variables will be different every time. Is that means this “save” will use them as static ?

Thank you!

19

In the Variables set up screen, can you do this?
image

and then this:
image

20

Can the user change the selection once in a while or they must be all selected all the time?