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.