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