help making a status page?

A

admin

Administrator
Staff member
I am a newbie trying to build a status page for my Certs to track patches for bills OS using a mySql database. we basically have a login script that checks for non compliant windoze boxes and pump the info into mySql via php.exe
and use PHP/apache to look at the results. I want to count all of the non compliant computers for each organization and have a page to see how many noncompliant there are for each vulnerability sorted by unit
example of what I would like to have

select unit,count(afcertstatus) from main2 where(SUBSTRING(afcertstatus,10,1)=0) group by unit

cert1 cert2 cert3 cert4
HQ 2 0 1 1
district12 1 1 0 3
total 3 1 1 4

I can seem to back up enough to see what I have to do, HELP! :)
thanks
-adam



here is the schema from mySql and sample data. followed by the php to view it.

Database afcert
# phpMyAdmin MySQL-Dump
# version 2.2.2-rc1
# <!-- m --><a class="postlink" href="http://phpwizard.net/phpMyAdmin/">http://phpwizard.net/phpMyAdmin/</a><!-- m -->
# <!-- m --><a class="postlink" href="http://phpmyadmin.sourceforge.net/">http://phpmyadmin.sourceforge.net/</a><!-- m --> (download page)
#
# Host: localhost
# Generation Time: Nov 28, 2001 at 09:22 AM
# Server version: 3.23.36
# PHP Version: 4.0.4pl1
# Database : `afcert`
# --------------------------------------------------------

#
# Table structure for table `afcertname`
#

CREATE TABLE afcertname (
afcertnum tinyint(3) NOT NULL auto_increment,
afcert_name varchar(20) NOT NULL default '',
description varchar(100) NOT NULL default '',
KEY afcertnum (afcertnum)
) TYPE=MyISAM;

#
# Dumping data for table `afcertname`
#

INSERT INTO afcertname VALUES ('1', 'AFCERT 98-49', 'whatever it is');
INSERT INTO afcertname VALUES ('2', 'AFCERT 99-2', 'another one');
INSERT INTO afcertname VALUES ('3', 'AFCERT 99-6', '');
INSERT INTO afcertname VALUES ('4', 'AFCERT 99-33', '');
INSERT INTO afcertname VALUES ('5', 'AFCERT 99-35a', '');
INSERT INTO afcertname VALUES ('6', 'AFCERT 99-36', '');
INSERT INTO afcertname VALUES ('7', 'AFCERT 99-45', '');
INSERT INTO afcertname VALUES ('8', 'AFCERT 99-49', '');
INSERT INTO afcertname VALUES ('9', 'AFCERT 99-53b', '');
INSERT INTO afcertname VALUES ('10', 'AFCERT CM 00-1', '');
INSERT INTO afcertname VALUES ('11', 'AFCERT CM 00-8a', '');
INSERT INTO afcertname VALUES ('12', 'AFCERT CM 00-8b', '');
INSERT INTO afcertname VALUES ('13', 'AFCERT TCNO 01-4', '');
INSERT INTO afcertname VALUES ('14', 'AFCERT TCNO 01-11', '');
# --------------------------------------------------------

#
# Table structure for table `main2`
#

CREATE TABLE main2 (
recid bigint(20) NOT NULL auto_increment,
date date NOT NULL default '0000-00-00',
time time NOT NULL default '00:00:00',
mac varchar(12) NOT NULL default '',
unit varchar(25) NOT NULL default '',
fsa varchar(25) NOT NULL default '',
phone varchar(7) NOT NULL default '',
machinename varchar(15) NOT NULL default '',
ipaddress varchar(15) NOT NULL default '',
userid varchar(14) NOT NULL default '',
fullname varchar(35) NOT NULL default '',
priv varchar(10) NOT NULL default '',
osshortname varchar(5) NOT NULL default '',
afcertcheckerversion varchar(5) NOT NULL default '',
computerdescription varchar(255) NOT NULL default '',
afcertstatus varchar(100) NOT NULL default '',
PRIMARY KEY (recid),
UNIQUE KEY mac (mac)
) TYPE=MyISAM;

#
# Dumping data for table `main2`
#

INSERT INTO main2 VALUES (1, '0000-00-00', '00:00:00', '123abcd0220', 'HQ 1', 'jerry', '7200', 'pc001', '192.168.0.1', 'jamesd', 'james dean', 'user', '2000', '3.8', 'my pc', '013011');
INSERT INTO main2 VALUES (2, '0000-00-00', '00:00:00', '123abcd0221', 'HQ 1', 'jerry', '7200', 'pc002', '192.168.0.2', 'jamesS', 'james stewart', 'user', '2000', '3.8', 'my pc2', '013013');
INSERT INTO main2 VALUES (4, '0000-00-00', '00:00:00', '123abcd0224', 'district12', 'jimm', '7890', 'pc004', '192.168.0.4', 'bonehead2', 'bad user', 'user', '2000', '3.8', 'my pc2', '333333');
INSERT INTO main2 VALUES (5, '0000-00-00', '00:00:00', '123abcd0222', 'district12', 'jimm', '7890', 'pc005', '192.168.0.5', 'bonehead3', 'bad user', 'user', '2000', '3.8', 'my pc2', '1111111');

---------------------------------
here are the php files I am using
--------------------------------
config.php
<?
$title = "(AFCERT) Compliancy Homepage";
$location = "HQ";
$Server = "localhost";
?>


--------------------------------
listall.php

<?
require("config.php");

$db = mysql_connect("$Server","afcert");

mysql_select_db("afcert",$db);
if($CSV)
{
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=$unit.csv");
header("Content-Transfer-Encoding: binary");
}

$QUERY = "select * from main2";
//$QUERY = "select recid,date,time,mac,unit,machinename,afcertstatus from main2";

if($unit)
{
$QUERY = sprintf("%s WHERE unit='%s'", $QUERY, $unit);
}
else
{
if($unit=='all')
$QUERY = "select * from main2";
}
if($ORDERBY) {
if(strlen($ORDERBY)<=2){
$QUERY = sprintf("%s order by (SUBSTRING(afcertstatus,%s,1))",$QUERY,$ORDERBY);
}else{
$QUERY = sprintf("%s order by %s", $QUERY, $ORDERBY);
}
}

if($SortOrder){
$QUERY = sprintf("%s %s",$QUERY,$SortOrder);
}

$QUERY2 = "select afcert_name from afcertname";
$QUERY3 = "select count(*) from main2 WHERE ((unit) like '917%') and (SUBSTRING(afcertstatus,14,1))= '1'";
$result = mysql_query("$QUERY");
$result2 = mysql_query("$QUERY2");
$result3 = mysql_num_rows($result2);
$result4 = mysql_query("$QUERY3");
$result5 = mysql_num_rows($result4);
$rows = mysql_num_rows($result);
$cols = mysql_num_fields($result)-1;

// save to a csv link
printf("<FONT ID=LABEL><A HREF=http://www.phpbuilder.com/board/archive/index.php/%s?ORDERBY=%s&SortOrder=%s&unit=%s&CSV='on'>Save this query to a CSV</A></FONT><BR>", $PHP_SELF, $h, $SortOrder, rawurlencode($unit));

?>

<HEAD><TITLE>AFCERT Database</TITLE></HEAD>
<link rel="stylesheet" type="text/css" href=http://www.phpbuilder.com/board/archive/index.php/"weeniestyle1.css" title="Weenie Style">

<BODY><CENTER>

<TABLE BORDER=1>

<?
//date time print
$today = date("r",time()-21600);
echo "<font id=title>Report generated <i>$today</i></font>";

// print the report title requested
if($unit=="0"){ $unit = "ALL UNITS"; }
echo " <font id=title>for <b>$unit</b></font>";
function extractfieldnames($result,$cols,$unit)


{
$i = 0;
while ($i <$cols) {
$meta = mysql_fetch_field ($result);
if (!$meta) {
echo "No information available<BR>\n";
}

printf("<TD><FONT ID=LABEL><A HREF=%s?ORDERBY=%s&SortOrder=%s&unit=%s>%s</A></TD>",$PHP_SELF,strtolower($meta->name),$SortOrder, rawurlencode($unit),$meta->name);


$i++;
}
}

function tableformat($fieldname)

{ switch ("$fieldname")
{

case'0':
printf("<TD BGCOLOR=RED><FONT COLOR=WHITE><B>No</B></FONT></TD>");
break;
case '1':
printf("<TD BGCOLOR=GREEN><FONT COLOR=WHITE><B>Yes</B></FONT></TD>");
break;
case '3':
printf("<TD BGCOLOR=DARKSLATEGRAY><FONT COLOR=yellow>N/A</FONT></TD>");
break;
}

}

// print table field names
printf("<TD></TD>");
extractfieldnames($result,$cols,$unit);

//Spit out the afcert headers
for($h=1;$CurrentRec2 = mysql_fetch_array($result2);$h++)
{
printf("<TD><FONT ID=LABEL><A HREF=http://www.phpbuilder.com/board/archive/index.php/%s?ORDERBY=%s&SortOrder=%s&unit=%s>%s</A></FONT></TD>", $PHP_SELF, $h, $SortOrder, rawurlencode($unit), $CurrentRec2["afcert_name"]);

}

printf("</TR>");

//print row data
for($i=0;$CurrentRec = mysql_fetch_array($result);$i++)
{
printf("<TD><A HREF=\"delete.php?recid=%s\">Delete</A></TD>", $CurrentRec["recid"]);
//print data from every col in $result
for($a=0;$a<$cols;$a++){
printf("<TD><FONT ID=DATA>%s</FONT></TD>", $CurrentRec["$a"]);
}
//print afcert
$status = $CurrentRec["afcertstatus"];
for($j=0;$j<$result3;$j++){
tableformat(substr($status,$j,1));
}
//finsh table row
printf("</TR>");
}

printf("</TABLE><CENTER><P>Records Found: %s</P></CENTER>",$i);
echo "number of AFCERTS in database <i>$result3</i>";


//clean up
mysql_free_result ($result);
mysql_close($db);
?>
</BODY><hr>
 
You must log in or register to reply here.
Back
Top