Using SCCM Queries - step by step document

Here is an Excellent Document I found on the Internet shared by questnet.edu which details on how to use the 'Queries' feature in SCCM Console.

https://www.questnet.edu.au/download/attachments/20873328/Using+MS+SCCM+Queries.pdf

Queries are run against SCCM site's database and retrieve specific data according to the criteria of the query. SMS provides many predefined queries, and if required, we can create additional queries.

Query-based collections are dynamic objects. If a resource no longer meets the collection's query, it is automatically removed from the collection. And if a resource that originally did not meet the collection's query has changed in a way that it now meets the collection's query, it is automatically added to the collection. This behavior greatly reduces and simplifies the administrative work of managing the clients.

SCCM Roles applicable for Central, Primary & Secondary Sites

The below table from Microsoft will help us to identify the Site System roles that we can install at each type of site in a System Center 2012 Configuration Manager hierarchy, and whether the site system role provides functionality for its site only, or for the entire hierarchy. We can install any supported site system role on the site server computer or on a remote site system server at a central administration site or primary site. At a secondary site, only the distribution point is supported on a remote site system server.

Site system role	Central administration site	Child primary site	Stand-alone primary site	Secondary site	Site-specific or Hierarchy-wide option
Application Catalog Web Service Point	No	Yes	Yes	No	Hierarchy
Application Catalog Website Point	No	Yes	Yes	No	Hierarchy
Asset Intelligence Synchronization Point1	Yes	No	Yes	No	Hierarchy
Certificate Registration Point	Yes	Yes	Yes	No	Hierarchy
Distribution Point2,5	No	Yes	Yes	Yes	Site
Fallback Status Point	No	Yes	Yes	No	Hierarchy
Management Point2,3,5	No	Yes	Yes	Yes	Site
Endpoint Protection Point	Yes	No	Yes	No	Hierarchy
Enrollment Point	No	Yes	Yes	No	Site
Enrollment Proxy Point	No	Yes	Yes	No	Site
Out of Band Service Point	No	Yes	Yes	No	Site
Reporting Services Point	Yes	Yes	Yes	No	Hierarchy
Software Update Point4,5	Yes	Yes	Yes	Yes	Site
State Migration Point5	No	Yes	Yes	Yes	Site
System Health Validator Point	Yes	Yes	Yes	No	Hierarchy
Windows Intune Connector	Yes	No	Yes	No	Hierarchy

¹ Configuration Manager supports only a single instance of this site system role in a hierarchy.

² By default, when you install a secondary site, a management point and a distribution point are installed on the secondary site server.

³ This role is required to support clients in Configuration Manager. Secondary sites do not support more than one management point and this management point cannot support mobile devices that are enrolled by Configuration Manager.

⁴ When your hierarchy contains a central administration site, install a software update point at this site that synchronizes with Windows Server Update Services (WSUS) before you install a software update point at any child primary site. When you install software update points at a child primary site, configure it to synchronize with the software update point at the central administration site.

⁵ Prior to System Center 2012 R2 Configuration Manager, all site system roles at a secondary site must be located on the site server computer. The only exception is the distribution point. Secondary sites support installing distribution points on the site server computer and on remote computers. Beginning with System Center 2012 R2 Configuration Manager, the state migration point can also be installed on the site server computer or on a remote computer, and can be co-located with a distribution point.

1. http://technet.microsoft.com/en-us/library/gg712282.aspx#Plan_Where_to_Install_Site
2. http://gallery.technet.microsoft.com/SCCM-2012-Roles-applicable-727614d7

Remove Delete Special option from SCCM console

I found this TechNet forum post interesting on which the SCCM team wants to control their engineers from using Delete Special. Though you can set Role based permission to engineers, there are cases where mistake happens by experienced engineers or when people unwittingly delete machines. 

The 'Delete Special' option comes with the SCCM product, and here is how Christjan Schumann explained in the forum post with steps to remove the option from the SCCM console. 

• Make a backup and then edit "SCCM\AdminUI\XmlStorage\ConsoleRoot\adminconsole.xml" file.
• Search for lines containing: DisplayName="DeleteSpecialAction". You will find 3 matches. 
• All of them are parameters of "ActionDescription" tag and 2 of those matches should be removed from XML (from start tag to end tag ).
• To remove "Delete Special" option from context menu of root level collections: delete lines 7787 to 7807.
• To remove "Delete Special" option from context menu of child level collections: delete lines 8529 to 8549.

The above line numbers are valid in original SP2 adminconsole.xml. They might differ if you've customized it before.

Ref. Original Post: http://social.technet.microsoft.com/Forums/systemcenter/en-US/6868c190-1aee-4690-8e23-6e019f8d7472/delete-special

http://social.technet.microsoft.com/Forums/systemcenter/en-US/4b467010-3c55-4fa3-9a61-127c9d846416/sccm-2007-delete-special?forum=configmgradminconsole

Custom SQL query to report MS-Excel and MS-Access

Here is a custom Report to list only MS-Excel (excel.exe) and MS-Access (msaccess.exe) with Office 2003, 2007 and 2010 versions. You can also use to query by editing it to fetch other components of Microsoft Office.

SELECT b.Netbios_Name0,
b.User_Name0,
CASE WHEN a.FileVersion LIKE '11.%' THEN 'Office 2003'
WHEN a.FileVersion LIKE '12.%' THEN 'Office 2007'
WHEN a.FileVersion LIKE '14.%' THEN 'Office 2010'
ELSE 'NULL'
END AS 'Office Version',
a.FileName,
a.FileVersion
FROM v_GS_SoftwareFile a
JOIN v_R_System b ON a.ResourceID = b.ResourceID
WHERE (a.FileName = 'excel.exe' or a.FileName = 'msaccess.exe')
GROUP BY b.Netbios_Name0, b.User_Name0, a.FileName, a.FileVersion
ORDER BY b.Netbios_Name0

Note that you may get repeated machine names if the machine has got multiple updates installed for the MS office versions. 

Ref.: How to bifurcate clients having MS Office 2010/2007/2003 and to put further bifurcate on Office products

WQL Query to add bulk computers to SCCM collection

We use WQL queries to create dynamic SCCM Collections to list machines with a particular software, hardware, etc. But when it comes to adding a random machines from a given list and when we don’t use the SCCM Right-click tools, mostly we go the conventional manual method. Once during a conversation with a Microsoft tech, he helped me with this simple query which made most of my deployments easier. I can say this is faster and lighter than using Right-click tools. Sharing this query here.

We can add any number of Computer list to a Collection, but max 1500 hostnames per query. We can also remove any single machine from this customized 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
('Machine1','Machine2','Machine3')

Easy Steps to consolidate the computer list into the query with comma: 
* Add the machines to MS Excel by listing each machine on each row.
* Include a column (A) before machines list with the single code symbol  (').
* On the Columns C and D enter the comma symbol (,) and single code ('). 
* Drag the single code and comma symbols till the last before row of the machine list.
* Add a formula to Column E [=A:A&B:B&C:C&D:D].
* On Column E, Copy - Paste Special with Values.
* Copy the entire column E and paste in the query between brackets ( ). 
* It should look like  ('Machine1',
                               'Machine2',
                               'Machine3',
                               'Machine4')
* Click Ok and close the New collection window and we are done.

SQL Report for TPM (Trusted Platform Module)

A TPM is a microchip designed to provide basic security-related functions, primarily involving encryption keys. The TPM is usually installed on the motherboard of a computer or laptop.

To get an SCCM report on TPM status, we need to extend the hardware inventory to get the TPM chip information included in the inventory. Check this myitforum post for information on extending the inventory.
http://www.myitforum.com/forums/Creating-a-Query-to-determine-TPM-Chip-status-on-Laptops-m209158.aspx

If the above steps are made, then the below given query can be used to get a SQL Report for listing the Laptop names on which on TPM is enabled and/or activated.

Select sys.Name0 as "Machine Name",

sys.AD_Site_Name0 as "Site Name",

TPM.timestamp,

v_GS_COMPUTER_SYSTEM.Manufacturer0 AS Manufacturer,

v_GS_COMPUTER_SYSTEM.Model0 AS Model,

TPM.ManufacturerVersion0 as 'Manufacturer Version',

"TPM_VERSION" = CASE

When TPM.SpecVersion0 in ('1.2, 2, 0', '1.2, 2, 1', '1.2, 2, 2', '1.2, 2, 3')

THEN '1.2'  ELSE 'Null'

END,

"TPM_Activated" = CASE

When TPM.IsActivated_InitialValue0 = 1 THEN 'Yes'

ELSE 'No'

END,

"TPM_Enabled" = CASE

When TPM.IsEnabled_InitialValue0 = 1 THEN 'Yes'

ELSE 'No'

END

from v_GS_TRUSTED_PLATFORM_MODULE TPM

Join v_r_system sys on sys.ResourceID = TPM.ResourceID

inner join v_GS_COMPUTER_SYSTEM on (v_GS_COMPUTER_SYSTEM.ResourceID = tpm.ResourceID)

--where sys.AD_Site_Name0 = @ADSitename

order by "TPM_Activated" asc, "TPM_VERSION" desc, TPM.timestamp 

Ref.:http://social.technet.microsoft.com/Forums/systemcenter/en-US/760c10c5-324a-4e3f-a5e5-5ebdc5b37d3a/create-sccm-report-to-show-tpm-status

SQL Query for MS Office with Hostname details

SCCM Report for all versions of MS Office with Service pack and Hostname details

SELECT  dbo.v_R_System.Name0,

dbo.v_R_System.user_name0,

dbo.v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System],

arp.DisplayName0,

CASE WHEN arp.version0 LIKE '11.0.6361.0' THEN 'SP1'

     WHEN arp.version0 LIKE '11.0.7969.0' THEN 'SP2'

     WHEN arp.version0 LIKE '11.0.8173.0' THEN 'SP3'

     WHEN arp.version0 LIKE '12.0.6215.1000' THEN 'SP1'

     WHEN arp.version0 LIKE '12.0.6425.1000' THEN 'SP2'

     WHEN arp.version0 LIKE '14.0.6029.1000' THEN 'SP1'

ELSE 'NULL' END as 'Service Pack', arp.Version0

FROM dbo.v_Add_Remove_Programs AS arp

INNER JOIN dbo.v_R_System ON arp.ResourceID = dbo.v_R_System.ResourceID

INNER JOIN dbo.v_RA_System_SMSInstalledSites AS ASSG ON dbo.v_R_System.ResourceID = ASSG.ResourceID

INNER JOIN dbo.v_GS_OPERATING_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID

  --JOIN v_FullCollectionMembership on v_R_System.ResourceID = v_FullCollectionMembership.ResourceID

WHERE 
--v_FullCollectionMembership.CollectionID = ‘@CollectionID’ AND

(arp.DisplayName0 LIKE '%Microsoft Office%edition%' OR

 arp.DisplayName0 LIKE '%Microsoft Office Standard 2007%' OR

 arp.DisplayName0 LIKE '%Microsoft Office Enterprise 2007%' OR

 arp.DisplayName0 LIKE '%Microsoft Office Professional%2007%' OR

 arp.DisplayName0 LIKE '%Microsoft Office Standard 2010%' OR

 arp.DisplayName0 LIKE '%Microsoft Office Enterprise 2010%' OR

 arp.DisplayName0 LIKE '%Microsoft Office Professional%2010%' OR

 arp.DisplayName0 LIKE 'Microsoft Office 2000%' OR

 arp.DisplayName0 LIKE 'Microsoft Office XP%') AND

(arp.DisplayName0 NOT LIKE '%update%') AND

(arp.DisplayName0 NOT LIKE '%Microsoft Office XP Web Components') AND

(dbo.v_R_System.Operating_System_Name_and0 NOT LIKE '%server%') AND

  (arp.InstallDate0 NOT LIKE 'NULL')

ORDER BY arp.Version0, dbo.v_R_System.Name0 

Please note that the above query excludes Windows Server operating system records. You may comment Line-30 to include server OS records on the report.

If you wish to run the query to prompt for a collection, you can remove the comment on Line-16 and Line-18 to achieve it.