Close Menu
    Facebook X (Twitter) Instagram
    Monday, June 23
    X (Twitter) LinkedIn
    All about Endpoint Management
    • Home
    All about Endpoint Management
    Home»SCCM 2007»SCCM Report Computers with Maintenance Window

    SCCM Report Computers with Maintenance Window

    Eswar KonetiBy Eswar KonetiNovember 26, 8:22 pm1 Min Read SCCM 2007 9,343 Views
    Share
    Facebook Twitter LinkedIn Reddit

    Here is another report today ,to list all the computers with their maintenance window setting and are part of which collection.

    The maintenance window information( collection ID,Description,time etc.) is available from SQL view called v_ServiceWindow.

    Below is the report to display all the computer with their maintenance window settings .You can also limit this collection to specific with the help of Prompts.

    select
    v_FullCollectionMembership.Name as Computername ,v_Collection.Name as CollectionName,
    v_ServiceWindow.Description as “Next Maintenance Window”
    from v_ServiceWindow
    inner join v_FullCollectionMembership on (v_FullCollectionMembership.CollectionID = v_ServiceWindow.CollectionID)
    inner join v_Collection on (v_Collection.CollectionID = v_FullCollectionMembership.CollectionID)
    order By v_Collection.Name

    Configmgr 2007 configmgr report Maintanance Window maintenance Window report for computers with maintanance windows SCCM SCCM 2007 sccm report for maintanance window SCCM Reports SMS SQL
    Share. Twitter LinkedIn Email Facebook Reddit

    Related Posts

    Optimize Your Intune Workflow with a Powerful Browser Extension

    March 22, 10:39 am

    Migrate Microsoft 365 Updates from SCCM/MECM to Intune for Co-Managed Devices

    February 11, 9:50 pm

    Investigating Co-Management Issues with Windows Endpoints in SCCM/Intune

    October 26, 10:45 pm

    21 Comments

    1. Manoj on May 17, 2023 11:37 PM

      is there a way to create a collection for server are not have maintenance window

      Reply
      • Eswar Koneti on June 26, 2023 7:01 AM

        Hi,
        Even though you apply the maintenance windows at a collection, they are actually applied at the client side.
        so client may be part of multiple collections and some of them may have maintenance window.
        by default when you create a collection, it will not have any maintenance window but client may be part of other collections that may be.
        there is no setting that can block you completely from having MW windows.

        Thanks,
        Eswar

        Reply
    2. Neal on August 3, 2016 3:07 AM

      When copy and pasting you must mnually type the "" marks in the following line

      "Next Maintenance Window"

      Reply
      • Eswar Koneti on August 3, 2016 11:07 PM

        Hi Neal,
        WordPress converts the original quotes to fancy quotes and you must correct it when running the query.

        thanks,
        Eswar

        Reply
    3. JP on November 6, 2015 4:53 AM

      How do you add the prompts for collection id?

      Reply
      • Eswar Koneti on November 7, 2015 8:57 AM

        SSRS ? create a dataset (either report builder or other tools like visual studio tools or Business intelligence etc) for the prompt that you used in SQL query ,go to parameters ,select the properties of the collection,change the available values ,you are done.

        Reply
    4. Shawn on July 30, 2014 2:41 AM

      Is there a way to get a report that tell what computers are NOT in a maintenance window?

      Reply
      • Eswar Koneti on August 12, 2014 11:52 AM

        try this report:

        SELECT
        A.Name0,
        MAX (B.SerialNumber0) AS 'Serialnumber',
        A.Manufacturer0,
        A.Model0, C.Name0 AS 'Processor',
        D.TotalPhysicalMemory0/1024 AS 'Memory (MB)',
        MAX (F.MACAddress0) AS 'MAC Adress',
        MAX (F.IPAddress0) AS 'IP Adress',
        G.AD_Site_Name0 AS 'AD Site',
        MAX (A.UserName0) AS 'Last user logged in',
        H.Caption0 AS 'Operating System',
        H.CSDVersion0 AS 'Service Pack',
        G.Creation_Date0 AS 'Creationdate in SMS',
        I.LastHWScan
        FROM
        v_GS_COMPUTER_SYSTEM A,
        v_GS_PC_BIOS B,
        v_GS_PROCESSOR C,
        v_GS_X86_PC_MEMORY D,
        v_GS_DISK E,
        v_GS_NETWORK_ADAPTER_CONFIGUR F,
        v_R_System G,
        v_GS_OPERATING_SYSTEM H,
        v_GS_WORKSTATION_STATUS I
        WHERE
        A.ResourceID = B.ResourceID AND
        A.ResourceID = C.ResourceID AND
        A.ResourceID = D.ResourceID AND
        A.ResourceID = E.ResourceID AND
        A.ResourceID = F.ResourceID AND
        A.ResourceID = G.ResourceID AND
        A.ResourceID = H.ResourceID AND
        A.ResourceID = I.ResourceID
        and A.name0 not in ( select FCM.Name as Computername From v_ServiceWindow SW,v_FullCollectionMembership FCM
        where sw.CollectionID=fcm.CollectionID)
        GROUP BY A.Name0, A.Manufacturer0, A.Model0, C.Name0, D.TotalPhysicalMemory0, G.AD_Site_Name0, A.UserName0, H.Caption0, H.CSDVersion0, G.Creation_Date0, I.LastHWScan

        Reply
    5. VR on July 25, 2014 10:00 PM

      That start time is the problem. it is not reporting right. If you compare the service window manager log on the client, it is not same as what is resported as the start time in this report. There is also canned report under software distribution -->maintenace window applied to client. I have opened case with MS to get this resolved, i will keep you all posted. I am not sure if it just for us or to all.

      Reply
      • Eswar Koneti on August 12, 2014 11:33 AM

        in the report,you have column called 'Next maintenance window' which is start time if not wrong and it will tell you the next available maintenance window.
        what did MS says on this ?

        Reply
    6. VR on July 3, 2014 1:59 AM

      i tried adding the start time from v_servicewindow, it doesn't seem to be correct. Actually there is canned report software Distribution -collections --> Maintenance windows available to a specified client. The start time doesn't seem to correct in this report.

      Reply
    7. VR on July 3, 2014 1:57 AM

      ok, it now worked for me, i had to try without quotes everywhere. is it possible to get the start time?

      Reply
      • Eswar Koneti on July 8, 2014 11:09 AM

        you can try adding v_ServiceWindow.starttime.

        Reply
    8. VR on July 3, 2014 1:44 AM

      I just tried this report, i get Incorrect syntax near the keyword 'as' on line 1. i tried manipulating quotes with & without. No worky yet.

      Reply
      • Eswar Koneti on July 7, 2014 7:27 PM

        I did fix to the blog.All quires in blog should be working without changing quotes.

        Reply
    9. Joe on June 26, 2014 9:10 AM

      Hi Eswar,

      I run in SQL Management Studio and an error occurred when executed the command " Msg 208, Level 16, State 1, Line 1
      Invalid object name 'v_ServiceWindow'.

      Cheers

      Reply
      • Eswar Koneti on July 1, 2014 12:45 PM

        It works for me .I just tried replacing the quotes for Next Maintenance Window ,nothing else.

        Reply
    10. Joe on June 25, 2014 11:26 AM

      I copied and pasted the query above to the custom query on sccm 2012 R2 and received an error "The query has a syntax error. Are you sure you want to save it?" I also replace the quotes ' and " but error is still occurred.
      Need an advise, please?

      Reply
      • Eswar Koneti on June 25, 2014 1:06 PM

        if it says,the query has a syntax error,then there must be typo error(Quotation marks) in your report. can you try to run this in SQL Management Studio.
        It works fine for me.

        Reply
    11. SCCM Guy on April 2, 2014 2:31 AM

      Query is broke, get a SQL error when trying to use in SCCM 2012

      Reply
      • Eswar Koneti on April 2, 2014 5:09 AM

        What is not working for you? Syntax errors? You need to replace the quotes ' and " as blog convert them to fancy in utf mode.

        Reply

    Leave a ReplyCancel reply

    This site uses Akismet to reduce spam. Learn how your comment data is processed.

    Sign Up

    Get email notifications for new posts.

    Author

    I’m Eswar Koneti ,a tech enthusiast, security advocate, and your guide to Microsoft Intune and Modern Device Management. My goal? To turn complex tech into actionable insights for a streamlined management experience. Let’s navigate this journey together!

    Support

    Awards

    Archives

    © Copyright 2009-2024 Eswar Koneti, All rights reserved.

    Type above and press Enter to search. Press Esc to cancel.