Generating Common Database Reports

Update Manager uses Microsoft SQL Server and Oracle databases to store information. Update Manager does not provide a reporting capability, but you can use a third-party reporting tool to query the database views to generate reports.

IMPORTANT The Update Manager database does not contain information about the objects in the inventory, but contains internal inventory entity IDs. To get the original IDs for virtual machines, virtual appliances, and hosts, you must have access to the vCenter Server system database. From the vCenter Server system database, you can retrieve the ID of the objects that you want to access. To obtain the Update Manager database IDs of the objects, Update Manager adds the prefix vm- (for virtual machines), va- (for virtual appliances), or host- (for hosts)

Generate Common Reports Using Microsoft Office Excel

Using Microsoft Excel, you can connect to the Update Manager database and query the database views to generate a common report.

Prerequisites

You must have an ODBC connection to the Update Manager database.

Procedure for Microsoft SQL Server (Express Procedure below)

Note: I am using Microsoft Office 2010

• Log in to the computer on which the Update Manager database is set up.
• From the Windows Start menu, select Programs > Microsoft Office > Microsoft Excel.
• Click Data > Import External Data > New Database Query.
• In the Choose Data Source window, select VMware Update Manager and click OK
• If necessary, in the database query wizard, select the ODBC DSN name and enter the user name and password for the ODBC database connection.
• In the Query Wizard – Choose Columns window, select the columns of data to include in your query

• Click Next
• For example, if you want to get the latest scan results for all objects in the inventory and all patches for an inventory object, select the following database views and their corresponding columns from the Available tables and columns pane:
• VUMV_UPDATES
• VUMV_ENTITY_SCAN_RESULTS
• Click OK in the warning message that the query wizard cannot join the tables in your query.
• In the Microsoft Query window, drag a column name from the first view to the other column to join the columns in the tables manually.
• For example, join the META_UID column from the VUMV_UPDATES database view with the UPDATE_METAUID column from the VUMV_ENTITY_SCAN_RESULTS database view.
• A line between the columns selected indicates that these columns are joined.
• The data is automatically queried for all inventory objects in the Microsoft Query window.

Procedure for Microsoft SQL Server Express

Note: I am using Microsoft Office 2010

• Log in to the computer on which the Update Manager database is set up.
• From the Windows Start menu, select Programs > Microsoft Office > Microsoft Excel.
• Click Data > From Other Sources > From Microsoft Query

•  Click VMware vSphere Update Manager

• Choose relevant colums etc
• For example, if you want to get the latest scan results for all objects in the inventory and all patches for an inventory object, select the following database views and their corresponding columns from the Available tables and columns pane:
• VUMV_UPDATES
• VUMV_ENTITY_SCAN_RESULTS

• Click OK to the message that they cannot be joined

• In the Microsoft Query window, drag a column name from the first view to the other column to join the columns in the tables manually.
• For example, join the META_UID column from the VUMV_UPDATES database view with the UPDATE_METAUID column from the VUMV_ENTITY_SCAN_RESULTS database view.
• A line between the columns selected indicates that these columns are joined.
• The data is automatically queried for all inventory objects in the Microsoft Query window

• And this is what you will see

Generate Common Reports Using Microsoft SQL Server Query

Using a Microsoft SQL Server query, you can generate a common report from the Update Manager database.

Procedure

To generate a report containing the latest scan results for all objects in the inventory and for all patches for an inventory object, run the query in Microsoft SQL Client.

• The query displays all patches that are applicable to the scanned objects in the inventory.