Reports

Reports Database Tables

Report-generating functionality is available via the SMS Java screens, to provide for service management reports of data.

This topic describes how to create reports:

Service reports, to be installed at the time of service installation
General reports, installed subsequently

Database Tables

There are three database tables which are specific to report generation:

SMF_REPORT_SCRIPT contains one entry for each report script
SMF_REPORT_PARAMETER contains one entry for each report parameter (may be none)
SMF_REPORT_SCHEDULE contains one entry for each scheduled report instance. This table is not used for report installation, and is not covered in this document.

In addition, the following tables are used for controlling who has access to a report:

SMF_APPLICATION
SMF_APPLICATION_PART
SMF_APPLICATION_ACCESS

These tables are reviewed here in regards to their role in report security. Security is handled by the standard SMS application part mechanism (see, File Format Example 3). Auditing is provided by the standard SMS audit mechanism, and should not need changing. The last change fields are the standard SMS last change fields, and are not listed in this table.

Report Scripts Table

The report database table is called SMF_REPORT_SCRIPT. It contains the details of reports as shown below.

Table 8-1 Report Scripts Table

Field	Description
REPORT_ID	Unique identifier, primary key, generated by a counter.
APP_ID	Application ID, foreign key to SMF_APPLICATION(app_id)
PART_ID	Part ID for security, foreign key to SMF_APPLICATION_PART (part_id)
CATEGORY	Script category, identifies the subdirectory.
SCRIPT	Script name, identifies the .sql file or shell script to run.
NAME	Name to list in the report directory.
DESCRIPTION	Help text.

Report Parameter Table

The parameter table is called SMF_REPORT_PARAMETER. It contains the details of report parameters as shown below.

Table 8-2 Report Parameter Table

Field	Description
REPORT_ID	The ID of the report this parameter belongs to.
PARAM_NUMBER	The position of the parameter in the list, for example 1st, 2nd.
NAME	The parameter name.
DESCRIPTION	Help text.
TYPE	The type – INT, STRING, DATE, and so on. (See table following for details)
DEFAULT_VALUE	Default value, optional.
VALID_VALUES	Valid comma separated values.
CONSTRAINT1	A constraint on the parameter (interpretation depends on TYPE).
CONSTRAINT2	A constraint on the parameter (interpretation depends on TYPE).

Installing a Report Script

A script must be installed before it can be made available to the system. This process is described here, along with examples.

The main steps in the procedure detailed below are:

Choose an application ID, a category, and a report name.
Determine the parameters (if any) required by your script, and write the script.
Decide which application part your report will belong to.
Install the actual script on the SMS in the correct location.
Insert entries into the REPORT tables in the SMS database.

How to Install a Report Script?

Follow these steps to install a report script:

The Application ID must be an existing entry from SMF_APPLICATION. Common values are shown below. If you have additional services installed, additional choices may be available.
```
SQL> select app_id, application from smf_application;
APP_ID APPLICATION
---------- --------------------
1 SMS
4 SYSTEM
2 Acs_Service
```
The Category is an arbitrary name for a group of reports within one application. For example: "Customer", "Management", "Resource Usage".

The Name is a name for your report. Typically, this will be similar to the script name. For example, if your script is monthly_usage.sql, your report name could be "Monthly Usage".
Your script may take user parameters. The SMS report functions allow you to determine whether these are string, numeric, or values from a constrained list of parameters. Refer to the description of the SMF_REPORT_PARAMETER table to see the parameter types supported.
A report must one of the following:
- Have a .sql extension. In this case, it will be run using sqlplus.
- Be executable by the smf_oper user.
In either case, the script will be passed (n + 1) command line parameters, where n is the number of user parameters defined in SMF_REPORT_PARAMETER. Command line parameter one will always be the absolute output file name allocated to this report.

Examples:

For a .sql file:
```
sqlplus script-name output-file [user-parameters]
```
For an executable file without a .sql extension:
```
script-name output-file [user-parameters]
```
Exit status of report scripts are defined by the following:

0 = okay

> = not okay. (Unix style)

<0 = undefined

Neither the smsReportsDaemon nor the smsReportScheduler is responsible for the clean up or reclaim of resources used by reports. This must be done explicitly by the application programmer.

The user may request cancelation of a script, in which case it will be sent a SIGTERM. Scripts should not ignore SIGTERM.

If the report spawns children, it should implement a SIGTERM handler to dispose the children, in case the user cancels a report.
For simplicity, an application part may be reused. Access to multiple reports may be controlled by one application part. You can even re-use access parts controlling existing installed screens.

You can list all existing defined application parts with the SQL command:
```
SQL> select app_id, part_id, part from smf_application_part;
```
If you choose to re-use application part 1030 (SMSReportScreens), all users who can access report screens will be able to run this report.
The script itself must be placed into /IN/service_packages/SMS/input/application-name/category/script-file.
The script must now be made known to the SMS screens, and available for use to any SMS user who has access to the part ID, which owns your report script. This means:
- Inserting an entry into SMF_REPORT_SCRIPT for your script, indicating the category and script filename.
- Inserting one entry into SMF_REPORT_PARAMETER for each parameter in your report (if any). This indicates any constraints you wish enforced (for example, min/max values).

Report Script Worked Example

A script must be installed before it can be made available to the system. This process is described here, along with examples.

The main steps are:

Choose a category and a name for your script.
Determine the parameters (if any) required by your script, and write the script.
Decide which application part your report will belong to.
Install the actual script on the SMS in the correct location.
Insert entries into the REPORT tables in the SMF database.

Example Report Script

Follow these steps to work through the example report script. The example script appears below description of each action in the procedure.

The details for this example are:

Application: SMS (ID is 1)

Category: “Errors”

Name: “Program Errors”

Script File: program_errors.sql

Application Part: 1805 (new part)

In this example, we are installing into the application SMS, which has the unique application ID of 1. Typically, you will install a service-specific report under the unique application ID that has been allocated to your service.

We are free to choose the category; we have chosen the "Errors" category.

We are free to choose a unique script name within this application and category; we have chosen program_errors.sql. We have chosen to create a new application part (1805) to control access to this report. An existing part may be used, for example: 1030.

The report takes three user parameters:

Num Hours: Integer in range 1..999, default is 24

Program Prefix:String length 0..20 characters

Category: One of FATAL, SERIOUS, WARNING, INFORMATIONAL

Note:

The script itself will take four parameters. The first parameter is the output file name, which is determined by the reports daemon and is handed to us. You must generate your output to that file, if you wish it to be seen by the user.

The script below accepts three arguments, and shows the essential basic techniques of accepting input parameters, and spooling to the correct output file.

/*-----------------------------------------------------------
* File: program_errors.sql
*
* Updates:
*
* Parameters:    &1 Output file, determined by reports daemon
*                &2 Hours back
*                &3 Program prefix
*                &4 Severity
*                (FATAL,SERIOUS,WARNING,INFORMATIONAL)
*

* Copyright Notice:
* (c)1998 This source code is owned and copyrighted by Oracle
*---------------------------------------------------------*/
-- #ident "@(#)$Id: telephony_errors.sql,v 1.4 1999/02/25 22:10:29 rhwang
Exp $"

-- so we won't print to stdout.
set termout off
set verify off

-- we are going to access the sms_program_errors table.
-- set the column titles.
column program 'Program' format a20
column error_code 'Error Code' format a16
column node_name 'Node Name' format a20
column severity 'Error Severity'

set linesize 80
set pagesize 2100

spool &1
-- now set the title at the top of the page.
ttitle center 'Recent Telephony Errors for Application &3 Severity &4'
skip 1 -
center 'PROGRAM TELEPHONY ERRORS' -
center ============================ skip 1 -
RIGHT 'PAGE:' FORMAT 999 SQL.PNO SKIP 2
break on program skip 1;
break on severity skip 1; 

select program, severity, error_code,   node_name, timestamp
from smf_program_errors
where (program like '%&3') and     
      (severity like '%&4') and     
      (timestamp < sysdate - &2/24)
order by program, severity; 

spool off
quit

The user must now specify an application part for security purposes. If you have decided to re-use an existing part_id (for example: 1030), proceed to Example part 4.
1. The Application ID for this example is 1. This is the unique ID for SMS.
2. Application Part IDs must be in the range App-ID * 1000 + (0 … 999) so for SMS, this means 1000 .. 1999. In this example, it has been determined that the ID 1805 is available for use. This is a new ID, which will control access to this report (and possibly others placed in the same security domain).
3. Application Access IDs must be in the range <Part-ID> * 100 + (0..99). This is the part ID, so any Access ID within this range may be chosen. In the example, 180500 has been chosen.
As part of the installation for this script, run SQL to create this new Part ID.

Note:

It is not necessary to create the SMF_APPLICATION for the SMS, since this is already created as part of the smsSms installation.
```
/*
 *  Create our application part. We can re-use this if we
 *  have multiple reports that we want to have all controlled
 *  by a single security identifier
 */
insert into smf_application_part (part_id, app_id, part, description)
  values (1805, 1, 'SMSErrorReports', 'Access SMS error reports
category');
insert into smf_application_access(access_id, part_id, rights_name,
description)
  values (180500, 1805, 'Access', 'Run reports');

/*
 *  We also add this to the 'SMS CreateDelete' user template,
 *  so that any user who is granted this template will get
 *  access to this report. We could add this to other
 *  templates too...
 */
var temp_id number;

EXEC select template_id into :temp_id from smf_template where
template='SMS CreateDelete';

insert into smf_template_access (template_id, access_id)
  values (:temp_id, 180500);

commit;
```
As part of the installation package, ensure that the file, program_errors.sql is installed into the correct destination location, for example, /IN/service_packages/SMS/input/SMS/Errors/program_errors.sql.

The smf_oper user must have read access to this file. If this was a shell script or a binary program, it is necessary to ensure that the smf_oper also has execute access to this file.

The final task is to notify the SMS about the script, to make it visible.

In this example, the report and the three user parameters to be collected are defined.

Note:

The screens constrain the content of the parameters to be passed to the script, but the interpretation of the parameters is of course up to the script itself.

/*
 * Add our script to the list of scripts.  
 */
var report_ref number;

insert into smf_report_script   
  (app_id, part_id, category, script, name, description)
values  
  (1, 1805, 'Errors', 'program_errors.sql', 'Program Errors',
   'Dumps all the program errors for the specified program(s)');

exec select report_id into :report_ref -
from smf_report_script -  
where (app_id=1) -  
and (category = 'Errors') - 
and (name = 'Program Errors');

insert into smf_report_parameter (
  report_id, param_number, name, description, type,
  default_value, valid_values, constraint1, constraint2)
values
  (:report_ref, 1, 'Num Days', 'Number of hours to go back',
   'INT', '24', '', '1','999');

insert into smf_report_parameter (
  report_id, param_number, name, description, type,
  default_value, valid_values, constraint1, constraint2)
values
  (:report_ref, 2, 'User',
   'Leading string of program (0-20 characters)',
   'STRING', '', '', '0', '20');

insert into smf_report_parameter (
  report_id, param_number, name, description, type,
  default_value, valid_values, constraint1, constraint2)
values
  (:report_ref, 3, 'Category', 'Error Category (pulldown menu)',
   'STRING', 'FATAL', 'FATAL,SERIOUS,WARNING,INFORMATIONAL', '', '');

commit;

Database Auditing

Changes to the data held in the SMF are tracked in the SMF_AUDIT table.

The listAudit.sh tool enables reports to be run on the changes tracked in SMF_AUDIT.

Purpose

listAudit.sh enables you to run queries against the audit data held in the SMF_AUDIT table. The results are processed into a comma separated report.

Configuration

listAudit.sh accepts the following command line options.

Usage:

listAudit.sh usr/pwd [start_date] [end_date] [db_user] [table]

The available parameters are:

Table 8-3 Configuration Parameters for Database Auditing

Parameter	Default	Description
`usr/pwd`	Not applicable.	The user and password combination to be used to log into the SMF. (Required.)
`start_date`	Not applicable.	The time and date the query will start reporting on. The format is yyyymmddhh24mmss. (Optional.)
`end_date`	Not applicable.	The time and date the query will stop reporting on. The format is yyyymmddhh24mmss. (Optional.)
`db_user`	Not applicable.	The userid for the database user which made the changes to the database. (Optional.)
`table`	Not applicable.	The database table which was changed. (Optional.)

The square brackets indicate optional parameters, but if a parameter is missed out and a later one used the missed out parameters should be indicated by using "".

Failure

If listAudit.sh fails, the report will not be completed. Errors will be sent to stdout.

Output

listAudit.sh writes error messages to the system messages file, and produces reports to stdout.

Example: This text shows an audit report for changes to the SMF_USER table by the SU user on the 08 Mar 2005.

$ listAudit.sh smf/smf 20050308000000 20050308235959 SU SMF_USER
Connected.
SU,20050308225724,192168007165,SMF_USER,ADMIN_TRAINING1_EX2,Student Training,Student
1,0,,31,20050321010942,LANGUAGE=ENGLISH
,,,,ADMIN_TRAINING1_EX2,Student Training,Student 1,0,Locked for
testing,31,20050408000000,LANGUAGE=ENGLISH

SU,20050308225808,192168007165,SMF_USER,ADMIN_TRAINING1_EX1,Student Account,Student
1,0,,31,20050320205427,LANGUAGE=ENGLISH
,,,,ADMIN_TRAINING1_EX1,Student Account,Student 1,0,Locked for
training,31,20050408000000,LANGUAGE=ENGLISH

SU,20050308225828,192168007165,SMF_USER,ADMIN_TRAINING1_EX1,Student Account,Student
1,0,Locked for training,31,20050408000000,LANGUAGE=ENGLISH
,,,,ADMIN_TRAINING1_EX1,Student Account,Student
1,0,,31,20050408000000,LANGUAGE=ENGLISH

SU,20050308225838,192168007165,SMF_USER,ADMIN_TRAINING1_EX2,Student Training,Student
1,0,Locked for testing,31,20050408000000,LANGUAGE=ENGLISH
,,,,ADMIN_TRAINING1_EX2,Student Training,Student
1,0,,31,20050408000000,LANGUAGE=ENGLISH

8 Reports