SCCM Collection Queries

sccm logo

We have been building a number of collections within SCCM lately so we thought we would start to build up a reference here of various collections, some simple, some more complex. We will be adding to this when we can so make sure you come back and check for updates.

Computers with certain prefixes – % being a wildcard

select SMS_R_System.NetbiosName from
 SMS_R_System
 WHERE
 SMS_R_System.NetbiosName = "ABC%"

Adobe Reader XI Installations – from Programs in Control Panel

select SMS_R_System.NetbiosName,  
 SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName from  
 SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on  
 SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceId =  
 SMS_R_System.ResourceId where  
 SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like “Adobe Reader XI”


Devices based on OU

select distinct * from SMS_R_System
 WHERE
 SMS_R_System.SystemOUName = "OU NAME"

Windows 8 Installations

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

More Queries

Our full range of SQL and WQL Collection queries are available here.

Feedback

If you have any questions or feedback about this post, or if you would like us to create any queries for you, please go ahead and leave us a message below in the comments section and we will get back to you as quick as we can.

Free Anti-Virus Software

Computer Virus Removal 600x315

One of the perils of working in IT is that I am constantly asked by friends and family the age old question;

What is the best free anti-virus software?

Well, there’s plenty of free anti-virus software out there and there is not really an answer to this question – there is no “best” anti-virus software, only personal opinion!

Myself, at home (and recommendations to said friends and family!) I use Microsoft Security Essentials (MSE) being the Microsoft slave that I am, but a lot of my colleagues go for AVG, but there’s also Avira and Avast! that will supply you with free AV protection.

To help those out in the hunt for free anti-virus protection, I’ll list below some links to the freebie AV sites, I’ll even pop in some free Mac anti-virus protection sites!

Windows

Microsoft Security Essentials – My personal recommendation

Avast – Be careful of them trying to get you to actually buy their full product!

Avira – Never used it so I can’t really give an opinion on this one!

AVG – Age old free anti-virus, one of the first to supply free AV to the masses

Mac

Sophos Anti-Virus Home for Mac

Clamxav

So, hopefully, this answers the question of free anti-virus software, or at least what your options are and where you can get it from, the choice is yours!

Renew a Self Signed Certificate on an Exchange Server 2007

Exchange 2013 1

If you are using an Exchange system and have self signed certificates, every year you will have to renew the certificate. An expired certificate may cause problems such as connectivity to web services, SMTP transport and Outlook prompting certificate security warnings which is extremely annoying for users and also can cause problems with Out of Office settings.

To do this is very simple and can be done in minutes.

The following guide explains how to do this simple task.

First of all, you should check the current certificate to ensure that it has expired. To do this, logon to your Exchange server as an enterprise admin (usually a domain admin is not enough…), then start up the Exchange Management Shell (EMS).

In the EMS, type the following command:

Get-ExchangeCertificate | FL

This will then display the expiry status and also the date and time of when the certificate expires(d).

You should then make a note of the Thumbprint for this certificate as you will need it.

Once you have the thumbprint, type the following command:

Get-ExchangeCertificate –Thumbprint “ENTERYOURCURRENTCERTIFICATETHUMBPRINTHERE” | New-ExchangeCertificate

This will create a new certificate you will then be prompted to confirm if you want to overwrite the expired certificate, you should press Y to overwrite and continue.

You then need to run this command again:

Get-ExchangeCertificate | FL

You should take note of the new certificate thumbprint as we now need to enable the IIS services for it.

To do this, type the following command:

Enable-ExchangeCertificate –Thumbprint “ENTERYOURNEWCERTIFICATETHUMBPRINTHERE” –Services IIS

Once you have done this, you should check the new certificate is working, usually firing up Outlook on a client machine will prove this.

Once you are happy the new certificate is working, you should remove the old certificate.

To do this, type the following command:

Remove-ExchangeCertificate –Thumbprint “ENTERYOUROLDCERTIFICATETHUMBPRINTHERE”

You can then close down the EMS, logout of your Exchange server and your certificate should be fine for another year.

One last thing to take note of, your users when they fire up Outlook, will need to install the newly created certificate when they are prompted to do so.

Office 2010 Language Pack Deployment in the Software Catalog for SCCM 2012

Microsoft Dumps Physical Office 2

If you have a requirement to add any Office 2010 language packs to your SCCM 2012 Software Catalog then you may find that it is not the simplest of tasks.

I’ve spent some time working on different methods of doing this and although creating the packs as an application and pushing them out is fairly simple, to offer them through the catalog was more difficult.

After lot’s of testing, I have found a way that not only works, but also seems to be the more simple and as always, I thought I’d document this information to help others facing the same task.

So, below is a guide to help you create your own language packs through the software catalog.

The below guide assumes you have access to language packs and that you know how to create packages already in the catalog. For the purposes of this guide, I am using the Norwegian Office 2010 Language Pack.

To begin, you need the language pack itself, so you can download individual packs from your Microsoft Volume Licence website.

Once you have downloaded the files (or if you have downloaded the ISO, extract the files using an extract tool like 7-Zip), you should copy them to your network location storage ready for you to point your package to.

Then go into the folder with the files in it, you should see a folder called “OMUI.nb-no” (NOTE: the nb-no in this guide states the language is Norwegian, this part of the folder name changes dependent on the language you are using..).

Go into this folder and you should then see a file called “config.xml”, right click on this file and click edit.

Where the line says (yours may be slightly different):

<!– <Display Level=”full” CompletionNotice=”yes” SuppressModal=”no” AcceptEula=”no” /> –>

You should replace this with:

<Display Level=”none” CompletionNotice=”no” SuppressModal=”yes” AcceptEula=”yes” />

Then save the file and exit. You then move on to packaging the folder in SCCM.
Go into the SCCM Console and click on “Software Library” then Applications.

Then right click and select “Create Application”

On the wizard page, select “Manually specify the application information” and click next

Office 2010 Language Pack Deployment in the Software Catalog for SCCM 2012 1

On the second wizard page, fill out all the fields as you wish and click next

Office 2010 Language Pack Deployment in the Software Catalog for SCCM 2012 2

On the next page, you can set the custom user settings and click next

Office 2010 Language Pack Deployment in the Software Catalog for SCCM 2012 3

On the next page, click the Add button

Office 2010 Language Pack Deployment in the Software Catalog for SCCM 2012 4

Then on the new dialog box, click “Manually specify the deployment type information” then click next

Office 2010 Language Pack Deployment in the Software Catalog for SCCM 2012 5

Enter the custom package details and then click next

Office 2010 Language Pack Deployment in the Software Catalog for SCCM 2012 6

You should then enter the content location (where you copied the files to earlier in this guide..)

You then need to enter the installation program, it will be the same apart from the changes in the language pack folder details for different languages:

setup.exe /config OMUI.nb-noconfig.xml

This is the config.xml file that we edited earlier in this process.

You also need to enter the uninstall information as:

setup.exe /uninstall

Office 2010 Language Pack Deployment in the Software Catalog for SCCM 2012 7

On the next page, click “Add Clause”

Office 2010 Language Pack Deployment in the Software Catalog for SCCM 2012 8

In the Setting Type, from the drop down menu select “Windows Installer” then under Product Code, click Browse. Then browse to the OMUI.nb-no folder at the network location and select the OMUI.msi file.

Office 2010 Language Pack Deployment in the Software Catalog for SCCM 2012 9

Then click OK

Office 2010 Language Pack Deployment in the Software Catalog for SCCM 2012 10

and then next.

You can then setup your User Experience settings and click next

Office 2010 Language Pack Deployment in the Software Catalog for SCCM 2012 11

You then have the option to set your requirements and software dependencies.

You can go on and complete the wizard.

Once completed, you can then distribute the package and deploy where you need to.

If you have any issues with this guide, please leave a comment and I’ll try and help.

Windows XP Sysprep ready for SCCM OSD Capture Guide

windows

This guide will show you what you need to do to capture a Windows XP Service Pack 3 image, which you can then capture and deploy using Configuration Manager.

First of all, you need to create a full  image that is ready to be captured. This is usually best to install on a virtual machine using VMWare or Hyper-V, however, you can if you want, use a physical machine.

Once you have fully completed your image and have all programs and settings on it that you want, you can start the Sysprep and capture configuration process.

You will need the sysprep files, extra driver files and also the custom sysprep file to complete this process successfully. I have already created these for you and they are available to download from below – note that this is for XP Service Pack 3 – if you are using a different service pack for some reason, either upgrade or get the version of Sysprep for that Service Pack:

Download Sysprep Pack

Once you have downloaded this pack, you can extract the zip files and copy the “DRIVERS” and “SYSPREP” folders to the root of your capture machines C: drive.

You should then edit the C:SYSPREPsysprep.ini file with your own custom settings, like Company Name, AdminPassword and ProductKey.

THIS IS A CRITICAL PART OF THE PROCESS!!!

If you do not complete this part, if will not work correctly and will limit the image to the hardware that you have built the capture machine on.

Go into Computer Management and then into the Device Manager.

Then Expand the IDE ATA/ATAPI Controllers like below:

Windows XP Sysprep ready for SCCM OSD Capture Guide 12


You need to ensure that all your hard drive controllers are set to Standard, as you can see from above, mine is currently not so will need changing.

If yours needs changing too, follow the below process, if not, you can ignore the following part;

Double click the controller you need to change then click the “Driver” tab:

Windows XP Sysprep ready for SCCM OSD Capture Guide 13
 
Click the Update Driver button, then, when the dialog box appears choose “No not at this time” and click next:
 
Windows XP Sysprep ready for SCCM OSD Capture Guide 14
 
Then click “Install from a list or specify location (Advanced)” and then click next:
 
Windows XP Sysprep ready for SCCM OSD Capture Guide 15
 
Then click “Don’t search, I will choose the driver to install”:
 
Windows XP Sysprep ready for SCCM OSD Capture Guide 16
 

You should then have the option to select the “Standard” driver that you require, highlight this and click next:

Windows XP Sysprep ready for SCCM OSD Capture Guide 17


This will then install and complete as below:

Windows XP Sysprep ready for SCCM OSD Capture Guide 18


Ensure that you do this for all your IDE controllers and it should look similar to below:

Windows XP Sysprep ready for SCCM OSD Capture Guide 19


Before rebooting the capture machine, you also need to follow the same process to change your “Computer” driver. As below, my default “Computer” driver is “ACPI Uniprocessor PC”

Windows XP Sysprep ready for SCCM OSD Capture Guide 20


But it should be “Advanced Configuration and Power Interface (ACPI) PC”, so follow the above steps to change this so it then should look like the below:

 
Windows XP Sysprep ready for SCCM OSD Capture Guide 21


You should then reboot the machine to enable all these settings.

Once rebooted, the machine should then be ready to run the Configuration Manager capture media and the captured image will then run on any hardware.