Useful SCCM 2012 Device Collection Queries

Below are some useful SCCM Device Collection Queries. Some of these may require the device collection to be Dynamic.

Device Collection based on OU

SELECT * from SMS_R_System WHERE SMS_R_System.SystemOUName = "YOURDOM.LOCAL/Computers/YourCustomOU"

Device Collection Laptops/Notebooks

SELECT * FROM SMS_R_System INNER JOIN SMS_G_System_SYSTEM_ENCLOSURE ON SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceID WHERE SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes in ( "8", "9", "10", "14" ) 

64bit Devices


Windows 8 Devices

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
SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceId
"%Workstation 6.2%" or SMS_R_System.OperatingSystemNameandVersion LIKE "%Windows 8%" 

All Servers

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_G_System_SYSTEM ON SMS_G_System_SYSTEM.ResourceId = SMS_R_System.ResourceId WHERE SMS_G_System_SYSTEM.SystemRole = "Server"

Domain Controllers

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 SMS_R_System.PrimaryGroupID = "516"

Computer Name Contains…

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_G_System_SYSTEM ON SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId WHERE SMS_G_System_SYSTEM.Name LINK "%COMP%" ORDER BY SMS_R_System.Name

Powershell – Export All Email Addresses from Office 365 Exchange Online

See below to export all E-Mail addresses from Office 365 Exchange Online. If you have never connected to Powershell before then you need to download the Office 365 Powershell Cmdlets from Microsoft.

Connecting to Powershell is easy: Installing Windows PowerShell cmdlets only sets the stage.

This does nothing for actually connecting them to the Microsoft Office 365 cloud. Connecting to the cloud requires a cumbersome—but straightforward—five-step process:

1. Import your newly installed Online Services module
2. Enter Office 365 credentials
3. Create a remote session
4. Import that session’s commands into your local Windows PowerShell session
5. Connect to Office 365 services After installing the Cmdlets, open up Powershell and type the following:

$session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri "" -Credential $cred -Authentication Basic -AllowRedirection

This will prompt for a login username and password; use your administrator login for Office 365. You next need to import the session:

Import-PSSession $session

This should connect you to Powershell. If you cannot execute commands you need to set the execution policy to Remote-Signed, as below:

Set-ExecutionPolicy RemoteSigned

After this is done you’ll be able to run your command to export data. Running the following snippet will export ALL email addresses in your organization to a CSV file called addresses.csv.

Get-Recipient -ResultSize Unlimited | select DisplayName,RecipientType,EmailAddresses | Export-Csv addresses.csv

Have fun.

Redesigned website and blog

siteI have finally gotten around to replacing my old website with a shiny new one. This has a lot more features and allows me to update the website more frequently. I will also be posting some of the new related “techy” news on the blog as well.

I have set up some sections to allow me to post some of my PHP/JavaScript code that I will be releasing, this will give me good grounds and a basic “code portfolio” to work and learn from. I have kept the design of the website as simple as possible but also easy to read.

Any questions please feel free to leave me a comment or contact me from the relevant pages.

Ubuntu 9.04 Release

Ubuntu is a community developed, Linux-based operating system that is perfect for laptops, desktops and servers. It contains all the applications you need – a web browser, presentation, document and spreadsheet software, instant messaging and much more.

The latest release of Ubuntu brings the best open source technologies together on one platform, with the benefit of free updates for 18 months. Constant improvements, feature additions and extended machine and network support explain why Ubuntu wins more converts with every release. This tour will help you discover, like millions of users worldwide, why Ubuntu Desktop Edition 9.04 is the desktop operating system for you.

Check out this release over at

Mozilla Releases Firefox 3.5 BETA

Mozilla pushed a fourth beta of Firefox 3.5, formerly known as Firefox 3.1, out the door yesterday.

The oft-delayed next version of the open source browser is expected to arrive at some point in the first half of this year, it had originally been pencilled in to rock up in December 2008.

However, a series of problems brought on by show-stopping bugs in Mozilla’s new JavaScript engine TraceMonkey meant the organisation was forced to push back its release date and change the browser’s name to reflect the huge amount of changes it’s undergone in beta.

So Firefox 3.5 Beta 4 should be the final beta of the browser ahead of its release at some point in the next few months.

It comes loaded with performance, web compatibility and speed improvements, according to Mozilla.

Read More

Opera Browser Fails With Patching Techniques

operaIt seems that users of Apple Safari and Opera web browsers are highly susceptible to latest online threats owing to the way their patch updates are downloaded while users of Google Chrome and Mozila Firefox are relatively well protected.

According to a new study conducted by Google Switzerland and Swiss Federal Institute of Technology, nearly 97 percent of Chrome users and around 85 percent of Firefox user were browsing the internet using the latest version of their browser, 21 days after its release.

In contrast only 53 percent of Safari users had the latest version of the browser while the figure for Opera users stood at mere 24 percent. A major cause for this difference owes to the fact that both Chrome and Firefox checks for automatic updates and implements them without user intervention.

In contrast, Apple Safari allows it users to set an update schedule while Opera users have to manually download a new installer program when a new version is available.