Update 4/29/15 

This utility is now available for download as a VMware Fling!

https://labs.vmware.com/flings/horizon-view-events-database-export-utility

Update 2/9/15 – Version 1.5 available for download.

New Features:

• Added pool filtering
• Added Client IP address output (from BROKER_USERLOGGEDIN event type)
• Additional fixes, debugging and error trapping

Download and try it out today!  As always, please reach out to me with issues and suggestions.

Also check out a great article on this utility from my friend and EUC Rockstar Stephane Asselin:   http://myeuc.net/2015/01/29/the-big-easy-button/

---

Background:

The VMware View Events Database is used to record all events that happen in a View environment.   There is a ton of great information in the database, but it can be difficult to extract.   I wrote the View Event Notifier  last year which provides for real-time alerting, but it doesn’t allow for a lot of filtering.  I wrote this utility to allow administrators to easily apply very detailed filtering to the data and export it to .csv.  You can filter on time range,  event severity, event source, session type (Application or Desktop), Usernames and Event Types.   The application allows for extremely granular export of data.   The exported columns can also be customized and the application will export data from both the live and the historical tables in the View Events Database.

Prerequisites:

• Single application .exe that only requires the Microsoft .NET 3.5 Framework
• At least DB Reader access to the View Events DB via SQL or Windows authentication

Usage:

When you open the application the first time you will need to establish a connection to an existing View Events DB.  You can choose to authenticate via SQL or Windows authentication, depending on what your SQL server is currently configured for.  This connection data is stored in the registry under HKCU\Software dynamically as you type in the settings.   The password if using SQL Authentication is encrypted in the registry.

SQL Auth Type:  Choose Windows or SQL.  Windows will pass through the credentials of the currently logged in user.

DB Server:  The address to the SQL server along with the instance name if required.  server\instance

Database:  The name of the View Events Database

Username:  The SQL username with at least read access to the DB if using SQL Authentication.

Password:  The SQL password if using SQL Authentication.   The password is encrypted when stored.

Table Prefix:  View allows the use of a table prefix so you can monitor multiple view instances from the same database.   If table prefix is used, type it in.

Click “Test Connection”  – this will initiate a connection to the database and indicate success or any connection issues.  If any required data is missing you will be notified as to what information must be entered in order to connect to the SQL DB.

When you are able to connect to the database successfully, click the “Connect to DBServer” button at the bottom of the form.  You are now connected to the View Events DB.  The button will indicate connection state.  You can click the button again to disconnect from the DB server.

Filtering Data:

As mentioned earlier the application allows for many layers of filtering of the View Event data before export.

General Filtering

The general filtering tab allows for high-level filters to be set that will apply to the data as it is queried and exported.

Query Historical Data:  By checking the “Query Historical Data” checkbox, the application will use the historical tables in the View Events DB to query older data.

Time Range:  The time range will automatically be populated with the time of the first and last event in the View Events DB.  You can click the date/time picker to choose a specific start and end date range to query.

Query on Severity:  The “Audit Fail”, “Warning”, “Informational” and “Error” checkboxes control what severity of events you want to query and export.  They are all selected by default.

Query on Module: The “Broker”, “Admin”, “Agent” and “Vlsi” checkboxes allow you to determine what modules you want events to be exported for.

• Broker –  All brokering activity
• Admin –  Administrative actions, typically through the GUI
• Agent –  View Agent events
• Vlsi – VMOMI Leveraged Server Infrastructure – These are API related calls.

Session Type:  You can query if you want to return only desktop or application session types.  NOTE:  This entry is only populated for specific events, so use sparingly to avoid limiting search results too much.

User Filtering:

By default the application will return data for all usernames when running a query.  The user filtering tab allows you to filter the results returned on specific usernames.  The usernames are populated dynamically out of the Events Database, so there is no need to type them in.

To filter on usernames do the following:

• Click the “Refresh Users” button to populate the list of usernames.
• Select the users you want to filter on and click the down arrow to add to the “Users to Export Data For” list box.
• You can de-select a user by selecting it in the “Users to Export Data For” listbox and clicking the Up Arrow.
• If you want to reset the filter to all users, click the “Return Data for All Users” checkbox.
• NOTE: If you choose to filter on usernames, the application will automatically filter the items in the “Event Filtering” and “Pool Filtering” tabs based on records for those specific users.  That way you will see the only records that actually apply to those users.

Pool Filtering:

The application allow filtering of specific desktop pools,  you can select as many pools as you want to return.   If you filter on any users, you will only see the pools that apply to those specific users.

• Select the pool names you want to query on and click the Down Arrow to add to the “Pools to Export Data For” listbox.
• Selected Pools can be removed by selecting them in the “Pools to Export Data For” listbox and clicking the Up Arrow.

Event Filtering:

The application allow filtering of specific event types, you can select as many event types as you want to return.   These events are also filtered by selecting the “Module” checkboxes on the “General Settings” tab as shown above (Broker, Agent, Admin, Vlsi).  In addition, if you filter on any users, you will only see the event types that apply to those specific users.

• Select the event types you want to query on and click the Down Arrow to add to the “Events to Export Data For” listbox.
• Notice in the screenshot below how events are filtered based on selected users and the label indicates this filtering.
• Selected Event Types can be removed by selecting them in the “Events to Export Data For” listbox and clicking the Up Arrow.

All Event Types:

Event Types Filtered by Users:

Exporting Data:

At this point, you are ready to export data based on the filters you selected.  Click on the “Export Data” tab.

Columns to Export
You can choose which columns to export into the .CSV file.   Simply check or uncheck the column name to add or remove it from the output.  NOTE:  certain columns are specific to user events (Pool Name, Username, Session Type and Desktop Name), and the Server Name column is only available when no users are selected.  Client IP Address is only available when the BROKER_USERLOGGEDIN event type is selected or All Events are returned.

Select File to Export Data Into

At this point you will select a .csv file to export the data into.  You need to use the built-in file save dialog to select the file.   Click the  button to open the file save dialog.   The application will provide a default name – you can chance the name as required.   Click the “Save” button when you are satisfied with the file name and location.

Show SQL Command:  This is an optional item that will show you the parameterized SQL command to be executed.

Click the button when ready to export the data.

The query will now export and you will see a progress bar and the “Export Status list box will populate with the data as it is written to the .csv file.

When complete, you will receive a message box indicating the path to the file and how many records were exported.

A button below the “Export Status” listbox will also appear which will allow you to open the export file automatically with the application associated with the .csv extention (typically excel).

At this point the data has been exported and you can review as needed and run as many more exports as needed.

I hope this utility will be helpful for organizations running VMware View.  Please let me know of any issues, questions or suggestion at chrisdhalstead@gmail.com or on Twitter at @chrisdhalstead.

You can download the application here:

https://labs.vmware.com/flings/horizon-view-events-database-export-utility