Symantec Management Platform (Notification Server)

 View Only
  • 1.  The "Microsoft Compliance and Vulnerability by Update - Details" report fails.

    Posted Dec 10, 2009 10:36 AM

    <o p=""> </o><o p=""> </o>

    Hi everyone, please could you help me with this issue?
    We are trying to execute the report I mentioned above. Parameters we are using are:

    Report: Microsoft Compliance and Vulnerability by Update - Details <o p=""></o>

    Folder: <o p=""></o>

    \Reports\Software Management\Patch Management\Compliance and Vulnerability\<o p=""></o>


    - Collection: All computers (or any other collection with over 200 machines)
    - Release Date From 01/01/2008
    - Release Date To: 10/12/2009
    - Severity: by default
    - Custom Severity: by default
    - Distribution Status: by default
    - Supersedence Status: by default
    - Operating system: by default
     
    NS Version: 6.0.6074
    MS SQL Server 2005
    MS Windows Server 2003 R2 SP2 Enterprise ED.

    When we execute this report, it fails if the collection targeted have more than 200 machines. It takes over 10 minutes to release and finally this error appears:

    Sql error in query. Error: System.Data.SqlClient.SqlError: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.Sql CommandText: -- MAX ROWCOUNT SET ROWCOUNT 50000 -- exec spPMWindows_ComplianceByUpdate_Details @SupersedenceStatus = 'non-superseded', @OperatingSystem = '%', @DistributionStatus = 'active', @CollectionGuid = '9c56ee5b-4798-422d-9a4d-b935e98d6230', @VendorSeverity = 'Critical', @CustomSeverity = '%', @StartDate = @param1, @EndDate = @param2, @SWBGuid = '%', @pCulture = 'en-US'

    If the collection contains less than 200 computers, it works correctly (although It takes around 10 minutes), but over this quantity (250, 300, 1000 or whatever), it fails.
    "All computers" collection have around 4500 machines and we need to take that information (what the report shows) about all of this computers (it could be bothers running the report by step of 200 computers every time and also creating this collections of course)

    Do  you have any idea of this?
    What do you advice me?

    Regards

    David.
    <o p=""></o>



  • 2.  RE: The "Microsoft Compliance and Vulnerability by Update - Details" report fails.

    Posted Dec 10, 2009 11:49 AM
    This may indicate other issues with your database, and you may want to investigate the defrag and optimization route (for SQL 2005 maintenance plans).

    However, you can set this timeout (by default it is 600 seconds) on the database configuration settings on the Configuration -> Server Settings - Notification Server Settings -> Database Settings  (Command Timeout).

    I would also import the reports that look at the following:

    Table Size By Solution (look for anything that appears overly large)

    Database Fragmentation


  • 3.  RE: The "Microsoft Compliance and Vulnerability by Update - Details" report fails.

    Broadcom Employee
    Posted Dec 10, 2009 11:57 AM
    Check the following KB article which contains a good number of report fixes into a single update:

    https://kb.altiris.com/article.asp?article=46144&p=1


  • 4.  RE: The "Microsoft Compliance and Vulnerability by Update - Details" report fails.

    Posted Dec 10, 2009 01:00 PM
    OK guys, thanks you for answer me, I will do what you say and then I will tell you about.
    Bye.


  • 5.  RE: The "Microsoft Compliance and Vulnerability by Update - Details" report fails.

    Posted Dec 14, 2009 01:44 PM
     You may have problems even after you apply the fixes the KB suggests.

    Another thing you may want to look at is your SQL environment.  Are you running SQL on the same box as the NS?  How powerful is this box?  At one point we had our database on an older SQL 2000 cluster and was sharing resources with about 130 other (smaller) databases and we had all sorts of issues getting bigger queries to run within the 10 minute window before it timed out.

    The stored procedure that drives that report is nearly 10 pages long and I've been told by one person "in the know" at Altiris on the patch management team that the updated query is as efficient as it can get.

    There's an additional KB that will let you "drill into" the details report from the non-details report at  https://kb.altiris.com/article.asp?article=44351&p=1 that may help you out.