drew.d.lenhart

programming, software, technology, anything on my mind...

Monitor Your Servers with Powershell & More – Part 3 - Dashboard

2015/08/06

In the last two articles I showed how to create a PowerShell script to ping a list of servers, e-mail a failure, and automate the script via Windows Task Scheduler. See part 1 and part 2 before moving on.

This article is just a bit of fun. Having e-mail alerts is a fine notification but I want to have a auto refreshing dashboard that shows pass/fail!

Heres what I created:

finalprod

Modifications

The dashboard will consist of the following components:

HTML/PHP/AJAX JQuery Bootstrap & MySQL database.

The dashboard can be hosted anywhere that supports PHP. For my case, I have the PowerShell script running on a Windows 7 box and the dashboard/database on a Linux box.

I modified the PowerShell script by adding a function called "run_api()". Heres the latest PowerShell script:

## Desc: Simple Test-Connection Script - pings hosts in servers.txt file - stUpDown
## Auth: Drew D. Lenhart
## http://www.drewlenhart.com
## 08/04/15

##Declare file##
$ServerListFile = "servers.txt"   
$ServerList = Get-Content $ServerListFile -ErrorAction SilentlyContinue  
$LogTime = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
$Outputreport = ""

##EMAIL function##

function sendMail($datestamp){
    ##Use GMAILs free smtp services, must use port 587
    $smtpServer = "smtp.gmail.com"
    $srvPort = 587
    $smtpFrom = "youremail@gmail.com"
    $smtpTo = "to email address"
    $messageSubject = "Ping Script Results - $datestamp"
    $message = New-Object System.Net.Mail.MailMessage $smtpfrom, $smtpto
    $message.Subject = $messageSubject
    $message.IsBodyHTML = $true
    $message.Body = "<html><head></head><body>"
    $message.Body += Get-Content Logsupdown.htm
    $message.Body += "</body></html>"
    $smtp = New-Object Net.Mail.SmtpClient($smtpServer, $srvPort)
    $smtp.EnableSsl = $true
    $smtp.Credentials = New-Object System.Net.NetworkCredential("gmail username without @gmail.com", "gmail password")
    ##Send message
    $smtp.Send($message)
}

##Update Database through API

function run_api($server, $stat, $time){
    $curl = curl --data "server=$server&status=$stat&time=$time" http://localhost/api/
    ##Dont forget to change the url to match your configuration!
}

##Iterate through each hostname in servers.txt#

$failCount = 0

$Outputreport = "<h3>Ping Stats:</h3>"
$Outputreport += "<table><tr style='background-color: grey; color: white'><td>Hostname</td><td>Status</td></tr>"

foreach ($Server in $ServerList) {
    if (test-Connection -ComputerName $Server -Count 2 -Quiet ) {  
        #show some status in console
        echo "$Server is pingable"
        run_api $server "yes" $LogTime
        $Outputreport += "<tr><td>$Server</td><td style='background-color: green; color: white'>ONLINE</td></tr>"
    } else {
        #show some status in console
        echo "$Server seems dead Jim"
        run_api $server "no" $LogTime
        $Outputreport += "<tr><td>$Server</td><td style='background-color: red'>OFFLINE</td></tr>"
        $failCount = $failCount + 1
    }     
}

$Outputreport += "</table>"
##Save results to .html file for e-mail
$Outputreport | out-file Logsupdown.htm

##If the counter is greater than 1, send failure email

if ($failCount -ge 1) {
    sendMail "$LogTime"
    }

So whats "run_api()" doing here? Basically I created a small API for the PowerShell script to do a POST and update data in the MySQL database. So on the Windows box thats running the PS scripts cURL will need to be installed. I'll get more into the API later. Normally I would create a connector in the PS script to a database and do whatever. But I did not have very good luck connecting to a MySQL database ( I have better luck with a MSSQL DB and PowerShell ). Also I kinda wanted to explore what its like creating an mini API.

**Be sure to install cURL on the same box running your PS scripts. DON'T forget to add cURL to your PATH variables!


Database

Included in the demo code is the .sql file I exported from my project. It has some mock data in it. It also has a list of IP/Server names that match up with the "servers.txt" file included with the PowerShell script. For right now the list of servers in the .txt file must match up with whats in the database.

Use phpMyAdmin to create a new database. Then import the table. Also dont forget to set up a db user.

When this is created update; includes/database.class.php with the database name, username, and password.


Dashboard

Here are the important pieces. I'm not going over how to connect to a database, etc. I borrowed my database class from a previous article for this project.

index.php


<?php
/*
    Author:  Drew D. Lenhart
    http://www.drewlenhart.com
    Page: index.php
    Desc: Main view screen. Displays server status.
    */
include("includes/header.php");
include("includes/navbar.php"); ?>

<article>
<h1>Server Status</h1>
<br />
<div class="container-fluid">

<div id="server"></div>
</div>
</article>
<!--Ajax file-->
<script src="resources/js/status_refresh.js" type="text/javascript"></script>
<?php include("includes/footer.php"); ?>

There is really not much to this page, other than it includes the DIV box that the AJAX renders the refreshing content in.


resources/js/status_refresh.js

/*
 * Server Status ajax file
 * d.lenhart
 * 8/01/2015
 *
 */

(function($){
    $(document).ready(function()
    {
        $.ajaxSetup(
        {
            cache: false,
            beforeSend: function() {
                //$('#server').hide().fadeOut("fast");
                //$('#loading').show();
            },
            complete: function() {
                //$('#loading').hide();
                $('#server').show().fadeIn("slow");
            },
            success: function() {
                //$('#loading').hide();
                $('#server').show().fadeIn("slow");
            }
        });
        var $container = $("#server");
        $container.load("php/get_server.php").fadeIn("slow");
        var refresh = setInterval(function()
        {
            $container.load('php/get_server.php').fadeIn("slow");
        }, 10000);
    });
})(jQuery);

Here is the AJAX. I've reused this script many times for things. For the life of me I can't remember who's tutorial I got this from, if I find it, I will give credit. I have some options in the before, complete, success commented out, sometimes I use the fade in functions for things.

Also notice the setInterval() function. This is doing the refreshing. The 10000 is milli-seconds. So every 10 seconds, the file "php/get_server.php" is being called and displayed into the DIV box #server on index.php!

The advantage of refreshing the content in the DIV box is that the whole page does NOT refresh!


php/get_server.php


<?php
/*
    Author:  Drew D. Lenhart
    http://www.drewlenhart.com
    Date: 8/01/2015
    Page: get_server.php
    Desc: Queries database for server status, executed by status_refresh.js ajax.
    */
require_once('../includes/database.class.php');
$db = database::getInstance();?>

<div class="row" style="text-align: center">
    <?php
    $sql = 'SELECT * FROM srvr_status';
    $rows = $db->getData($sql);
    foreach ($rows as $row) :
        $entryID = $row['s_ID'];
        $server = $row['s_server'];
        $stat = $row['s_status'];
        $date = $row['s_date'];
        if($stat == "no"){
            //no = offline
            $out = "boxStatBad";
            $status = "Offline";
        }elseif($stat == "yes"){
            //yes = online
            $out = "boxStatGood";
            $status = "Online";
        }else{
            //else other than 1 or 0 make unknown.
            $out = "boxStatBad";
            $status = "Unknown Status";
        }
        echo "<div class='col-xs-6 col-sm-3' style='margin-bottom: 4%'><div class='boxC'><div class='boxTitle'>" .  $server . "</div><div class='" . $out . "'>" . $status . "<br />". $date . "</div></div></div>";
    endforeach;
    ?>
</div>
<?php $db->closeConn() ?>

This file is checking the database for servers and displaying their status accordingly.


Poor Man's API

I call this my poor man's API for a reason. I'm pretty sure this is not how you create one. Basically this file only accepts POST:

<?php
/*
    Author:  Drew D. Lenhart
    http://www.drewlenhart.com
    Date: 8/01/2015
    Page: api/index.php
    Desc: poor mans api for Updating server status:  http://localhost/api/index.php?server=192.168.1.2&status=true
    -Uses post
    */

require_once('../includes/database.class.php');
include('../includes/validator.php');
$db = database::getInstance();

if ($_SERVER["REQUEST_METHOD"] == "POST") {
    if(!empty($_POST['status']) && !empty($_POST['server']) && !empty($_POST['time'])) {
        $grab_server = validator($_POST['server']);
        $grab_stat = validator($_POST['status']);
        $grab_time = validator($_POST['time']);

        $data = array($grab_stat, $grab_time, $grab_server);

        $sql="UPDATE srvr_status SET s_status=?, s_date=? WHERE s_server=?";
        $result = $db->updateData($sql, $data);
        if(!$result){
            die("Database query failed: update server info.");
        }else{
             $answer = "success!";
             echo $answer;
        }
    }
    else {
        echo "Invalid Parameters!";
    }
}
?>

I'm still exploring in creating a PHP web service or API. Typically for a web service when you make a request, you get a response back in the form of a 200 for good, or 500 for bad. This may be a topic for a later post. It works for now.

If you set up this code on a server, you can test the API by using cURL in a terminal:

curl --data "server=192.168.1.12&status=no&time=2015-08-06" http://localhost:8888/stUpDownP3/api/


TODO

Some things to work on later:

  1. The servers in the "servers.txt" file must match up with what you enter in the database. This could get annoying over time. Should be ONE location for servers.

  2. Have some input in the Dashboard to add new servers, create/update/delete.

Thanks for reading!! -Drew

Download Code Github