Welcome to the community forums for Theopenem. All responses are provided by other users that wish to help out. Theopenem will not respond to these posts. If you require more assistance than what the community can provide, we offer various paid support options.

Windows Update Global Report


  • Hi All-
    I'd like to generate a report that will show me the status of all windows updates on all machines- a Windows Update Compliance report of sorts, similar to what is in Intune. Is this possible with Toems? If not, is the data available in the SQL database in a queryable format so I can generate my own report?
    Thanks,
    BW


  • The built in reports won't be able to generate this. You can do it manually, the 3 tables you need are:

    computers
    computer_updates
    wu_inventory


  • @theopenem_admin
    Fantastic, thank you!


  • @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