locked
Identify New Products Recently Added RRS feed

  • Question

  • Occasionally I'll receive a notice in WSUS that new products have been added, but they aren't flagged in any way when searching through the several-hundred-item-long list of products. Anyone know of a way to identify the newly added products?


    Shaun

    Tuesday, April 8, 2014 1:38 PM

Answers

  • Some creative queries on the database can uncover this information.

    There's a PUBLIC_VIEW, vCategory, that can give us this information, but it's constrained by knowing the GUID (vCategory.CategoryID) to pull the correct row(s) from that view.

    The table dbo.tbCategory can help provide the GUID. It has an IDENTITY field, plus a LastChange field, that allows us to extract the GUID for the entry from another (non public) view: dbo.vwCategoryProperties which contains a Foreign Key from dbo.tbCategory.

    So...

    SELECT v.CategoryID, v.CategoryType, v.DefaultTitle, v.DefaultDescription
    FROM dbo.tbCategory c
    JOIN dbo.vwCategoryProperties cp
      ON cp.LocalUpdateID = c.CategoryID
    JOIN PUBLIC_VIEWS.vCategory
      ON v.CategoryID = cp.UpdateID
    ORDER BY c.CategoryIndex DESC

    will produce a list, in order most recent first, of all Product Families and Product Categories added to WSUS.

    And we find the following recently added items:

    • Product Family: Windows Azure
    • Product: Windows Azure Hyper-V Recovery Manager Provider

    Lawrence Garvin, M.S., MCSA, MCITP:EA, MCDBA
    SolarWinds Head Geek
    Microsoft MVP - Software Packaging, Deployment & Servicing (2005-2014)
    My MVP Profile: http://mvp.microsoft.com/en-us/mvp/Lawrence%20R%20Garvin-32101
    http://www.solarwinds.com/gotmicrosoft
    The views expressed on this post are mine and do not necessarily reflect the views of SolarWinds.

    • Marked as answer by Shaunm001 Monday, April 14, 2014 2:41 PM
    Thursday, April 10, 2014 5:19 PM

All replies

  • Hello,


    Sometimes you'll find new products announcement over the http://blogs.technet.com/b/wsus/

    The only other way I have found is to manually go through the list, but that can be painful at times.

    TiGrOu.



    • Edited by Brice Pradel Tuesday, April 8, 2014 3:04 PM duplicate c/p
    Tuesday, April 8, 2014 3:04 PM
  • Some creative queries on the database can uncover this information.

    There's a PUBLIC_VIEW, vCategory, that can give us this information, but it's constrained by knowing the GUID (vCategory.CategoryID) to pull the correct row(s) from that view.

    The table dbo.tbCategory can help provide the GUID. It has an IDENTITY field, plus a LastChange field, that allows us to extract the GUID for the entry from another (non public) view: dbo.vwCategoryProperties which contains a Foreign Key from dbo.tbCategory.

    So...

    SELECT v.CategoryID, v.CategoryType, v.DefaultTitle, v.DefaultDescription
    FROM dbo.tbCategory c
    JOIN dbo.vwCategoryProperties cp
      ON cp.LocalUpdateID = c.CategoryID
    JOIN PUBLIC_VIEWS.vCategory
      ON v.CategoryID = cp.UpdateID
    ORDER BY c.CategoryIndex DESC

    will produce a list, in order most recent first, of all Product Families and Product Categories added to WSUS.

    And we find the following recently added items:

    • Product Family: Windows Azure
    • Product: Windows Azure Hyper-V Recovery Manager Provider

    Lawrence Garvin, M.S., MCSA, MCITP:EA, MCDBA
    SolarWinds Head Geek
    Microsoft MVP - Software Packaging, Deployment & Servicing (2005-2014)
    My MVP Profile: http://mvp.microsoft.com/en-us/mvp/Lawrence%20R%20Garvin-32101
    http://www.solarwinds.com/gotmicrosoft
    The views expressed on this post are mine and do not necessarily reflect the views of SolarWinds.

    • Marked as answer by Shaunm001 Monday, April 14, 2014 2:41 PM
    Thursday, April 10, 2014 5:19 PM
  • Thanks Lawrence, this is great. Your SQL query didn't work for me exactly as written above, but I was able to get the information I needed using the query below:

    SELECT c.CategoryID, c.CategoryType, v.DefaultTitle, v.DefaultDescription, c.LastChange
    FROM dbo.tbCategory c
    JOIN dbo.vwCategoryProperties cp
      ON cp.LocalUpdateID = c.CategoryID
    JOIN PUBLIC_VIEWS.vCategory v
      ON v.CategoryID = cp.UpdateID
    ORDER BY c.CategoryIndex DESC


    Shaun

    Monday, April 14, 2014 2:41 PM
  • Your SQL query didn't work for me exactly as written above

    Arghh.. I left the alias off of the public view JOIN.

    The corrected query is:

    SELECT v.CategoryID, v.CategoryType, v.DefaultTitle, v.DefaultDescription
    FROM dbo.tbCategory c
    JOIN dbo.vwCategoryProperties cp
     
    ON cp.LocalUpdateID = c.CategoryID
    JOIN PUBLIC_VIEWS.vCategory v
     
    ON v.CategoryID = cp.UpdateID
    ORDER BY c.CategoryIndex DESC


    Lawrence Garvin, M.S., MCSA, MCITP:EA, MCDBA
    SolarWinds Head Geek
    Microsoft MVP - Software Packaging, Deployment & Servicing (2005-2014)
    My MVP Profile: http://mvp.microsoft.com/en-us/mvp/Lawrence%20R%20Garvin-32101
    http://www.solarwinds.com/gotmicrosoft
    The views expressed on this post are mine and do not necessarily reflect the views of SolarWinds.

    Monday, April 14, 2014 11:26 PM
  • Try the following powershell - note that this is written to run on server. (local connection specified)

    [reflection.assembly]::LoadWithPartialName("Microsoft.UpdateServices.Administration") | out-null
    
    if (!$wsus) {
                 $wsus = [Microsoft.UpdateServices.Administration.AdminProxy]::GetUpdateServer();
    }
    
    $wsus.GetUpdateClassifications("$((Get-Date).AddDays(-31))","$(Get-Date)") | select Title, Description, Releasenotes, arrivaldate
    $wsus.GetUpdateCategories("$((Get-Date).AddDays(-31))","$(Get-Date)") | select Title, Description, Releasenotes, arrivaldate


    • Edited by Mark__B Tuesday, March 10, 2015 9:16 PM Fix typo
    • Proposed as answer by G Ramsey Friday, November 20, 2015 3:51 PM
    Tuesday, March 10, 2015 9:16 PM