fritz
03/12/2020, 2:15 PMpatches
table (eg. SELECT * FROM patches;
or if that doesn't have what you are looking for I wrote a query to scan the registry for KBs:
WITH patch_packages AS (
SELECT path, key, type,
MAX(CASE WHEN name = 'InstallName' AND data LIKE 'Package_%_for_KB%' THEN SUBSTR(SPLIT(data, '_', 3), 1, 9)
WHEN name = 'InstallName' AND data LIKE 'Package_for_KB%' THEN SUBSTR(SPLIT(data, '_', 2), 1, 9) END) AS kb_id,
MAX(CASE WHEN name = 'CurrentState' THEN data END) AS current_state,
MAX(CASE WHEN name = 'InstallClient' THEN data END) AS install_client,
MAX(CASE WHEN name = 'Visibility' THEN data END) AS visibility,
MAX(CASE WHEN name = 'SelfUpdate' THEN data END) AS self_update,
MAX(CASE WHEN name = 'InstallLocation' THEN data END) AS install_location,
MAX(CASE WHEN name = 'InstallUser' THEN data END) AS install_user,
MAX(CASE WHEN name = 'InstallName' THEN data END) AS install_name
FROM registry WHERE path LIKE 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Component Based Servicing\Packages\%4534273%\%%'
GROUP BY key
)
SELECT
MAX(kb_id),
MAX(install_client),
SUM(CASE WHEN current_state = '0' THEN 1 END) AS current_state_absent,
SUM(CASE WHEN current_state = '5' THEN 1 END) AS current_state_uninstall_pending,
SUM(CASE WHEN current_state = '16' THEN 1 END) AS current_state_resolving,
SUM(CASE WHEN current_state = '32' THEN 1 END) AS current_state_resolved,
SUM(CASE WHEN current_state = '48' THEN 1 END) AS current_state_staging,
SUM(CASE WHEN current_state = '64' THEN 1 END) AS current_state_staged,
SUM(CASE WHEN current_state = '80' THEN 1 END) AS current_state_superseded,
SUM(CASE WHEN current_state = '96' THEN 1 END) AS current_state_install_pending,
SUM(CASE WHEN current_state = '101' THEN 1 END) AS current_state_partially_installed,
SUM(CASE WHEN current_state = '112' THEN 1 END) AS current_state_installed,
SUM(CASE WHEN current_state = '128' THEN 1 END) AS current_state_permanent
FROM patch_packages
GROUP BY kb_id
CyberUnify
12/28/2021, 1:13 PMfritz
12/28/2021, 3:36 PM