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

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, turbofuture.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://turbofuture.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)