Registrations to Open-AudIT forums are now closed. To ask any new questions please visit Opmantek Community Questions.

Open-AudIT

What's on your network?
It is currently Thu Mar 28, 2024 10:46 pm

All times are UTC + 10 hours




Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 7 posts ] 
Author Message
PostPosted: Thu Nov 22, 2007 7:48 pm 
Offline
Open-AudIT Fellow

Joined: Thu May 17, 2007 5:47 pm
Posts: 568
Location: Italy
With current queries, only few hotfixes/updates/service packs are retrieved because their names are language dependent. As it was discussed and suggested at viewtopic.php?f=5&t=2465 I think that, due to the impossibility to translate "update", "hotfix" or "service pack" in every language, it could be queried also the presence of the MS Knowledge Base update code (KBxxxxxx or Qxxxxxx) in the "software_name" field to identify it or not as an update. To add this modify (sources are from SVN rev. 900):

- list_viewdef_all_software.php at line 4
[code]
"sql"=>"SELECT count(software_name) AS software_count, software_name, software_version, software_publisher, software_url FROM software, system where software_name NOT LIKE '%hotfix%' AND software_name NOT LIKE '%update%' AND software_name NOT LIKE '%Service Pack%' AND software_name NOT REGEXP '[KB|Q][0-9]{6,}' AND software_uuid = system_uuid AND software_timestamp = system_timestamp GROUP BY software_name, software_version ",
[/code]
- list_viewdef_all_software_hosts.php at line 4
[code]
"sql"=>"SELECT software_name, software_version, software_publisher, system_name, net_user_name, system_uuid FROM software, system where software_name NOT LIKE '%hotfix%' AND software_name NOT LIKE '%update%' AND software_name NOT LIKE '%Service Pack%' AND software_name NOT REGEXP '[KB|Q][0-9]{6,}' AND software_uuid = system_uuid AND software_timestamp = system_timestamp ",
[/code]
- list_viewdef_all_hotfixes_patches.php at line 4
[code]
"sql"=>"SELECT count(software_name) AS software_count, software_name, software_version, software_publisher, software_url FROM software, system where (software_name LIKE '%hotfix%' OR software_name LIKE '%update%' OR software_name LIKE '%Service Pack%' OR software_name REGEXP '[KB|Q][0-9]{6,}') AND software_uuid = system_uuid AND software_timestamp = system_timestamp GROUP BY software_name, software_version ",
[/code]
- list_viewdef_software_for_system.php at line 10
[code]
"sql"=>"SELECT software_name, software_version, software_publisher, software_url FROM software, system WHERE system_uuid = '".$_REQUEST["pc"]."' AND software_name NOT LIKE '%hotfix%' AND software_name NOT LIKE '%update%' AND software_name NOT LIKE '%Service Pack%' AND software_name NOT REGEXP '[KB|Q][0-9]{6,}' AND software_uuid = system_uuid AND software_timestamp = system_timestamp GROUP BY software_name, software_version ",
[/code]
- list_viewdef_syscomp_for_system.php at line 6
[code]
"sql"=>"SELECT software_name, software_version, software_url, software_publisher FROM software, system WHERE software_uuid = '".$_REQUEST["pc"]."' AND software_uuid = system_uuid AND software_timestamp = system_timestamp AND software_name NOT LIKE '%codec%' AND software_name NOT LIKE '%hotfix%' AND software_name NOT LIKE '%update%' AND software_name NOT LIKE '%Service Pack%' AND software_name NOT REGEXP '[KB|Q][0-9]{6,}' AND software_system_component <> '1' ",
[/code]
- list_viewdef_hotfixes_patches_for_system.php at line 6
[code]
"sql"=>"SELECT software_name, software_version, software_publisher, software_url FROM software, system WHERE software_uuid = '".$_REQUEST["pc"]."' AND (software_name LIKE '%hotfix%' OR software_name LIKE '%update%' OR software_name LIKE '%Service Pack%' OR software_name REGEXP '[KB|Q][0-9]{6,}') AND software_uuid = system_uuid AND software_timestamp = system_timestamp ",
[/code]
- list_viewdef_software_audit_system_trail.php at lines 6-10
[code]
"sql"=>"
SELECT software_name, software_version, software_first_timestamp, system_name, system_uuid, net_ip_address
FROM software, system
WHERE software_uuid = system_uuid AND software_uuid = '".$_REQUEST["pc"]."' AND software_timestamp = system_timestamp
",
[/code]
- list_viewdef_software_uninstalled_for_system.php at line 6
[code]
"sql"=>"SELECT software_name, software_first_timestamp, software_timestamp, system_name FROM software, system WHERE software_uuid = system_uuid AND software_uuid = '".$_REQUEST["pc"]."' AND software_timestamp <> system_timestamp ",
[/code]
- index.php at lines 307-315
[code]
if ($show_software_detected == "y"){
$sql = "SELECT sw.software_name, sw.software_first_timestamp, sys.system_name, sys.system_uuid, sys.net_ip_address ";
$sql .= "FROM software sw, system sys ";
$sql .= "WHERE sw.software_first_timestamp >= '" . adjustdate(0,0,-$days_software_detected) . "000000' ";
//$sql .= "WHERE software_first_timestamp >= '" . adjustdate(0,0,-$days_software_detected) . "000000' ";
$sql .= "AND sys.system_first_timestamp < '" . adjustdate(0,0,-$days_software_detected) . "000000' ";
$sql .= "AND sw.software_timestamp = sys.system_timestamp ";
$sql .= "AND sw.software_uuid = sys.system_uuid ";
$sql .= "ORDER BY sw.software_name";
[/code]
- rss_new_software.php at lines 33-38
[code]
$sql = "SELECT sw.software_id, sw.software_name, sw.software_first_timestamp, sys.system_name, sys.system_uuid, ";
$sql .= "sys.net_ip_address FROM software sw, system sys WHERE ";
$sql .= "software_first_timestamp >= '" . adjustdate(0,0,-$days_software_detected) . "000000' ";
$sql .= "AND sys.system_first_timestamp < '" . adjustdate(0,0,-$days_software_detected) . "000000' ";
$sql .= "sw.software_timestamp = sys.system_timestamp AND ";
$sql .= "sw.software_uuid = sys.system_uuid ";
[/code]
- system_viewdef_report_full.php at lines 253-270
[code]
"installed_software"=>array(
"headline"=>__("Installed Software"),
"sql"=>"SELECT software_name, software_version, software_publisher FROM software, system WHERE system_uuid = '".$pc."' AND software_name NOT LIKE '%hotfix%' AND software_name NOT LIKE '%update%' AND software_name NOT LIKE '%Service Pack%' AND software_name NOT REGEXP '[KB|Q][0-9]{6,}' AND software_uuid = system_uuid AND software_timestamp = system_timestamp GROUP BY software_name, software_version ",
"table_layout"=>"horizontal",
"fields"=>array("10"=>array("name"=>"software_name", "head"=>__("Name"),),
"20"=>array("name"=>"software_version", "head"=>__("Version"),),
"30"=>array("name"=>"software_publisher", "head"=>__("Publisher"),),
),
),
"hotfixes_patches"=>array(
"headline"=>__("Hotfixes and Patches"),
"sql"=>"SELECT software_name, software_version, software_publisher FROM software, system WHERE system_uuid = '".$pc."' AND (software_name LIKE '%hotfix%' OR software_name LIKE '%update%' OR software_name LIKE '%Service Pack%' OR software_name REGEXP '[KB|Q][0-9]{6,}') AND software_uuid = system_uuid AND software_timestamp = system_timestamp GROUP BY software_name, software_version ",
"table_layout"=>"horizontal",
"fields"=>array("10"=>array("name"=>"software_name", "head"=>__("Name"),),
"20"=>array("name"=>"software_version", "head"=>__("Version"),),
"30"=>array("name"=>"software_publisher", "head"=>__("Publisher"),),
),
),
[/code]
- software_register_add.php at line 55
[code]
$sql .= "WHERE software_title IS NULL AND software_name NOT LIKE '%hotfix%' AND software_name NOT LIKE '%update%' AND software_name NOT LIKE '%Service Pack%' AND software_name NOT REGEXP '[KB|Q][0-9]{6,}' ";
[/code]
Note that I filtered out HFs and SPs from software_register_add.php (I think that we don't need to see listed hundreds of MS updates...) and rather I added them to uninstalled/audit trail/index/rss software views (it could be useful to know when they were uninstalled or discovered).
Please, let me know if it's OK. Thank you

_________________
Edoardo


Top
 Profile  
Reply with quote  
PostPosted: Fri Nov 23, 2007 12:45 am 
Offline
Moderator
User avatar

Joined: Tue Jan 25, 2005 3:09 am
Posts: 2140
Location: Scotland
Wow! Most impressive.

Added to SVN 901, but I kept the Hotfixes off the index page, I think we may want this to be optional from the setup page. Kept them in elsewhere.

Changed the index code to

[code]
if ($show_software_detected == "y"){
$sql = "SELECT sw.software_name, sw.software_first_timestamp, sys.system_name, sys.system_uuid, sys.net_ip_address ";
$sql .= "FROM software sw, system sys ";
$sql .= "WHERE sw.software_first_timestamp >= '" . adjustdate(0,0,-$days_software_detected) . "000000' ";
//$sql .= "WHERE software_first_timestamp >= '" . adjustdate(0,0,-$days_software_detected) . "000000' ";
$sql .= "AND sys.system_first_timestamp < '" . adjustdate(0,0,-$days_software_detected) . "000000' ";
$sql .= "AND sw.software_name NOT LIKE '%Hotfix%' AND sw.software_name NOT LIKE '%Update%' AND software_name NOT REGEXP '[KB|Q][0-9]{6,}' ";
$sql .= "AND sw.software_timestamp = sys.system_timestamp ";
$sql .= "AND sw.software_uuid = sys.system_uuid ";
$sql .= "ORDER BY sw.software_name";
[/code]


:?: What does everyone think, do we want the hotfixes updates and KB-patches on the index page and in the rss feed, or do we want them out... or do we want them optional :?:

_________________
Andrew

[size=85]OA Server: Windows XP/ XAMPP, Mandriva/Apache, Ubuntu
Auditing: 300+ Wstns, 20+ Srvrs, Thin clients, Linux boxes, Routers, etc
OS's: Windows XP , W2K Srvr, W2K3 Srvr, W2K8, Vista, Windows 7, Linuxes (and a Mac at home)
LDAP: Active Directory[/size]


Top
 Profile  
Reply with quote  
PostPosted: Fri Nov 23, 2007 1:29 am 
Offline
Newbie

Joined: Thu Nov 01, 2007 8:21 am
Posts: 27
cool !!!

good job.


Top
 Profile  
Reply with quote  
PostPosted: Fri Nov 23, 2007 2:48 am 
Offline
Moderator
User avatar

Joined: Tue Jan 25, 2005 3:09 am
Posts: 2140
Location: Scotland
[quote="exmachina"]cool !!!

good job.


.. I agree, but.. do you want the hotfixes updates etc on the index and RSS pages, do you want them off the index page etc or do you want them optional?

_________________
Andrew

[size=85]OA Server: Windows XP/ XAMPP, Mandriva/Apache, Ubuntu
Auditing: 300+ Wstns, 20+ Srvrs, Thin clients, Linux boxes, Routers, etc
OS's: Windows XP , W2K Srvr, W2K3 Srvr, W2K8, Vista, Windows 7, Linuxes (and a Mac at home)
LDAP: Active Directory[/size]


Top
 Profile  
Reply with quote  
PostPosted: Fri Nov 23, 2007 3:33 am 
Offline
Newbie

Joined: Thu Nov 01, 2007 8:21 am
Posts: 27
off the index page, please.


Top
 Profile  
Reply with quote  
PostPosted: Fri Nov 23, 2007 8:41 am 
Offline
Open-AudIT Fellow

Joined: Thu May 17, 2007 5:47 pm
Posts: 568
Location: Italy
Thank you, Andrew. Yes, for me too it's OK to keep them off the index and rss page (essentially they aren't new softwares), but both hotfixes and service packs: I noticed indeed that you didn't included the filter for service packs in index.php, could you please add it at line 313?
[code]
$sql .= "AND sw.software_name NOT LIKE '%Hotfix%' AND sw.software_name NOT LIKE '%Update%' AND sw.software_name NOT LIKE '%Service Pack%' AND sw.software_name NOT REGEXP '[KB|Q][0-9]{6,}' ";
[/code]
Consequently, could you please modify rss_new_software.php? (I forgot also one AND + one sw.)
[code]
$sql = "SELECT sw.software_id, sw.software_name, sw.software_first_timestamp, sys.system_name, sys.system_uuid, sys.net_ip_address FROM software sw, system sys ";
$sql .= "WHERE sw.software_first_timestamp >= '" . adjustdate(0,0,-$days_software_detected) . "000000' ";
$sql .= "AND sys.system_first_timestamp < '" . adjustdate(0,0,-$days_software_detected) . "000000' ";
$sql .= "AND sw.software_name NOT LIKE '%Hotfix%' AND sw.software_name NOT LIKE '%Update%' AND sw.software_name NOT LIKE '%Service Pack%' AND sw.software_name NOT REGEXP '[KB|Q][0-9]{6,}' ";
$sql .= "AND sw.software_timestamp = sys.system_timestamp ";
$sql .= "AND sw.software_uuid = sys.system_uuid ";
$result = mysql_query($sql, $db);
$bgcolor = "#FFFFFF";
[/code]
Thank you

_________________
Edoardo


Top
 Profile  
Reply with quote  
PostPosted: Fri Nov 23, 2007 9:03 pm 
Offline
Moderator
User avatar

Joined: Tue Jan 25, 2005 3:09 am
Posts: 2140
Location: Scotland
Done. SVN 902 Thanks.

_________________
Andrew

[size=85]OA Server: Windows XP/ XAMPP, Mandriva/Apache, Ubuntu
Auditing: 300+ Wstns, 20+ Srvrs, Thin clients, Linux boxes, Routers, etc
OS's: Windows XP , W2K Srvr, W2K3 Srvr, W2K8, Vista, Windows 7, Linuxes (and a Mac at home)
LDAP: Active Directory[/size]


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 7 posts ] 

All times are UTC + 10 hours


Who is online

Users browsing this forum: No registered users and 2 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron
Powered by phpBB® Forum Software © phpBB Group