There is a little known component of the MDT database that, when used with Configuration Manager, can automatically re-install applications that were previously installed on a device. It is not well known, simply because it isn’t readily visible from the database view within the Deployment Workbench.
I decided to write this entry, to explain the MDT PackageMapping table and RetrievePackages stored procedure.
This process is sometimes, unofficially, referred to as Zero Touch Applications. ZTA refers to the unattended application installation process integrated into Zero Touch Installation. It utilizes the Add/Remove Programs (ARP) data from the ConfigMgr database and maps that to a manually maintained “PackageMapping” table that is hosted on the MDT database server. When the ZTIGather is run, it queries the ConfigMgr database for all application names associated to the MAC address that is passed from the machine. It then attempts to match these to entries in the PackageMapping table in the MDT database. If there are matches, they are then used to populate the PACKAGES variable for use in the Install Software step of the task sequence.
The PackageMapping table maintains a list of mappings. The column ARPName refers to the legacy application and the column Packages refers to the package ID and program name of the new package to be installed. Many of my customers will use this to map a new, Windows 7 remediated application to an old application name, so that when the image is deployed, it will automatically install the new version via a dynamic upgrade.
I should point out that the following uses ConfigMgr 2012, but the process is the same on ConfigMgr 2007, even though the console look and feel may be different.
The configuration of the process is as follows…
First, make sure your package and program exist in ConfigMgr. You will notice I have a package called Project Professional 2010 with a package ID of CO100014. It also has a program named ‘Silent Install Project 2K10.’
Second, identify the name that ConfigMgr recognizes for the application to which you want to map. You can do this by launching SQL Management Studio and running the following query against the ConfigMgr database. Notice the DisplayName0 is ‘Microsoft Office Project Professional 2010’. Also, notice the ProdID0 is a GUID. This will be explained momentarily.
Now you need to modify the PackageMapping table. Notice I have added the previously queried DisplayName0, ‘Microsoft Office Project Professional 2010’ in the ARPName column. I have also added the ConfigMgr package CO100014:Silent Install Project 2K10. The format for the Packages column must be PACKAGEID:Program Name.
Now we need to modify the RetrievePackages stored procedure. This can be found in the Programmability node under the MDT database. Right-click this stored procedure and select Modify.
CREATE PROCEDURE [dbo].[RetrievePackages]
SET NOCOUNT ON
/* Select and return all the appropriate records based on current inventory */
SELECT * FROM PackageMapping
WHERE ARPName IN
SELECT DisplayName0 FROM [HYD-SRV1].CM_CO1.dbo.v_GS_ADD_REMOVE_PROGRAMS1 a,
WHERE a.ResourceID = n.ResourceID AND
MACAddress0 = @MacAddress
Notice I have added [HYD-SRV1].CM_CO1. in front of the two ConfigMgr views that are being referenced in the query. This represents my [SCCM_Server].SCCM_Database. There is also another change I like to make in order to make it easier to identify the applications. The ‘SELECT DisplayName0…’ in the original stored procedure is really ‘SELECT ProdID0…’. Remember when we ran our query earlier, I had you look at the ProdID0. It is a GUID. The DisplayName0 is what shows in the Add/Remove section of the operating system, so it is easier to reference. Once all changes are made, click Execute.
Now that we have done all that, we have to modify the customsettings.ini file so that it will run the stored procedure during the ZTIGather. Here is a sample customsettings.ini file with the changes needed in yellow…
Priority= RetrievePackages, DynamicPackages, Default
When I run my task sequence, the ZTIGather.log now looks like the following…
Notice that the SQL query has returned CO100014:Silent Install Project 2010 and assigned it the PACKAGES variable as PACKAGES001.
My lab environment is Windows 2008 R2 with Configuration Manager 2012 RC1, MDT 2012 Beta 2 and SQL 2008 SP2 CU6 . This process, however, is the same on MDT 2010 Update 1 with SCCM 2007 .
My lab has the MDT and ConfigMgr databases on the same server. If this is not your environment, you will need to setup the ConfigMgr server as a linked server in the SQL Management Studio.
Manipulating the PackageMapping table is manual out of the box. You could also script to pull the ARP data from ConfigMgr and then manually map, or you could produce a front-end application that helps users map the applications.
This post was contributed by Brad Tucker, a Senior Consultant with Microsoft Services, East Region, United States
Good explanation of Package Mapping! There are a few problems with this approach, which I wrote about here:
Essentially we have problems with historical data being referenced by the stored procedure, so out-of-date entries are re-populated.
We have also found that this approach doesn't perform well now that x64 OSs are common as the ARP entries are split on architecture. We now reference the InstalledSoftware table instead as this spans architecture. We also have some logic we add to interrogate a computer association to provide for package mapping in the replace computer scenario. Also on my blog.
Thanks for the comment.
First, I must point out the blog entry was more of an introduction to a built-in feature that I don’t see used enough. With that said, you are correct there are some scenarios that will cause some problems.
I was actually going to follow up with some of these, but now it looks like I may not have to.
I do see the replace scenario issue come up a bit, but the others not quite so often.
Some thoughts on other potential problems…
In my entry, I stated that I like to use the DisplayName0 instead of ProdID0. This is OK for smaller environments without a large number of applications. Larger environments mean the possibility of duplication of this entry is there, thus ProdID0 is probably a better approach. For most OEMs, the ProdID0 will be unique for each version and architecture. This is not always the case, however. Also, the ProdID0 is determined by the packager. I have been in many customers that package everything in a unique installer package (think Wise) and the created package file may be APP1.EXE. The ProdID0 for this application was also called APP1.EXE. This would be repeated across versions/architectures.
I have actually had a customer create a unique view that tracked information about the application packages that were installed on the devices via SCCM, among other things. It would use the name of the package as the mapped item. This approach, again, was not without some problems, but it did scale down the data quite a bit.
The key thing to take away from this is this tool is very customizable and will need to be molded to fit each unique environment.
I may at a later time put together a blog post on all known/potential pitfalls to be aware of when using this approach.
Thank you for this great article, I have a single package that contains multiple programs I have modified the stored procedure to return multiple entries referencing the same package but i get a error that I don't understand. Do you know where I can get help
This is what my stored procedure returns:
Adobe Acrobat 9 Pro - English, Français, Deutsch EBJ00005:Per-system unattended
Microsoft Project Professional 2010 EBJ00086:Per System - Install
WorkPoint Express EBJ000A3:Vstor 2010
WorkPoint Express EBJ000A3:PIA for Office 2007
WorkPoint Express EBJ000A3:PIA for Office 2010
WorkPoint Express EBJ000A3:Workpoint Express 4
the first two installs fine but the rest gives me these errors:
Installing pkg 'EBJ000A3', program 'Vstor 2010' InstallSoftware 21-02-2012 14:07:44 2704 (0x0A90)
GetExecRequestMgrInterface successful InstallSoftware 21-02-2012 14:07:44 2704 (0x0A90)
Retrieving value from TSEnv for '_SMSTSPolicyEBJ000A3_Vstor 2010' InstallSoftware 21-02-2012 14:07:44 2704 (0x0A90)
sPolicy.empty() == false, HRESULT=80004005 (e:\nts_sms_fre\sms\client\osdeployment\installsoftware\installsoftware.cpp,117) InstallSoftware 21-02-2012 14:07:44 2704 (0x0A90)
Policy not stored in TaskSequence Environement InstallSoftware 21-02-2012 14:07:44 2704 (0x0A90)
GetPolicyFromTSEnv(m_sPackageID, m_sProgramName, sPolicy), HRESULT=80004005 (e:\nts_sms_fre\sms\client\osdeployment\installsoftware\installsoftware.cpp,991) InstallSoftware 21-02-2012 14:07:44 2704 (0x0A90)
GetPolicy(), HRESULT=80004005 (e:\nts_sms_fre\sms\client\osdeployment\installsoftware\installsoftware.cpp,667) InstallSoftware 21-02-2012 14:07:44 2704 (0x0A90)
Step 4 out of 7 complete InstallSoftware 21-02-2012 14:07:44 2704 (0x0A90)
Install Dynamic software action failed to install packageID: 'EBJ000A3', programID: 'Vstor 2010'. Error Code 0x80004005 InstallSoftware 21-02-2012 14:07:44 2704 (0x0A90)
Is there anything in the MDT docs that walk through this?
I've followed the steps outlined above but i'm getting the following error in my ZTIGather.log file. Right after 'Records returned from SQL = -1'ERROR - Moving to first row (Error Number = 3021) (Error Description: Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.).Any ideas?
Geoff,Sorry for the delay. If you are still interested, in your Deployment Workbench, open the documentation and search for "Deploying Applications Based on Earlier Application Versions".
Martin,First verify in your SCCM DB the view - is it dbo.v_GS_ADD_REMOVE_PROGRAMS or dbo.v_GS_ADD_REMOVE_PRORGRAMS1.Secondly, run the following query (adjusted for answer to first step) replacing ServerA with the name of your server, SCCMDB with the name of your SCCM database, and %MACAddress% with the machine's MAC address.SELECT DisplayName0 FROM [ServerA].SCCMDB.dbo.v_GS_ADD_REMOVE_PROGRAMS a, dbo.v_GS_NETWORK_ADAPTER nWHERE a.ResourceID = n.ResourceID ANDMACAddress0 = %MACAddress0%If you get results, verify there is a matching entry in the ARPName column within the PackageMapping table of the MDT database.
Nice article - very useful!
I just want to provide some clarification for myself, Brad, on your last comment as we received the same error during testing on a specific machine. I've actually just
Reading that error in the ZTIGather.log, I'm assuming it's not "really" an error, but instead an informative message that the query in both cases (I've added Jason's Application Mapping to the database and stored proc's as well), merely reached the End Of File
(EOF) in the database - an indication that the search by the stored proc went through the MDT database and found no matches. Providing, of course, that the variables presented in the proc are correct.
Is this a correct interpretation of the message? (There were no matches in the machine's ARP and the MDT database ARP column.)
If the ARP returns nothing to query the MDT table, or the MDT table itself is blank, you should be receiving a '0' for returned results, not the error (I just confirmed this). My first recommendation is to run it manually (as my previous comment). If you get
results and no erorr, then it is possible the error may be connection related. I actually asked someone more proficient in SQL and they confirmed that SQL errors for the connection come back weird and are dependent on a RAISERROR command, which is not in the
RetrievePackages stored procedure by default.
Bottom line, if you can run everything manually without issue, it should not be table or stored proc related.