ITM v6.x Situation Event Reports: Part 1

by Nick Lansdowne

Many customers of Orb Data require management reports summarising situation events raised within their environment. This is a subject not covered in the standard ITM documentation, either for version 6.1 or 6.2. This article explains how to configure the historical data collection of situation event status changes, and subsequently generate some basic reports using the TEP Client. Although using the TEP Client to display such reports doesn’t replace a specialist reporting tool, for example Tivoli Common Reporting, it does provide a quick and easy route to displaying some basic interactive summaries of event history.

This article will look at three areas:

  • Enabling Historical Data Collection of Situation Event status
  • The Database table structure, and interpretation
  • Basic Reports

A further tip will expand on these concepts utilising advanced links to generate dynamic reports.

Situation Status Historical Data Collection

The historical data collection of the situation event status information is enabled from the TEP Client History Configuration Editor:

  1. Press Ctrl-H, within the TEP Client, to open the History Configuration Editor
  2. Select the CCC Logs option from the Product List drop-down menu
  3. Highlight the Situation Status Log option in the Attribute Groups panel
  4. Click Configure Groups **Note1
  5. Status History option in the Attribute Group window (again!)
  6. Click Start Collection **Note2

**Note1: The collection of the status history cannot be configured. The data is collected on a daily basis from the TEMS proprietary database. Additionally, it is not possible to configure automated pruning of this data.

**Note2: The user will be prompted to select the target TEMS if one or more Remote TEMS are configured within the environment. Select the Hub TEMS in such environments.

The Database Table, Fields and User Defined Functions

The database table created by the historical collection is named Status_History. This table includes the fields:

  • Global_Timestamp: The time of the status update (see below)
  • Status: A code to indicate the situation/event status (see below)
  • Situation_Name: The name of the situation
  • Managed_System: The name of the managed system where a situation event was generated
  • Managing_System: The name of the TEMS managing the effected managed system
  • Atomize: The display item from a situation event, where applicable

Global_Timestamp

The Global_Timestamp field uses the standard ITM timestamp format CYYMMDDHHMMSSmmm, where C specifies the century (1 for the 21st century), YY, MM, DD, HH, MM, SS, and mmm indicate the year, month, day, hour, minutes, seconds and milliseconds respectively. This format is not user-friendly, and hence it is recommended that the IBM supplied function DateForm is used to translate the timestamp to a more readable form. The syntax of this function is:
DateForm(<FIELD>, <OUTPUTFORMAT>)

where <OUTPUTFORMAT> is a single character indicating the required format of the output time stamp. Permissible options are Y, M, D and H to display in the format year, year/month, year/month/day or year/month/day hour:minute respectively.

For example, the following basic select command displays the raw Global_Timestamp plus a formatted date field using the DateForm UDF:

select "Global_Timestamp" "TimeCode", ITMUSER.DateForm("Global_Timestamp",'H') "RealTime" from "Status_History"

TimeCode RealTime
---------------- --------------------
1080609115014000 2008/06/09 11:50

1 record(s) selected.

Status

The Status field is a single character and can assume one of seven pre-defined values. To aid the interpretation of this data the status codes can be interpreted in a similar fashion to the global timestamp. This requires a custom written function, as IBM does not supply a function to interpret these codes. The Orb Data tip Interpreting the Situation Status in the Data Warehouse details the set-up of a function EVENTSTATUS for this purpose, and the queries defined in this tip assume the function is available.

UDF Usage

A combination of these two functions facilitates a more readable output from the Status_History table, as demonstrated below:
select
ITMUSER.DateForm("Global_Timestamp",'H') "RealTime",
ITMUSER.EVENTSTATUS("Status") Status, "Situation_Name", "Managed_System"
from "Status_History"

RealTime STATUS Situation_Name Managed_System

2008/06/09 17:10 Open NT_Notepad_Process_Running Primary:ITM62SVR:NT
2008/06/09 17:11 Closed NT_Notepad_Process_Running Primary:ITM62SVR:NT

Basic Reports

Details of Situation Status changes, as in the last example, are a useful demonstration, but are of little use from a reporting point of view. Management are more likely to be interested in trends over time for opened and closed situations. Queries delivering such information can be displayed using the Bar Chart view type for easy analysis.

The query to drive such a Bar Chart requires events to be group and the members of each group counted. For example, group the events by their Status and the number of events of each state counted. This is achieved using the standard SQL functions GROUP BY and COUNT. The following basic example provides a count for all Open, Acknowledged and Closed events:

SELECT ITMUSER.EVENTSTATUS("Status") "Status", COUNT("Status") "Total"
FROM "Status_History"
WHERE "Status" = 'Y' OR "Status" = 'A' OR "Status" = 'N'
GROUP BY "Status"
ORDER BY "Status" ASC

Status Total
------------ -----------
Acknowledged 6
Closed 35
Open 138

This can be refined to limit the counts to the data from the past month (31 days). The syntax of the date comparison is dependent on the RDBMS.

For Microsoft SQL Server, the Global_Timestamp, transformed using the ITM DateForm function, can be compared directly with dates from the SQL Server GETDATE function. For example:

(ITMUser.DATEFORM(Global_Timestamp, 'H') > GETDATE() - 31)

For IBM DB2 comparisons it is easier to use a custom user-defined function to convert the ITM Global_Timestamp into a DB2 recognised timestamp. The file db2timestamp.txt can be used to create the function named DB2TIMESTAMP. Copy the file to your DB2 Server, and create the function from the DB2 command line processor:

db2 connect to warehous
db2 -td@ -f db2timestamp.txt

The function facilitates the date comparison using the syntax:
ITMUSER.DB2TIMESTAMP("Global_Timestamp", 'D') > (current timestamp - 31 days)

Note: All subsequent examples will be demonstrated using SQL statements based on DB2 syntax.

So, to refine the original SQL statement to return the status totals for the past 31 days requires the syntax:
SELECT ITMUSER.EVENTSTATUS("Status") "Status", COUNT("Status") "Total"
FROM "Status_History"
WHERE "Status" = 'Y' OR "Status" = 'A' OR "Status" = 'N'
AND ITMUSER.DB2TIMESTAMP("Global_Timestamp", 'D') > (current timestamp - 31 days)
GROUP BY "Status"
ORDER BY "Status" ASC

Status Total
------------ -----------
Acknowledged 6
Closed 35
Open 138

(Comparison of the two outputs implies the historical data collection has been active for less than 31 days!)

This SQL can be used to generate the bar chart demonstrated below.

Situation Event Status Summary

Embed the SQL statement into an ITM Query, named Event Status Summary 1M, using the technique described in the tip Interpreting the Situation Status in the Data Warehouse, and configure a bar chart view as follows:

 
Tab Category Property Value
Query Query Name Event Status Summary 1M
Filters Total (Check box) Checked
Style Header Label Text Events Status Summary for Past 31 Days
  Ledgend Label Show Ledgend Label Unchecked
  Category Axis Axis Label, Text Status
  Category Axis Axis Label, Show Text Checked
  Category Axis Category Axis, Attribute Status
  Value Aixs Axis Label, Text Number of Events
  Value Aixs Axis Label, Show Text Checked

To count the number of events opened each day for the past month (31 days), the query must group by the date, extracted from the Global_Timestamp using the DateForm function. The data can be date sorted with the standard ORDER BY function.

SELECT ITMUSER.DateForm("Global_Timestamp",'D') "Date", COUNT("Global_Timestamp") "Total"
FROM "Status_History"
WHERE "Status" = 'Y'
AND ITMUSER.DB2TIMESTAMP("Global_Timestamp", 'D') > (current timestamp - 31 days)
GROUP BY ITMUSER.DateForm("Global_Timestamp",'D')
ORDER BY "Date" ASC

Date Total
-------------------- -----------
2008/05/29 32
2008/05/30 44
2008/06/02 7
2008/06/05 9
2008/06/06 19
2008/06/09 18
2008/06/10 8

This SQL can be used to generate the bar chart demonstrated below.

Opended Events in the Past Month grouped by date

Embed the SQL statement into an ITM Query, named Open Event History 1M by Date, and configure a bar chart view as follows:

 
Tab Category Property Value
Query Query Name Open Event History 1M by Date
Filters Total (Check box) Checked
Style Header Label Text Events Opened in the Past Month
  Ledgend Label Show Ledgend Label Unchecked
  Category Axis Axis Label, Text Date
  Category Axis Axis Label, Show Text Checked
  Category Axis Category Axis, Attribute Date
  Value Aixs Axis Label, Text Number of Events
  Value Aixs Axis Label, Show Text Checked

Finally, a bar chart could be created to display a count of each type of situation event opened in the past month, using the Situation_Name as the key for the GROUP BY function.

Create a query Open Event History 1M by Situation with the Custom SQL:
SELECT "Situation_Name", COUNT("Situation_Name") "Total"
FROM "Status_History"
WHERE "Status" = 'Y'
AND ITMUSER.DB2TIMESTAMP("Global_Timestamp", 'D') > (current timestamp - 31 days)
GROUP BY "Situation_Name"
ORDER BY "Situation_Name" ASC

This query can be used to generate the following bar:

Events Opened in Past MOnth grouped by Situation Name

The Bar Chart Properties are:

 
Tab Category Property Value
Query Query Name Open Event History 1M by Situation
Filters Total (Check box) Checked
Style Header Label Text Events Opened in the Past Month
  Ledgend Label Show Ledgend Label Unchecked
  Category Axis General, Invert Axis Checked
  Category Axis Axis Label, Text Situation Name
  Category Axis Axis Label, Show Text Checked
  Category Axis Category Axis, Attribute Situation Name
  Value Aixs Axis Label, Text Number of Events
  Value Aixs Axis Label, Show Text Checked

Summary

So far, the reports designed have provided basic, static situation event data summaries. These are a useful starting point for analysing the events within an enterprise. Part 2 of this tip explores the use of advanced links to provide dynamic reports, where the data displayed is filtered dependent on the bar the link is initiated from.

Visits: 277