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.
What I already found:
select DateCreated, DateLastModified, DisplayName, Manufacturer, SoftwareVersion, CreatedBy, LastModifiedBy
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]
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
from v_DeploymentSummary DS
where softwarename <> ' '
group by SoftwareName, collectionname,DeploymentTime,NumberSuccess,NumberInProgress,NumberErrors,NumberUnknown,NumberTotal,Collectionid