Open-AudIT
https://www.open-audit.org/phpBB3/

PATCH: NonCompliant Software
https://www.open-audit.org/phpBB3/viewtopic.php?f=9&t=3741
Page 1 of 1

Author:  jonbendtsen [ Fri Jul 30, 2010 3:02 am ]
Post subject:  PATCH: NonCompliant Software

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 />&nbsp;</td>\n";
+ echo " <td class=\"contenthead\">Noncompliant Software License Register.<br />&nbsp;</td>\n";
echo "</tr>\n";
echo "<tr>\n";
- echo "<td width=\"20%\"><b>Package&nbsp;&nbsp;</b></td>\n";
- echo "<td align=\"center\" width=\"20%\"><b>&nbsp;&nbsp;Purchased&nbsp;&nbsp;</b></td>\n";
- echo "<td align=\"center\" width=\"20%\"><b>&nbsp;&nbsp;Used&nbsp;&nbsp;</b></td>\n";
- echo "<td align=\"center\" width=\"20%\"><b>&nbsp;&nbsp;Audit&nbsp;&nbsp;</b></td>\n";
- echo "<td align=\"center\" width=\"20%\"><b>&nbsp;&nbsp;Remove&nbsp;&nbsp;</b></td>\n";
+ echo "<td width=\"80%\"><b>Package&nbsp;&nbsp;</b></td>\n";
+ echo "<td align=\"center\" width=\"7%\"><b>&nbsp;&nbsp;Purchased&nbsp;&nbsp;</b></td>\n";
+ echo "<td align=\"center\" width=\"4%\"><b>&nbsp;&nbsp;Used&nbsp;&nbsp;</b></td>\n";
+ echo "<td align=\"center\" width=\"4%\"><b>&nbsp;&nbsp;Audit&nbsp;&nbsp;</b></td>\n";
+ echo "<td align=\"center\" width=\"5%\"><b>&nbsp;&nbsp;Remove&nbsp;&nbsp;</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]

Page 1 of 1 All times are UTC + 10 hours
Powered by phpBB® Forum Software © phpBB Group
https://www.phpbb.com/