We help IT Professionals succeed at work.

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

janhoedt
janhoedt asked
on
Medium Priority
58 Views
Last Modified: 2020-02-21
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
Comment
Watch Question

Windows/Vmware
CERTIFIED EXPERT
Commented:
Something like this for collection

SELECT TOP (100) PERCENT fn_ListLatestApplicationCIs_1.DateCreated, fn_ListLatestApplicationCIs_1.DateLastModified, fn_ListLatestApplicationCIs_1.Manufacturer, fn_ListLatestApplicationCIs_1.SoftwareVersion, 
                  fn_ListLatestApplicationCIs_1.CreatedBy, fn_ListLatestApplicationCIs_1.LastModifiedBy, dbo.v_ApplicationAssignment.CollectionName, fn_ListLatestApplicationCIs_1.DisplayName
FROM     dbo.fn_ListLatestApplicationCIs(1033) AS fn_ListLatestApplicationCIs_1 INNER JOIN
                  dbo.v_ApplicationAssignment ON fn_ListLatestApplicationCIs_1.DisplayName = dbo.v_ApplicationAssignment.ApplicationName
GROUP BY fn_ListLatestApplicationCIs_1.DateCreated, fn_ListLatestApplicationCIs_1.DateLastModified, fn_ListLatestApplicationCIs_1.Manufacturer, fn_ListLatestApplicationCIs_1.SoftwareVersion, fn_ListLatestApplicationCIs_1.CreatedBy, 
                  fn_ListLatestApplicationCIs_1.LastModifiedBy, dbo.v_ApplicationAssignment.CollectionName, fn_ListLatestApplicationCIs_1.DisplayName
ORDER BY fn_ListLatestApplicationCIs_1.DateCreated DESC

Open in new window

Author

Commented:

Thanks, that's already a part of the solution :-)

aravind ancheWindows/Vmware
CERTIFIED EXPERT

Commented:
does that help you? or do you need anything else to add?

Author

Commented:

Your query show collections, my query gets applications but not per folder.


I d need to have the applications with the folder they are in (only 2 rootfolders exist in applications) and the collections they are deployed to.


So how do I match the different queries to 1 query that shows applications (with foldername and other details) and the.collections they are deployed to?