MDT 2012 Update 1 contains a nice little and very easy to use feature known as Package Mapping. This feature enables software to be automatically (re-)installed during a refresh scenario task sequence if it is installed in the current OS instance/installation.
It does this by mapping the software listed in Add/Remove Programs on the existing OS installation (by querying ConfigMgr’s hardware inventory information for that system) to ConfigMgr packages and then adds those packages (and programs) to the package install list. To easily set this up, follow Brad Tucker’s blog post: Dynamically Installing ‘Computer Specific’ Applications Using Configuration Manager with MDT. Note that are other blog posts out there on setting this up and improving upon the process, but Brad’s is a great starting point that should work for most folks initially.
Unfortunately, MDT’s built-in capabilities only take into account the “classic” packages and programs and not the new Application Model of ConfigMgr 2012; however, it is actually very easy to add this in.
1. Create the dbo.ApplicationMapping table
This is easily done by simply duplicating the existing dbo.PackageMapping table by right-clicking on the dbo.PackageMapping table in SQL Management studio, and select Script Table as –> CREATE To –> New Query Editor Window.
This opens up a new query windows with the data definition language (DDL) to create the PackageMapping table. Change DDL to reflect the name of our new table: ApplicationMapping. Also change the name of the Packages column to Applications (it is very important that you name it Applications because this is known to and used by the magic of ZTIGather. Hit Execute and the new table will be created.
Just like the PackageMapping table, you will need to populate this table with the display name of applications from Add/Remove Programs. Instead of using the package ID and program name separated by a colon though, use the name of the Application in the Applications column. You do not specify which deployment type to use because those are chosen dynamically at application installation time based upon the requirements of the deployment types just like during a normal application deployment.
2. Create the dbo.RetrieveApplications stored procedure
We’ll once again duplicate an existing object for this: right-click on dbo.RetrievePackages and select Modify.
This will also open a query window displaying DDL; this time it’s the DDL for the dbo.RetrievePackages stored procedure. Change this DDL to reflect the new stored procedure’s name, RetrieveApplications. Also, update the table it is querying from PackageMapping to ApplicationMapping. Finally, change the DDL to create the new stored procedure by changing ALTER to CREATE as highlighted in green below. (Note that the DDL will initially be mostly on a single line that requires you to scroll to the right to see and modify it – you can easily add a few new lines if you would like though to make it easier to read and modify as I have done in the screenshot below.) Note also the changes highlighted in red below for the name of my ConfigMgr DB and the highlight in purple to use the display name from ARP instead of the program ID – these changes are detailed in Brad’s post. Hit Execute and the new stored procedure will be created.
3. Add a section to your customsettings.ini
Similar to the section you added for the package mapping (and detailed in Brad’s post), except with the new stored procedure referenced:
[DynamicApplications]
SQLServer=HYD-SRV1
Database=MDT
StoredProcedure=RetrieveApplications
NetLib=DBNMPNTW
Parameters=MacAddress
SQLShare=DeploymentShare$
Make sure to add DynamicPackages to the Priority line under the Settings section in your customsettings.ini file also.
That’s it. As mentioned above, the magic of ZTIGather will automatically do the rest. Here’s a quick overview of what ZTIGather will do though once it executes the section:
1. Run the RetrieveApplications stored procedure, passing it the MAC address of the current system. This, in turn, queries the ConfigMgr DB for all ARP entries for the system with that MAC Address. These are then matched up, by display name, to the rows in the ApplicationMapping table and all matches are returned.
2. The returned, matched rows all have two columns: ARPName and Applications. We really don’t care about ARPName at this point and neither does ZTIGather; however, ZTIGather sees the Applications column and recognizes it as a “special” column because it is defined as a list in ZTIGather.xml. Thus, it adds the value of the Applications column from every returned row to the Applications MDT property list. Note that through more magic of ZTIGather, if the Application is already in the list, it won’t be added again.
3. ZTIGather translates the Applications property list into a series of task sequence variables, one for every application in the list, named Application01, Application02, Application03, etc.
4. Finally, the Install Applications task in the task sequence uses Applicationxy task sequence variables to install the applications from the ConfigMgr applications.
While I haven’t always been an ardent fan of MDT, it is pure magic sometimes and always works really well.
Jason,
I don’t think you actually need to do any of this to make it work in SCCM 2012 SP1 with MDT 2012 Update 1. Instead of putting the PKGID:ProgramName in the Packages field, put the Application Name only and it works out of the box.
-M
No, you definitely need to do this. The built in functionality updates the PACKAGES variable which is used in an Install Software task which can only reference packages. We need it to populate the APPLICATIONS variable which is used in an Install Applications task which is for installing applications.
It looks like the SQL query only works for x86 software, not 64-bit.
It may be useful to create an previously-installed software list instead of installing them through the task sequence. Would it be possible to query the application list from the SCCM DB, filter through a mapping table in MDT, and add to a text file on the target drive?
You are correct, but that’s super easy to fix, just replace v_GS_ADD_REMOVE_PROGRAMS with v_Add_Remove_Programs in the stored procedure. What you’ve described is exactly what is already happening.
Does this require MDT 2013? I currently have 2012 update 1 and my Applications SP returns my applications with “001” like packages and not the install application task format “01”.
Hi Terry,
I think you also posted this on the TechNet forums or myITForum DL and I think it got answered there, but I’ll just reiterate. Basically, ConfigMgr changed the format of the dynamic variables from using three digits to two at the end. To handle this, MDT, instead of actually changing their code to create variables with only two digits, added a script and step to convert the variables to two digits. Thus, you need to make sure that this task is included and running properly in your TS.
something to consider is that *IF* you are going to be using media to deploy your image relying on unknown computer support for your deployment, you may need to edit your stored procedure to include the resource discovery view. Otherwise your dynamic application discovery could return information despite deleting the existing computer account with a pre-execute powershell script… Instead of use v_add_remove, I used v_GS_INSTALLED_SOFTWARE…
Hi Jimmy,
Yes this is valid. It’s ultimately a scenario not accounted for (or explicitly not intended) by the package mapping functionality of MDT and since I’m really just copying their SP for this, I suffer from that same assumption that they made. I think the functionality also suffers from a difference in the way ConfigMgr 2012 handles resource deletes. A delete in 2007 actually deleted the record; this is no longer true in 2012.
Thanks!
Hi Jason,
I’m trying to setup Package/Application mapping with mapping table in MDT 2012 Update 1 and SCCM 2012 SP1. I understand for most your stored procedure in SQL query that processes app mapping. However I don’t know how this fits into and integrate with my Windows 7 x86 & x64 task sequence. Where do I insert the store procedure to scan, pairing an older app under ARP with the latest version in the mapping table and installing those XP packages & applications on the newly imaged Windows 7 machines. Looks like you might already done this so any help will be much appreciated. If contacting you on the phone is possible please let me know what number you can be reached at.
Thanks,
Rashid
You add the stored procedure call to your customsettings.ini per the linked blog that discusses adding package mapping.
Are you aware of any replace scenarios? I would like to use application mapping to make the transition to new hardware almost seamless by pulling down applications that were previously installed from the App Catalog.
Hi Scott,
No, not off hand, although with a little SQL no-how, the queries included in MDT (and re-purposed) here could be used. You would have to first query the computer association to get the old computer (or perhaps you could prompt for it) and then use that value in he queries instead of the current computer.
Jason
Hi Scott,
No, not off hand, although with a little SQL know-how, the queries included in MDT (and re-purposed) here could be used. You would have to first query the computer association to get the old computer (or perhaps you could prompt for it) and then use that value in he queries instead of the current computer.
Jason
Is there a way to modify the RetrieveApplications stored procedure so it queries for both 32bit and 64bit packages? Or, would we be better off copying the RetrieveApplications SP to something like RetrieveApplications64 and modifing the rest accordingly?
Great post, btw-
Rob
So, the answer to my question… just use v_ADD_REMOVE_PROGRAMS in the query/SP. That one includes teh 64bit apps, including all of the 32bit ones as well.
Thanks for the great post-
-Rob
You’re welcome and glad you found the answer.
Old thread, but wanted to add a comment: You can also modify this to query the “System Name”, instead of the MAC address. I was working with a client who built a lot of machines with USB dongles, so the MAC address issue was… not attractive.
If you modify the SP like this:
USE [MDT]
GO
/****** Object: StoredProcedure [dbo].[RetrieveApplicationsXXX] Script Date: 2/12/2016 8:11:47 AM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[RetrieveApplicationsXXX] @MachineName CHAR(7) AS SET NOCOUNT ON /* Select and return all the appropriate records based on current inventory */ SELECT * FROM ApplicationMapping WHERE ARPName IN ( SELECT DisplayName0 FROM CM_XXX.dbo.v_ADD_REMOVE_PROGRAMS a, CM_XXX.dbo.v_GS_System n WHERE a.ResourceID = n.ResourceID AND Name0 = @MachineName )
And then, for the CustomSettings.ini:
;Dynamic Applications; Using the RetrieveApplicationsXXX, which queires V_System instead of V_Network
[DynamicApplications]
SQLServer=YourFunFilledServerName.AwesomeDomain.AwesomeCom
Database=MDT
StoredProcedure=RetrieveApplicationsXXX
NetLib=DBNMPNTW
Parameters=OSDComputerName
SQLShare=DeploymentShare$
XXXARPApps=Applications
You need to populate the OSDComputerName in MDT, so there’s obviously some more ‘work’ needed to be done on the back end, but I found it much more reliable. YMMV of course.
Hello sir.
We keep getting “ZTI error opening SQL Connection: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. (-2147467259)” in ZTIGather.log. We noted that it seems to be using the SCCM Network Access Account to authenticate. We went into SQL Management Studio and gave the NAA account Read and Write permissions to the MDT database, but to no avail.
I also noted that my CustomSettings.ini file targets the SQL server by FQDN, not just it’s name. Is that a problem?
Was hoping you might offer an opinion.
Have you manually tried accessing the database using a UDL file (https://blogs.msdn.microsoft.com/steverac/2010/12/13/test-remote-sql-connectivity-easily/)?
Has this ever worked for you?
Did you enable named-pipes authentication for the SQL instance hosting the MDT database?