If you are planning to migrate your SCCM 2007 environment to SCCM 2012,you may have to think about your packages,collections,OSD and other stuff.

you cannot migrate all collections from SCCM 2007 to SCCM 2012.Couple of things to note before you migrate them.

Points to note  on collection migration:

1. Collection can contain only either devices or users but not both.

2.  Every collection must have Limitation.

3.   Sub collections are no longer exists instead you have folders.

blogged couple of posts on archiving/removing packages not been used for X days that will help you to not migrate them to CM12.

In this blog,will show you how to identify the collections that has both users and computers member of it  to look at the collections if you want to take of users/Computers query before you go on migration.

Configmgr Report:

select
fcm.CollectionID ‘Collection ID’,COLL.Name
from
v_FullCollectionMembership fcm,v_Collection COLL
where fcm.CollectionID=coll.CollectionID
group by
fcm.CollectionID,COLL.Name
having
count(distinct fcm.ResourceType) > 1

Collection to check if any of Primary or Secondary site not installed with SUP Role or any other Role?

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.SystemRoles like "SMS Site System" and SMS_R_System.ResourceId not in (select ResourceID  from  SMS_R_System where SMS_R_System.SystemRoles like "SMS Software Update Point")

Collection can be modified to check if any of the Roles is missing on sites.

I was looking at client health percentage other day and found many systems were reported as not healthy though I fixed some of the machines with sccm client installed and able to receive policies.

Looked at Database for healthy computers and see double entries with different ResourceID ,client0 and HardwareID0 is NULL.

So what next ? I created collection with filter computers with sccm client installed and should be in computers with no sccm client.

Collection:

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.Name in (select name  from  SMS_R_System where SMS_R_System.Client is not null) and
SMS_R_System.Client is null

Report:

select name0 from v_R_System where client0 is null
and Name0 in( select Name0 from v_R_System where client0 =1)
order by name0

I see 100+ computers which are duplicate with same hostname but different value for ResourceID,client0 and hardwareID0 etc.

Special delete will remove them completely from database.

Creating collection to get computers that starts and end with particular string is used mostly using % .If this percentile used at the end of the variable name, you get all computer names that starts from particular string and if you use this in the beginning, gets all computers that ends with particular string.

Examples : To get all computer names that starts with ESKONR, use ESKONR% , to get all computer name that ends with ESKONR ,use %ESKONR.

But what if  need all computer name that has letters like ESK in the middle of the computer name. Let says i have computers with 15 digits and they have ES placed in 10-11 . Example : INHYD1202ES0003

We can use underscore as wildcard for one character space and use them along with Like statement and apply to fields.

Collection :

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.Name like "__________ES___" and SMS_R_System.ResourceDomainORWorkgroup = "ESKONR"

We have used 9 underscores in our query before E and 4 Underscores after S to tell that first 9 digits can be any thing and last 4 digits can be anything .

Underscores (_) as wildcard can be used at any location in the query but one can replace one character only.

Hope it helps!

collection to get all 32 Bit and 64 Bit 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_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceId = SMS_R_System.ResourceId where (SMS_G_System_COMPUTER_SYSTEM.SystemType = "x64-based PC" or SMS_G_System_COMPUTER_SYSTEM.SystemType = "X86-based PC") and SMS_G_System_OPERATING_SYSTEM.Caption = "Microsoft Windows Server%"

Reference Via http://social.technet.microsoft.com/Forums/en-US/configmgradminconsole/thread/ee47ec21-b113-4aed-956d-36205b619091

SCCM collection that returns all windows XP or Windows 7 machines which has 1 GB of RAM(1024*1024 KB) running with MS office products.

select *  from  SMS_R_System inner join SMS_G_System_X86_PC_MEMORY on SMS_G_System_X86_PC_MEMORY.ResourceId = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_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_X86_PC_MEMORY.TotalPhysicalMemory = 1048576 and (SMS_G_System_OPERATING_SYSTEM.Caption = "Microsoft Windows XP%" or SMS_G_System_OPERATING_SYSTEM.Caption = "Microsoft Windows 7%") and SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Office 2003%"

Edit the display name correctly from your Add and remove programs and change the RAM as per the needs.

The collection listed below might help you to identify if any computers in organization that do not follow the standard naming convention .

Below collection is created to list servers and do not contain specific words in computer name:

select SMS_R_System.Name, SMS_R_System.OperatingSystemNameandVersion from  SMS_R_System where (SMS_R_System.OperatingSystemNameandVersion like “%Server 6.%” or SMS_R_System.OperatingSystemNameandVersion like “%Server 5.%”) and SMS_R_System.ResourceId not in (select ResourceID  from  SMS_R_System where SMS_R_System.Name like “%xxx%”)

The Logic is : list all servers with criteria given above and computer do not have xxx word in it.

Please change the bold letters as per the requirement.

Have seen lot of questions on how to get list of computers that do not have xxxxxx .This xxxx could be of anything like softwares,file names anything that do not have.

In this post,I will go through step by step procedure how to make it simple.

Step 1: To get list of computers that do not have xxxx,create a collection query that has xxxx. I think this is pretty much easy how to do it using criteria.

Create new collection and edit the query .

Click on Criteria Tab , click Yellow Burst

In the Criterion properties page, click on select

select attribute class and attribute which you want ( here i go with add and remove programs)

select the display name which you like xxxxx .I go with Adobe Acrobat .Why i used % is ,it list all computers that contains word like Adobe Acrobat.

Now Click on Show query Language to get the WQL code:

Here is the code we have got that list all computers with particular software installed :

select *  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 Acrobat%”

so cut the query and paste it in notepad we need this again.You will see blank query now.

Step 2: In this step,we will create collection with subselected criteria to get what we need .

Click on the Yellow Burst once again and select criterian type as “Subselected Values” and click on select

and select the operator as not in and past the query which you copied earlier into it.

query which we created earlier is :

select *  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 Acrobat%”

Replace * with SMS_R_System.ResourceID in your qeury and click on Ok.

 You can also filter the above collection to look only for windows 7 computer i.e OS version 6.1 etc .

Use Subselected query to get the results:

select *  from  SMS_R_System where SMS_R_System.NetbiosName like “%rts%” and SMS_R_System.ResourceId not in (select SMS_R_System.ResourceID  from  SMS_R_System inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceId = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FileName like “rtvscan.exe” AND SMS_R_System.Client = 1)

 

In my previous post,showed you how to create SCCM report to show computers with heartbeat timestamp http://eskonr.com/2011/11/sccm-report-for-computers-with-heartbeat-time-stamp/ . In this post ,you will Create a collection that gets all computer names based on heartbeat discovery agent: Create a new collection and past the below query into it.

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 ResourceId in (select ResourceID from SMS_R_System where AgentName in (“Heartbeat Discovery“) and DATEDIFF(day,AgentTime,GetDate())<23)

Replace the number of days and agent name as you desire which is in bold letters.