SCCM Computer Model SQL report

This is a sample report for HP Elitebook 8440p model. You can replace the expected model value. 

select distinct 
a.Name0 as 'Host Name', 
a.UserName0 as 'UserName', 
c.Caption0 as 'OS Detail', 
a.Model0 as 'Model' 
from v_GS_COMPUTER_SYSTEM a
inner join v_GS_OPERATING_SYSTEM c on c.ResourceID=a.ResourceID
where a.Model0 like 'HP EliteBook 8440p%'

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

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.

SQL Joins - INNER, OUTER, LEFT and RIGHT

Here is a simple post from Eswar explaining why and where to use SQL join. Please check the below link to view the post:

Writing SCCM Reports using SQL Joins

http://eskonr.com/2012/08/writing-sccm-reports-using-sql-joins/

For more detailed information on SQL joins, Check the below post by Suresh Dasari:

Joins in sql server, Inner Join, Cross Join, Left Outer Join, Equi join, Right Outer Join, Full Outer Join

http://www.aspdotnet-suresh.com/2011/12/different-types-of-joins-in-sql-server.html