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
When copy and pasting you must mnually type the "" marks in the following line
"Next Maintenance Window"
Hi Neal,
WordPress converts the original quotes to fancy quotes and you must correct it when running the query.
thanks,
Eswar
How do you add the prompts for collection id?
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.
Is there a way to get a report that tell what computers are NOT in a maintenance window?
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
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.
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 ?
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.
ok, it now worked for me, i had to try without quotes everywhere. is it possible to get the start time?
you can try adding v_ServiceWindow.starttime.
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.
I did fix to the blog.All quires in blog should be working without changing quotes.
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
It works for me .I just tried replacing the quotes for Next Maintenance Window ,nothing else.
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?
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.
Query is broke, get a SQL error when trying to use in SCCM 2012
What is not working for you? Syntax errors? You need to replace the quotes ' and " as blog convert them to fancy in utf mode.