Feb 5, 2019

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.