Finding the application of a given SCCM deployment via T-SQL

Recently a coworker was troubleshooting some application installation issues on a device. Looking through the “AppEnforce” log he found the deployment name and Id. As an example, here’s what it would look like if you were troubleshooting the same log (I highlighted the deployment name yellow, and the deployment id green for reference):

AppEnforce Log - DT Info

Although he had the deployment information, he didn’t know the application associated with the deployment. So he tried searching in the console using the deployment to try and find the associated application but wasn’t able to find anything. So, he asked me if I knew a way.

Through SQL this is actually pretty easy. There’s a table that stores all the CIs (both deployments and applications are CIs), and a table that stores the relationship between the CIs. So, I wrote a query that allows you to plug in a deployment and get the application information returned!

Just plug in the “Id” (highlighted green in above screenshot) from the log into the “@ModelName” variable and run the query:

DECLARE @ModelName nvarchar(300) = N'ScopeId_73F3BB5E-5EDC-4928-87BD-4E75EB4BBC34/DeploymentType_160c3c2d-1a94-4523-a999-2ff80066a53c';
SELECT  dts.CI_ID AS [DeploymentType_CIID]
       ,app.CI_ID AS [Application_CIID]
       ,loc.DisplayName AS [ApplicationName]
       --,loc.LocaleID AS [ApplicationLocaleId]
  FROM dbo.v_ConfigurationItems dts WITH (NOLOCK)
       INNER JOIN dbo.v_CIRelation cir WITH (NOLOCK)
          ON dts.CI_ID = cir.ToCIID
         AND cir.RelationType = 9
         AND dts.CIType_ID = 21
         AND dts.IsLatest = 1
       INNER JOIN dbo.v_ConfigurationItems app WITH (NOLOCK)
          ON cir.FromCIID = app.CI_ID
         AND app.IsLatest = 1
       INNER JOIN dbo.v_LocalizedCIProperties loc WITH (NOLOCK)
          ON app.CI_ID = loc.CI_ID
 WHERE dts.ModelName = @ModelName;

It’ll return the following:

DT to App Query Results

There you have it. An easy way to find the application name from the deployment!

Wait…what if you wanted to use the name of the DT (highlighted yellow in above screenshot) instead of the Id? Okay, here you go, just replace the variable “@DTName” with your value…

DECLARE @DTName nvarchar(513) = N'InstallMBAM-x64';
SELECT  dts.CI_ID AS [DeploymentType_CIID]
       ,app.CI_ID AS [Application_CIID]
       ,loc.DisplayName AS [ApplicationName]
       --,loc.LocaleID AS [ApplicationLocaleId]
  FROM dbo.v_LocalizedCIProperties dtn WITH (NOLOCK)
       INNER JOIN dbo.v_ConfigurationItems dts WITH (NOLOCK)
          ON dtn.CI_ID = dts.CI_ID
         AND dts.IsLatest = 1
       INNER JOIN dbo.v_CIRelation cir WITH (NOLOCK)
          ON dts.CI_ID = cir.ToCIID
         AND cir.RelationType = 9
         AND dts.CIType_ID = 21
         AND dts.IsLatest = 1
       INNER JOIN dbo.v_ConfigurationItems app WITH (NOLOCK)
          ON cir.FromCIID = app.CI_ID
         AND app.IsLatest = 1
       INNER JOIN dbo.v_LocalizedCIProperties loc WITH (NOLOCK)
          ON app.CI_ID = loc.CI_ID
 WHERE dtn.DisplayName = @DTName;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s