Grafana with Postgresql and MS SQL (2020)

5 years ago

espenk

3 minutes

The open platform for beautiful
analytics and monitoring.
The leading open source software for time series analytics

I used:4.6.0-beta2, the reason for that was to test Postgresql data source function that just came out for about 6-12 months ago.

https://grafana.com/grafana/download/4.6.0-beta2?platform=windows

Getting started

http://docs.grafana.org/guides/getting_started/

Start Grafana:

Navigate to http://localhost:3000

Grafana Dashboard:

In PostgreSql, make a table with the fields you want, insert some data:

Grafana data source

Add Db, type, table, port, user, password, etc:

Grafana Graph

Make a panel, and edit it:

Connect and make the query

Use timeseries:

All the updates in the tags table will be reflected in the graph:

#Updated post 26.09.2020

SQL SERVER in windows

Installing 7.2.0 (Open source license) in windows

Can now install as service

The service after installed

The service is running as NSSM, the non-sucking service manger

We can now use it at localhost:3000 (admin, admin, prompt to change)

https://grafana.com/docs/grafana/latest/getting-started/getting-started/

Lets add MS SQL as a datasource

https://grafana.com/docs/grafana/latest/features/datasources/mssql/

Lets open MS SQL create a small db and test it, db name

First get the port for SQL Server in Management, SQL Server logs

If an issue with connect, you could try

Open Sql Server Configuration Manager
From the left sidebar select and expand SQL Server Network Configuration
Select Protocols for MSSQLSEREVER
Then just enable TCP/IP and Named Pipes protocols
Restart SQLServer Service.

Now create a user that has access to only that db

CREATE LOGIN grafanareader
    WITH PASSWORD = 'grafanareader';  
GO  

-- Creates a database user for the login created above.  
CREATE USER grafanareader FOR LOGIN grafanareader;  
--
GO 
USE MonitoringDb
GO

GRANT SELECT ON dbo.monitoring_t TO grafanareader

Refresh server and look at properties

No try to login

If you get, Login error: mssql: Login failed for user ‘grafanareader’.

Look in the logs again, it might say “[…] Server is configured for Integrated authentication only”

Enable auth SQL Server

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/change-server-authentication-mode?view=sql-server-ver15

Change to the second one, and restart SQL service

Now connection with the user works

Now we can make a new dashboard add our source and add a query, hit apply after

SELECT
$__time(m_time),
m_value,
m_name
FROM dbo.monitoring_t
WHERE
$__timeFilter(m_time)
ORDER BY m_time

Now we can update the database with new values and monitor it.