@brywhi
Just wanted to reply and say thanks again- this database is beautifully organized and very easy to understand.
In case anyone else is interested, this query provides all updates in the inventory and which computers they are installed on. However, due to differences in OS versions, etc. we can't expect that all computers will have all of the updates installed.
SELECT
c.computer_name,
wu.title AS update_title,
wu.category AS update_category,
coalesce(cu.is_installed, 0) AS is_installed,
cu.install_date
FROM computers c
INNER JOIN wu_inventory wu
ON 1 = 1
left JOIN computer_updates cu
ON c.computer_id = cu.computer_id AND wu.wu_inventory_id = cu.wu_inventory_id
ORDER BY c.computer_name, wu.title
This query shows all the updates that are not installed and still required, as per the computer's own reporting. This is the one we're going to be monitoring:
SELECT
c.computer_name,
wu.title AS update_title,
wu.category AS update_category,
coalesce(cu.is_installed, 0) AS is_installed,
cu.install_date
FROM computers c
INNER JOIN computer_updates cu
ON c.computer_id = cu.computer_id
INNER JOIN wu_inventory wu
ON cu.wu_inventory_id = wu.wu_inventory_id
WHERE cu.is_installed = 0
ORDER BY c.computer_name, wu.title