Create SCCM collection based from Computers that ran Program during last X days

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 inner join SMS_MonthlyUsageSummary on SMS_R_SYSTEM.ResourceID = SMS_MonthlyUsageSummary.ResourceID    INNER JOIN SMS_MeteredFiles ON SMS_MonthlyUsageSummary.FileID = SMS_MeteredFiles.MeteredFileID    WHERE DateDiff(day, SMS_MonthlyUsageSummary.LastUsage, GetDate()) < 90 AND SMS_MeteredFiles.RuleID = XXXXX

SMS_MeteredFiles.RuleID   is the internal Software Metering rule ID

Also for collection based on software metering or how to create a collection on computers that did not run a pgroam/software during the last X days, here are intersting notes :

merge text files without duplicating

$local_path = "C:\Windows\System32\drivers\etc\services"
$server_path = "$dirFiles\services"

# This defines an empty array that will hold the contents
#of each of the files
$final_file = @()

# These two If blocks first use Test-Path to see if the file
# exists. If the file exists -- the local one and then the
# server one -- it continues.

If (Test-Path -Path $local_path) {
# This for each loops through the contents of the file
# (Get-Content) and stores each line of the file in
# a variable called line. Then that line is added
# to the array we created above ($final_file += $line).

ForEach ($line in (Get-Content -Path $local_path)) {
$final_file += $line

If (Test-Path -Path $server_path) {

ForEach ($line in (Get-Content -Path $server_path)) {
$final_file += $line

# This redefines the array with only unique values.
$final_file = $final_file | Select-Object -Unique

# the encoding of the services file is UTF8 without
# the byte order mark. If you make a file with the
# byte order mark, it will remove an entry. At least,
# that is what it did for me. So this is a call to
# a couple of .Net functions that -- the only way to
# my understanding -- to do that encoding in powershell.

$UTF8_No_BOM = New-Object System.Text.UTF8Encoding($False)

Inspired from :

(GPO) Track what Policy is modifying a registry key or setting

1. Open a Windows Explorer window on your domain’s Sysvol folder. This will be something like “\\\Sysvol\”

2. In the Explorer Search field in this window, type the name of the registry value you are working with (in my case “passwordexpirywarning”)


3. Press Enter and wait for the search to complete – it won’t find anything, so you will then need to click Search in File Contents. Once the search completes, you should have one or more GptTmpl.inf files returned in the results, as shown below:


4. Open each of these files in turn in Notepad (or your preferred text editor), and search each for the registry value in question:


In the above screenshot, you can see that this is a “candidate” GPO as it is setting “passwordexpirywarning” to 0. Ignore any files that don’t correspond to the value you are interested in

5. For each of the corresponding GptTmpl.inf files that contain the value in question, obtain the GPO GUID (Globally Unique Identifier) by inspecting the properties of the GptTmpl.inf file, as shown below, and copying and pasting the GUID part of the path, EXCLUDING the curly brackets {} into a temporary text file:


The aim of this is to build up a list of GUIDs for candidate GPOs

6. Now, if you are not already on a Windows 2008 R2 server with PowerShell and the Group Policy Management Console (GPMC) installed, log on to one, and open a PowerShell session

7. In the PowerShell session, type the following command:



where <GUID> is the first GUID you collected in the text file above, for example

Get-GPO -GUID AD77FD0E-3E55-4B7B-AD7A-2C6B4E680F80

This should return the display name of the GPO corresponding to this GUID


Source :


Quick SCCM 2012 R2 Lab

This post will depict how I setup an “ultra-quick & dirty” SCCM 2012 R2 Lab in Hyper-V for my test purposes.

Sources come from personal experience, Microsoft Technet and the excellent tutorial :

Main server will have roles of AD/DHCP/DNS/SCCM/SQL all-in one!

Prepare Lab

  1. Create a Virtual switch called “PRIVATE” as a private network (for VMs interconnection), and another called “EXTERNAL” linked to your physical host card (must distribute DHCP) as an external network (for internet access)
  2. Create a new VM with 2 network cards, each one connected to the virtual switches and install Windows Server 2012 R2 Standard with GUI.
  3. Rename computername to SCCM
  4. Change Password setting of Administrator to never expire (to prevent being bothered later on)
  5. Install all updates until nothing else is needed (I recommend installing the latest version of .NET framework  first (4.6.1 as of today) to prevent installing unecessary updates)
  6. Add .NET 3.5 feature (Remember to point to your DVD \Sources\SxS  folder in “Specify Alternate Source”)
  7. Install updates


Configure Network 

  1. Go to Network Connections, go to the Properties of the card connected to PRIVATE.

  2. Disable IPv6. Double-click Internet Protocol Version 4 (TCP/IPv4).

  3. IP address :  Preferred DNS server : Subnet mask :

  4. It is not necessary to provide an entry in Default gateway.Click Validate upon exit
  5. Cancel the network troubleshooting window

Configure AD/DHCP/DNS

  1. In the Add Roles and Features Wizard,  when you are prompted to add required features, select Active Directory Domain Services, DHCP Server, DNS Server checkbox.Validate

  2. click Promote this server to a domain controller
  3. Select Add a new forest. Enter domain.local

  4. Enter the desired DSRM password. Complete config

  5. Wait for restart

  6. After restart, go to server manager , and click the yellow notification flag
  7. Click Complete DHCP configuration
  8. Select Use the following users’s credentials (should be the domain admin). Click Commit
  9. Go to the DHCP mmc
  10. Right-click IPv4 and then click New Scope
  11. Type a name for the new scope
  12. In IP Address Range,  Start IP address :, End IP address : ,Length : 24  . The value of Subnet mask will change automatically to
  13. Click Next and then in Lease Duration under Limited to enter 8 Days, 0 Hours, and 0 Minutes

  14. Click Next. Select Yes, I want to configure these options now

  15. Add as router (Default Gateway)
  16. In Domain Name and DNS Servers, verify that the Parent domain is domain.local and is listed as the only DNS server.
  17. Click Next twice, and then in Activate Scope select Yes, I want to activate this scope now.


Setup SCCM Pre-requisites : AD Schema extension

  1. Open ADSI Edit from the Server Manager Tools
  2. If not automatically  connected to your DC (sccm.domain.local) : Click on Connect to. On the Connection Settings window, the naming context should be Default naming context. Do not change anything here, click on OK.
  3. In the ADSI Edit Console, expand the Default Naming Context, right click CN=System, click on New and create an Object.
  4. On the Create Object windows, select the class named container and click on Next
  5. In value enter  System Management . Click on Next and click on Finish to close the wizard. Close ADSI Edit.
  6. Open Active Directory Users and Computers from the Server Manager Tools
  7. Click on View and click Advanced Features.
  8. Expand Domain.local , expand System, right click System Management and click on Delegate Control.
  9. Click on Add, on select users,computers or groups window click on Object Types and check for Computers as object types. Click on OK. Type the name of the primary site server computer account : sccm and click on OK.You must see the primary site server computer account listed under the users or groups. Click on Next.
  10. On the Tasks to Delegate page, click on Create a custom task to delegate. Click on Next.
  11. On the Active Directory Object Type window, select the option This folder, existing objects in this folder and creation of new objects in this folder. Click on Next.
  12. Select General, Property Specific and Creation/deletion of specific child objects. Under the permissions, click on Full Control. when you check the box for Full Control all the other permissions gets checked automatically. Click on Next and click on Finish to close the wizard
  13. Insert/Mount SCCM 2012 Media
  14. Open a command prompt as admin. Execute “<the media drive> \SMSSETUP\BIN\X64\extadsch.exe”
  15. You should see the line “Successfully extended the Active Directory Schema”.If not, open the log file extadsch.log located in the root of the system drive.


Setup SCCM Pre-requisites : Features

  1. In Server Manager, go to Add Roles and Features
  2. In Server Roles, Check Web Server (IIS) and click Next
  3. Then in Features select :
    1.  .Net Framework 3.5 Features [Install all sub features]
    2.  .Net Framework 4.5 Features [Install all sub features]
    3.  BITS
    4. Remote Differential Compression and click Next
  4. In IIS Roles services activate:
    1.  Common HTTP Features – Default Document, Static Content
    2. Security – Windows Authentication
    3. Application Development – ASP.NET 3.5, .NET Extensibility 3.5, ASP.NET 4.5, .NET Extensibility 4.5, ISAPI extensions
    4. IIS 6 Management Compatibility – IIS Management Console, IIS 6 Metabase Compatibility, IIS 6 WMI Compatibility, IIS Management Scripts and Tools
  5. Click Install
  6. Do a round of Windows updates🙂


Setup SCCM Pre-requisites : ADK 8.1

  1. Download & run the Windows ADK for Windows 8.1
  2. Select Deployment ToolsWindows Preinstallation Environment and User State Migration Tool. Click on Install


Setup SCCM Pre-requisites : SQL 2012 Std SP1

  1. Select Installation in the right menu
  2. Click New SQL Server stand-alone …..
  3. Click Ok
  4. Enter Product key (if you have one)
  5. Accept License Terms and click Next
  6. Accept Upgrades and click Next
  7. Validate the Setup Support Rules by clicking Next
  8. Select  SQL Server Feature Installation and click Next
  9. On the Feature Selection window, select Database Engine Services, Reporting Services-Native and Management Tools – Complete
  10. Click  Next
  11. For Instance Configuration click on Default Instance,Click  Next
  12. Click  Next
  13. In service account section, change all accounts to the domain adminstrator and password  (except SQL SErver browser which is greyed) , and change all Statrup Type to Automatic.
  14. In Collation Tab, Select Customize, then SQL collation, used for backaward compatibilty , change Collation to : SQL_Latin1_General_CP1_CI_AS. Click Next
  15. Click Add curent user to add the current user (domain Admin). Click Next
  16. Click on Install and Configure. Click on Next. Click on Next. Install
  17. Let install process… Click Close
  18. Launch the SQL Server Management Studio. Login to the server and right click the Server and click on Properties.
  19. Click on Memory, set Minimum server memory value to 1024 MB (The minimum value can be less) and set Maximum server memory to 4096 MB. Note : these values prevent SQL Server from taking all the RAM space, but we put low values because of lab purposes, if you plan to have many clients 8192 Minum would be recommended
  20. Validate and Close


Setup SCCM Pre-requisites : WSUS Role

  1. In Server Manager, go to Add Roles and Features
  2. In Server Roles, Check WSUS and click Next twice
  3. Choose WSUS Services and Database. Click on Next
  4. Provide a folder path for content location (D:\WSUS for example) and click on Next
  5. Click on Check connection and you must see the message Successfully connected to server. Click on Next. Click on Install
  6. Once the installation is complete click on Launch Post-Installation tasks but DO NOT launch anything else. Click on Close


Setup SCCM Pre-requisites : Firewall Rules

  1. In Server Manager, click on Tools, open Group policy management console
  2. Expand Forest and Domains folder, Right Click on the domain and select  Create a GPO in this domain and link it here …
  3. Provide a name to the GPO (SCCM Client Push Policy for example) and click on OK
  4. Right click on the policy that you created and click on Edit.
  5. Expand Computer configuration, Policies, Windows settings, Security settings, Windows Firewall with advanced security,Windows Firewall with advanced security – LDAP…..
  6. Right click on Inbound rules and click on New Rule…
  7. Click on Predefined and select File and Printer Sharing. Click on Next
  8. Don’t change anything here, click on Next
  9. Click on Allow the connection. Click Finish
  10. Create the same as Outbound Rule
  11. Then right click on Inbound Rule and click on New Rule. Click on Predefined and select Windows Management Instrumentation (WMI). Click on Next.

Install SCCM 2012 R2:

  1. Insert SCCM 2012 R2 Media
  2. Run the file splash to launch the Setup screen. Click on Install to the begin the Installation
  3. Click on Next
  4. Click on Install a Configuration Manager Primary Site and click Next (Do not click Use typical options…)
  5. Enter the product key if you have it or choose Install the evaluation edition and click on Next
  6. Click I accept the license terms and click on Next
  7. Accept all license terms and click on Next
  8. Prerequisite Downloads1) Download required files – Select this option to download the setup prerequisites from Microsoft and you can store them in a folder or shared path.2) Use previously downloaded files – Select this option if you have downloaded the prerequisites. Browse to the folder where the prerequisites are stored and click on Next.
  9. Keep English and click on Next
  10. Keep English and click on Next
  11. Specify a Site Code (I like P01 for test purposes), Site name and check the box Install the Configuration Manager Console. Click on Next
  12. Select Install the primary site as a stand-alone site, click Yes and click on Next
  13. Leave SQL settings as they are (should point to your server) and click on Next
  14. Leave SQL paths as they are and click on Next
  15. Leave SMS Provider as it is and click on Next
  16.  Client Computer Communication Settings –  Choose Configure the communication method on each site system role, uncheck Clients wil us HTTPS … and click Next.
  17. Check the box for Install a management point and Install a distribution point and click on Next
  18.  Prerequisite Check – Here the Configuration Manager setup checks whether all prerequisites are installed correctly. If successful,  Click on Begin Install
  19. Once the installation is complete you can click on Close. You can view the setup log file with CMTrace. The tool is located in SCCM 2012 R2 source DVD under \SMSSETUP\TOOLS. Launch the CMTrace tool, open the log file ConfigMgrSetup.log located in C drive. Install Complete


To be continued …. (R2 SP1 and migration to SCCM vNext)