troubleshooting Question

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

Avatar of janhoedt
janhoedt asked on
PowershellSCCMSQL
4 Comments1 Solution121 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
aravind anche
Windows/Vmware

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros