Eswar Koneti's Blog

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!

Full Details :http://social.technet.microsoft.com/Forums/en-US/configmgrgeneral/thread/7b4d3810-a262-4c63-8dd6-7cc906b85941

select sys.name0,sys.AD_Site_Name0,FCM.SiteCode,ARP.DisplayName0,ARP.Version0,ARP.InstallDate0 from V_R_system sys
inner join v_Add_Remove_Programs ARP on arp.resourceID=sys.ResourceID
inner join v_FullCollectionMembership FCM on FCM.ResourceID=sys.ResourceID
and arp.DisplayName0 like ‘%project reader%’ and ARP.Version0 < ’4%’

For collection ,follow the post here on http://eskonr.com/2012/05/sccm-collection-computers-with-lower-version-application/

Full Details http://social.technet.microsoft.com/Forums/en-US/configmgrgeneral/thread/7b4d3810-a262-4c63-8dd6-7cc906b85941

This collection is created using sub selected queries http://eskonr.com/2011/12/sccm-collection-sub-selected-quiries/

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_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID not 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 inner join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceId = SMS_R_System.ResourceId where (SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%project reader%" or SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName like "%project reader%") and (SMS_G_System_ADD_REMOVE_PROGRAMS.Version > "4%" or SMS_G_System_ADD_REMOVE_PROGRAMS_64.Version > "4%"))

earlier I posted entry to list all virtual computers http://eskonr.com/2011/04/sccm-collection-for-virtual-or-physical-computers/

this collection gives you all computers that are Physical (running as Host) not Virtual…

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 SMS_R_System.ResourceId 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.Model like "%Virtual%") and SMS_R_System.Client is not null

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.

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.

If your boss ask to get list of laptops which are managed by SMS or SCCM.what do you do and how do you get that. Right click on computer and go to resource explorer to identify the computer is Laptop or Desktop ?

You can identify if the computer is Laptop or Desktop based on its chassis Types.

Below are listed the Chassis types available to create SCCM collection or reports.

For Laptops Chassis Types : 8 , 9, 10, 11, 12, 14, 18, 21

For Desktop Chassis Type : 3, 4, 5, 6, 7, 15, 16

For server Chassis  Type: 23

Below is the collection to list all the computers which are laptops which fall in above Chassis type. All these values are from SQL table called SMS_G_System_SYSTEM_ENCLOSURE .If you are looking to create SCCM report,you can create using Views(v_GS_SYSTEM_ENCLOSURE).

You can also replace the values with Desktop computers or servers also you can use joins to club these with AD groups for deploying the applications based on this.

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_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes in ( “8″, “9″, “10″, “11″, “12″, “14″, “18″, “21″ )

Chassis Type Value Description :

1 Other
2 Unknown
3 Desktop
4 Low Profile Desktop
5 Pizza Box
6 Mini Tower
7 Tower
8 Portable
9 Laptop
10 Notebook
11 Hand Held
12 Docking Station
13 All in One
14 Sub Notebook
15 Space-Saving
16 Lunch Box
17 Main System Chassis
18 Expansion Chassis
19 Sub Chassis
20 Bus Expansion Chassis
21 Peripheral Chassis
22 Storage Chassis
23 Rack Mount Chassis
24 Sealed-Case PC

SCCM Report to list all the laptop computers :

select a.name0,a.Operating_System_Name_and0,a.Client_Version0 from v_R_System a,v_GS_SYSTEM_ENCLOSURE b
where a.ResourceID=b.ResourceID and b.ChassisTypes0 in ( ’8′, ’9′, ’10′, ’11′, ’12′, ’14′, ’18′, ’21′ )
group by a.name0,a.Operating_System_Name_and0,a.Client_Version0

Here are some sample reports/queries available which you can make use of it

http://myitforum.com/cs2/blogs/snorman/archive/2007/09/18/count-of-chassis-types-by-collection.aspx

In my last post http://eskonr.com/2010/01/sccm-report-for-applications-installled-on-computers-with-without/ showed how to create sccm report to list computers with different versions of application installed on computers. This is to list the computers in collection rather report.

Create a collection and edit the query ,paste the following into it.

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 “%Office1%” or SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like “%Office2%”

Please replace the bold letters with the correct application name displayed from add and remove programs.