SCCM collection Software not installed. Two methods same outcome.

I needed to quickly create a collection based on AppX not installed on workstations.

There are 2 ways achieve this;

1. Create a collection of PC's that have AppX installed then create a second collection excluding the installed.

OR

2. Do all the above in a single collection.

Both are acceptable answers and will lead you to same outcome. Step 1 will allow you to quickly report how many of 'AppX' is installed which might be handy to some.

I'll show you how to do both methods.

Method 1: Create 2 collections.

--Collection 1 determine the workstations that have APPX installed.
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.ResourceId
in
(select SMS_R_System.ResourceID
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 'APPX%')


--Collection 2 shows clients not in first collection.
-- Also client must = 1
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.ResourceId not in
(select ResourceId from SMS_CM_RES_COLL_SSDXXXXX)
and SMS_R_System.Client = 1


Method 2: Create a single collection based query.
Base

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_G_System_COMPUTER_SYSTEM.Name not in
(select distinct SMS_G_System_COMPUTER_SYSTEM.Name
from SMS_R_System
inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId
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 "%AppX%")

PXE Error after booting - The certificate associated with this media has expired

Error: the certificate associated with this media has expired. New
Media with a valid certificate will need to be created.

If your boot media cert's have not expired then this is easy to fix as it will just be the PXE point that has expired.

1. Go to Site Database --> Site Management --> --> Site Settings --> Site Systems and choose the server where the PXE Service Point is located.

2.In the right pane, right click on the ConfigMgr PXE service point and choose Properties.

3. Click on the Database tab and locate the Create self-signed PXE certificate option.

4. Under Create self-signed PXE certificate, set the Set expiration date option to some time in the future.
5. Click OK.
6. Go to Site Database --> Site Management --> --> Site Settings --> Certificates --> PXE and verify that there is now a valid non-expired non-blocked Certificate.

This should fix up your issue.. However if your boot media Cert's have expired then you need to rebuild your boot images with new Cert's

How to disable the Open file Security warning

I come across this error fairly regularly when coding for SCCM.. and mostly when using VBscript.

I found this KB is the best solution without having to disable it entirely.

http://support.microsoft.com/kb/889815

Power Management in SCCM R3

Microsoft Systems Center guest blog:
http://blogs.technet.com/b/systemcenter/archive/2011/01/20/guest-blog-how-power-consumption-and-cost-calculations-work-in-configuration-manager-2007-r3.aspx

http://blogs.technet.com/b/systemcenter/archive/2010/09/20/mvp-guest-blog-how-to-save-money-with-configmgr-2007-r3-and-feel-good-about-it.aspx

http://technet.microsoft.com/en-us/library/gg398008.aspx

Joining two or more collections into a new collection

Use the below code to create a new SCCM 'Collection' using the members of other one or more other collections.

- Create new collection
- Use a 'Query' to define the Membership Rules
- Use system resources
- Use the below SQL code to define the members.

Change the XXXXXXX to the Collection ID you want.

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.ResourceId in (select ResourceId from SMS_CM_RES_COLL_XXXXXXX) or SMS_R_System.ResourceId in (select ResourceId from SMS_CM_RES_COLL_XXXXXXX)

Boom! your new collection is now populated with the members of other collections.

SCCM Collection based on 'File' and version less than "X"

The below System query shows "X.exe" file with anything less than version "X.x.x.x.". Very useful for discovering out of date files and creating and advertisement to install the correct version

Make sure you have your file inventory configured correctly.

SELECT        VRS.ResourceID, VRS.ResourceType, VRS.Name0

FROM            dbo.v_R_System AS VRS INNER JOIN
                         dbo.v_GS_SoftwareFile AS SF ON VRS.ResourceID = SF.ResourceID

 WHERE        SF.FileName = 'X.exe' AND SF.FileVersion <= 'x.x.x.x'

Please note i've used 'less than or equal to' in the above query if you want just less than remove the '='

Also a different way to do the same. Create 2 queries

http://www.sccm-tools.com/tools/wqlqueries/WQLQueries-Colcompswithfile.html

My RedBubble Profile