Novell Documentation: ZENworks for Desktops - Generating Reports from a Database

Generating Reports from a Database

You can use database queries to generate reports that contain the event information you want. The following sections describe the database tables and fields used to store event information and provide sample SQL queries you can use:

Database Tables and Fields

The database includes the following three tables:

T_Success: Stores information about successful events.
T_Failure: Stores information about failed events.
T_Info: Stores information about rogue process management events. For information about rogue process management, see Tracking and Controlling Rogue Processes.

Each database table contains as many as 17 information fields.The following table lists the fields and the database tables that include the fields. The data type for all fields is varchar(256).

Field	Tables	Description
zenEventType	T_Success T_Failure T_Info	The event that occurred. Successful event types (T_Success) are: Launch Success Distribute Success Uninstall Success Cache Success Process Terminated Failed event types (T_Failure) are: Launch Failed Distribute Failed Uninstall Failed Cache Failed Filter Hide Filter Show Rogue process management event types (T_Info) are: Process Terminated Process Ignored
zenDateTime	T_Success T_Failure T_Info	The date and time the event occurred.
zenUserTDN	T_Success T_Failure T_Info	The distinguished name and tree of the user for which the event occurred.
zenWSTDN	T_Success T_Failure T_Info	The distinguished name and tree of the workstation on which the event occurred. If the workstation has not been imported into eDirectory as a Workstation object, the field contains WORKSTATION NOT REGISTERED.
zenWSAddr	T_Success T_Failure T_Info	The IPX^TM or IP address of the workstation on which the event occurred.
zenAppTDN	T_Success T_Failure T_Info	The distinguished name and tree of the Application object for which the event occurred. Because rogue processes do not have an Application object, this field will always be "ZEN Process Management" in the T_Info table.
zenAppGUID	T_Success T_Failure T_Info	The global unique identifier assigned to the Application object. The GUID is located on the Application object's Options page (Distribution Options tab). Because rogue processes do not have an Application object, this field will always be empty in the T_Info table.
zenAppVer	T_Success T_Failure T_Info	The version number assigned to the Application object. Possible numbers range from 0 to 65535. The version number is located on the Application object's Options page (Distribution Options tab). Because rogue processes do not have an Application object, this field will always be empty in the T_Info table.
zenMajor	T_Success T_Failure T_Info	For successful events (T_Success table), this field is always 0. For failed events (T_Failure table), this field lists the error code generated by Application Launcher. For rogue process events (T_Info table), this field is left blank.
zenMinor	T_Success T_Failure T_Info	For successful events (T_Success table), this field is always 0. For failed events (T_Failure table), this field lists additional error code information. For rogue process events (T_Info table), this field is left blank.
zenEventString1	T_Failure T_Info	For failed events (T_Failure table), this field might list additional information describing the reason for the failure. For rogue process events (T_Info table), this field lists the executable path for the rogue process.
zenEventString2	T_Failure T_Info	For failed events (T_Failure table), this field might list additional information describing the reason for the failure. For rogue process events (T_Info table), this field lists the original filename of the process.
zenEventString3	T_Failure T_Info	For failed events (T_Failure table), this field might list additional information describing the reason for the failure. For rogue process events (T_Info table), this field contains the process ID (PID).
zenEventString4	T_Failure T_Info	For failed events (T_Failure table), this field might list additional information describing the reason for the failure. For rogue process events (T_Info table), this field contains the parent process ID (parent PID).
zenEventString5	T_Failure T_Info	For failed events (T_Failure table), this field might list additional information describing the reason for the failure. For rogue process events, this field contains the event action, either "process ignored" or "process terminated successfully."
zenAppFlags	T_Success T_Failure T_Info	For successful events (T_Success table) and failed events (T_Failure table), this field lists the Application object bitmask. For rogue process events (T_Info table), this field is always 0.

Custom Queries

You can create custom database queries to search for specific information. The following samples are SQL queries you can use.

All Fields for an Event

The following queries return all information fields for a successful, failed, or rogue process management event stored in the database. Events will be sorted by the order in which they where entered into the database.

SELECT * FROM T_SUCCESS

SELECT * FROM T_FAILURE

SELECT * FROM T_INFO

All Fields for an Event, Sorted on a Specific Field

To sort the list on a specific field, add ORDER BY field_name, as in the following examples:

SELECT * FROM T_SUCCESS ORDER BY zenWSTDN

SELECT * FROM T_FAILURE ORDER BY zenAppTDN

SELECT * FROM T_INFO ORDER BY zenUserTDN

All Fields for Specific Event Types

To include only a specific event type (launch, distribute, etc.) for an event (success, failure, etc.), add WHERE zenEventType="event_type", as in the following examples:

SELECT * FROM T_SUCCESS WHERE zenEventType="Launch Success"

SELECT * FROM T_FAILURE WHERE zenEventType="Launch Failure"

SELECT * FROM T_INFO WHERE zenEventType="Process Terminated"

The valid event types are listed in the table in

Specific Fields for an Event

To include only specific fields, replace the * with a comma-delimited field list, as in the following example:

SELECT zenEventType,zenDateTime,zenUserTDN,zenAppTDN FROM T_SUCCESS WHERE zenEventType="Cache Success" ORDER BY zenUserTDN

Predefined Queries

Previous versions of ZENworks for Desktops Application Management include predefined queries.They are not included in this version of ZfD. If you would like to continue to use the queries, you can use the information in the following table to create the queries in your database querying tool.

Query	Explanation
SELECT * FROM T_SUCCESS ORDER BY zenWSTDN	Generates a report showing any successful events, with events grouped by workstation.
SELECT * FROM T_SUCCESS ORDER BY zenUserTDN	Generates a report showing any successful events, with events grouped by user.
SELECT * FROM T_SUCCESS ORDER BY zenAppTDN	Generates a report showing any successful events, with events grouped by application.
SELECT * FROM T_FAILURE ORDER BY zenWSTDN	Generates a report showing any failed events, with events grouped by workstation.
SELECT * FROM T_FAILURE ORDER BY zenUserTDN	Generates a report showing any failed events, with events grouped by user.
SELECT * FROM T_FAILURE ORDER BY zenAppTDN	Generates a report showing any failed events, with events grouped by application.