How to use JSON data in Grafana with C# Webapi Backend

1. Install Grafana

1.1 Run Grafana in Docker command line

>docker run -d -p 3000:3000 --name=grafana -e "GF_SERVER_ROOT_URL=http://localhost" -e "GF_SECURITY_ADMIN_PASSWORD=secret"  grafana/grafana

1.2 Install JSON plugin

You can use user interface to install plugins. I will use docker command line interface to install JSON plugin.
Install

>docker exec -it 00798572de34 grafana-cli plugins install simpod-json-datasource

Restart container

>docker restart 00798572de34

Note that 00798572de34 is the container id of the grafana instance in my installation.

>docker ps
CONTAINER ID        IMAGE           ...
00798572de34        grafana/grafana ...

1.3 Login to dashboard

Check Grafana user interface.

http://localhost:3000

username: admin
password: secret

2. Implement Backend

We will prepare some functions for our JSON datasource.

/            should return 200 ok. Used for "Test connection" on the datasource config page.
/search      used by the find metric options on the query tab in panels.
/query       should return metrics based on input.
/annotations should return annotations.

2.1 Webapi implementations

Except the root "/" all functions must have POST method.

 

[HttpPost]
public HttpResponseMessage Query()
{
 return new HttpResponseMessage()
 {
  Content = new StringContent("[{\"columns\":[{\"text\":\"Time\",\"type\":\"time\"},{\"text\":\"Country\",\"type\":\"string\"},{\"text\":\"Number\",\"type\":\"number\"}],\"rows\":[[1234567,\"SE\",182],[1234567,\"DE\",282],[1234567,\"US\",382]],\"type\":\"table\"}]", Encoding.UTF8, "application/json")
 };
}

[HttpPost]
public HttpResponseMessage Search()
{
 return new HttpResponseMessage()
 {
  Content = new StringContent("[ { \"text\" :\"upper_25\", \"value\": 1}, { \"text\" :\"upper_75\", \"value\": 2} ]", Encoding.UTF8, "application/json")
 };            
}

[HttpPost]
public HttpResponseMessage Annotation()
{
 string body = Request.Content.ReadAsStringAsync().Result;
 string headers = Newtonsoft.Json.JsonConvert.SerializeObject(Request.Headers);

 FileHelper fh = new FileHelper();
 fh.FileName = "search.txt";

 fh.AppendFile(body);
 fh.AppendFile(headers);
 return new HttpResponseMessage()
 {
  Content = new StringContent("[{\"text\":\"text shown in body\",\"title\":\"Annotation Title\",\"isRegion\":true,\"time\":\"timestamp\",\"timeEnd\":\"timestamp\",\"tags\":[\"tag1\"]}]", Encoding.UTF8, "application/json")
 };            
}

3. Dashboard & Datasource Setup

3.1 Add dashboard

Go to Grafana web interface and create a new dashboard. Grafana website has a good document for this

3.2 Add datasource

For datasource select newly installed plugin JSON type from the list. We will select most basic settings. Enter a name and datasource url of your API.

HTTP Access
Since we used Docker here, the container must be configured for access to remote site.
If your Grafana server has an access to webapi URL select "Server". Otherwise you can select "Browser".

3.3 Add panel to dashboard

Go to Dashboards > Manage > Select your dashboard.
On right up side there is a button to add a new panel.
Click panel title to see dropdown menu and select Edit.

3.4 Datasource settings

Select your datasource name from Data Source dropdown.
Push "Add Query" button.
If you open Query Inspector panel you will see Grafana tries to reach "/search" url of your webapi.

In metrics textbox enter a column name in your json. In our example I entered @Country.
In Query Inspector panel you will see Grafana tries to reach "/query" URL of your webapi.

If everything is OK you will see your data in a table on the same page.
Save your datasource and return back to your dashboard to see table.

Notes

Response type must be in application/json.

Don't forget to implement root "/" url function for GET method. Grafana checks root function for verifying URL.

Don't forget to add CORS headers if your backend is installed on remote site.

Read More

Install MySQL Docker (Windows)

I assume Docker already installed and running.

Pull mysql container

You can specify version (5.7). If version is not given it will download latest version.

C:\>docker pull mysql/mysql-server:5.7
5.7: Pulling from mysql/mysql-server
e64f6e679e1a: Already exists
478c78606b7e: Pull complete
81ab104e859d: Pull complete
d3565df0a804: Pull complete
Digest: sha256:79d65bf4360056b0709b4a1c4996f7ef8265ae6ca67462a8570ac1fa0855758b
Status: Downloaded newer image for mysql/mysql-server:5.7

Check docker image

C:\>docker images
REPOSITORY                 TAG                 IMAGE ID            CREATED             SIZE
mysql/mysql-server         5.7                 76ac6291d3cf        2 weeks ago         234MB

Run mysql instance

docker run --name mysql1 -e MYSQL_USER=root -e MYSQL_PASSWORD=root -e MYSQL_DATABASE=homedb -p 3306:3306 -d mysql/mysql-server:5.7 

Check docker processes

C:\>docker ps
CONTAINER ID        IMAGE                    COMMAND                  CREATED             STATUS                             PORTS                 NAMES
44f6c176bb23        mysql/mysql-server:5.7   "/entrypoint.sh mysq…"   14 seconds ago      Up 13 seconds (health: starting)   3306/tcp, 33060/tcp   mysql1

Connect MySQL on docker

docker exec -it mysql1 mysql -uroot -p

If you did not provide a password in docker run command then system will generate a password. To change password and give some permissions:

C:\>docker logs mysql1 2>&1 | FindStr GENERATED
[Entrypoint] GENERATED ROOT PASSWORD: yBiG5ynmyvVurAw93HDok3buGhi

docker exec -it mysql1 mysql -uroot -p

ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';

CREATE USER 'root'@'%' IDENTIFIED BY 'root';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

SHOW GRANTS FOR 'root'@'%';

Connect MySQL on client

C:\Program Files\MySQL\MySQL Workbench 8.0 CE>mysql -h localhost -P 3306 -u root -p

mysql> select @@hostname;
+--------------+
| @@hostname   |
+--------------+
| 44f6c176bb23 |
+--------------+
1 row in set (0.00 sec)

Read More