https://github.com/osquery/osquery logo
#general
Title
# general
f

fritz

03/12/2020, 2:15 PM
Hi @Diego Erazo , there is the
patches
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:
Copy code
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
🎉 1
👌 1
c

CyberUnify

12/28/2021, 1:13 PM
this query takes too long ... is there any way to improve it?
f

fritz

12/28/2021, 3:36 PM
I would say wait for the upcoming windows_updates table which is coming in the following PR: https://github.com/osquery/osquery/pull/7407
3 Views