In complex ConfigMgr 2007 environments it was often hard to locate packages and collection that had been buried as child objects. John Nelson wrote some pretty handy SQL queries that would parse the exact path to a given object. ConfigMgr 2012 made an attempt to remedy this issue in several ways. First of all we no longer nest object for organizational purposes, instead we use folder. Secondly each node is searchable. This does tend to make life easier however from time to time I still need to know what folder an object resides in and the built-in search doesn’t give me that ability.

As an example let’s assume I have a collection to which I deploy Adobe Flash 13.x. When Adobe 14.x releases I want to create a new collection called Adobe Flash 14.x and I want it in the same folder as the aforementioned Adobe Flash 13.x. Sure, if I want to make an edit to the Flash 13.x collection I can search for it, locate it and make changes without needing to know where in the Assets and Compliance node it truly lives but that’s not my goal here. I need to know where it lives. To find this I have a created a few SQL queries that can be used in SQL Management Studio or added as SSRS reports.

Below you will find 3 queries. One will list all of your collections with their containing folders, the next does the same for applications and lastly one for traditional packages. I wasn’t able to figure out how to get the full path to the folder if the folder is a sub-folder of other folders but that way I have my folders organized just knowing the containing folder is normally good enough.

 

–Find my collection

select
col.Name as ‘Collection Name’,
F.Name as ‘Folder Name’,
COL.CollectionID as ‘Collection ID’
from
v_collection col
LEFT JOIN vFolderMembers FM on fm.InstanceKey = col.CollectionID
LEFT Join vSMS_Folders F on F.ContainerNodeID = FM.ContainerNodeID
Where FM.ObjectTypeName = ‘SMS_Collection_Device’

order by col.Name

–Find my application

Select Distinct

Apps.DisplayName as ‘Application Name’,
C.ContentSource as ‘Application Source Path’,
F.Name as ‘FolderName’

From fn_ListApplicationCIs(1033) Apps
Left Join v_CIContentPackage CP on CP.CI_ID = Apps.CI_ID
Left Join v_Content C on C.PkgID = CP.PkgID
Left Join vFolderMembers FM on FM.InstanceKey =Apps.ModelName
Left Join vSMS_Folders F on F.ContainerNodeID = FM.ContainerNodeID
Where ContentSource Is not null
Order By ContentSource

 

— Find my package

select
Pkg.Name as ‘Package Name’,
F.Name as ‘Folder Name’,
PKGSourcePAth as ‘Package Source Path’
from
v_Package pkg
LEFT JOIN vFolderMembers FM on fm.InstanceKey = pkg.PackageID
LEFT Join vSMS_Folders F on F.ContainerNodeID = FM.ContainerNodeID
Where FM.ObjectType = 2

I’ve recently migrated all of my software deployments from the legacy package and program model into the ConfigMgr 2012 application model. During a lunch and learn that I was presenting to our User Services team explaining how this works and why we moved to it one of the technicians posed a good question. He asked, “How do I check the status of an application that I’ve targeted to a user”? I know how I would do it; I’d check the monitoring node in the console, however I don’t give the console to User Services. I start digging through the built-in reports and found nothing that would answer his question.

That seemingly simple question turned out to be less than simple. In the past I’ve always trained support staff to read execmgr.log and use the built-in reports. The application model isn’t so simple though, frankly application logs on the client still confuse me. I set out to write a report that accepts the user name as input and displays the status of all deployments targeted to that user and/or targeted to that users’ primary devices.

Here’s a simple way for the Support Desk and Computer Technicians to see the status of applications. This report accepts the user name as input and can be filtered on a given application, set of applications of all applications in ConfigMgr.

image

Here’s the SQL I’m using for the report divided by the 3 datasets required.

–Main dataset:

select
all

SMS_AppDeploymentAssetDetails.AppName as ‘App Name’,

CASE WHEN SMS_AppDeploymentAssetDetails.AppStatusType = ‘1’ Then ‘Success’
when SMS_AppDeploymentAssetDetails.AppStatusType = ‘2’ Then ‘In Progress’
when SMS_AppDeploymentAssetDetails.AppStatusType = ‘3’ Then ‘Requirements Not Met’
when SMS_AppDeploymentAssetDetails.AppStatusType = ‘4’ Then ‘Unknown’
when SMS_AppDeploymentAssetDetails.AppStatusType = ‘5’ Then ‘Error’
else ‘Unknown’
End as ‘App Status Type’,

CASE WHEN SMS_AppDeploymentAssetDetails.ComplianceState = ‘1’ Then ‘Compliant’
WHEN SMS_AppDeploymentAssetDetails.ComplianceState = ‘2’ Then ‘Non-Compliant’
WHEN SMS_AppDeploymentAssetDetails.ComplianceState = ‘4’ Then ‘Error’
WHEN SMS_AppDeploymentAssetDetails.ComplianceState = ‘6’ Then ‘Partial Compliance’
else ‘Unknown’
End as ‘Compliance State’,

CASE WHEN SMS_AppDeploymentAssetDetails.EnforcementState  >= 5000 then ‘Error’
when SMS_AppDeploymentAssetDetails.EnforcementState  >= 4000 then ‘Unknown’
when SMS_AppDeploymentAssetDetails.EnforcementState  >= 3000 then ‘Requirements Not Met’
when SMS_AppDeploymentAssetDetails.EnforcementState  >= 2000 then ‘In Progress’
when SMS_AppDeploymentAssetDetails.EnforcementState  >= 1000 then ‘Success’
else ‘Unknown’
End As ‘Enforcement State’,

CASE WHEN SMS_AppDeploymentAssetDetails.DeploymentIntent  = ‘1’ Then ‘Install’
WHEN SMS_AppDeploymentAssetDetails.DeploymentIntent  = ‘2’ Then ‘Uninstall’
WHEN SMS_AppDeploymentAssetDetails.DeploymentIntent  = ‘3’ Then ‘Preflight’
else ‘Unknown’
End as ‘Deployment Intent’,

SMS_AppDeploymentAssetDetails.DTName as ‘Deplyoment Type Name’,

CASE WHEN SMS_AppDeploymentAssetDetails.InstalledState  = ‘1’ Then ‘Uninstall’
WHEN SMS_AppDeploymentAssetDetails.InstalledState  = ‘2’ Then ‘Install’
WHEN SMS_AppDeploymentAssetDetails.InstalledState  = ‘3’ Then ‘Unknown’
else ‘Unknown’
End as ‘Installed State’,

CASE WHEN SMS_AppDeploymentAssetDetails.IsMachineAssignedToUser = ‘1’ Then ‘Yes’
Else ‘No’
End as ‘Device Assigned to User?’,
SMS_AppDeploymentAssetDetails.MachineName as ‘Device Name’,

 

CASE WHEN CHARINDEX(‘\’,SMS_AppDeploymentAssetDetails.UserName)>0 THEN SUBSTRING
(SMS_AppDeploymentAssetDetails.UserName,CHARINDEX(‘\’,SMS_AppDeploymentAssetDetails.UserName)+1,255)
ELSE SMS_AppDeploymentAssetDetails.UserName END ‘User Name’

from fn_AppDeploymentAssetDetails(1033) AS SMS_AppDeploymentAssetDetails

where

((SMS_AppDeploymentAssetDetails.UserName like ‘%’ +  @user
or SMS_AppDeploymentAssetDetails.UserName =  ‘(SYSTEM)’)

and

(SMS_AppDeploymentAssetDetails.AppName in (@appname )))

ORDER BY UserName

 

–AppName parameter:

Select

distinct

SMS_AppDeploymentAssetDetails.AppName from fn_AppDeploymentAssetDetails(1033) AS SMS_AppDeploymentAssetDetails

 

–The User parameter is simply a text field.

 

Happy reporting!