Jump to content


Lagamorph

Collection Query to find No Maintenance Window

Recommended Posts

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?

Share this post


Link to post
Share on other sites

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?

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 :)

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.