Interpreting the Situation Status in the Data Warehouse
ITM 6.x provides the facility for archiving the status of situation events to the Data Warehouse. However, the data in the table created by the historical data collection of that information, Status_History, requires interpretation prior to incorporation into Workspaces. The Status field within the databases uses single character codes for the situation event status, codes that are not obvious to an administrator, let alone a user. However, through the use of a User Defined Function, or UDF, the status codes can be translated as part of a user define ITM Query.
ITM ships with a number of pre-defined UDFs, for example DATEFORM for interpreting the ITM time stamps. These can be used as models for any custom UDFs defined by the user. Similar to many programming languages, a basic UDF can accept one or more argument, and return a value. For example, the IBM supplied DATEFORM function manipulates an ITM timestamp, supplied as an argument, returning the time as a string in a format determined by a second argument.
Using a similar approach, a UDF can be defined to accept the contents of the Status field, and return a string representing the status in a more redable format. The interpretation of the permitted codes is as follows:
- N = Closed
- D = Deleted
- P = Stopped
- A = Acknowledged
- E = Reopened
- F = Expired
- S = Reset
A UDF for the Situation Event Status Codes
The syntax of a User Defined Function differs between the various relational database systems. The RDBMSs supported by IBM Tivoli Data Warehouse are dependent on the platform and version of ITM, but include IBM UDB (DB2), Microsoft SQL Server and Oracle. The method to create a UDF named EVENTSTATUS on DB2 and SQL Server is described below.
UDF for DB2
Run the following command from a DB2 Command Line Processor:
db2 -td@ -f mpeventstatus.db2
where the file mpeventstatus.db2 contains the following code:
CREATE FUNCTION "ITMUSER"."EVENTSTATUS"( Status VARCHAR(1)) RETURNS VARCHAR(12)
LANGUAGE SQL DETERMINISTIC NO EXTERNAL ACTION CALLED ON NULL INPUT
DECLARE statusRet VARCHAR(12);
IF Status = 'Y' THEN SET StatusRet = 'Open';
ELSEIF Status = 'N' THEN SET StatusRet = 'Closed';
ELSEIF Status = 'D' THEN SET StatusRet = 'Deleted';
ELSEIF Status = 'P' THEN SET StatusRet = 'Stopped';
ELSEIF Status = 'A' THEN SET StatusRet = 'Acknowledged';
ELSEIF Status = 'E' THEN SET StatusRet = 'Reopened';
ELSEIF Status = 'F' THEN SET StatusRet = 'Expired';
ELSEIF Status = 'S' THEN SET StatusRet = 'Reset';
ELSE SET StatusRet = 'UNKNOWN';
Note: This code was tested on IBM DB2 v220.127.116.112 FixPak 14.
UDF for SQL Server
SQL Server User Defined Functions can be defined from the Enterprise Manager. Navigate to the Tivoli Data Warehouse database, and create a new function under the User Defined Functions branch. The code is listed below:
ITMUSER.EVENTSTATUS( @Status char(1))
RETURNS varchar(12) AS
DECLARE @statusRet VARCHAR(12);
if @Status = 'Y' SET @statusRet = 'Open';
else if @Status = 'N' SET @statusRet = 'Closed';
else if @Status = 'D' SET @statusRet = 'Deleted';
else if @Status = 'P' SET @statusRet = 'Stopped';
else if @Status = 'A' SET @statusRet = 'Acknowledged';
else if @Status = 'E' SET @statusRet = 'Reopened';
else if @Status = 'F' SET @statusRet = 'Expired';
else if @Status = 'S' SET @statusRet = 'Reset';
else SET @statusRet = 'UNKNOWN';
Note: This function was tested on Microsoft SQL Server 2000 with ServicePack 4.
Using the Event Status Function
The function is now available to be used by ITM queries of the Status_History table in the Tivoli Data Warehouse. The function can be called as part of the SQL statement within an ITM query, manipulating the Status field as part of the select statement. For example, instead of returning the Status field a normal:
select "Status" from "Status_History"
call the EVENTSTATUS function and define a new column title:
select EVENTSTATUS("Status") "RealStatus" from "Status_History"
To create a new query from the TEP Client:
- Press Ctrl-Q to open the Query Editor
- Click the Create Query… tool in the top left hand corner of the Query Editor
- Type a suitable name for the query, select a suitable Category (Tivoli Enterprise Monitoring Server is suggested), highlight the ITM Warehouse data source and clickOK
- Add the SQL from the listings below
SELECT DateForm("Global_Timestamp",'D') "Date",
EVENTSTATUS("Status") "Status", "Situation_Name", "Managed_System" FROM
- Click OK to save the new query
The new query may be used for any query based view within a workspace, as demonstrated in the screenshot below:
The tip Situation Event Reports: Part 1 explores the use of historical situation information for report generation and uses the user defined functions discussed above.