Create a Monitoring Dashboard (Web Page) Using SQL

Updated on January 22, 2018

I like statistics especially when I'm the one gathering the data and presenting them.

  • Innocent numbers tell stories. The volume of transactions, number of sessions, processing speed. I say "innocent" because these numbers know nothing and can't tell a meaningful story by themselves.
  • Timestamps put some colors and details to these stories. When does the volume of transactions increase or decrease? When does the majority of users use the application? When does the processing speed decline?
  • Discrepancies give malice to these stories. A discrepancy is something that catches your attention. You follow the story because of the discrepancy. It is either the interesting main character of the story or a clue for a crime in a detective story.

Dashboard (Web Page) generated using SQL syntax.
Dashboard (Web Page) generated using SQL syntax.

Dashboard

In this article, I will teach you how to generate a web page using SQL*Plus. You can use this link for a full view of the web page shown in the screenshot above: View SQL*Plus dashboard

I will not paste the source code here because it is too long and quite painful in the eyes. This is because I did not format it manually using any text editor or IDE, it was automatically generated by a batch script.

The raw source code looks like this: SQLPlus Dashboard on GitHub.

Batch File

Create a batch file that contains the database access credentials and the file path of the SQL query that will fetch the information you need displayed on the dashboard which will generate the HTML file itself.

Replace the username, password, and dbname in the code snippet below. The file path should be the absolute path including the SQL file itself.

For example, "Z:\SQL Scripts\SQLPlusScript.sql"

sqlplus <username>/<password>@<dbname> @"<file path>"

SQL File

The SQL file contains the same SQL queries as you would run using an IDE (i.e. Oracle SQL Developer) except there are a few unfamiliar syntax that you don't normally use when performing basic queries. Each SELECT statement generates a table. The example in the first screenshot has three SELECT statements so it displays three tables.

The code from line 1 to line 9, line 11, 16, and 18 is used by SQL*Plus to export the result of your SQL queries into an HTML file. You can also use CSS for additional formatting by using the style tag.

set pagesize 50000
set feedback off
SET MARKUP HTML ON SPOOL ON -
HEAD '<title>Dashboard</title> -
<style type="text/css"> -
   table { background: #eee; font-size: 80%; font-family: "Arial", Helvetica, sans-serif; border-collapse: collapse; border: 1px solid black; width: 95%} -
   th { background: #000000; font-size: 80%; color: #ffffff; width: 150px} -
   td { padding: 2px; font-size: 90%; width: 150px; text-align: center} -
</style>'

SPOOL "Z:\Dashboard\dashboard.html"

SELECT QUEUE, SERVERNAME, TASKNAME, LASTRUN FROM DUAL;
SELECT RUNDATE, INTRANS AS INBOUND_TRANS, QAPRO AS "QNAME1 PROCESSED", QBPRO AS "QNAME2 PROCESSED", TOTAL_TRANS FROM DUAL;

SPOOL OFF

EXIT

Multiple Reports

You can also export to different paths using the same SQL file by putting in another SPOOL / SPOOL OFF block. This is useful if you want to generate different reports where you may need to extract different data for different people who are only allowed to access some of the dashboards. You can just group people by their roles and set the read access restrictions based on that.

There are, of course, better ways of doing this but I am just setting examples on quick and dirty stuff you can do with this when you are bored, lack resources, or just like me had a thought out of nowhere that "Hey, what if you can create a web page using SQL? That would be kinda cool."

The example below will generate two files from different directories. One in drive Z and another in drive D. You can then set permissions on who can access these files over the network and tailor each one for the specific audience.

set pagesize 50000
set feedback off
SET MARKUP HTML ON SPOOL ON -
HEAD '<title>Dashboard</title> -
<style type="text/css"> -
   table { background: #eee; font-size: 80%; font-family: "Arial", Helvetica, sans-serif; border-collapse: collapse; border: 1px solid black; width: 95%} -
   th { background: #000000; font-size: 80%; color: #ffffff; width: 150px} -
   td { padding: 2px; font-size: 90%; width: 150px; text-align: center} -
</style>'

SPOOL "Z:\Dashboard\dashboard.html"

SELECT QUEUE, SERVERNAME, TASKNAME, LASTRUN FROM DUAL;
SELECT RUNDATE, INTRANS AS INBOUND_TRANS, QAPRO AS "QNAME1 PROCESSED", QBPRO AS "QNAME2 PROCESSED", TOTAL_TRANS FROM DUAL;

SPOOL OFF

SPOOL "D:\Dashboard\dashboard.html"

SELECT QUEUE, SERVERNAME, TASKNAME, LASTRUN FROM DUAL;
SELECT RUNDATE, INTRANS AS INBOUND_TRANS, QAPRO AS "QNAME1 PROCESSED", QBPRO AS "QNAME2 PROCESSED", TOTAL_TRANS FROM DUAL;

SPOOL OFF

EXIT

Scheduling

You should have the following files by now:

  • Batch file
  • SQL file
  • HTML file (Generate this by running the .bat file)

Instead of manually running the batch file, you can set a schedule on when it should automatically run using a cron task, Windows Task Scheduler, or whatever program you have that allows you to set scheduled runs for batch files. If you are simply using the Windows Task Scheduler, I provided some steps in another article on how to set this up: A Simple Time Tracking Tool Using Batch Files and Windows Task Scheduler

A Little History

When I was a junior software engineer back in the late 2012, a large portion of my tasks involves monitoring servers and transactions. I was supporting a Middleware application that has more than 100 interfaces with multiple external systems. Since we're in the middle, it technically means that if we're dead, everyone else is. You can imagine how crucial monitoring tasks were and it was reasonable to spend all nine hours of your shift to perform all monitoring, reporting, and maintenance tasks unless you will split the tasks with someone else. I believe that was the time I began my adventure in automation.

I was performing my usual tasks when I found some batch files. Curious, I decided to open the files to see the scripts inside. I've always been fascinated with scripts and command lines. The ones that you just enter into a terminal, no compilations, and get a quick result. I've used various Linux distributions since I first had a laptop mainly because I enjoyed terminals. Not that other operating systems don't have terminals or that Linux distributions have no user interface. It's just that Linux, to me, is a soul.

Nevertheless, I did not know that there was a command line version for SQL (Yes, I was a noob back then), that you can execute the scripts without the limitation of having to use a developer with a graphical user interface and that you can do this kind of thing for free. This was the time my obsession began. This was the time when it all started, when I went crazy over finding command line versions of everything, when I felt excited like a child and thought that all automations are possible.

I'm talking about SQL*Plus. At first, I played around with it and automated some of my daily extracts. I saved the extracts to CSV, XLS, and other formats. I searched through the web and found out that you can save the data into an HTML format and open it using a browser. Your data will appear in a web page. Like a kid, I was so happy about the new knowledge that I was not thought in school. I focused on the CSV and XLS formats because that's what we needed back then.

After I left my first company, I played around with the HTML format for a while during my three month hiatus from work. I applied the code I developed during my three month break to suit my next employer's needs. It turned out to be useful and made a good impact on my career.

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.

    working