SCCM Report Computers with Maintenance Window

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

19 Responses to "SCCM Report Computers with Maintenance Window"

    1. 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
    1. 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
  1. 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
    1. 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
  2. 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
  3. 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
  4. 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
  5. 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
    1. 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

Leave a Reply