Collection Query to find No Maintenance Window

[Lagamorph]

Hi all,

I've been tasked with creating a collection in SCCM that will run a query and add any servers with no maintenance window configured to the collection. This will allow us to then target those machines to get them added to the maintenance windows we want. Unfortunately building SCCM queries is completely new to me so I'm not really sure where to begin. The query builder is somewhat overwhelming with how many options there are available when presented with the Select Attribute screen.

I have been direct to this query that was posted on another website,

SELECT sys.Name0 'Name', v_R_System.Operating_System_Name_and0 'Operating System'

FROM v_ServiceWindow AS sw

INNER JOIN v_FullCollectionMembership AS fcm ON sw.CollectionID = fcm.CollectionID

RIGHT JOIN v_R_System AS sys ON fcm.ResourceID = sys.ResourceID

WHERE sw.Name is NULL AND sys.Client0 = 1

ORDER BY sys.Name0


But putting that into the Query Language returns a syntax error and from looking at the date I believe it may have been written for Config Manager 2007 and isn't compatible with 2012.

 

Can anyone offer some advice on where to even start with creating a query to do what I need?

[GarthMJ]

11 hours ago, Lagamorph said:

I've been tasked with creating a collection in SCCM that will run a query and add any servers with no maintenance window configured to the collection. This will allow us to then target those machines to get them added to the maintenance windows we want. Unfortunately building SCCM queries is completely new to me so I'm not really sure where to begin. The query builder is somewhat overwhelming with how many options there are available when presented with the Select Attribute screen.

I have been direct to this query that was posted on another website,

SELECT sys.Name0 'Name', v_R_System.Operating_System_Name_and0 'Operating System'

FROM v_ServiceWindow AS sw

INNER JOIN v_FullCollectionMembership AS fcm ON sw.CollectionID = fcm.CollectionID

RIGHT JOIN v_R_System AS sys ON fcm.ResourceID = sys.ResourceID

WHERE sw.Name is NULL AND sys.Client0 = 1

ORDER BY sys.Name0


 

Did you ask the original query write ,why you are having problems? AKA why post it here, instead of ask the original person? Why didn't you post a link to the other post?

 

[Lagamorph]

On 2017-6-27 at 10:07 PM, GarthMJ said:

Did you ask the original query write ,why you are having problems? AKA why post it here, instead of ask the original person? Why didn't you post a link to the other post?

 

It was posted to a website back in 2010,
http://www.madanmohan.com/2010/11/how-to-find-clients-with-no-maintenance.html
And the writer hasn't updated since 2013.

 

The problem I'm having is if I paste that query into the Query language of the SCCM query rules, it just comes back with Syntax errors. As I say I've virtually no experience with writing queries and don't know where to begin with debugging it, or how to build a similar query using the query builder as I've no idea which options I'd need to be selecting.

 

EDIT - Found where I was going wrong. The queries I've been finding are SQL queries so are made for an SCCM report rather than a Collection Membership Rule query. It doesn't look like what I want to do is possible to do directly with a collection, but I've got a Report that gives me the output I need and can then use to do what we need to do.

[Magdann]

Hi all,

Sorry, this thread is old but i will add my 2 cts because i wanted to create a collection of all workstation without maintenance windows.
And for now i couldn't create it because after checking (with wmi explorer) sms_servicewindow, that table does not contain CollectionID proprietie. And my table is empty (also we have almost 380 MW)

Soooo. Still searching to do it

[Mark D]

SELECT sys.Name0 'Name', sys.Operating_System_Name_and0 'Operating System'
FROM v_ServiceWindow AS sw
INNER JOIN v_FullCollectionMembership AS fcm ON sw.CollectionID = fcm.CollectionID
RIGHT JOIN v_R_System AS sys ON fcm.ResourceID = sys.ResourceID
WHERE sw.Name is NULL AND sys.Client0 = 1
ORDER BY sys.Name0