SCCM Database Queries: Find Executable Files by Name, Version and Install Count

Find all instances of a particular file by its distinct Version number (e.g. "FileVersion" property).  In this example, I'm looking for what versions of Sysinternals' PsExec.exe are in the environment, and how many instances were found for each version.

[Begin T-SQL]
SELECT DISTINCT 
[ExecutableName0],[FileVersion0], COUNT(*) AS QTY
FROM [dbo].[v_GS_INSTALLED_EXECUTABLE]
WHERE ExecutableName0 = 'psexec.exe'
GROUP BY ExecutableName0, FileVersion0
ORDER BY FileVersion0
[End T-SQL]

Find Configuration Manager Collections with Both Direct and Query Rule Members

List all Collections which have both Direct members and Query-Rules assigned within your SCCM 2012 Site.  This also works with Configuration Manager 2007...

[begin T-SQL]
SELECT DISTINCT 
dbo.v_CollectionRuleDirect.CollectionID, 
dbo.v_Collection.Name
FROM 
dbo.v_CollectionRuleDirect INNER JOIN
dbo.v_CollectionRuleQuery ON dbo.v_CollectionRuleDirect.CollectionID = dbo.v_CollectionRuleQuery.CollectionID INNER JOIN
dbo.v_Collection ON dbo.v_CollectionRuleDirect.CollectionID = dbo.v_Collection.CollectionID
ORDER BY dbo.v_Collection.Name
[end T-SQL]