Web Tracking Board to Display SQL Query - Overclock.net - An Overclocking Community

Forum Jump: 

Web Tracking Board to Display SQL Query

 
Thread Tools
post #1 of 3 (permalink) Old 01-12-2017, 12:17 PM - Thread Starter
New to Overclock.net
 
CJston15's Avatar
 
Join Date: Jan 2014
Posts: 140
Rep: 3 (Unique: 3)
At work we have a SQL query that gets run against one of our application databases and then gets emailed out to a list of recipients who need to then follow up and take action on the items in that report.

What we would like to do is create a web tracking tool that auto refreshes say every 5mins that runs and displays that SQL query. What would be the best way to achieve this?

Would something like using XAMPP on a VM webserver to host a php site that's displaying the query work?

Could there be a way to send out text message or email alerts when that query returns something?

Any guidance would be appreciated!

Thanks

CJston15 is offline  
Sponsored Links
Advertisement
 
post #2 of 3 (permalink) Old 01-12-2017, 03:36 PM
New to Overclock.net
 
Join Date: Dec 2009
Location: In a Box
Posts: 1,046
Rep: 30 (Unique: 28)
You seem to have come up with the gist of it yourself. One thing to note is that XAMPP is a development software suite, so you wouldn't want to run your live server with it. For scripting the SQL query, you have plenty of options, but if you have php developers at work or somebody who knows some basic php you should be able to write a service quite easily. Using javscript with Node.js is also a good option, as you don't need to setup apache since node.js can be used as a webserver directly via express.js.

If you want to setup a way to respond to the new data, I'd make sure you've got the server setup with SSL. After that you can send the data via POST requests. I'd also set up some type of authentication scheme, but you'll have to decide weather you want to use basic authentication or something more complicated. In either case, make sure all data input from the user is properly escaped to avoid SQL injection attacks.

Depending on how many people are accessing the page at once, you may want to so some caching of the SQL query results. Refreshing the page is a just a bit of javascript.
Emails are pretty simple, as PHP has a built in function to send mail, and sending mail with node.js is nearly as simple. I have no experience with text messages, but you might want to take a look at this page: https://davidwalsh.name/send-text-message

I hope this helped you out a bit

Jtvd78 is offline  
post #3 of 3 (permalink) Old 01-13-2017, 06:40 AM
New to Overclock.net
 
Mrzev's Avatar
 
Join Date: Feb 2008
Location: Texas
Posts: 2,258
Rep: 96 (Unique: 76)
Quote:
Originally Posted by Jtvd78 View Post

Depending on how many people are accessing the page at once, you may want to so some caching of the SQL query results. Refreshing the page is a just a bit of javascript.
Emails are pretty simple, as PHP has a built in function to send mail, and sending mail with node.js is nearly as simple. I have no experience with text messages, but you might want to take a look at this page: https://davidwalsh.name/send-text-message

Emails can be a pain. The main issue i have with doing Emails is figuring out which server and port to target and how to pass the credentials to send the email. Like for me to send an email from my gmail account, the first time i did this I hardcoded my username and password which is BAD to do (was for a quick test project). The second time i think I was able to generate an API key from them and use it, but then I ran into issues with something else. I also ran into issues where emails that were being sent were getting flaged as spam for whatever reason.

But, I do agree, this is a pretty simple straight forward application. Setting up PHP with Apache is sometimes easy, and sometimes a pain... i never know why. Just to point out, you have 2 componets to this. You have your webpage that reads data from a DB, and an application that checks the DB for alerts every 5 minutes. The webpage will need to be a server side scripting language like PHP\Java\ASP\Node.JS so you can get information from the webpage. The app that reads every 5 minutes can be a simple Python script, C++ app, C# app, really just about anything. You then need to tie a scheduler to that application, or perhaps make it a service that is always on.

To take it another step, you add a watchdog app where its only job is to make sure that application is always running, or start it back up if it goes down for whatever reason.



Mrzev is offline  
Reply

Quick Reply
Message:
Options

Register Now

In order to be able to post messages on the Overclock.net - An Overclocking Community forums, you must first register.
Please enter your desired user name, your email address and other required details in the form below.
User Name:
If you do not want to register, fill this field only and the name will be used as user name for your post.
Password
Please enter a password for your user account. Note that passwords are case-sensitive.
Password:
Confirm Password:
Email Address
Please enter a valid email address for yourself.
Email Address:

Log-in



Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Show Printable Version Show Printable Version
Email this Page Email this Page


Forum Jump: 

Posting Rules  
You may post new threads
You may post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off