SCCM query to populate Device collection

Hi!

In SCCM if you want to build a Device Query based on their OS version you can apply the following query:

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.OperatingSystemNameandVersion like “%workstation%”   and SMS_R_System.OperatingSystemNameandVersion like “%6.2%” and SMS_G_System_COMPUTER_SYSTEM.SystemType = “x86-based PC”

Where %workstation% is to populate only workstations but if you prefeer servers change it for %server%

Where %6.2% is the OS version. In the below link you will find the OS version that you are loooking for, change it on the query 🙂

https://msdn.microsoft.com/en-us/library/windows/desktop/ms724832(v=vs.85).aspx

And also, if you need to know the architecture, you can indicate if 64 or 32 bits indicating “x86-based PC” or “x64-based PC” 

Regards!

 

Creating Device Query based on Language ID in SCCM

Hi!

To create a collection populated with computers based on their language ID use the following query:

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_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.OSLanguage = 3082

Where 3082 is the language ID that you are interested.

In the following link you can find all language IDs

https://msdn.microsoft.com/en-us/goglobal/bb964664.aspx

Regards!

SCCM Colections based on computer arquitecture

Hello,

In SCCM we can do querys based on computer arquitecture. If we need to make a colection that shows all x86 OS versions or x64 we can do the following query:

x86:

select distinct 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_G_System_COMPUTER_SYSTEM.SystemType = “x86-based PC”

x64:

select distinct 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_G_System_COMPUTER_SYSTEM.SystemType = “x64-based PC”

Regards!

Query to discover objects in AD from a SCCM Collection

Hi,

When you generate a collection in SCCM and want that appear all objects that are in a AD Sub OU you can use the following query:

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.SystemOUName = “DOMAIN/SERVERS_OU/SUB_OU”

Regards!

SCCM – How to create collections of machines based on subnet queries

Hi,

IN SCCM we can do a collection of machines based on subnet query with the following:.

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.IPSubnets = “X.X.X.X”

If you want to do queries with various subnets you can use this:

select *  from  SMS_R_System where SMS_R_System.IPSubnets in (“X.X.X.X”,”X.X.X.X”)

Regards

Remove a computer from a Collection when OSD Task Sequence is completed

Hi!

I find a very interesting post where indicate how to put out of a collection a new computer after to be created with OSD.

The post is writed by Jörgen Nilsson.

http://ccmexec.com/2010/03/remove-a-computer-from-a-collections-when-osd-task-sequence-is-completed/

Regards

——–

When using a mandatory OSD advertisement to install a Operating system it is a great benefit to remove the computer from the Collection to where the OS deployment is advertised. The OSD task sequence advertisement can then be set to always rerun and all problems related to reinstalling an existing computer is solved.

This can be achieved by using a status filter rule together with a VBscript which removes the computer from the collection once the Task Sequence completes successfully.

UPDATE!!
I have updated the script to search for active computer records in SCCM using the name and then removing the computer from the collection using the ResourceID instead of using the name for matching. I have seen at customers that some third party applications created direct memberships with a different naming convention than the SCCM Admin Console does, this updated script will solve this problem.

Update 2
The script have been updated with the possibility to enter more than one collection to remove the computer from, it can also write an event to the event-log on the SCCM server with the name of the computer and the collection/collections it will be removed from.
I have removed the script code from this blog and made it available as a file instead, to avoid problems when cut/pasting the text.

You can download it here: http://ccmexec.com/wp-content/uploads/2010/12/Remove.vbs.txt

Download the script and save it as “remove.vbs”  edit the following line with the collection/collections you want the computer removed from

sCollectionIDs = “00100053:0010004A:00100069″

when that is done, complete the steps below to configure the status filter rule.

———————————————-

Configuring the status filter rule:

  1. Under site settings create a new status filter rule
  2. Configure it to use the following settings:

Component : Task Sequence Manager
Message Id: 11171

Run a Program: cscript.exe e:\sccmtools\remove.vbs %msgsys

Status1 status filter rule 2

Command lines for Silent Installations

Hi there!

Here I put some command lines for install applications in silent mode, with the time I will add more.

Regards

java 7 update 9 x64 jre-7u9-windows-x64.exe /s MOZILLA=1 IEXPLORER=1 ADDLOCAL=ALL SYSTRAY=0 EULA=0 REBOOT=Suppress /qn
java 7 update 9 x86 jre-7u9-windows-i586.exe /s MOZILLA=1 IEXPLORER=1 ADDLOCAL=ALL SYSTRAY=0 EULA=0 REBOOT=Suppress /qn
MS Office 2003 PRO MUI setup.exe TRANSFORMS=”Name_mst.MST” /qn
SP3 Office 2003 msiexec /p MAINSP3.msp /qn /norestart
File Format Converters FileFormatConverters.exe /quiet /norestart
Adobe Acrobat Reader XI US AcroRead.msi TRANSFORMS=”AcroRead.mst”  /norestart /qn
Lotus Notes 8.5.1 setup.exe /s /v”/qn”
Lotus Notes 8.5.1 FIX setup.exe /s /v”/qn”

Update Adobe Acrobat Reader 1015             msiexec /update AdbeRdrUpd1015.msp /qn