Menu
e-lo
  • Home
    • Tech
    • Inspiration and about
  • Database
    • T-SQL
    • SQL Server quick
    • SQL server docs
    • MySql quick sheet
    • Postgre
    • InfluxDB
  • Programming
    • Automating the boring stuff
    • Python 101
    • Python Docs
    • Python Logging
    • Powershell 4 lang ref
    • MS Azure PS
    • MS Azure Command-Line Interface (CLI) doc
  • Azure
    • AZ-104-MS Azure Administrator 101 quick ref
    • AZ-104 Lab
    • MS Windows virtual machines in Azure
    • MS ARM Docs
    • MS ARM Template Docs
    • MS ARM Tutorial
    • MS Deployment scripts (intern/extern)
    • ARM Quickstart
    • MS ARM templates 4h
  • Az Adm
    • AD 101
    • Governance and Compliance 102
    • Administration 103
    • Virtual Networking 104
    • Storage 107 (With table (NoSQL and more))
    • Virtual Machines 108
    • Azure Virtual Machines 101
    • Monitor VM (and market)
  • Linux
    • Top CMD’s
    • Useful CMD Linux
    • ss64 Linux
    • Ubuntu
    • 30 things Ubuntu 18.04
    • Bootable Ubuntu USB
    • LinuxFilesystemTreeOverview
  • Zen
    • Not thinking about anything is Zen
e-lo

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

  • Insights and best practices to run business-critical applications on Azure April 8, 2021

RSS Python

  • PEP 656: Platform Tag for Linux Distributions Using Musl March 17, 2021

Cloud

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

Recent Posts

  • TODO Ubuntu Ubuntu 20.04.2 LTS and Bash
  • Disk measurements with typeperf (Pandas py)
  • 2 TODO Azure, Arm and Bash
  • Cryptography with Python – Caesar Cipher
  • 1 TODO Udemy AZ-104 Microsoft Azure Administrator Exam Certification (Scott Duffy)

Archives

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
©2021 e-lo | Powered by WordPress & Superb Themes