Last week,I was working on request to get the application information which includes Application Name ,Deployment Type ,Content location,Command lines (install and uninstall),what type of deployment is it,detection type,user requirement and other information. Since there are no default reports to get this kind of information ,you must consider writing new SQL/SSRS report else go manual procedure to write down the information for the applications.
In Configmgr 2007 and Configmgr 2012 ,the information about packages like installation command line,uninstallation,requirements (windows 7,windows 8) and other properties can be retried directly from SQL database without any trouble but if you consider the same requirement for applications,its different story.
In configmgr 2012,the application information is not stored directly as you see for packages ,instead it is stored in .XML file (SDMPackageDigest) .So retrieving this information might require additional SQL skills .It took little while for me to figure this out. Information about applications and its properties is stored in different views or I would call them as functions. Some of them are v_ConfigurationItems, dbo.fn_ListApplicationCIs(1033),dbo.fn_ListDeploymentTypeCIs 1033),dbo.fn_ListApplicationCIs_List(1033) etc.
Couple of reference articles helped me to achieve this report are :http://blogs.technet.com/b/hhoy/archive/2012/07/19/how-to-query-configuration-manager-2012-xml-data-with-sql-xquery.aspx and https://msdn.microsoft.com/en-IN/library/ms177400.aspx?
Lets jump into the subject line to extract the information stored in XML file .
AS I said above,the application properties includes deployment type,install,uninstall and properties that you see within the application+deployment type are stored in table with column called:SDMPackageDigest ,For more ,please read here.
AS I highlighted in above screen in red color,all the fields information is stored in one XML file for each deployment type. For ex: if you have application with 3 deployment types,you will have 3 XML files that store the information.
To see how the XML store the information ,you can simply run the below Query to display top 10 application information and click on the SDMPackageDigest. It will pop-up XML file .
select TOP 10 * from vCI_ConfigurationItems where CIType_ID = '21'
CIType_ID=21 is for applications.
XML File:
To create report, I like to use functions (dbo.fn_ListApplicationCIs(1033) and dbo.fn_ListDeploymentTypeCIs(1033) ) to retrieve the information instead of using views as functions always executes faster compare to views.
I have presented the most used and limited information in this report .You can still add other information which you might be interested from the XML file using the same logic .
SQL Query:
You can take the below SQL syntax to create SSRS Report (I will post the RDL file soon ) or run this query in SQL server Management studio .
;WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/06/14/Rules', 'http://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest' as p1)
SELECT
A.[App Name],max(A.[DT Name])[DT Title],A.Type
,A.ContentLocation ,A.InstallCommandLine,A.UninstallCommandLine,A.ExecutionContext,A.RequiresLogOn
,A.UserInteractionMode,A.OnFastNetwork,A.OnSlowNetwork,A.DetectAction
from (
SELECT LPC.DisplayName [App Name]
,(LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Title)[1]', 'nvarchar(max)')) AS [DT Name]
,LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/@Technology)[1]', 'nvarchar(max)') AS [Type]
,LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/p1:Contents/p1:Content/p1:Location)[1]', 'nvarchar(max)') AS [ContentLocation]
,LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/p1:InstallAction/p1:Args/p1:Arg)[1]', 'nvarchar(max)') AS [InstallCommandLine]
,LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/p1:UninstallAction/p1:Args/p1:Arg)[1]', 'nvarchar(max)') AS [UninstallCommandLine]
,LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/p1:InstallAction/p1:Args/p1:Arg)[3]', 'nvarchar(max)') AS [ExecutionContext]
,LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/p1:InstallAction/p1:Args/p1:Arg)[4]', 'nvarchar(max)') AS [RequiresLogOn]
,LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/p1:InstallAction/p1:Args/p1:Arg)[8]', 'nvarchar(max)') AS [UserInteractionMode]
,LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/p1:Contents/p1:Content/p1:OnFastNetwork)[1]', 'nvarchar(max)') AS [OnFastNetwork]
,LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/p1:Contents/p1:Content/p1:OnSlowNetwork)[1]', 'nvarchar(max)') AS [OnSlowNetwork]
,LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/p1:DetectAction/p1:Provider)[1]', 'nvarchar(max)') AS DetectAction
FROM
dbo.fn_ListApplicationCIs(1033) LPC
RIGHT Join fn_ListDeploymentTypeCIs(1033) LDT ON LDT.AppModelName = LPC.ModelName
where LDT.CIType_ID = 21 AND LDT.IsLatest = 1
) A
GROUP BY A.[App Name],A.Type,A.ContentLocation,A.InstallCommandLine,A.UninstallCommandLine,A.ExecutionContext,A.RequiresLogOn,A.UserInteractionMode,
A.OnFastNetwork,A.OnSlowNetwork,A.DetectAction
Results:
when you copy paste ,take care of quotes (‘), sometimes blog convert them to fancy quotes.
Download the SSRS Report (.RDL) file from TechNet Gallary here .
Upload the RDL file to your SSRS Report folder,change the data source,run the report.
11 Comments
Probably a long abandoned thread, but just wondering if there was a way to standardize all msiexec install and uninstall commands, say add or replace all /L*v switches with the desired log folder for every package... could be a long stretch...?
Hi Danzi,
if you want to replace the command line syntax for all MSI applications, you can use the PowerShell script or community tools.
Making changes to the database is not supported unless you go through the Microsoft support engineer.
Thanks,
Eswar
Thx Eswar, you're article was one of the many articles I red to reach my own goal to have an SQL query for creating a temporary table to use with info about apps which are superseeded with the Uninstall selected.
I need this info for our application archiving process as an extra argument to not archive apps which are superseeded and has the option Uninstall checked in the superseed.
I know this is an outdated article but just to help others I will contribute my query:
/*
We must drop the temporary tables if they already exist.
*/
IF Object_id('tempdb..#AppsAndThereSupersedes') IS NOT NULL
DROP TABLE #AppsAndThereSupersedes
/*
Get All DT's from superseeded apps which Action Uninstall is TRUE, #SuperseedingDTS.
DTAppRef = App ID which is superseeded
DTRef = DT ID from superseeded App
DTUninstall = Is superseed with Uninstall
*/
;WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest')
SELECT DISTINCT DTName,
DTAppRef,
DTRef,
DTUninstall
INTO #AppsAndThereSupersedes
FROM (
SELECT SDMPackageDigest AS objXML,
SDMPackageDigest.value('(/AppMgmtDigest/*:DeploymentType/*:Title)[1]', 'nvarchar(256)') AS DTName,
(DTAppRef.X.value('./@LogicalName','nvarchar(256)')) AS DTAppRef,
(DTRef.X.value('./@LogicalName','nvarchar(256)')) AS DTRef,
(DTRef.X.value('./@Changeable','nvarchar(256)')) AS DTUninstall
FROM vCI_ConfigurationItems
CROSS APPLY SDMPackageDigest.nodes('/AppMgmtDigest/DeploymentType/Supersedes/*:DeploymentTypeRule/*:DeploymentTypeIntentExpression/*:DeploymentTypeReference/.') AS DTRef(X)
CROSS APPLY SDMPackageDigest.nodes('/AppMgmtDigest/DeploymentType/Supersedes/*:DeploymentTypeRule/*:DeploymentTypeIntentExpression/*:DeploymentTypeApplicationReference/.') AS DTAppRef(X) ) AS TEMP
SELECT *
FROM #AppsAndThereSupersedes
ORDER BY DTName ASC
Hi Eric,
Appreciate your contribution and helping others.
Thanks,
Eswar
Pingback: How to find a Configuration Manager application that uses a specific file for install – System Center Configuration Manager Notes
I never leave comments on stuff. But this is some of the coolest and most useful SQL I've ever encountered. And it just works! Thank you so much.
Thanks for the feedback and glad you liked it.
Regards,
Eswar
Hey Eswar, this is great stuff. Can you elaborate how to grab the Requirements defined on the deployment type? I'm having challenges getting the XML pathing just right. If I add "Memory > 1MB", I get the value "GreaterThan" back but not the 1MB.
Hi Russ,
you can use the following SQL code to get the requirements tab but it is not dynamic means, if you have set multiple requirements ,you need to that many times in the SQL.
,LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Requirements/Rule/Annotation/DisplayName/@Text)[1]', 'nvarchar(max)') AS [Requirement1]
,LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Requirements/Rule/Annotation/DisplayName/@Text)[2]', 'nvarchar(max)') AS [Requirement2]
Full SQL Code:
;WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/06/14/Rules', 'http://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest' as p1)
SELECT
A.[App Name],max(A.[DT Name])[DT Title],A.Type
,A.ContentLocation ,A.InstallCommandLine,A.UninstallCommandLine,A.ExecutionContext,A.RequiresLogOn
,A.UserInteractionMode,A.OnFastNetwork,A.OnSlowNetwork,A.DetectAction ,A.Requirement1,A.Requirement2
from (
SELECT LPC.DisplayName [App Name]
,(LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Title)[1]', 'nvarchar(max)')) AS [DT Name]
,LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/@Technology)[1]', 'nvarchar(max)') AS [Type]
,LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/p1:Contents/p1:Content/p1:Location)[1]', 'nvarchar(max)') AS [ContentLocation]
,LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/p1:InstallAction/p1:Args/p1:Arg)[1]', 'nvarchar(max)') AS [InstallCommandLine]
,LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/p1:UninstallAction/p1:Args/p1:Arg)[1]', 'nvarchar(max)') AS [UninstallCommandLine]
,LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/p1:InstallAction/p1:Args/p1:Arg)[3]', 'nvarchar(max)') AS [ExecutionContext]
,LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/p1:InstallAction/p1:Args/p1:Arg)[4]', 'nvarchar(max)') AS [RequiresLogOn]
,LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/p1:InstallAction/p1:Args/p1:Arg)[8]', 'nvarchar(max)') AS [UserInteractionMode]
,LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/p1:Contents/p1:Content/p1:OnFastNetwork)[1]', 'nvarchar(max)') AS [OnFastNetwork]
,LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/p1:Contents/p1:Content/p1:OnSlowNetwork)[1]', 'nvarchar(max)') AS [OnSlowNetwork]
,LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/p1:DetectAction/p1:Provider)[1]', 'nvarchar(max)') AS DetectAction
,LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Requirements/Rule/Annotation/DisplayName/@Text)[1]', 'nvarchar(max)') AS [Requirement1]
,LDT.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:DeploymentType/p1:Requirements/Rule/Annotation/DisplayName/@Text)[2]', 'nvarchar(max)') AS [Requirement2]
FROM
dbo.fn_ListApplicationCIs(1033) LPC
RIGHT Join fn_ListDeploymentTypeCIs(1033) LDT ON LDT.AppModelName = LPC.ModelName
where LDT.CIType_ID = 21 AND LDT.IsLatest = 1
) A
GROUP BY A.[App Name],A.Type,A.ContentLocation,A.InstallCommandLine,A.UninstallCommandLine,A.ExecutionContext,A.RequiresLogOn,A.UserInteractionMode,
A.OnFastNetwork,A.OnSlowNetwork,A.DetectAction,A.Requirement1,A.Requirement2
I am not sure how to make the requirements column as dynamic .
Great article Eswar. Really useful!
Thanks Nicke 😉