Skip to content
Menu
e-lo [IT Engineer life]
  • Home
    • Note
  • Database
    • T-SQL
    • SQL Server quick
    • SQL server docs
    • MySql quick sheet
    • Postgre
    • InfluxDB
  • Programming
    • MS Azure Powershell
    • MS Azure Command-Line Interface (CLI) doc
    • Python Docs
    • Python Logging
    • Python-cheat-sheet
    • Git-guide
  • Azure
    • MS Windows virtual machines in Azure
    • MS ARM Docs
    • MS ARM Template Docs
    • MS ARM Functions
    • MS Bicep+ARM
    • MS ARM Tutorial
    • MS Deployment scripts (intern/extern)
    • MS Virtual Network
  • Az-nutshell
    • ms-technology-choices-compute-decision-tree
    • ms-data-store-decision-tree
    • ms-data-explorer
    • ms-storage-explorer
    • ms-azure-sql
    • ms-common-data-services
    • ms-azure-mysql-daas
    • ms-sla
    • az paas
    • az glossary-quicksheet
    • az-test-vm-script-quickguide
  • Linux
    • Top CMD’s
    • Useful CMD Linux
    • ss64 Linux
    • Ubuntu
    • 30 things Ubuntu 18.04
    • Bootable Ubuntu USB
    • LinuxFilesystemTreeOverview
  • Sys Admin
    • System Administrator
    • Sys News
  • Zen
    • Not thinking about anything is Zen
e-lo [IT Engineer life]

Grafana with Postgresql and MS SQL (2020)

Posted on July 30, 2018December 16, 2020 by espenk

Grafana https://grafana.com/

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.

RSS Azure

  • Scale your cloud-native apps and accelerate app modernization with Azure, the best cloud for your apps May 24, 2022

RSS RabbitMQ

  • RabbitMQ 3.8.15 release

RSS Python

  • PEP 691: JSON-based Simple API for Python Package Indexes May 4, 2022

Tags

5 min (26) Ansible (1) ARM (10) azure (40) cmd (3) Django (4) Docker (1) e-lo (2) Flask (2) Github (9) Grafana (2) Information (7) Information Retrieval (13) JAVA (1) kivy (2) Kotlin (6) linux (15) mobile (2) Natural Language Prossesing (NLP) (2) Net.Core (1) Networking and Security (6) OPC (2) PEP8 (1) Philosophy (3) Python (47) Python Networking and Security (5) Reason (2) RMQ (3) Solr (11) Sql (10) Uncategorized (2) VSC (1) Warframe (2) WMVARE (4) Zabbix (7)

Recent Posts

  • 5 min Logic App Storage Table
  • 5 min Logic App PSQL
  • 5 min Logic App
  • 5 MIN Azure Data Explorer
  • TODO Build a Hash Table in Python With TDD Real Python

Archives

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

Photo by Markus Spiske from Pexels "Matrix"

©2022 e-lo [IT Engineer life] | Powered by WordPress & Superb Themes