Hi
I wanted OpenAudIT to show only the Noncompliant Software without showing the software which i have licenses for. So based on software_register.php i made a new file called software_register_noncompliant.php which uses a database view. I will show the view i use in mysql. I probably should have logged the command i used to create it, now i have to use show create view to get it for you which is maybe not so nice.
[code]mysql> show create view noncompliant_software; +-----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | View | Create View | +-----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | noncompliant_software | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `noncompliant_software` AS select `software_register_count`.`software_reg_id` AS `software_reg_id`,`software_register_count`.`software_title` AS `software_title`,`software_register_count`.`number_used` AS `number_used`,`software_license_count`.`purchased` AS `purchased` from (`software_register_count` join `software_license_count`) where ((`software_license_count`.`license_software_id` = `software_register_count`.`software_reg_id`) and (`software_license_count`.`purchased` <> -(1)) and (`software_register_count`.`number_used` > `software_license_count`.`purchased`)) | +-----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.05 sec)[/code] (i recommend copying it to the clipboard and then paste it into a text viewer with a wide windows for easy reading).
This diff is made against the file software_register.php from revision 1245 [code] dkopen-audit:/var/www/open-audit# diff -u software_register.php software_register_noncompliant.php --- software_register.php 2010-04-14 16:04:09.000000000 +0200 +++ software_register_noncompliant.php 2010-07-29 18:51:51.000000000 +0200 @@ -3,49 +3,32 @@ $extra = ""; $software = ""; $count = -1; +$totalnumber=0; if (isset($_GET['software'])) {$software = $_GET['software'];} else {} if (isset($_GET['sort'])) {$sort = $_GET['sort'];} else {$sort= "system_name";} include "include.php"; echo "<td valign=\"top\">\n"; echo "<div class=\"main_each\">"; -#$sql = "SELECT software_reg_id, software_title, count(software.software_name) AS number_used, sum(license_purchase_number) as number_purchased FROM "; -#$sql .= "software_register, software, system, software_licenses WHERE "; -#$sql .= "software_reg_id = license_software_id AND "; -#$sql .= "software_title = software_name AND "; -#$sql .= "software_uuid = system_uuid AND "; -#$sql .= "software_timestamp = system_timestamp "; -#$sql .= "GROUP BY software_title"; - -$sql = "SELECT software_reg_id, software_title, count(software.software_name) AS number_used FROM "; -$sql .= "software_register, software, system WHERE "; -$sql .= "software_title = software_name AND "; -$sql .= "software_uuid = system_uuid AND "; -$sql .= "software_timestamp = system_timestamp "; -$sql .= "GROUP BY software_title"; + +$sql = "SELECT software_reg_id, software_title, number_used, purchased FROM noncompliant_software"; $result = mysql_query($sql, $db); if ($myrow = mysql_fetch_array($result)){ echo "<table border=\"0\" cellpadding=\"0\" cellspacing=\"0\" width=\"100%\">\n"; echo "<tr>\n"; - echo " <td class=\"contenthead\">Software License Register.<br /> </td>\n"; + echo " <td class=\"contenthead\">Noncompliant Software License Register.<br /> </td>\n"; echo "</tr>\n"; echo "<tr>\n"; - echo "<td width=\"20%\"><b>Package </b></td>\n"; - echo "<td align=\"center\" width=\"20%\"><b> Purchased </b></td>\n"; - echo "<td align=\"center\" width=\"20%\"><b> Used </b></td>\n"; - echo "<td align=\"center\" width=\"20%\"><b> Audit </b></td>\n"; - echo "<td align=\"center\" width=\"20%\"><b> Remove </b></td>\n"; + echo "<td width=\"80%\"><b>Package </b></td>\n"; + echo "<td align=\"center\" width=\"7%\"><b> Purchased </b></td>\n"; + echo "<td align=\"center\" width=\"4%\"><b> Used </b></td>\n"; + echo "<td align=\"center\" width=\"4%\"><b> Audit </b></td>\n"; + echo "<td align=\"center\" width=\"5%\"><b> Remove </b></td>\n"; echo "</tr>\n"; do { $sql2 = "SELECT sum(license_purchase_number) as number_purchased FROM "; - $sql2 .= "software_licenses, software_register WHERE "; - $sql2 .= "license_software_id = software_reg_id AND "; - $sql2 .= "software_reg_id = '" . $myrow['software_reg_id'] . "'"; - $result2 = mysql_query($sql2, $db); - $myrow2 = mysql_fetch_array($result2); - - $number_purchased = $myrow2["number_purchased"]; + $number_purchased = $myrow["purchased"]; $number_used = $myrow["number_used"]; settype($number_purchased, "integer"); settype($number_used, "integer"); @@ -74,6 +57,7 @@ echo "<a href=\"#\" onclick=\"sendRequest('" . url_clean($myrow["software_reg_id"]) . "');\"><img border=\"0\" src=\"images/button_fail.png\" width=\"16\" height=\"16\" alt=\"\" /></a>"; echo "</div></td>\n"; echo "</tr>"; + $totalnumber = $totalnumber + 1; } while ($myrow = mysql_fetch_array($result)); echo "</table>"; } else { @@ -82,6 +66,7 @@ echo "</div>\n"; +echo "<p>Total number of noncompliant Software Licenses in this OpenAudIT system are: $totalnumber</p>"; echo "</td>\n"; include "include_right_column.php"; ?> [/code]
And here are the patch to add it to the menu (also against revision 1245) [code] dkopen-audit:/var/www/open-audit# svn diff include_menu_array.php Index: include_menu_array.php =================================================================== --- include_menu_array.php (revision 1245) +++ include_menu_array.php (working copy) @@ -187,6 +187,7 @@ "childs"=>array("10"=>array("name"=>"Software Register", "link"=>"./software_register.php", "image"=>"images/software.png", "title"=>"",), "20"=>array("name"=>"Add Software", "link"=>"./software_register_add.php", "image"=>"images/software_2.png", "title"=>"",), "30"=>array("name"=>"Delete Software", "link"=>"./software_register_del.php", "image"=>"images/software_3.png", "title"=>"",), + "40"=>array("name"=>"NonCompliant Software", "link"=>"./software_register_noncompliant.php", "image"=>"images/emblem_important.png", "title"=>"",), ), ), "45" => array("name"=>"Audits", [/code]
|