Sccm sql: query all applications, deploys to collections and foldername

Hi,

I d need a sql query to directly query sccm for all applications with details plus to which collections they are deployed.

I also would like to know in which folder the applications are located (we have some folders named like f.e. test, tophaseout etc).

I can do this via Powershell but it takes me 10 minutes to run the query. Don t need an ssrs report, just need an (almost) instant sql query to the sccm db.

I need to execute it remotely to a sql server (so no wql, don't get the logic why wql is useful anyways).

Found some queries but can t match them together (below).

We have the latest sccm version.

Thanks!
---

What I already found:

Applications

select DateCreated, DateLastModified, DisplayName, Manufacturer, SoftwareVersion, CreatedBy, LastModifiedBy
from fn_ListLatestApplicationCIs(1033)
 
GROUP BY DateCreated,DateLastModified,DisplayName,Manufacturer,SoftwareVersion,CreatedBy, LastModifiedBy
ORDER BY DateCreated DESC



Packages per folder, but would need applications, not packages:

SELECT v_Package.Name AS [Package Name]
FROM v_Package
JOIN vFolderMembers ON v_package.PackageID = vFolderMembers.InstanceKey
JOIN vSMS_Folders on vFolderMembers.ContainerNodeID = vSMS_Folders.ContainerNodeID
WHERE vSMS_Folders.Name = 'x64'
ORDER BY v_Package.Name



Software deployment per collections

Softwarename,CollectionName,Collectionid,ds
DeploymentTime,NumberSuccess,NumberInProgress,NumberErrors,NumberUnknown,NumberTotal
from v_DeploymentSummary DS
where softwarename <> ' '
group by SoftwareName, collectionname,DeploymentTime,NumberSuccess,NumberInProgress,NumberErrors,NumberUnknown,NumberTotal,Collectionid