Location: PHPKode > projects > Network Asset Management Archive > nama/liaso/summarylab.php
<?php
include "../connect.php";
print '<body bgcolor="#F9F3E0">';
$now = date("j/m/y h:i:s",time());
$LAB = $_GET['lab'];

$headers  = "MIME-Version: 1.0\r\n";
$headers .= "Content-type: text/html; charset=iso-8859-1\r\n";
$headers .= "From: " . $EMAIL;

$tablerow = "style='border-color:#DBB748; border-width:1px; border-right: 2px solid #DBB748; border-collapse: collapse; border-style:solid;'";
$empty=0;
//$stmt = "select distinct o.display_name, o.phone, o.email_address, o2.email_address as liaso, a.ip_address, a.netbios_name, a.dns_name, a.building, a.room, a.barcode, a.last_seen, a.md5, a.os from arp_table a, owner o, owner o2, lab l, subnets s where (o2.serial=l.liaso) and (l.serial=s.lab) and (a.subnet=s.subnet) and (o.serial=a.iaso) and (l.serial = " . $LAB . ") and ((owner is null) or (building is null) or (room is null) or (barcode is null) or (owner is null) or (os is null)) and (ip_address <> '0.0.0.0') order by o.display_name, a.ipnum";
$stmt = "select a.* from arp_table a, subnets s where (a.subnet=s.subnet) and (s.lab = " . $LAB . ") and ((owner is null) or (building is null) or (room is null) or (barcode is null) or (owner is null) or (os is null)) and (ip_address <> '0.0.0.0') order by a.iaso, a.ipnum";
//echo $stmt;
$sth = ibase_query($dbh, $stmt);
$i = -1;
$first = 1;
$mymsg = "";
$myhtml = '<html><body text="#000000" bgcolor="#F9F3E0" link="#0000EE" vlink="#551A8B" alink="#FF0000">';
$ip="";
$i = -1;
while ($row = ibase_fetch_object($sth)) {
    if ($i==-1) {
        $empty=1;
        $mymsg .= '<H1>Red Machines</H1><br><table style="border-color:#DBB748; border-width:3px; border-right: 3px solid #DBB748; border-collapse: collapse; border-style:solid;" width="700" border="2">';
        $mymsg .= "<tr BGCOLOR='#DBB748' ALIGN=left><th " . $tablerow . ">IASO</th><th " . $tablerow . ">IP</th><th " . $tablerow . ">Name</th><th " . $tablerow . ">Building</th><th " . $tablerow . ">Room</th><th " . $tablerow . ">Bar Code</th><th " . $tablerow . ">OS</th><th " . $tablerow . ">Last Seen</th></tr>";
    }
    $mymsg .= '<input type="hidden" name="serial[]" value="' . $row->SERIAL . '">';
    if ($i%2) {
        $mymsg .= "<tr BGCOLOR='#F5EACB'>";
        } else {
        $mymsg .= "<tr>";
    }
    list ($lastseendate, $lastseentime) = split(" ", $row->LAST_SEEN);
    
    $i = $i + 1;
    if ($i%2) {
        $mymsg .= "<tr BGCOLOR='#F5EACB'>";
        } else {
        $mymsg .= "<tr>";
    }
    if ($row->IASO!=0)
       {$stmtiaso="select O3.DISPLAY_NAME, O3.PHONE from OWNER O3 where (o3.serial = '$row->IASO');";
       //echo $stmtiaso . '<br>';
       $sthiaso = ibase_query($dbh, $stmtiaso);
       $rowiaso = ibase_fetch_object($sthiaso);
       $LIASO=$rowiaso->DISPLAY_NAME;
       $LIASO_PHONE=$rowiaso->PHONE;
       $mymsg .= '<td ' . $tablerow . '>' . $rowiaso->DISPLAY_NAME . '</td>';
    } else {
       $mymsg .= '<td ' . $tablerow . '><Select name="IASO[]">';
       $stmt2 = "SELECT  O.DISPLAY_NAME, O.SERIAL FROM OWNER o, lab l where (o.lab=l.lab) and (l.serial=$LAB) order by display_name";
       $sth2 = ibase_query($dbh, $stmt2);
       $mymsg .= '<option value="999999">Not Assigned</option>';
       while ($row2 = ibase_fetch_object($sth2)) {
             $mymsg .= '<option value="' . $row2->SERIAL . '"';
             if ($row2->SERIAL==$row->IASO) {
                 $mymsg .=  "selected "; 
             }
             $mymsg .= '>' . $row2->DISPLAY_NAME . '</option>';
       }
       $mymsg .= ' </select></td>';
    }
    $mymsg .= '<td ' . $tablerow . 'width="15%"><a href="/right.php?SERIAL=' . $row->MD5 . '" target = "_blank">' . $row->IP_ADDRESS . '</a></td>';
    if ($row->NETBIOS_NAME!='') {
        $mymsg .= '<td ' . $tablerow . '>' . $row->NETBIOS_NAME . '</td>';
        } else {
        $mymsg .= '<td ' . $tablerow . '>' . $row->DNS_NAME . '</td>';
    }
    $mymsg .= '<td ' . $tablerow . '>' . $row->BUILDING . '</td>';
    $mymsg .= '<td ' . $tablerow . '>' . $row->ROOM . '</td>';
    $mymsg .= '<td ' . $tablerow . '>' . $row->BARCODE . '</td>';
    $mymsg .= '<td ' . $tablerow . '>' . $row->OS . '</td>';
    $mymsg .= '<td ' . $tablerow . '>' . $lastseendate . '</td>';
    
    $mymsg .= '</tr>';
    $ip=$row->IP_ADDRESS;
}
$mymsg .= '</table>';

$stmt = "select a.* from arp_table a, subnets s where (a.subnet=s.subnet) and (s.lab = " . $LAB . ") and (OS starts with 'Win') and (update_server is null) and (laptop is null) and (ip_address <> '0.0.0.0') order by a.iaso, a.ipnum";
//echo $stmt;
$sth = ibase_query($dbh, $stmt);
$i = -1;
while ($row = ibase_fetch_object($sth)) {
    if ($i==-1) {
        $empty=1;
        $mymsg .= '<H1>Not on Update Server</H1><br><table style="border-color:#DBB748; border-width:3px; border-right: 3px solid #DBB748; border-collapse: collapse; border-style:solid;" width="800" border="2">';
        $mymsg .= "<tr BGCOLOR='#DBB748' ALIGN=left><th " . $tablerow . ">IASO</th><th " . $tablerow . ">IP</th><th " . $tablerow . ">Name</th><th " . $tablerow . ">Building</th><th " . $tablerow . ">Room</th><th " . $tablerow . ">Bar Code</th><th " . $tablerow . ">OS</th><th " . $tablerow . ">Last Seen</th></tr>";
    }
    $mymsg .= '<input type="hidden" name="serial[]" value="' . $row->SERIAL . '">';
    if ($i%2) {
        $mymsg .= "<tr BGCOLOR='#F5EACB'>";
        } else {
        $mymsg .= "<tr>";
    }
    list ($lastseendate, $lastseentime) = split(" ", $row->LAST_SEEN);

    $i = $i + 1;
    if ($i%2) {
        $mymsg .= "<tr BGCOLOR='#F5EACB'>";
        } else {
        $mymsg .= "<tr>";
    }
    if ($row->IASO!=0)
       {$stmtiaso="select O3.DISPLAY_NAME, O3.PHONE from OWNER O3 where (o3.serial = '$row->IASO');";
       //echo $stmtiaso . '<br>';
       $sthiaso = ibase_query($dbh, $stmtiaso);
       $rowiaso = ibase_fetch_object($sthiaso);
       $LIASO=$rowiaso->DISPLAY_NAME;
       $LIASO_PHONE=$rowiaso->PHONE;
       $mymsg .= '<td ' . $tablerow . '>' . $rowiaso->DISPLAY_NAME . '</td>';
    } else {
       $mymsg .= '<td ' . $tablerow . '>&nbsp</td>';
    }
    $mymsg .= '<td ' . $tablerow . 'width="15%"><a href="/right.php?SERIAL=' . $row->MD5 . '" target = "_blank">' . $row->IP_ADDRESS . '</a></td>';
    if ($row->NETBIOS_NAME!='') {
        $mymsg .= '<td ' . $tablerow . '>' . $row->NETBIOS_NAME . '</td>';
        } else {
        $mymsg .= '<td ' . $tablerow . '>' . $row->DNS_NAME . '</td>';
    }
    $mymsg .= '<td ' . $tablerow . '>' . $row->BUILDING . '</td>';
    $mymsg .= '<td ' . $tablerow . '>' . $row->ROOM . '</td>';
    $mymsg .= '<td ' . $tablerow . '>' . $row->BARCODE . '</td>';
    $mymsg .= '<td ' . $tablerow . '>' . $row->OS . '</td>';
    $mymsg .= '<td ' . $tablerow . '>' . $lastseendate . '</td>';

    $mymsg .= '</tr>';
    $ip=$row->IP_ADDRESS;
}
$mymsg .= '</table>';

$stmt = "select a.* from arp_table a, subnets s where (a.subnet=s.subnet) and (s.lab = " . $LAB . ") and (OS starts with 'Win') and (av_server is null) and (ip_address <> '0.0.0.0') and (laptop is null) order by a.iaso, a.ipnum";
//echo $stmt;
$sth = ibase_query($dbh, $stmt);
$i = -1;
while ($row = ibase_fetch_object($sth)) {
    if ($i==-1) {
        $empty=1;
        $mymsg .= '<H1>Not on Antivirus Server</H1><br><table style="border-color:#DBB748; border-width:3px; border-right: 3px solid #DBB748; border-collapse: collapse; border-style:solid;" width="800" border="2">';
        $mymsg .= "<tr BGCOLOR='#DBB748' ALIGN=left><th " . $tablerow . ">IASO</th><th " . $tablerow . ">IP</th><th " . $tablerow . ">Name</th><th " . $tablerow . ">Building</th><th " . $tablerow . ">Room</th><th " . $tablerow . ">Bar Code</th><th " . $tablerow . ">OS</th><th " . $tablerow . ">Last Seen</th></tr>";
    }
    $mymsg .= '<input type="hidden" name="serial[]" value="' . $row->SERIAL . '">';
    if ($i%2) {
        $mymsg .= "<tr BGCOLOR='#F5EACB'>";
        } else {
        $mymsg .= "<tr>";
    }
    list ($lastseendate, $lastseentime) = split(" ", $row->LAST_SEEN);

    $i = $i + 1;
    if ($i%2) {
        $mymsg .= "<tr BGCOLOR='#F5EACB'>";
        } else {
        $mymsg .= "<tr>";
    }
    if ($row->IASO!=0)
       {$stmtiaso="select O3.DISPLAY_NAME, O3.PHONE from OWNER O3 where (o3.serial = '$row->IASO');";
       //echo $stmtiaso . '<br>';
       $sthiaso = ibase_query($dbh, $stmtiaso);
       $rowiaso = ibase_fetch_object($sthiaso);
       $LIASO=$rowiaso->DISPLAY_NAME;
       $LIASO_PHONE=$rowiaso->PHONE;
       $mymsg .= '<td ' . $tablerow . '>' . $rowiaso->DISPLAY_NAME . '</td>';
    } else {
       $mymsg .= '<td ' . $tablerow . '>&nbsp</td>';
    }
    $mymsg .= '<td ' . $tablerow . 'width="15%"><a href="/right.php?SERIAL=' . $row->MD5 . '" target = "_blank">' . $row->IP_ADDRESS . '</a></td>';
    if ($row->NETBIOS_NAME!='') {
        $mymsg .= '<td ' . $tablerow . '>' . $row->NETBIOS_NAME . '</td>';
        } else {
        $mymsg .= '<td ' . $tablerow . '>' . $row->DNS_NAME . '</td>';
    }
    $mymsg .= '<td ' . $tablerow . '>' . $row->BUILDING . '</td>';
    $mymsg .= '<td ' . $tablerow . '>' . $row->ROOM . '</td>';
    $mymsg .= '<td ' . $tablerow . '>' . $row->BARCODE . '</td>';
    $mymsg .= '<td ' . $tablerow . '>' . $row->OS . '</td>';
    $mymsg .= '<td ' . $tablerow . '>' . $lastseendate . '</td>';

    $mymsg .= '</tr>';
    $ip=$row->IP_ADDRESS;
}
$mymsg .= '</table>';

$stmtav = "select max(virusdefdate) from arp_table";
$sthav = ibase_query($dbh, $stmtav);
$rowav = ibase_fetch_object($sthav);
$ANTIVIRUS=$rowav->MAX;

$stmt = "select a.* from arp_table a, subnets s where (a.subnet=s.subnet) and (s.lab = " . $LAB . ") and (OS starts with 'Win') and (av_server is not null) and (virusdefdate<>'$ANTIVIRUS') and (ip_address <> '0.0.0.0') and (laptop is null) order by a.iaso, a.ipnum";
//echo $stmt;
$sth = ibase_query($dbh, $stmt);
$i = -1;
while ($row = ibase_fetch_object($sth)) {
    if ($i==-1) {
        $empty=1;
        $mymsg .= '<H1>Antivirus Out of Date</H1><br><table style="border-color:#DBB748; border-width:3px; border-right: 3px solid #DBB748; border-collapse: collapse; border-style:solid;" width="800" border="2">';
        $mymsg .= "<tr BGCOLOR='#DBB748' ALIGN=left><th " . $tablerow . ">IASO</th><th " . $tablerow . ">IP</th><th " . $tablerow . ">Name</th><th " . $tablerow . ">Building</th><th " . $tablerow . ">Room</th><th " . $tablerow . ">Bar Code</th><th " . $tablerow . ">OS</th><th " . $tablerow . ">Last Seen</th><th " . $tablerow . ">Virus Server</th><th " . $tablerow . ">Definition Date</th></tr>";
    }
    $mymsg .= '<input type="hidden" name="serial[]" value="' . $row->SERIAL . '">';
    if ($i%2) {
        $mymsg .= "<tr BGCOLOR='#F5EACB'>";
        } else {
        $mymsg .= "<tr>";
    }
    list ($lastseendate, $lastseentime) = split(" ", $row->LAST_SEEN);

    $i = $i + 1;
    if ($i%2) {
        $mymsg .= "<tr BGCOLOR='#F5EACB'>";
        } else {
        $mymsg .= "<tr>";
    }
    if ($row->IASO!=0)
       {$stmtiaso="select O3.DISPLAY_NAME, O3.PHONE from OWNER O3 where (o3.serial = '$row->IASO');";
       //echo $stmtiaso . '<br>';
       $sthiaso = ibase_query($dbh, $stmtiaso);
       $rowiaso = ibase_fetch_object($sthiaso);
       $LIASO=$rowiaso->DISPLAY_NAME;
       $LIASO_PHONE=$rowiaso->PHONE;
       $mymsg .= '<td ' . $tablerow . '>' . $rowiaso->DISPLAY_NAME . '</td>';
    } else {
       $mymsg .= '<td ' . $tablerow . '>&nbsp</td>';
    }
    $mymsg .= '<td ' . $tablerow . 'width="15%"><a href="/right.php?SERIAL=' . $row->MD5 . '" target = "_blank">' . $row->IP_ADDRESS . '</a></td>';
    if ($row->NETBIOS_NAME!='') {
        $mymsg .= '<td ' . $tablerow . '>' . $row->NETBIOS_NAME . '</td>';
        } else {
        $mymsg .= '<td ' . $tablerow . '>' . $row->DNS_NAME . '</td>';
    }
    $mymsg .= '<td ' . $tablerow . '>' . $row->BUILDING . '</td>';
    $mymsg .= '<td ' . $tablerow . '>' . $row->ROOM . '</td>';
    $mymsg .= '<td ' . $tablerow . '>' . $row->BARCODE . '</td>';
    $mymsg .= '<td ' . $tablerow . '>' . $row->OS . '</td>';
    $mymsg .= '<td ' . $tablerow . '>' . $lastseendate . '</td>';
    $mymsg .= '<td ' . $tablerow . '>' . $row->AV_SERVER . '</td>';
    $mymsg .= '<td ' . $tablerow . '>' . $row->ANTIVIRUS . '</td>';

    $mymsg .= '</tr>';
    $ip=$row->IP_ADDRESS;
}
$mymsg .= '</table>';


echo $mymsg;
$mymsg = $myhtml . $mymsg;
if ($empty==0) {
   echo "All your machines are up to date";
}

//     echo 'Sent Email to ' . $iasoemail . ' , ' . $liasoemail . ' --------------------------------------------------<br>';
//     mail($iasoemail,$SUBJECT, $mymsg, $headers);
if ($iasoemail != $liasoemail) {
    //         mail($liasoemail,$SUBJECT, $mymsg, $headers);
}
?>
Return current item: Network Asset Management Archive