One of the major drawbacks of deploying applications by GPO is the complete lack of any centralized reporting. After a normal deployment, there are two ways to find out if it was successful – by looking in the event log, and by looking in the log of the installation package itself, provided that it was enabled by group policy. Obviously, this is a major drawback.
Deployment systems like SMS provide reporting by means of their agent deployed at each workstation. The agent launches the installation, watches it, and then reports a return code to the central server where it can then be analyzed.
There is however another way to provide the reporting – by inserting custom actions in the installation script, by modifying the MSI package. In this article, we will illustrate how to build such a reporting system by using a transform, applied to the installation package.
The actions we insert will be reporting the state of the installation to our database. We will need 3 actions – one in the beginning to report the installation start; and two in the end to report installation success or failure.
For starters, we will need the database table to report to. In this article, we will be using SQL Server, but of course it can be any database – you just need to modify the connection string for your system. The below SQL procedure will create the table we need.
Here’s the plan. Our first custom action will create a new record in the database; we will get back the newly created record id and store it in a property. At the end of the deferred execution, we will place two more custom actions – one will run in the case of a commit, reporting success, another in case of a rollback, reporting the failure.
Here’s the code in vbscript of the whole script, which provides our functions.
Function dbRegStart() On Error Resume Next If Session.Property("REMOVE") = "ALL" Then State = 0 Else State = 1 End If Set cn = CreateObject("ADODB.Connection") cn.open "Provider=SQLOLEDB;Integrated Security=SSPI;Initial Catalog=MYDB;Data Source=MYSERVER" ProductName = Session.Property("ProductName") ProductVersion = Session.Property("ProductVersion") Set rs = cn.execute("insert into _GPOInstallations(product,version,Started, ResultingState) select '" & ProductName & "','" & ProductVersion & "',getdate(), " & state & " ; select scope_identity()") Set rs=rs.nextrecordset NewId=cstr(rs(0)) rs.close cn.close Set cn=Nothing Session.Property("DBNewID") = NewId Session.Property("DBRegCommit") = NewId Session.Property("DBRegRollback") = NewIdEnd FunctionFunction DBRegCommit() On Error Resume Next NewId = Session.Property("CustomActionData") If IsNumeric(NewId) Then Set cn=createobject("adodb.connection") cn.open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=MYDB;Data Source=MYSERVER" cn.execute "update _GPOInstallations set Finished=getdate(), commited=1 where id=" & newid cn.close Set cn=Nothing End IfEnd FunctionFunction DBRegRollback() On Error Resume Next NewId = Session.Property("CustomActionData") If IsNumeric(NewId) Then Set cn=createobject("adodb.connection") cn.open "Provider=SQLOLEDB;Integrated Security=SSPI;Initial Catalog=MYDB;DataSource=MYSERVER" If Session.Property("REMOVE") = "ALL" Then State = 0 Else State = 1 End If cn.execute "update _GPOInstallations set Finished=getdate(), rolledback=1 where id=" & Session.Property("CustomActionData") cn.close Set cn=Nothing End IfEnd Function
The first function, dbRegStart, will be called at the beginning of the execute sequence; and functions dbRegCommit and dbRegRollback are called at the end of the deferred execution - because only then it becomes known whether the installation was committed or rolled back.
Here’s how to insert custom actions in ORCA (see article "Orca.exe" at MSDN website; as of this writing, the official page was at
http://msdn.microsoft.com/en-us/library/aa370557%28VS.85%29.aspx, which instructs you to download the 4GB SDK; it may be possible to find the ORCA application itself in other places if you search the web).
First, we save the text of the above vbscript in a text file, for example c:\dbReg.vbs.
We open the original MSI, and start a new transform; we then go to the table Binary, and add new row. Give it any name, for instance, dbreg1. Then highlight the row "Data" and specify the file name of the saved script, c:\dbreg.vbs . ORCA will read the file contents into the table.
Now we go to the table CustomAction. Here, we create three custom actions:
All 3 actions have the Name we gave in the binary table shown in the Source column. For the type, type 6 indicates a regular custom action; type 1542 and 1286 respectively specify execution on Commit and on Rollback in the deferred phase. Column Target points to the respective function within our vbscript.
Finally, we insert our custom actions into the right places in the script.
Assuming that the very first action in the original execute sequence had a sequence number of 100, we place "DBRegStart" before it, i.e. 90; we place DBRegCommit and DBRegRollback right before InstallFinalize.
Then we generate the new transform in ORCA, and apply it to the original MSI when deploying it in Group Policy (on the "Modifications" tab in the package dialog in the group policy). Once the package is installed or uninstalled, a new record will appear in our database with the name of the machine, product, version, and result of the installation. Using some trivial SQL query statements against the database, it’s then easy to report on workstations where the installation has failed:
Select machine from _gpoinstallations Where Product = 'MyProduct' and version = '1.0.0' And isnull(rolledback,0) = 0