SQL query to list Microsoft patches available in UPM and indicates if this patch appears in Software inventory or in Hardware Inventory

Products

• CA Client Automation - Asset Management
• CA Client Automation - IT Client Manager
• CA Client Automation
• CA Client Automation - Software Delivery
• CA Client Automation - Remote Control
• CA Client Automation - Asset Intelligence
• CA Client Automation - Desktop Migration Manager
• CA Client Automation - Patch Manager

Issue/Introduction

SQL query to list Microsoft patches available in UPM and indicates if this patch appears in Software inventory or in Hardware Inventory

Environment

Release : 14.0

Component : CLIENT AUTOMATION 

Resolution

Execute the below query to get the result. 

SET NOCOUNT ON
IF OBJECT_ID ('tempdb..#TMP1', 'U') IS NOT NULL DROP TABLE #TMP1 
IF OBJECT_ID ('tempdb..#TMP2', 'U') IS NOT NULL DROP TABLE #TMP2 

SELECT a.object_uuid, a.agent_name 'Computer Name', os.name 'OS Name', patch.name 'UPM Patch',
CASE CHARINDEX('KB',pkg.ipkg_name)
   WHEN 0 THEN ''
   ELSE REPLACE(REPLACE(SUBSTRING(pkg.ipkg_name, CHARINDEX('KB',pkg.ipkg_name)+2,7), '-',''), ')', '')
END 'HotFIXID', 
CASE 
   WHEN SUBSTRING(pkg.release_notes,29,8) like 'MS%' THEN SUBSTRING(pkg.release_notes,29,8)
   ELSE ''
END 'BulletinID',
CASE 
  WHEN dis_patch.sw_def_uuid IS NULL THEN 'N'
  ELSE 'Y'
END 'Installed (SW Inventory)',
'N' 'Installed (HW Inventory)',
convert(datetime, NULL) 'Install Date'
INTO #TMP1
FROM ca_agent a
JOIN ca_discovered_software dis_os ON a.object_uuid=dis_os.asset_source_uuid
JOIN ca_software_def os ON os.sw_def_uuid=dis_os.sw_def_uuid
JOIN ca_link_sw_def l ON l.primary_sw_def_uuid=os.sw_def_uuid
JOIN ca_software_def patch ON l.secondary_sw_def_uuid=patch.sw_def_uuid
JOIN ca_install_package pkg ON pkg.sw_def_uuid=l.secondary_sw_def_uuid
LEFT JOIN ca_discovered_software dis_patch ON dis_patch.sw_def_uuid=l.secondary_sw_def_uuid AND dis_patch.asset_source_uuid=a.object_uuid
WHERE a.agent_type=1 AND
(os.name like 'Microsoft Windows XP%'
OR os.name like 'Microsoft Windows 2000 Pro%'
OR os.name like 'Microsoft Windows 2000 %Server x%'
OR os.name like 'Microsoft Windows Vista %Edition%'
OR os.name like 'Microsoft Windows 7 %'
OR os.name like 'Microsoft Windows 8 %'
OR os.name like 'Microsoft Windows 10%'
OR os.name like 'Microsoft Windows Server 2003 %Edition%'
OR os.name like 'Microsoft Windows Server 2008 %Edition%'
OR os.name like 'Microsoft Windows Server 2008 R2 Datacenter%'
OR os.name like 'Microsoft Windows Server 2008 R2 Enterprise%'
OR os.name like 'Microsoft Windows Server 2008 R2 Standard%'
OR os.name like 'Microsoft Windows Server 2008 R2 Standard Edition%'
OR os.name like 'Microsoft Windows Web Server 2008 %'
OR os.name like 'Microsoft Windows Server 2012 % Datacenter%'
OR os.name like 'Microsoft Windows Server 2012 % Essentials%'
OR os.name like 'Microsoft Windows Server 2012 % Foundation%'
OR os.name like 'Microsoft Windows Server 2012 % Standard%'
OR os.name like 'Microsoft Windows Server 2016 % Datacenter%'
OR os.name like 'Microsoft Windows Server 2016 % Essentials%'
OR os.name like 'Microsoft Windows Server 2016 % Standard%'
) and os.software_type_id =3 and os.is_active=1 and patch.is_active=1 
and patch.name like '%KB%'
order by a.agent_name, HotFIXID

UPDATE #TMP1
SET [Installed (HW Inventory)]='Y',
[Install Date]=dateadd ( ss, gen2.item_value_double + datediff(ss,getutcdate(),getdate()), convert(datetime,'19700101'))
FROM #TMP1 t
JOIN inv_generalinventory_item gen ON gen.object_uuid=t.object_uuid
JOIN inv_tree_name_id tree ON tree.tree_name_id=gen.item_parent_name_id AND tree.tree_name='$System Updates$' AND tree.domain_uuid=gen.domain_uuid
JOIN inv_item_name_id item ON item.item_name_id=gen.item_name_id AND item.item_name='Update' AND item.domain_uuid=gen.domain_uuid
JOIN inv_generalinventory_item gen2 ON gen2.object_uuid=t.object_uuid
JOIN inv_tree_name_id tree2 ON tree2.tree_name_id=gen2.item_parent_name_id AND tree2.tree_name='$System Updates$' AND tree2.domain_uuid=gen2.domain_uuid
JOIN inv_item_name_id item2 ON item2.item_name_id=gen2.item_name_id AND item2.item_name='Installed Date' AND item2.domain_uuid=gen2.domain_uuid
WHERE gen.item_value_text=t.HotFIXID OR SUBSTRING(gen.item_value_text, 3,8)=t.HotFIXID
and gen.item_parent_id=gen2.item_parent_id

select a.object_uuid, a.agent_name 'Computer Name', NULL 'OS Name','' 'UPM Patch',
CASE CHARINDEX('KB',gen.item_value_text)
   WHEN 0 THEN gen.item_value_text
   ELSE SUBSTRING(gen.item_value_text, 3,8)
END 'HotFIXID', '' 'BulletinID','N' 'Installed (SW Inventory)','Y' 'Installed (HW Inventory)',
dateadd ( ss, gen2.item_value_double + datediff(ss,getutcdate(),getdate()), convert(datetime,'19700101')) 'Install Date'
INTO #TMP2
FROM ca_agent a
JOIN inv_generalinventory_item gen ON gen.object_uuid=a.object_uuid
JOIN inv_tree_name_id tree ON tree.tree_name_id=gen.item_parent_name_id AND tree.tree_name='$System Updates$' AND tree.domain_uuid=gen.domain_uuid
JOIN inv_item_name_id item ON item.item_name_id=gen.item_name_id AND item.item_name='Update' AND item.domain_uuid=gen.domain_uuid
JOIN inv_generalinventory_item gen2 ON gen2.object_uuid=a.object_uuid
JOIN inv_tree_name_id tree2 ON tree2.tree_name_id=gen2.item_parent_name_id AND tree2.tree_name='$System Updates$' AND tree2.domain_uuid=gen2.domain_uuid
JOIN inv_item_name_id item2 ON item2.item_name_id=gen2.item_name_id AND item2.item_name='Installed Date' AND item2.domain_uuid=gen2.domain_uuid
WHERE gen.item_parent_id=gen2.item_parent_id

INSERT INTO #TMP1 
SELECT * FROM #TMP2 t2 
WHERE NOT EXISTS (SELECT * FROM #TMP1 t1 WHERE t1.object_uuid=t2.object_uuid AND t1.HotFIXID=t2.HotFIXID)

UPDATE #TMP1
SET [OS Name]=(select distinct t2.[OS Name] FROM #TMP1 t2 WHERE #TMP1.object_uuid=t2.object_uuid and t2.[OS Name] IS NOT NULL)

select distinct [Computer Name], [OS Name], [UPM Patch], HotFIXID, BulletinID, [Installed (SW Inventory)], [Installed (HW Inventory)], [Install Date]
from #TMP1
ORDER BY [Computer Name], HotFIXID