How to Report Result of Installation in Active Directory Deployment

Vadim Rapp
CERTIFIED EXPERT
Published:
Updated:
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.

 
CREATE TABLE [dbo].[_GPOInstallations]
                      
                      (id] [int] IDENTITY (1, 1) NOT NULL ,
                                  [machine] [varchar] (50) NOT NULL ,
                                  [username] [varchar] (50) NOT NULL ,
                                  [product] [varchar] (50) NOT NULL ,
                                  [version] [varchar] (50) NOT NULL ,
                                  [started] [datetime] NULL ,
                                  [finished] [datetime] NULL ,
                                  [commited] [bit] NULL ,
                                  [rolledback] [bit] NULL ,
                                  [ResultingState] [tinyint] NULL
                      )

Open in new window


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") = NewId
                      End Function
                      
                      Function 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 If
                      End Function
                      
                      Function 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 If
                      End Function

Open in new window


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.

image002.jpg
Now we go to the table CustomAction. Here, we create three custom actions:

image004.jpg
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.

image006.jpg
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

Open in new window

1
4,446 Views
Vadim Rapp
CERTIFIED EXPERT

Comments (1)

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Vadimrapp1, this is a good article!  It provides a great alternative approach for those administrators that wish to gain meaningful reporting on the status of their application deployments.

Thanks.

Regards,

Rob.

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.