How To: Create A Collection for Each AD Site in the Current Forest

August 14, 2012 Leave a comment

Here’s an example of leveraging PowerShell to query AD to find all Active Directory Sites, and then create a collection for each one (unless it already exists) in ConfigMgr 2012:

#update Site Server and Namespace information for SMS Provider
$Server = "myServer"
$NameSpace = "root\sms\site_LAB"
#enter collectionID that will be used as the limiting collection for the new collections.
$CollectionLimiter = "LAB00018"

function CreateCollectionQueryRule($Coll, $Server, $Namespace, $RuleName, $WQL)
{
#function used to create the rule for the query, based on QueryID.
$wmiclass = "\\" + $Server + "\" + $NameSpace + ":sms_CollectionRuleQuery"
$wmiclass
$CollQuery = ([wmiclass] $wmiclass).CreateInstance()
$collquery.QueryExpression = $WQL
$CollQuery.RuleName = $RuleName
$coll = gwmi sms_collection -namespace $Namespace -computer $server | where-object { $_.CollectionID -eq $coll }
$coll.AddMembershipRule($CollQuery)
}


#Find ADSites in the current forest
$forest = [System.DirectoryServices.ActiveDirectory.Forest]::GetCurrentForest() 
$ADSites = $forest.get_Sites()

$ADSites | foreach-object {
#see if the collection already exists
$strFilter = "Name='" + $_ + "'"
if ((gwmi sms_collection -namespace $NameSpace -computername $Server -filter $strfilter| measure).count -eq 1) {
"Collection {0} already exists!" -f $_
}

else {
#Collection doesn't exist, create new collection and rule now
$strWQL = "Select resourceid from SMS_R_System where SMS_R_System.ADSiteName = '$_'"
$Path = "\\" + $Server + "\" + $NameSpace + ":SMS_Collection"
$coll = ([wmiclass] $path).CreateInstance()
$coll.Name = $_.Tostring()
$coll.LimitToCollectionID=$collectionLimiter
#Create refresh schedule - run once a week on Saturdays
$Path = "\\" + $server + "\" + $Namespace + ":SMS_ST_RecurInterval"
$refreshSchedule = ([wmiclass] $path).CreateInstance()
$refreshSchedule.DaySpan = 7
$refreshSchedule.StartTime = "20120804060000.000000+***"
$coll.RefreshSchedule = $refreshSchedule
$coll.RefreshType = 2 #use a value of 6 if you want to enable dynamic collections
#save the collection
$coll.Put()
$coll
#Create the collection Rule
CreateCollectionQueryRule $Coll.CollectionID $Server $Namespace $_.Name.Tostring() $strWQL
}
}

Download CreateCollectionsBasedOnADSites.ps1

Filed under ConfigMgr 2012, PowerShell

Using DateDiff and GetDate in WQL for SMS and ConfigMgr Queries and Collections

August 2, 2012 Leave a comment

Another classic from my old blog (flash back to 2007) – I had to use DateDiff and GetDate in my new ConfigMgr 2012 infrastructure today, so I thought I’d re-post on my new blog…

Queries and collections based on time stamps with DateDiff and GetDate – That’s the best thing that’s happened to collections since. . .well, as long as I can remember. I use these every day, and you should too. A couple years ago, my good friend Warren Byle ran across the following bullet in the SMS 2003 SDK, in a section titled “Extended WMI Query Language”:

  • In SMS 2003 the WHERE clause now supports GetDate(), DateDiff(), and DateAdd().

That’s it. . one small bullet, yet it adds so much functionality to SMS, and makes writing some of my collections much easier, and very low maintenance.

From our book, SMS 2003 Recipes: A Problem-Solution Approach, recipe 9-27 Creating a Date-Based Query:

SMS 2003 supports the following date functions in the WHERE clause of WQL queries.

GetDate(): This function will return the current date and time on the system. The data is returned in date-time format (e.g., 12:56 AM 12/02/2006).

DateDiff(): This function will return the difference between two date-time values in the increment that you specify (e.g., minute, hour, day). In our testing, the DateParts listed in Table 9-1 are supported when using DateDiff in a WHERE clause in SMS WQL.

• DateDiff ( DatePart, StartDate, EndDate )

• DatePart is the part of the date you want to calculate (e.g., minute, day, month, etc.).

• StartDate is the begin date.

• EndDate is the ending date.

DateAdd(): Returns a new date-time value based on adding an interval to the specified date. In our testing, the DateParts listed in Table 9-1 are supported when using DateAdd in a WHERE clause in SMS WQL. The proper syntax for DateAdd follows:

• DateAdd ( DatePart, Number, Date )

• DatePart is the part of the date you want to calculate (e.g., minute, day, month, etc.).

• Number is the value to increment DatePart.

• Date is a valid date-time value that will be used to calculate the new date.

Note In SMS WQL queries, never put the abbreviation in quotes—this is different than when using DateParts with date functions in VBScript.

Also mentioned in this section is GetDate(), which simply returns the current date-time from the server.

And now for some examples:

All systems that have reported a LastHardwareScan date within the last 30 days:

select *  from  SMS_R_System inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceId = SMS_R_System.ResourceId where DATEDIFF(dd,SMS_G_System_WORKSTATION_STATUS.LastHardwareScan,GetDate()) < 30

All systems that have been discovered since midnight:

SELECT SMS_R_System.ResourceID,SMS_R_System.ResourceType,SMS_R_System.Name,  SMS_R_System.SMSUniqueIdentifier,  SMS_R_System.ResourceDomainORWorkgroup,SMS_R_System.Client FROM SMS_R_System WHERE (DateDiff(day, CreationDate, GetDate()) < 1)

Combine these queries with the power of setting the query update interval (by default is 24 hours), and you have a very dynamic query to help you with troubleshooting.  You could also combine my previous blog post on How to Create a Collection Based on Advertisement Status, using DateDiff and the LastStatusTime property from the SMS_ClientAdvertisementStatus class.

Filed under ConfigMgr 2007, ConfigMgr 2012 Tagged with

← Older posts

Newer posts →