Kaseya Community

PHP Based NOC Dashboard with Sourcecode

  • Hi,

    ok pb solved, where should go the files from the NOC_zip

    rgds

  • Hi,

    ok,, modified the dblogin.php

    but nothing happening when entering index.php in the browser

    rgds

  • and finally here's is the errmsg

    Fatal error: Call to undefined function sqlsrv_connect() in C:\xampp\htdocs\kaseyaNOC\dblogin.php on line 7

  • forumpost-loadtest5_php.txt

    This is very cool and thank you to others for giving me the inspiration to also do such.

    I spent essentially a day working on the darn PHP to MS SQL issue.

    Having CentOS (Linux) based webservers, I wanted a Linux based code foundation.

    Hours googling, reading, trying different things, etc. etc.  to get php to mssql
    query's working.  I wish I had documented every step I took and thing that I did.

    I can tell you, that I did install FreeTDS, enable full logging, re-configured
    the MS SQL server's listening ports, changed/specificed the port to use with MS SQL
    php (it was defaulting to a port 4000 rather than 1443, and I forget why, but
    did have alot of communication issues, including port, name to ip resolution, etc.

    We're working on integrating between Kaseya, the custom NOC, Quickbooks, and SugarCRM
    (our own custom ConnectWise/Autotask equivalent).

    I want click through funcationality from the NOC to Kaseya, and as you can see from
    the screenshot, next to each machine is a small icon of which if clicked on pop's up
    the Kaseya agent/lc screen giving instant access to alot.

    Though I spent a good hour trying to track down the html/js code that did the popup
    window when hovering over the circle icons in kaseya, but gave up, just too damn
    much work, identifying and separating that code to re-use.

    I ended up trying about a half dozen different popup window solutions, and found
    colorbox to be my preference and nicest.   So the underlying code for these popups
    is Colorbox.


    On the topic of installing Mssql php extensions on CentOS (linux),
    it was hours of fighting and reading, and googling, before discovering on the 101st
    page I read, that on CentOS, i could install php mssql extension with YUM!!

    One simple darn line, and it automatically installs mssql and freetds dependancies.

    That sure beat the time I spent fighting with compiling php, freetds, mssql, and still
    after failing to get things working, just ended up trying the yum install which worked fine.

    I used detailed logging and tsql for diagnosis and trying to resolve connection issues.

    I've attached SQL code for Linux.  The code I've attached also includes the colorbox popup
    as seen in the screenshot.

    Chris,

  • Sharing for others...
    I just wrote myself, the below code to convert the Kaseya db raw data into
    usable hard drive percentages or gb amounts. Very simple code, but might be of use to others.
    -Chris ,
    // Calculate the percentage of HD space used 
    // UsedSpace / TotalSpace
    $UsedSpacePercentage = $row["UsedSpace"] / $row["TotalSpace"];
    $UsedSpacePercentage = round((float)$UsedSpacePercentage * 100 );
    $UsedSpacePercentageNumber = $UsedSpacePercentage;
    
    $FreeSpacePercentage = 100 - $UsedSpacePercentage;
    $FreeSpacePercentageNumber = $FreeSpacePercentage;
    
    $FreeSpacePercentage = $FreeSpacePercentage . '%'; 
    $UsedSpacePercentage = $UsedSpacePercentage . '%';
    // End of Calculate the percentage of HD space used
    
    
    // convert the freespace variable into GB from bytes
    if ($row["FreeSpace"] > 1000) {
    $row["FreeSpace"] = $row["FreeSpace"] / 1000;
    $row["FreeSpace"] = number_format($row["FreeSpace"]);
    $row["FreeSpace"] = $row["FreeSpace"] . " <font size=\"-1\">GB</font>";
    } 
    // end of convert the freespace variable into GB from bytes
    
    // convert the freespace variable into GB from bytes
    if ($row["TotalSpace"] > 1000) {
    $row["TotalSpace"] = $row["TotalSpace"] / 1000;
    $row["TotalSpace"] = number_format($row["TotalSpace"]);
    $row["TotalSpace"] = $row["TotalSpace"] . " <font size=\"-1\">GB</font>";
    } 
    // end of convert the freespace variable into GB from bytes
    
    // convert the freespace variable into GB from bytes
    if ($row["UsedSpace"] > 1000) {
    $row["UsedSpace"] = $row["UsedSpace"] / 1000;
    $row["UsedSpace"] = number_format($row["UsedSpace"]);
    $row["UsedSpace"] = $row["UsedSpace"] . " <font size=\"-1\">GB</font>";
    } 
    // end of convert the freespace variable into GB from bytes
    
    // Only display this row if the hard disk has less than xx% free, of which xx% 
    //is a variable which is user selectable, 10%, 15%, 20%, etc.
    $PercentFreeFilter = "10" ;
    
    if ($FreeSpacePercentageNumber < $PercentFreeFilter ) { 
    echo "<font size=\"-1\">";
    echo "<tr>";                   
    
    echo "<td class=\"colL\">" . "<a class='k2popupiframe' href=\"http://k2/Toolbox/agentDashFrame.asp?agentGuid=" . $row["agentGuid"] . "\">" . "<img src=\"http://192.168.1.250/noc/images/computericon.jpg\"  width=\"20\" height=\"20\"></a>" . "</td>";
    
    $row["machName"] = substr($row["machName"], 0, 20); 
    
    echo "<td class=\"colL\"><font size=\"-1\"><FONT COLOR=\"FF0000\">" . $row["machName"] . "</font></td><td class=\"colM\"><font size=\"-1\">" . $row["FreeSpace"] . "</font></td><td class=\"colM\"><font size=\"-1\">" . $row["DriveLetter"] . ":\</font></td><td class=\"colM\"><font size=\"-1\">" . $row["TotalSpace"] . "</font></td><td class=\"colM\"><font size=\"-1\">" . $row["UsedSpace"] . "</font></td><td><font size=\"-1\">" . $UsedSpacePercentage . "</font></td><td><font size=\"-1\">" . $FreeSpacePercentage . "</font></td></tr>";   
    }  // end of the FreeSpacePercentageNumber IF statement
  • Nice one Chris! I developed a slightly different SQL query for 'low disk' issues - one that processes BOTH by % free and absolute bytes free. My code helps deal with things like very large and very small disks much better, since EITHER or BOTH conditions trigger alerts. Without this, things like very small flash drives and very large disks (Terabyte sized) don't tend to trigger the warnings you need a the right times.

    The variable 'rank' is used to rate each machine by severity, where rank 1 = most urgent, down to rank  7 (no issues). you can adjust the thresholds yourself as you see fit.

    select machName, groupName, DriveLetter, TotalSpace, UsedSpace, FreeSpace,

    (cast(freespace as float)/cast(totalspace as float))*100 as percFree,

    rank = case

      when FreeSpace < 1024 and (cast(freespace as float)/cast(totalspace as float))*100 < 10 then 1

      when FreeSpace < 1024 or (cast(freespace as float)/cast(totalspace as float))*100 < 10 then 2

      when FreeSpace < 10240 and (cast(freespace as float)/cast(totalspace as float))*100 < 15 then 3

      when FreeSpace < 10240 or (cast(freespace as float)/cast(totalspace as float))*100 < 15 then 4

      when FreeSpace < 20480 and (cast(freespace as float)/cast(totalspace as float))*100 < 20 then 5

      when FreeSpace < 20480 or (cast(freespace as float)/cast(totalspace as float))*100 < 20 then 6

      else 7

    end

    from dbo.vCurrDiskInfo

    join vdb_Scopes_Machines foo on foo.agentGuid = dbo.vCurrDiskInfo.agentGuid

    where DriveType = 'Fixed' and TotalSpace >0 and case

      when FreeSpace < 1024 and (cast(freespace as float)/cast(totalspace as float))*100 < 10 then 1

      when FreeSpace < 1024 or (cast(freespace as float)/cast(totalspace as float))*100 < 10 then 2

      when FreeSpace < 10240 and (cast(freespace as float)/cast(totalspace as float))*100 < 15 then 3

      when FreeSpace < 10240 or (cast(freespace as float)/cast(totalspace as float))*100 < 15 then 4

      when FreeSpace < 20480 and (cast(freespace as float)/cast(totalspace as float))*100 < 20 then 5

      when FreeSpace < 20480 or (cast(freespace as float)/cast(totalspace as float))*100 < 20 then 6

      else 7

     end <7

     order by rank, FreeSpace

    and the PHP Code:

    <?php

    function formatBytes($size, $precision = 2)

    {

       $base = log($size) / log(1024);

       $suffixes = array('b', ' kB', ' MB', ' GB', ' TB');  

       return round(pow(1024, $base - floor($base)), $precision) . $suffixes[floor($base)];

    }

    $pageContent = null;

    ob_start();

    include 'dblogin.php';

    $tsql = "select machName, groupName, DriveLetter, TotalSpace, UsedSpace, FreeSpace,

    (cast(freespace as float)/cast(totalspace as float))*100 as percFree,

    rank = case

      when FreeSpace < 1024*10 and (cast(freespace as float)/cast(totalspace as float))*100 < 10 then 1

      when FreeSpace < 1024*10 or (cast(freespace as float)/cast(totalspace as float))*100 < 10 then 2

      when FreeSpace < 1024*15 and (cast(freespace as float)/cast(totalspace as float))*100 < 15 then 3

      when FreeSpace < 1024*15 or (cast(freespace as float)/cast(totalspace as float))*100 < 15 then 4

      else 5

    end

    from dbo.vCurrDiskInfo

    join vdb_Scopes_Machines foo on foo.agentGuid = dbo.vCurrDiskInfo.agentGuid

    where DriveType = 'Fixed' and TotalSpace >0

    and case

      when FreeSpace < 1024*10 and (cast(freespace as float)/cast(totalspace as float))*100 < 10 then 1

      when FreeSpace < 1024*10 or (cast(freespace as float)/cast(totalspace as float))*100 < 10 then 2

      when FreeSpace < 1024*15 and (cast(freespace as float)/cast(totalspace as float))*100 < 15 then 3

      when FreeSpace < 1024*15 or (cast(freespace as float)/cast(totalspace as float))*100 < 15 then 4

      else 5

     end <5

     order by rank, FreeSpace";

    $stmt = sqlsrv_query( $conn, $tsql);

    if( $stmt === false )

    {

        echo "Error in executing query.</br>";

        die( print_r( sqlsrv_errors(), true));

    }

    echo "<div class=\"heading\">";

    Echo "Agents with Low Disk Space</br>";

    echo "</div>";

    echo "<table id=\"lowdisklist\">";

    echo "<tr><th class=\"colL\">Machine Name</th><th class=\"colM\">Drive</th><th class=\"colR\">Free Space</th><th class=\"colR\">Total Space</th><th class=\"colM\">Free Space %</th><th class=\"colM\">Urgency</th></tr>";

    while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC))

    {

     echo "<tr><td class=\"colL\">".$row['machName'].".".$row['groupName']."</td><td class=\"colM\">".$row['DriveLetter'].":</td><td class=\"colR\">";

     if ($row['FreeSpace'] < 1024*15) {

        if ($row['FreeSpace'] < 1024*10) {

           echo "<font color=red>";

        } else {

           echo "<font color=orange>";

        }

        echo formatBytes($row['FreeSpace']*1024*1024)."</font></td>";

     } else { echo formatBytes($row['FreeSpace']*1024*1024)."</td>"; }

     echo "<td class=\"colR\">".formatBytes($row['TotalSpace']*1024*1024)."</td><td class=\"colM\">";

     $perc = round(($row['percFree']),2);

     if ($perc < 15) {

        if ($perc < 10) {

           echo "<font color=red>";

        } else {

           echo "<font color=orange>";

        }

        echo $perc."%</font></td>";

     } else { echo $perc."%</td>"; }

    echo "<td class=\"colM\">".$row['rank']."</td></tr>";

    }

    echo "</table>";

    $pageContent = ob_get_contents(); // collect above content and store in variable

    ob_end_clean();

    echo $pageContent;

    ?>

  • Hi Graig,

    this is what i'm getting :

    Microsoft Windows [Version 6.1.7601]

    Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

    C:\Users\RDK.WASLET>cd..

    C:\Users>cd..

    C:\>cd xampp

    C:\xampp>cd php

    C:\xampp\php>php -v

    Warning: PHP Startup: sqlsrv: Unable to initialize module

    Module compiled with module API=20090626

    PHP    compiled with module API=20100525

    These options need to match

    in Unknown on line 0

    Warning: PHP Startup: Unable to load dynamic library 'c:\xampp\php\ext\php_sqlsr

    v_53_nts_vc9.dll' - The specified module could not be found.

    in Unknown on line 0

    PHP 5.4.7 (cli) (built: Sep 12 2012 23:48:31)

    Copyright (c) 1997-2012 The PHP Group

    Zend Engine v2.4.0, Copyright (c) 1998-2012 Zend Technologies

    C:\xampp\php>

  • rdk, this error tells me you haven't set up the Microsoft SQL drivers for PHP properly.  Specifically, you don't have php_sqlsrv_53_nts_vc9.dll present in c:\xampp\php\ext, or the file has the wrong permissions (Set it to inherit permissions from the parent folder).

    Personally, I'm using the extension=php_sqlsrv_53_ts.dll and that is what works for me.

  • Hi Graig

    just got it up.....

    rgds

  • Hi Graig,

    seems nice indeed, just did a test with your freespace post.

    the subwindows should be scrollable because i got à list of aboot 100 pc's ( C, D, E drives )

    rgds

  • Wow! Been a long time since I checked in here but I come back for a quick look and I find this jewel. Very nice job, Craig, and thank you for posting this. I threw it on an IIS machine with PHP installed and it works a champ. You're a genius.

  • @RDK: you can modify the CSS if you want scrollable windows....it's all standard CSS styling on DIVs, so you can do whatever you like.

    to scroll the low disk screen, change the mytech.css as follows:

    #lowdisk

    {

    float:left;

    border:1px solid gray;

    height:200px;

    overflow:auto;

    padding:2px;

    margin:2px;

    }

  • Thanks Craig for this post.  I got everything workin on my laptop, but I can't get the page to refresh.  Actually I can't get the data to refresh.  Any suggestions?

    Thanks

    Kerry

  • Are you sure the data isn't refreshing? I'm using some clever scripting that stops the "screen from flashing" the way browsers did 10 years ago, so if you're just expecting to see the screen redraw, well you won't.

    It does require javascript to be enabled in your browser, however, for the above to work. Have tested in Chrome, Firefox and IE 9, 10. Pretty sure it won't work in IE6 however.....

  • I couldn't get the screen to refresh in IE 10 until I changed the following line in the "index.php" file from:

    setTimeout('refresh();',5000);

    to:

    setTimeout("location.reload(true);", '5000');