Impress Your Boss With Automated Reporting Displays

Get a proof of concept up in 15 minutes using Docker, MySQL and Grafana Visualization

I Sun
I Sun
Dec 2, 2020 · 7 min read
Image for post
Image for post
Source: Author

Motivation

Throughout my engineering career as a systems engineer (i.e. business analyst), my handy dandy go-to has always been Excel spreadsheets. With VBA and pivot tables, there wasn’t anything I couldn’t tackle. So why not keep using spreadsheets? Well, it was time to get out of my comfort zone and embrace/practice all the DevOps training my organization has been pushing.

The best way to bring about change is to show quick results and minimize the amount of maintenance to keep it going. If you tell management, you’re coming up with some automated process they worry you’ll take forever building some complicated system that will be impossible to maintain…and it’s true, I’ve seen many times when people make it way more complicated than it needs to be.

Not today though. Take my hand, join me on this journey to get a prototype running in 15 minutes or less while learning a couple of technologies:

  • Docker — spinning up containers to get apps up and running quickly
  • MySQL — creating a simple test database from scratch
  • Grafana — leverage open source visualization to come up with fancy charts

Install Docker

  • Docker image — represents an application and its virtual environment. There are libraries of ready-to-go Docker images you can grab such as from Docker Hub.
  • Docker container — a virtualized run-time environment where users can isolate applications from the underlying system. These contain the running images which start quickly and easily.

Follow the Docker installation info for your platform of choice. After you’re done you should be able to type the following in a terminal to test that Docker is ready to go:

$ docker --version

Get Running with MySQL

In a nutshell, all the configuration has already been specified by somebody who (hopefully) knows what they’re doing as part of the Docker image so all you have to do is grab that image and run it (in a Docker container).

Let’s get rolling. Run the following Docker command:

  • mysql — this is the name you’re specifying for the Docker container, feel free to use something else
  • my-secret-pw — this is the password for the root account to the database. Obviously not the most secure, so you’ll want to change it if things get serious.
  • latest — this lets Docker know that you want the latest version. You can enter a specific version here if you’re particular.
$ docker run --name mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:latest

After you run this command you’ll see a flurry of action as Docker goes to work trying to grab a Docker image for MySQL and spin up a container. What is this black magic? When it can’t find the image locally it goes off and pulls it from Docker Hub.

$ docker run --name mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:latestUnable to find image 'mysql:latest' locallylatest: Pulling from library/mysqlbb79b6b2107f: Pull complete49e22f6fb9f7: Pull complete...Status: Downloaded newer image for mysql:latestcb1661a2c070c5e4326e470c5311d5194c16c7948e7a2fa7522c5921b0baa004

After the Docker run command has done its thing, check to see if MySQL is running by typing:

$ docker ps -a

With the Docker container running, we can connect to it using an interactive terminal with the following command:

$ docker exec -it mysql mysql -uroot -p

After entering in the root password that we defined when executing the Docker run comment (“my-secret-pw”), you’ll be welcomed into the database world with a message like this:

Welcome to the MySQL monitor.  Commands end with ; or \g.Server version: 8.0.22 MySQL Community Server - GPLCopyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

Congratulations! That wasn’t too difficult right? A MySQL database running in just 2 lines of script.

Create Source Data

Obviously, depending on what you’re working on there may be existing software suites out there such as Atlassian Jira for task management or HP Application Lifecycle Management that tracks testing against requirements. If your company is paying for the licenses, by all means use them if they meet your business needs.

What this example shows is what you can accomplish with some open-source software in a short amount of time. With enough programming know-how, the flexibility is there to pipe in data from different sources and run some data transformation.

So let’s get back to business. When we ran the Docker run command at the beginning it created a database using the name given to the container which was “mysql”. Run this command to use that database:

mysql> use mysql;

Now we create a simple table to store pass/fail/skip data for our fictional test runs:

myqsl> create table test_results(    id INT NOT NULL AUTO_INCREMENT,    tester VARCHAR(100) NOT NULL,    test_name VARCHAR(100) NOT NULL,    test_date DATE,    steps_passed INT,    steps_failed INT,    steps_skipped INT,    PRIMARY KEY ( id ));

We don’t want our reporting software querying our database as the root user so we set up a read-only “report user”:

CREATE USER 'reportuser' IDENTIFIED BY 'password';
GRANT SELECT ON mysql.test_results TO 'reportuser';
FLUSH PRIVILEGES;

Insert some records into the table:

mysql> INSERT INTO test_results(tester, test_name, test_date, steps_passed, steps_failed, steps_skipped)VALUES(’Tester1’, ‘procedure1’, ’2020-11-15’, 8, 1, 1);mysql> INSERT INTO test_results(tester, test_name, test_date, steps_passed, steps_failed, steps_skipped)VALUES(’Tester2’, ‘procedure1’, ’2020-11-16’, 9, 1, 0);

That’s it for the data. Next we grab Grafana to create the visuals for us.

Setting up Grafana

docker run -d -p 3000:3000 grafana/grafana

This grabs Grafana and runs a container through internal port 3000. We now have two Docker containers running if you want to run this command again:

$ docker ps -a

Navigate to http://localhost:3000. Login using admin/admin and you’ll be presented with the Grafana dashboard. No need for monkeying around with downloading and installing files for a particular OS…it just works.

Image for post
Image for post
Source: Author

Now we’ll need to connect Grafana to a data source. The data source in this case will be the table we set up earlier in MySQL that contains test results.

We first need to find the IP address of the Docker container that MySQL is running in. To do that, use the “docker ps -a” command to obtain the container id of the MySQL container.

$ docker ps -a

It should looks something like the bolded text below. Run the following command (substituting the container ID of your MySQL instance) to obtain the IP address.

docker inspect f1fb2f8887b4 | grep "IPAddress"

From the Grafana dashboard, click on the card that says “Add your first data source”. You’ll enter the following:

  • Host: Use the IP address obtained for the MySQL container, it should be running on port 3306 by default
  • Database: Name of the database
  • User/Password: Login credentials for the read-only report user we created
Image for post
Image for post
Source: Author

Click Save and Test to continue. Voila! We’ve got Grafana running and a data source configured. Now we’re cooking.

Create a Report Dashboard

Image for post
Image for post
Source: Author

There’s a report query builder that loads up by default but it was easier for me to just jump to straight SQL. Enter a query such as the one below and right away you’ll see a preview of the results:

Image for post
Image for post
Source: Author

There you have it. During your lunch break you’ve created a reporting dashboard that is reading from a MySQL database. A more robust solution than a shared Excel spreadsheet.

Note: After Docker containers are stopped, the data isn’t persisted unless you’ve set up storage on the host system, read more about Docker volumes.

Image for post
Image for post
Source: Author

Takeaway

The Startup

Medium's largest active publication, followed by +771K people. Follow to join our community.

I Sun

Written by

I Sun

Engineer, restaurant owner, dabbler in the written arts. Proud to call JHU, USC, and UIUC my alma mater. Find me on Twitter @kaistreetfare

The Startup

Medium's largest active publication, followed by +771K people. Follow to join our community.

I Sun

Written by

I Sun

Engineer, restaurant owner, dabbler in the written arts. Proud to call JHU, USC, and UIUC my alma mater. Find me on Twitter @kaistreetfare

The Startup

Medium's largest active publication, followed by +771K people. Follow to join our community.

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store