EditArticle

Edit Article XML Content

<document> <header> <issuecode /> <articlecode /> <zone /> <title>Developing Dashboards Using Grafana</title> <authors /> <copyright>CODE Magazine</copyright> <owner>CODE Magazine</owner> </header> <body> In my earlier article on Building Dashboards Using Bokeh (https://www.codemag.com/Article/2111061/Building-Dashboards-Using-Bokeh), I talked about how you can build dashboards programmatically using the Bokeh Python library. What if you want to create dashboards but don’t want to spend too much time coding them? The good news is that you can do that using a tool called Grafana. In this article, I’ll walk you through how to get started with Grafana, and how you can use it to build interesting dashboards. I’ll also describe two projects that you can build using Grafana: <list type="bulleted"> <bulletedlist>How to create dynamic auto-updated time series charts</bulletedlist> <bulletedlist>How to display real-time sensor data using MQTT</bulletedlist> </list> <h2>Installing Grafana</h2> The first good news about Grafana is that it’s free to use for most use cases, except where you need enterprise features such as advanced security, reporting, etc. If you want to modify the source code of Grafana, be sure to check the licensing info at https://grafana.com/licensing/. To download the free enterprise version of Grafana, go to https://grafana.com/grafana/download (see Figure 1). There, you’ll find the instructions to download Grafana for the OS that you’re running. <figure id="1" src="image1.png"> Figure 1 : Instructions for downloading Grafana for the various OSes</figure> For Windows, you can download the Windows installer (https://dl.grafana.com/enterprise/release/grafana-enterprise-8.5.2.windows-amd64.msi) and then run it. For Mac, you can download and install Grafana directly using Terminal: <codesnippet>$»curl»-O»</codesnippet> <codesnippet>https://dl.grafana.com/enterprise/release/grafana-</codesnippet> <codesnippet>enterprise-8.5.2.darwin-amd64.tar.gz</codesnippet> <codesnippet>$»tar»-zxvf»grafana-enterprise-8.5.2.darwin-</codesnippet> <codesnippet>amd64.tar.gz</codesnippet> Once the above steps are done, you should now find a folder named Grafana-8.5.2 in your home directory. On Windows, Grafana will be installed in the following directory: C:\Program Files\GrafanaLabs\grafana\. <h3>Starting the Grafana Service</h3> On the Mac, you need to manually start the Grafana service by using the following commands in Terminal: <codesnippet>$»cd»~</codesnippet> <codesnippet>$»cd»grafana-8.5.2</codesnippet> <codesnippet>$»./bin/grafana-server»web</codesnippet> On Windows, Grafana is installed as a Windows service and is automatically started after you have installed it. <h3>Logging in to Grafana</h3> Once Grafana has been installed on your computer, open it using a Web browser using the following URL: http://localhost:3000/login (see Figure 2). <figure id="2" src="image2.png"> Figure 2 : Logging in to Grafana</figure> The default username is admin and password is also admin. Once you’ve logged in, you’ll be asked to change your default password. After that, you’ll see the page shown in Figure 3. <figure id="3" src="image3.png"> Figure 3 : The home page of Grafana</figure> <pullquote>If you forget your password, you can reset the admin password using the following command: ./bin/grafana-cli admin reset-admin-password password</pullquote> <h2>Creating Data Sources</h2> Grafana can load data from a variety of data sources, such as Prometheus, MySQL, InfluxDB, PostgreSQL, Microsoft SQL Server, Oracle, MongoDB, and more. If the data source that you need isn’t available, you can locate additional data sources via plug-ins (Settings > Plugins). A good example of a data source that a lot of data analyst use is CSV, which is supported in Grafana through the CSV data source plug-in. Although you can use CSV as a data source in Grafana, querying your data (and manipulating it) is much easier if you use a database data source, such as MySQL. For this article, I’ll show you how to import your CSV file into a MySQL database server and then use MySQL as the data source in Grafana. <h3>Preparing the MySQL databases</h3> For this article, I’m going to assume that you already have MySQL and MySQL Workbench installed. In the MySQL Workbench, log in to your local database server. Create a new query and enter the following SQL statements to create a new account named user1 with password as the password: <codesnippet>CREATE»USER»'user1'@'localhost'»IDENTIFIED»BY»</codesnippet> <codesnippet>'password';</codesnippet> <codesnippet>GRANT»ALL»ON»*.*»TO»'user1'@'localhost'</codesnippet> Next, create a new databased named Insurance: <codesnippet>CREATE»DATABASE»Insurance;</codesnippet> <codesnippet>USE»Insurance;</codesnippet> Once the Insurance database is created on MySQL, right-click on Tables and select Table Data Import Wizard (see Figure 4): <figure id="4" src="image4.png"> Figure 4 : Import data into the table.</figure> You’ll be loading a CSV file into the Insurance database. For this example, I’ll use the insurance dataset from: https://www.kaggle.com/datasets/teertha/ushealthinsurancedataset. In the Table Data Import wizard that appears, enter the path of the CSV file that you’ve downloaded and follow the steps. Specify that the data of the CSV file be loaded onto the Insurance database and you also have the option to name the table. If you take the default options, the content of the CSV file is loaded onto a table named Insurance. You should now be able to see the imported table and its content (see Figure 5). <figure id="5" src="image5.png"> Figure 5 : Viewing the imported Insurance dataset</figure> <h3>Using the MySQL Data Source</h3> With the database prepared, it’s now time to configure the MySQL data source in Grafana. In Grafana, click Configuration > Data sources (see Figure 6). <figure id="6" src="image6.png"> Figure 6 : Configuring a new data source</figure> Click Add data source (see Figure 7). <figure id="7" src="image7.png"> Figure 7 : Add a new data source.</figure> Type in MySQL and then click on the MySQL data source that appears (see Figure 8). <figure id="8" src="image8.png"> Figure 8 : Locate the MySQL data source.</figure> Enter the details of the MySQL connection as shown in Figure 9. <figure id="9" src="image9.png"> Figure 9 : Configure the MySQL data source to connect to the Insurance database.</figure> On the bottom of the page, click Save & test to ensure that the connection to the MySQL database server is working correctly. <h2>Creating a Dashboard</h2> Now that the data source configuration is done, it’s time to get into the meat of what we want to do! In Grafana, select + > Dashboard to create a new dashboard (see Figure 10). <figure id="10" src="image10.png"> Figure 10 : Add a new dashboard.</figure> <pullquote>In Grafana, a dashboard is a set of one or more panels organized and arranged into one or more rows.</pullquote> Click on the Save icon and give a name to your dashboard (see Figure 11). Click Save. <figure id="11" src="image11.png"> Figure 11 : Name and save the dashboard.</figure> <h3>Creating Panels</h3> With the dashboard created, you’re now ready to add a panel to your dashboard. <pullquote>The panel is the basic visualization building block in Grafana. A panel can display a bar chart, pie chart, time series, map, histogram, and more.</pullquote> Click the Add Panel icon and then the Add a new panel button to add a panel to your dashboard (see Figure 12). <figure id="12" src="image12.png"> Figure 12 : Add a new panel to the dashboard.</figure> You should see the default panel, as shown in Figure 13. <figure id="13" src="image13.png"> Figure 13 : The default panel added to the dashboard</figure> Let’s try to create a pie chart. Enter the details shown in Figure 14. <figure id="14" src="image14.png"> Figure 14 : Display a pie chart in the current panel.</figure> In the above, you set: <list type="bulleted"> <bulletedlist>Visualization type to Pie chart</bulletedlist> <bulletedlist>Data source to MySQL</bulletedlist> <bulletedlist>Manually set the SQL query to:</bulletedlist> </list> <codesnippet>SELECT»</codesnippet> <codesnippet>»»»»now()»as»time,</codesnippet> <codesnippet>»»»»count(region)»AS»value,</codesnippet> <codesnippet>»»»»region»as»metric</codesnippet> <codesnippet>FROM»Insurance»</codesnippet> <codesnippet>GROUP»BY»region</codesnippet> <list type="bulleted"> <bulletedlist>Format to Time Series</bulletedlist> </list> <pullquote>Time series visualization is the default and primary way to visualize data in Grafana. Your SQL query’s result should have a field named Time.</pullquote> In addition to the pie chart, you can also display bar chart (see Figure 15). <figure id="15" src="image15.png"> Figure 15 : Displaying a bar chart in the current panel</figure> You can also display the data as Stat (statistics; see Figure 16). <figure id="16" src="image16.png"> Figure 16 : Displaying the result of the query as statistics</figure> If you want to display a histogram showing the distribution of the various age range, you can set the panel as shown in Figure 17. <figure id="17" src="image17.png"> Figure 17 : Displaying a histogram showing the age distribution</figure> When you’re done with the panel, clicking on the Apply button at the top of the page returns you to the dashboard. Your dashboard now has one panel (see Figure 18). <figure id="18" src=""> Figure 18 : The dashboard with one panel</figure> From this point, you can add additional panels to your dashboard. <h3>Exporting and Importing Dashboards</h3> Once you’ve created your dashboard, you can export it so that you can back them up or load them into Grafana on another computer. To export a dashboard, click the Share icon and then click the Export tab (see Figure 19). Then, click the Save to file button. <figure id="19" src="image19.png"> Figure 19 : Exporting a dashboard</figure> A JSON file will now be downloaded to your computer. The JSON file now contains all the details of your dashboards and can be sent to another computer. To import the exported dashboard, select + > Import (see Figure 20) and load the JSON file that you saved earlier. The dashboard will now be imported into Grafana. <figure id="20" src="image20.png"> Figure 20 : Importing a dashboard</figure> <h2>Creating Dynamic Auto-Updated Time Series Charts</h2> So far in this article, you’ve seen how to load data from a MySQL database using the MySQL Data Source. However, in real-life, a lot of times the data is from Web APIs, and so it makes sense to be able to use Web APIs in Grafana. Although this looks like a straight-forward affair, it’s more involved than you would imagine. For this section, you’ll: <list type="bulleted"> <bulletedlist>Build a REST API back-end containing stock prices so that it can be used by Grafana’s SimpleJson data source.</bulletedlist> <bulletedlist>Build the front-end using Grafana. The chart that you’ll build shows the stock price of either AAPL or GOOG (fetched from the back-end REST API) and will be automatically updated every five seconds.</bulletedlist> </list> <h3>Creating the Sample Data</h3> First, let’s start off with the server side. For this project, you’ll build a REST API that allows a client to retrieve the stock prices of either AAPL or GOOG based on a specified range of dates. For the stock prices, I’m going to simulate the prices by randomly generating them and then saving them in CSV files. <pullquote>In the real-world, your stock data will more likely come from databases that are updated dynamically.</pullquote> For this article, I’ll pre-generate the prices for four days, from one day before the current day, to three days after the current day. First, generate the simulated stock prices for GOOG: <codesnippet> from»datetime»import»timedelta,»timezone</codesnippet> <codesnippet> import»datetime</codesnippet> <codesnippet> import»numpy»as»np</codesnippet> <codesnippet> import»pandas»as»pddate_today»=»\</codesnippet> <codesnippet> »»»»datetime.datetime.now()</codesnippet> <codesnippet>days»=»pd.date_range(date_today»-»timedelta(1),»</codesnippet> <codesnippet>»»»»»»»»»date_today»+»timedelta(3),»freq»=»'5s',»</codesnippet> <codesnippet>»»»»»»»»»tz»=»timezone.utc)</codesnippet> <codesnippet>df»=»pd.DataFrame(</codesnippet> <codesnippet>»»»»{</codesnippet> <codesnippet>»»»»»»»»'value':np.random.uniform(</codesnippet> <codesnippet> »»»»»»»»»»»»»»»» 2500,3200,len(days))»</codesnippet> <codesnippet>»»»»},»index»=»days)</codesnippet> <codesnippet>display(df)</codesnippet> <codesnippet>df.to_csv('stocks_goog.csv')</codesnippet> The simulated stock price is saved as stocks_goog.csv. Next, generate the prices for AAPL: <codesnippet> from»datetime»import»timedelta,»timezone</codesnippet> <codesnippet> import»datetime</codesnippet> <codesnippet> import»numpy»as»np</codesnippet> <codesnippet> import»pandas»as»pddate_today»=»\</codesnippet> <codesnippet> »»»»datetime.datetime.now()</codesnippet> <codesnippet>days»=»pd.date_range(date_today»-»timedelta(1),»</codesnippet> <codesnippet>»»»»»»»»»date_today»+»timedelta(3),»freq»=»'5s',»</codesnippet> <codesnippet>»»»»»»»»»tz»=»timezone.utc)</codesnippet> <codesnippet>df»=»pd.DataFrame(</codesnippet> <codesnippet>»»»»{</codesnippet> <codesnippet>»»»»»»»»'value':np.random.uniform(</codesnippet> <codesnippet> »»»»»»»»»»»»»»»» 150,190,len(days))»</codesnippet> <codesnippet>»»»»},»index»=»days)</codesnippet> <codesnippet>display(df)</codesnippet> <codesnippet>df.to_csv('stocks_aapl.csv')</codesnippet> The simulated stock price is saved as stocks_aapl.csv. Here is a sample output for the stocks_aapl.csv file: <codesnippet>,value</codesnippet> <codesnippet>2022-03-16»11:19:56.209523+00:00,184.55338767944096</codesnippet> <codesnippet>2022-03-16»11:20:01.209523+00:00,168.76885410294773</codesnippet> <codesnippet>2022-03-16»11:20:06.209523+00:00,188.02816186918278</codesnippet> <codesnippet>2022-03-16»11:20:11.209523+00:00,164.63482117646518</codesnippet> <codesnippet>2022-03-16»11:20:16.209523+00:00,161.33806737466773</codesnippet> <codesnippet>2022-03-16»11:20:21.209523+00:00,169.10779687119663</codesnippet> <codesnippet>2022-03-16»11:20:26.209523+00:00,169.90405158220707</codesnippet> <codesnippet>2022-03-16»11:20:31.209523+00:00,189.30501099950166</codesnippet> <codesnippet>...</codesnippet> <h3>Creating the REST API</h3> Let’s now focus attention on creating the REST API, which is the more challenging aspect of this project. As mentioned earlier, you can use the SimpleJson data source on Grafana to connect to a REST API. However, it requires the REST API to implement specific URLs (see https://grafana.com/grafana/plugins/grafana-simple-json-datasource/ for more details). This means that your REST API must be written specially for the SimpleJson data source. To make my life simpler, I decided to use the Grafana pandas datasource module (https://github.com/panodata/grafana-pandas-datasource) to create my REST API. This module runs an HTTP API based on Flask, and it returns Panda’s dataframes to Grafana, which is what SimpleJson data source can work with. This module also provides samples where you can see how you can implement your own REST API. I’ve adapted one of the provided samples (sinewave-midnights) and modified it for my purposes. Create a new text file and name it demo.py. Populate it with the following statements, shown in Listing 1. Observe the following: <list type="bulleted"> <bulletedlist>The define_and_register_data() function contains another function called get_stock().</bulletedlist> <bulletedlist>The add_metric_reader() function links the query sent by Grafana with the function that handles the query. In this case, it will link the "symbol" query with the get_stock() function.</bulletedlist> <bulletedlist>The query sent by Grafana includes a value. For example, you can configure Grafana to send a query "symbol:AAPL". The value of "AAPL" will be passed to the first parameter of the get_stock() function (see Figure 21).</bulletedlist> </list> <figure id="21" src="image21.png"> Figure 21 : Understanding how the REST API works with Grafana</figure> <list type="bulleted"> <bulletedlist>The second parameter of the get_stock() function—a ts_range—gets its value from Grafana. You’ll see this later.</bulletedlist> <bulletedlist>In the get_stock() function, you load the required CSV file and then perform a filter to only return the rows that match the time range (ts_range) passed in by Grafana. In real-life, you should load the data from a database server.</bulletedlist> <bulletedlist>The REST API listens at port 3003 on the local computer.</bulletedlist> </list> Before you can run the REST API, you need to install the grafana-pandas-datasource module: <codesnippet>$»pip»install»grafana-pandas-datasource</codesnippet> You now can run the REST API: <codesnippet>$»python»demo.py</codesnippet> You should see the following: <codesnippet>*»Serving»Flask»app»"grafana_pandas_datasource"»</codesnippet> <codesnippet>»(lazy»loading)</codesnippet> <codesnippet>*»Environment:»production</codesnippet> <codesnippet>WARNING:»This»is»a»development»server.»Do»not»use</codesnippet> <codesnippet>»»it»in»a»production»deployment.Use»a»production»</codesnippet> <codesnippet>»»WSGI»server»instead.</codesnippet> <codesnippet>*»Debug»mode:»on</codesnippet> <codesnippet>2022-03-17»13:37:38,454.454»[werkzeug»»»»»»»»»]»</codesnippet> <codesnippet>INFO»»»:»»*»Running»on»http://127.0.0.1:3003/»</codesnippet> <codesnippet>»»(Press»CTRL+C»to»quit)</codesnippet> <codesnippet>2022-03-17»13:37:38,455.455»[werkzeug»»»»»»»»»]»</codesnippet> <codesnippet>INFO»»»:»»*»Restarting»with»stat</codesnippet> <codesnippet>2022-03-17»13:37:38,895.895»[werkzeug»»»»»»»»»]</codesnippet> <codesnippet>WARNING:»»*»Debugger»is»active!</codesnippet> <codesnippet>2022-03-17»13:37:38,902.902»[werkzeug»»»»»»»»»]</codesnippet> <codesnippet>INFO»»»:»»*»Debugger»PIN:»301-215-354</codesnippet> <h3>Testing the REST API</h3> You can now test the REST API. You can issue the following command using the curl utility: <codesnippet> $» curl»-H»"Content-Type:»application/json"»-X»POST» </codesnippet> <codesnippet> »»http://127.0.0.1:3003/query»-d»‘{"targets":»</codesnippet> <codesnippet> »»[{"target":»"symbol:GOOG"}],»"range":»{"from": </codesnippet> <codesnippet> »»"2022–03–17»03:00:49.110000+00:00",» </codesnippet> <codesnippet> »»"to":»"2022–03–17»03:05:49.110000+00:00"}}’ </codesnippet> The result looks like this: <codesnippet>[</codesnippet> <codesnippet>»»{</codesnippet> <codesnippet>»»»»"datapoints":»[</codesnippet> <codesnippet>»»»»»»[</codesnippet> <codesnippet>»»»»»»»»3169.441653300435,»</codesnippet> <codesnippet>»»»»»»»»1647486052594</codesnippet> <codesnippet>»»»»»»],»</codesnippet> <codesnippet>»»»»»»[</codesnippet> <codesnippet>»»»»»»»»2748.501265212758,»</codesnippet> <codesnippet>»»»»»»»»1647486057594</codesnippet> <codesnippet>»»»»»»],»</codesnippet> <codesnippet>»»»»»»[</codesnippet> <codesnippet>»»»»»»»»3195.3559754568632,»</codesnippet> <codesnippet>»»»»»»»»1647486062594</codesnippet> <codesnippet>»»»»»»],»</codesnippet> <codesnippet>»»»»»»...»</codesnippet> <codesnippet>»»»»»»[</codesnippet> <codesnippet>»»»»»»»»2744.0582066482057,»</codesnippet> <codesnippet>»»»»»»»»1647486342594</codesnippet> <codesnippet>»»»»»»],»</codesnippet> <codesnippet>»»»»»»[</codesnippet> <codesnippet>»»»»»»»»3098.521949302881,»</codesnippet> <codesnippet>»»»»»»»»1647486347594</codesnippet> <codesnippet>»»»»»»]</codesnippet> <codesnippet>»»»»],»</codesnippet> <codesnippet>»»»»"target":»"value"</codesnippet> <codesnippet>»»}</codesnippet> <codesnippet>]</codesnippet> <pullquote>Each value in the datapoint array contains the stock price, as well as the epoch time. You can use the Epoch time converter at https://www.epochconverter.com to convert the value of 1647486052594, which yields the date of Thursday, 17 March 2022 03:00:52.594 (GMT).</pullquote> Now that the REST API is up and running, the next thing is to work on the Grafana side. <h3>Adding the SimpleJson Data Source</h3> The next step is to add and configure the SimpleJSON data source in Grafana. Using the SimpleJSON data source, you can connect to REST APIs and download the data in JSON format. In Grafana, first add a new Data Source (Configuration > Data sources). Click on the Add data source button. Search for SimpleJson and double click on it (see Figure 22). <figure id="22" src="image22.png"> Figure 22 : Add the SimpleJson data source to the project.</figure> For the URL, enter http://localhost:3003 and click Save & test (see Figure 23). <figure id="23" src="image23.png"> Figure 23 : Configure the SimpleJson data source.</figure> <h3>Creating the Dashboard</h3> Create a new dashboard in Grafana. Then add a new Panel by clicking Add a new panel (see Figure 24). <figure id="24" src="image24.png"> Figure 24 : Add a new panel to the dashboard.</figure> Using the default Time series visualization, configure the Data source as shown in Figure 25. Also, enter symbol:AAPL for the timeserie query. You should now see the chart plotted. <figure id="25" src="image25.png"> Figure 25 : Configure the SimpleJson data source in the Panel.</figure> Earlier I mentioned that Grafana will send a query to the REST API. This is the query: symbol:AAPL. You can control how often you want Grafana to fetch the data for you by selecting the date range (see Figure 26). <figure id="26" src="image26.png"> Figure 26 : Select how often you want Grafana to fetch your data for you.</figure> For example, if the current date and time is 17th March 2022, 5.53am (UTC-time), Grafana will send the following time range to the back-end REST API if you select the Last 5 minutes option: <codesnippet> { </codesnippet> <codesnippet> »»'$gt':»datetime.datetime(2022,»3,»17,»5,»48,» </codesnippet> <codesnippet> »»»»»»»»» 18,»555000,» </codesnippet> <codesnippet> »»»»»»»»»tzinfo=<UTC>),» </codesnippet> <codesnippet> »»'$lte':»datetime.datetime(2022,»3,»17,»5,»53,» </codesnippet> <codesnippet> »»»»»»»»»» 18,»555000,» </codesnippet> <codesnippet> » » »»»»»»»»tzinfo=<UTC>) </codesnippet> <codesnippet> } </codesnippet> The chart will be updated with the data for the last five minutes (see Figure 27). <figure id="27" src="image27.png"> Figure 27 : Display the data fetched from the REST API.</figure> Click the Save button at the top right corner to name and save the dashboard (see Figure 28). <figure id="28" src="image28.png"> Figure 28 : Save the dashboard.</figure> You’ll now be returned to the dashboard. <h3>Configuring a Variable</h3> You can configure a variable in Grafana for your dashboard to allow users to display the chart for different stock symbols. <pullquote>In Grafana, a variable is a placeholder for a value that you can reference in your queries and panels.</pullquote> Click on the Settings button for your current dashboard (see Figure 29). <figure id="29" src="image29.png"> Figure 29 : Configure the dashboard</figure> Click the Variables section on the left and then click Add variable (see Figure 30). <figure id="30" src="image30.png"> Figure 30 : Add a variable to the current dashboard.</figure> Enter the following information for the variable and then click Update (see Figure 31). <figure id="31" src="image31.png"> Figure 31 : Configure the variable to be added to the dashboard.</figure> Back in the dashboard, select Edit for the Panel (see Figure 32). <figure id="32" src="image32.png"> Figure 32 : Editing the panel</figure> Set the title of the Panel as Company: $stock and update the query to symbol:$stock (see Figure 33). Prefixing the variable name with the $ character allows you to retrieve the value of the variable and use it in your query and panel. <figure id="33" src="image33.png"> Figure 33 : Using the newly added variable in the panel</figure> Click Apply. In the textbox next to the stock variable, enter AAPL. You should now see the chart for AAPL (see Figure 34). <figure id="34" src="image34.png"> Figure 34 : Viewing the chart for AAPL</figure> In the textbox next to the stock variable, enter GOOG. You should now see the chart for GOOG (see Figure 35). <figure id="35" src="image35.png"> Figure 35 : Viewing the chart for GOOG</figure> <h3>Auto-Updating the Chart</h3> To automatically update the chart, select the drop-down list next to the Refresh button and select how often you want to refresh the chart (see Figure 36). <figure id="36" src="image36.png"> Figure 36 : Select how often you want the chart to refresh.</figure> If you select 5s, the chart will now be updated every five seconds. <h2>Displaying Real-Time Sensor Data Using MQTT</h2> In Grafana 8.0, as part of the Grafana Live feature, it’s now possible to perform real-time data updates using a new streaming API. This means that it’s now possible for you to create charts that update in real-time and on-demand. To make use of this feature, you can make use of the MQTT data source (https://github.com/svet-b/grafana-mqtt-datasource), a plug-in that allows Grafana users to visualize MQTT data in real-time. In this section, you’ll learn how to use the MQTT data source to display sensor data in real-time. <pullquote>In order to use the MQTT data source, you need to use Grafana 8.0, which makes it possible to perform real-time data updates using a new streaming API that’s part of the Grafana Live feature.</pullquote> Using MQTT is ideal for projects such as those involving the Raspberry Pi (see Figure 37). <figure id="37" src="image37.png"> Figure 37 : A Raspberry Pi</figure> <h3>Installing the MQTT Data Source into Grafana</h3> Although there is the MQTT data source built for Grafana, it doesn’t come bundled with Grafana—you need to build and install it yourself. This is where the complexity comes in. In this section, I’ll show you how to install the MQTT Data Source on Windows. You’ll need a few tools/language: <list type="bulleted"> <bulletedlist>Node.js</bulletedlist> <bulletedlist>yarn</bulletedlist> <bulletedlist>Go</bulletedlist> <bulletedlist>Mage</bulletedlist> </list> Don’t worry if you’re not familiar with these tools and language. I’ll show you how to install them in the following sections. <h3>Install Node.js</h3> Download and install Node.js from https://nodejs.org/en/download/. Once Node.js is installed on your system, type the following command in Command Prompt to install yarn: <codesnippet>npm»install»—g»yarn</codesnippet> <pullquote>Yarn is a package manager that doubles down as a project manager.</pullquote> <h3>Install Go</h3> Download and install Go from https://go.dev/dl/. Follow the instructions in the installer. <pullquote>Go is a statically typed, compiled programming language designed at Google by Robert Griesemer, Rob Pike, and Ken Thompson.</pullquote> <h3>Download Mage</h3> Go to the source of Mage at HYPERLINK "https://github.com/magefile/mage" https://github.com/magefile/mage. Click on the Code button and select Download ZIP (see Figure 38). <figure id="38" src="image38.png"> Figure 38 : Download the source code for Mage.</figure> <pullquote>Mage is a build tool like Make, but instead of writing bash code, Mage lets you write the logic in Go.</pullquote> Once downloaded, extract the content of the zip file onto the Desktop. Next, find out the location of your GOPATH by typing this command in Command Prompt: <codesnippet>go»env</codesnippet> You can locate the path for GOPATH from the output: <codesnippet>...</codesnippet> <codesnippet>set»GOOS=windows</codesnippet> <codesnippet>set»GOPATH=C:\Users\Wei-Meng»Lee\go</codesnippet> <codesnippet>set»GOPRIVATE=</codesnippet> <codesnippet>...</codesnippet> Type the following command to create a path call Go (if this path does not already exist on your computer; remember to use your own path): <codesnippet>cd»c:\users\Wei»Meng»Lee\</codesnippet> <codesnippet>mkdir»Go</codesnippet> In the Command Prompt, change the directory to the mage-master folder: <codesnippet>cd»C:\Users\Wei-Meng»Lee\Desktop\mage-master</codesnippet> Type the following command: <codesnippet>go»run»bootstrap.go</codesnippet> You should see something like this: <codesnippet>Running»target:»Install</codesnippet> <codesnippet>exec:»go»"env"»"GOBIN"</codesnippet> <codesnippet>exec:»go»"env"»"GOPATH"</codesnippet> <codesnippet>exec:»git»"rev-parse"»"»—»short"»"HEAD"</codesnippet> <codesnippet>exec:»git»"describe"»"»—»tags"</codesnippet> <codesnippet>exec:»go»"build"»"-o"»"C:\\Users\\Wei-Meng»</codesnippet> <codesnippet>Lee\\go\\bin\\mage.exe"»"-ldflags=-X»</codesnippet> <codesnippet>\"github.com/magefile/mage/mage.timestamp=2022–03–</codesnippet> <codesnippet>18T12:44:11+08:00\"»-X»</codesnippet> <codesnippet>\"github.com/magefile/mage/mage.commitHash=\"»-X»</codesnippet> <codesnippet>\"github.com/magefile/mage/mage.gitTag=dev\""»</codesnippet> <codesnippet>"github.com/magefile/mage"</codesnippet> <h3>Download the Source for the MQTT Data Source</h3> The next step is to download the source code of the MQTT data source. Go to https://github.com/grafana/mqtt-datasource and download the ZIP file (see Figure 39). <figure id="39" src="image39.png"> Figure 39 : Download the source code of the MQTT Data Source.</figure> Once the file is downloaded, extract the content of the zip file to the Desktop. Edit the file named package.go in the mqtt-datasource-main folder using a code editor. Replace "rm -rf dist && ..." with "del /F /Q dist && ..." (the command you replaced is to ensure that it works on Windows): <codesnippet>{</codesnippet> <codesnippet>»»"name":»"grafana-mqtt-datasource",</codesnippet> <codesnippet>»»"version":»"0.0.1-dev",</codesnippet> <codesnippet>»»"description":»"MQTT»Datasource»Plugin",</codesnippet> <codesnippet>»»"scripts":»{»"build":»"del»/F»/Q»dist»&&»grafana-</codesnippet> <codesnippet>toolkit»plugin:build»&&»mage»build:backend",</codesnippet> <codesnippet>»»...</codesnippet> In the Command Prompt, cd to the mqtt-datasource-main folder: <codesnippet>cd»C:\Users\Wei-Meng»Lee\Desktop\mqtt-datasource-main</codesnippet> And type in the following commands: <codesnippet>yarn»build</codesnippet> <codesnippet>yarn»install</codesnippet> <h3>Configuring the Plugin</h3> Move the mqtt-datasource-main folder into the C:\Program Files\GrafanaLabs\grafana\data\plugins folder. <pullquote>The C:\Program Files\GrafanaLabs\grafana\data\plugins folder is where Grafana stores the various plug-ins.</pullquote> Next, load the defaults.ini file in the C:\Program Files\GrafanaLabs\grafana\conf folder and add in the following statement in bold: <codesnippet>[plugins]</codesnippet> <codesnippet>enable_alpha»=»false</codesnippet> <codesnippet>app_tls_skip_verify_insecure»=»false</codesnippet> <codesnippet>#»Enter»a»comma-separated»list»of»plugin»identifiers»</codesnippet> <codesnippet>to»identify»plugins»to»load»even»if»they»are»</codesnippet> <codesnippet>unsigned.»Plugins»with»modified»signatures»are»never</codesnippet> <codesnippet>loaded.</codesnippet> <codesnippet>allow_loading_unsigned_plugins»=»grafana-mqtt-</codesnippet> <codesnippet>datasource</codesnippet> <codesnippet>#»Enable»or»disable»installing»/»uninstalling»/»</codesnippet> <codesnippet>updating»plugins»directly»from»within»Grafana.</codesnippet> <codesnippet>plugin_admin_enabled»=»true</codesnippet> The above addition indicates to Grafana to allow unsigned plug-ins (which, in this case, is the MQTT Data Source). Restart Grafana in Windows Services. If you have performed the above steps in building the MQTT Data Source for Grafana, you should now be ready to use it. <h3>Publishing Data to a MQTT Broker</h3> Before you start to build a Panel to display data using the MQTT Data Source, you need to write data to a MQTT broker so that you can subscribe to it. For this, I’ll write a Python program to simulate some sensor data. <pullquote>If you’re not familiar with a MQTT broker, refer to my article on MQTT at: https://weimenglee.medium.com/using-mqtt-to-push-messages-across-devices-1465855b36e5</pullquote> Create a text file and name it as publish.py. Populate it with the following statements: <codesnippet> #»pip»install»paho-mqtt </codesnippet> <codesnippet> import »paho.mqtt.client» as »mqtt </codesnippet> <codesnippet> import »numpy» as »np </codesnippet> <codesnippet> import »time </codesnippet> <codesnippet> MQTTBROKER»=» 'test.mosquitto.org' </codesnippet> <codesnippet> PORT»=»1883 </codesnippet> <codesnippet> TOPIC»=» "home/temp/room1/storeroom" </codesnippet> <codesnippet> mqttc»=»mqtt.Client( "python_pub" ) </codesnippet> <codesnippet> mqttc.connect(MQTTBROKER,»PORT) </codesnippet> <codesnippet> while » True : </codesnippet> <codesnippet> »»»»MESSAGE»=»str(np.random.uniform(20,30)) </codesnippet> <codesnippet> »»»»mqttc.publish(TOPIC,»MESSAGE) </codesnippet> <codesnippet> »»»»print( "Published»to»" »+»MQTTBROKER»+» ':»' »+» </codesnippet> <codesnippet> »»»»»»»»»»TOPIC»+» ':' »+»MESSAGE) </codesnippet> <codesnippet> »»»»time.sleep(3) </codesnippet> The above Python program sends some data to the public MQTT broker every three seconds. To run the publish.py file, type the following commands in Command Prompt: <codesnippet>pip»install»paho-mqtt</codesnippet> <codesnippet>python»publish.py</codesnippet> <h3>Subscribe Using the MQTT Data Source</h3> In Grafana, add a new data source by searching for mqtt (see Figure 40). <figure id="40" src="image40.png"> Figure 40 : Add the MQTT data source to Grafana.</figure> Configure the MQTT data source as follows (see also Figure 41): <list type="bulleted"> <bulletedlist> Host: test.mosquitto.org</bulletedlist> <bulletedlist> Port: 1883</bulletedlist> </list> You can leave the fields under the Authentication section empty. Click Save & test once you’re done. <figure id="41" src="image41.png"> Figure 41 : Configure the MQTT data source.</figure> Next, create a new Dashboard and add a new Panel (see Figure 42): <figure id="42" src="image42.png"> Figure 4 2: Add a new panel to the dashboard.</figure> Configure the panel with the following (see also Figure 43): <list type="bulleted"> <bulletedlist> Visualization: Time Series</bulletedlist> <bulletedlist> Data source: MQTT</bulletedlist> <bulletedlist> Topic: home/temp/room1/storeroom</bulletedlist> </list> Click Apply when done. <figure id="43" src="image43.png"> Figure 43 : Configuring the panel</figure> You should now see the chart plotted and updated every three seconds (see Figure 44). If you can see the chart, this means that you’re able to receive the data through the MQTT data source. <figure id="44" src="image44.png"> Figure 44 : Display the data from MQTT as a line chart.</figure> <pullquote>Hint: Switch the Dashboard to view the Last 5 minutes to see a close-up of the chart.</pullquote> <h2>Summary</h2> In this article, you’ve seen how to use Grafana to build dashboards with a minimal amount of code needed. Instead of code, all you need is some basic SQL skills (of course, there will be cases where you need to write complex SQL queries to extract your data). In addition, I’ve walked you through two projects where you learned how to dynamically update your chart from a REST API, as well as fetch data from a MQTT data source. Have fun! </body> <sidebars> <sidebar title="Sponsored Sidebar" /> <sidebar title="Need FREE Project Advice? CODE Can Help!"> No strings free advice on a new or existing software development projects. CODE Consulting experts have experience in cloud, Web, desktop, mobile, microservices, containers, and DevOps projects. Schedule your free hour of CODE call with our expert consultants today. For more information visit www.codemag.com/consulting or email us at info@codemag.com. </sidebar> </sidebars> <tables /> <codelistings> <codelisting id="1" header="Listing 1. The REST API for stock prices"> <code> import»numpy»as»np</code> <code> import»pandas»as»pd</code> <code> from»grafana_pandas_datasource»import»create_app</code> <code> from»grafana_pandas_datasource.registry»import»data_generators»</code> <code> »»as»dg</code> <code> from»grafana_pandas_datasource.service»import»pandas_component</code> <code /> <code> from»datetime»import»datetime,»timedelta</code> <code> from»pytz»import»timezone»</code> <code /> <code> def»define_and_register_data():»»»»</code> <code>»»»»def»get_stock(stock_symbol,»ts_range):</code> <code>»»»»»»»»if»stock_symbol»==»'AAPL':</code> <code>»»»»»»»»»»»»df»=»pd.read_csv('stocks_aapl.csv',»</code> <code>»»»»»»»»»»»»»»»»parse_dates=True,»index_col=0)</code> <code>»»»»»»»»if»stock_symbol»==»'GOOG':</code> <code>»»»»»»»»»»»»df»=»pd.read_csv('stocks_goog.csv',»</code> <code>»»»»»»»»»»»»»»»»parse_dates=True,»index_col=0)»»»»»»»»</code> <code>»»»»»»»»»»»»»»»»</code> <code>»»»»»»»»#»return»the»rows»that»falls»within»the»specified»dates</code> <code>»»»»»»»»return»df[(df.index»>»ts_range['$gt'])»&</code> <code>»»»»»»»»»»»»»»»»»»(df.index»<»ts_range['$lte'])]</code> <code /> <code>»»»»#»Register»data»generators</code> <code>»»»»dg.add_metric_reader("symbol",»get_stock)</code> <code>»»»»</code> <code> def»main():</code> <code>»»»»#»Define»and»register»data»generators.</code> <code>»»»»define_and_register_data()</code> <code>»»»»</code> <code>»»»»#»Create»Flask»application.</code> <code>»»»»app»=»create_app()»»»»</code> <code>»»»»</code> <code>»»»»#»Register»pandas»component.</code> <code>»»»»app.register_blueprint(pandas_component,»url_prefix="/")»»»»</code> <code>»»»»</code> <code>»»»»#»Invoke»Flask»application.</code> <code>»»»»app.run(host="127.0.0.1",»port=3003,»debug=True)</code> <code /> <code> if»__name__»==»"__main__":</code> <code>»»»»main()</code> </codelisting> </codelistings> <Errors> <Error>Actual figure not found for this caption: Figure 18: The dashboard with one panel</Error> </Errors> </document>

Add New Technology/Category
Category Name: *	(Example: .NET)
Category Group: (optional)	(Example: .NET, Visual Studio, Web Development)