none
WSUS 3.0 Custom Report - Public View SQL example

    Question


  • I need a report broken down by WSUS group membership then by Computer stating which updates are approved but not installed.

    The report needs to be executable from a scheduling system and deliverable via email to recipients.


    OS - Windows 2008R2, WSUS 3.0 SP2, Active Directory - Native Mode, Computers configured by GPO and configured to add themselves to pre-existing WSUS groups automatically based on GPO. There is one unique configuration GPO per Active Directory group. WSUS groups were created by importing Active Directory groups with a PowerShell command.


    No recent configuration changes, functionaility does not exist in current product.


    I've investigated the WinDB SUSDB Public Views and constructed a join statement to retrieve the  relevant fields. Then investigated installing the MSSQL Studio Express and using the sqlcmd command. And using the Windows Task Sceduler with an Xpath query to email a report on completion of a task that runs an sqlcmd query on a schedule.


    The following is a first pass at the procedure:


    sqlcmd -d SUSDB -i report.sql -o report.txt


    SET NOCOUNT ON
    SET ANSI_WARNINGS OFF

    SELECT     

    vCTG.Name AS Target_Group,
    vCT.Name AS Computer,
    vU.CreationDate AS [Microsoft_Released],
    vUA.CreationDate AS [WSUS_Approved],
    vUA.AdministratorName AS [Approved_By],
    vU.KnowledgebaseArticle AS [KB_Article],
    fSM.Name AS State,
    vU.SecurityBulletin AS [Security_Bulletin],
    vU.MsrcSeverity AS [Severity_Rating],
    vU.DefaultTitle AS Description

    FROM

    PUBLIC_VIEWS.vUpdateApproval AS vUA
    INNER JOIN
    PUBLIC_VIEWS.vUpdate AS vU ON vU.UpdateId = vUA.UpdateId
    INNER JOIN
    PUBLIC_VIEWS.vUpdateInstallationInfoBasic AS vUII ON vUII.UpdateId = vUA.UpdateId AND vUII.State = 2
    INNER JOIN
    PUBLIC_VIEWS.fnUpdateInstallationStateMap() AS fSM ON vUII.State = fSM.Id
    INNER JOIN
    PUBLIC_VIEWS.vComputerTargetGroup AS vCTG ON vUA.ComputerTargetGroupId = vCTG.ComputerTargetGroupId
    INNER JOIN
    PUBLIC_VIEWS.vComputerTarget AS vCT ON vUII.ComputerTargetId = vCT.ComputerTargetId

    WHERE

    (vCTG.Name = N'TargetGroup Servers')

    ORDER BY  Computer, [Microsoft_Released], [WSUS_Approved]


    The Xpath command for using the Windows Task Scheduler to email the report was trivial.


    I would appreciate commentary on whether the assumptions made when constructing the query are valid. And whether there is a simpler or more efficient way of extracting the information.


    Thank you for your time.



    • Edited by jwillis84 Monday, April 16, 2012 5:42 AM
    Monday, April 16, 2012 5:30 AM

Answers

  • Since you want to query the update from database. However this request is not officially supported.
    You can figure this out by yourself.

    Here is some information related to SQL query(Using WSUS View)
    http://msdn.microsoft.com/en-us/library/bb410149(VS.85).aspx


    Thanks
    Zero

    Hi jwillis84,

    I requested escalation to CTS, but it seems the request is out of their scope.
    According to the description, the question seems to be related to development. As we are mainly focus on the management issue and here is not the best support resource for development.

    So I suggest you initial a new thread in our MSDN forum:http://social.msdn.microsoft.com/Forums/en-US/categories/
    More information Using and Understanding APIs for WSUS:http://msdn.microsoft.com/en-us/library/ms744624(v=vs.85).aspx

    Thank you for your understanding.

    Clarence


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Marked as answer by jwillis84 Wednesday, April 18, 2012 9:42 PM
    Wednesday, April 18, 2012 9:31 AM
    Moderator

All replies

  • Hi,

    Thank you for posting here.

    I am trying to involve someone familiar with this topic to further look at this issue. There might be some time delay. Appreciate your patience.
     

    Regards,
    Clarence
    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contacttnmff@microsoft.com.


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Tuesday, April 17, 2012 9:14 AM
    Moderator
  • Hi,

    Thanks for looking at this.

    I've already spotted two invalid assumptions.

    One was I wasn't using the junction view to relate ComputerTarget to ComputerGroupMembership to ComputerTargetGroup.

    The other was assumption that the UpdateApproval view contains "all" UpdateApprovals - they are in fact (only) the explicit "UpdateApprovals" and do not include the inherited or "implicit" "UpdateApprovals".

    I believe the "UpdateEffectiveApprovalsPerComputer" contain both inherited and explicit update approvals.

    So I have adjusted the sql query here:

    SELECT vctg.Name AS ComputerTargetGroup, vct.Name AS ComputerTarget, vu.CreationDate AS [Microsoft_Released], vU.KnowledgebaseArticle AS [KB_Article], uism.Name AS State, vU.SecurityBulletin AS [Security_Bulletin], vU.MsrcSeverity AS [Severity_Rating], vU.DefaultTitle AS Description FROM [SUSDB].[PUBLIC_VIEWS].[vComputerTarget] AS vct INNER JOIN [SUSDB].[PUBLIC_VIEWS].[vComputerGroupMembership] AS vcgm ON vct.ComputerTargetId = vcgm.ComputerTargetId INNER JOIN [SUSDB].[PUBLIC_VIEWS].[vComputerTargetGroup] AS vctg ON vctg.ComputerTargetGroupId = vcgm.ComputerTargetGroupId AND vctg.Name LIKE 'Target Group' INNER JOIN [SUSDB].[PUBLIC_VIEWS].[vUpdateEffectiveApprovalPerComputer] AS vueapc ON vueapc.ComputerTargetId = vct.ComputerTargetId INNER JOIN [SUSDB].[PUBLIC_VIEWS].[vUpdate] AS vu ON vueapc.UpdateId = vu.UpdateId INNER JOIN [SUSDB].[PUBLIC_VIEWS].[vUpdateInstallationInfoBasic] AS vuiib ON vuiib.UpdateId = vueapc.UpdateId AND vuiib.State IN (2, 3, 5, 6) INNER JOIN [SUSDB].[PUBLIC_VIEWS].fnUpdateInstallationStateMap() AS uism ON vuiib.State = uism.Id -- WHERE -- vctg.Name LIKE 'Target Group' -- AND vuiib.State IN (2) ORDER BY ComputerTarget, [KB_Article], State

    However the problem remains the result set returned is far to large to be reasonable > 50000 updates

    Reducing the sql to return only computers in the target group returns only 71 computers, so the issue appears to be with another misunderstanding of what an Update entry is, or how to reduce the result set.

    I'm using the WSUS gui Computers, Target Group, Status -> Failed or Needed as a sanity check.

    When the query is close to accurate I would assume it would return a similar number of "Updates needed" times the number of computers in that group: example 12 x 71 = 852 which is far less than 53321

    Thanks for your attention.



    • Edited by jwillis84 Thursday, April 19, 2012 7:17 AM
    Tuesday, April 17, 2012 8:24 PM
  • Since you want to query the update from database. However this request is not officially supported.
    You can figure this out by yourself.

    Here is some information related to SQL query(Using WSUS View)
    http://msdn.microsoft.com/en-us/library/bb410149(VS.85).aspx


    Thanks
    Zero

    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.


    • Edited by Zero Xia Wednesday, April 18, 2012 9:10 AM
    Wednesday, April 18, 2012 9:09 AM
  • Since you want to query the update from database. However this request is not officially supported.
    You can figure this out by yourself.

    Here is some information related to SQL query(Using WSUS View)
    http://msdn.microsoft.com/en-us/library/bb410149(VS.85).aspx


    Thanks
    Zero

    Hi jwillis84,

    I requested escalation to CTS, but it seems the request is out of their scope.
    According to the description, the question seems to be related to development. As we are mainly focus on the management issue and here is not the best support resource for development.

    So I suggest you initial a new thread in our MSDN forum:http://social.msdn.microsoft.com/Forums/en-US/categories/
    More information Using and Understanding APIs for WSUS:http://msdn.microsoft.com/en-us/library/ms744624(v=vs.85).aspx

    Thank you for your understanding.

    Clarence


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Marked as answer by jwillis84 Wednesday, April 18, 2012 9:42 PM
    Wednesday, April 18, 2012 9:31 AM
    Moderator
  • Follow Up:


    I found an SQL query that accomplishes the goal here:


    Approved Security Update Compliance Report


    After two line changes it is returning the data from the Public Views as needed.


    Thanks




    • Edited by jwillis84 Thursday, April 19, 2012 8:26 AM
    Thursday, April 19, 2012 7:11 AM
  • Hi jwillis84,

    can you kindly help me with the query that outputs the following:

    ComputerName, IP Address, KB Article, Update Title, Update Classification (Only Security Updates) and Status(except unknown) -  For all the approved updates and for all downstream compters.

    Thursday, January 03, 2013 12:00 PM
  • I can take a look at it perhaps tomorrow night.

    We implemented something like that for our state network. It breaks out the reports by OU and emails the reports to each OU manager in each county once a month.

    That the WSUS group didn't promote it as a major feature is kind of disappointing.



    -- John Willis, Facebook: John-Willis, Skype: john.willis7416

    Monday, January 07, 2013 7:55 AM
  • thanks John for acknowledging the request.

    Will wait for you inputs.

    thank you.

    Wednesday, January 09, 2013 6:35 AM
  • Hi jwillis84,

    Do you have a copy of your modified report.sql script that you can share?

    Thursday, May 08, 2014 12:31 AM