How to Customize eTrust Admin Reporting

Document ID : KB000055744
Last Modified Date : 14/02/2018
Show Technical Document Details

1. How the reporting works

In order to display a report a user must run the Report Explorer and then

  • Setup the Report
  • Reload the Report Database

1.1. Setup the Report

The Setup Report displays a dialog with all categories of reports. Only the categories corresponding to the installed option are displayed. Information about categories and options are taken from the setup.msg, the setup.scf and the namespacelist.ini files. These files are stored into the reporting\report folder of the eTrust Admin installation folder.
The goal of Setup Report is to select the categories of reports user want to display.
The Report Setup allows also to enter selection criteria for each selected category of reports. The selection is stored in currentsetup.cfg and currentsetup.sel files, these files are stored into the reporting\report folder of the eTrust Admin installation folder.

1.2. Reload the Report Database

The Reload Report Database action has two steps :

  • Extract data from eTrust Admin Repository for eTrust Admin objects like Global Users, Policy, Role and so on, retrieve data from target directories for objects like Accounts, Groups and so on.
  • Load the Reporting database with extracted data

Reporting database is created at eTrust Admin installation, it's an Ingres database, the ingrpt.sql file describes the structure of the reporting tables. This file stored in the reporting\config folder of the eTrust Admin Installation folder.

1.2.1. Data Extraction

The data are extracted according to the currentsetup.sel file providing the selected categories of report and the corresponding criteria and the extract.esp file which contains the LDAP requests. Extracted data are store in "bcp" files.

1.2.2. Data Loading

The data stored in the Reporting tables are cleared before the reload. A reporting table is loaded using an '.ing' copy file and a '.bcp' data file. For instance, the QAUSER table is loaded using the QAUSER.ing file and the QAUSERn.bcp file ( where n corresponds to a sequence number given by the extract program).

When Setup then Reload has been done, you can view the report. The Setup Report can be done only once, but if your data changed, your must reload the report database.

User can also use batch process to reload report database and display reports he must :

  • Setup the Report
  • Run the Batch data extraction program
  • Run Report command line

1.3. Setup the Report

In order to create the batchsetup.sel configuration file, you must select in the template combo box : the "(Batch)" item.

1.4. Run the Batch Data Reporting Extraction

It must be called from a command file.
It requires 3 case-sensitive parameters : the Domain Name, the User Name and the Password

1.5. Report Command Line

A report can be executed in batch mode by using the CAQR3LCH.exe program, below are its options :


-r <ReportName>    Report File name
-p                 Print the report.
-v                 Print Preview a report or view a query.
-f <FileName>      Export a report to an RTF file.
-t <FileName>      Export a report to a text file.
-s                 Silent mode.  This option turns off the print dialog box.

The command below writes the result of the "OS400 User Profile List.ret" report in a text file :

Caqr3lch -r "f:\ca_appsw\reports\enterprise administration\OS400\OS400 User Profile List.ret" -f "F:\WORK\ OS400 User Profile List.txt"

2. To Customize a report

The following sample will show how to customize eTrust Admin reporting.

We need a global user report displaying the following information : the global user ID, the full name, the department, the title and the user data, unfortunately such report does not exist so we will build it.

  • If we have a look at the global user table description we find in the "eTrust Admin"\reporting\config\ingrpt.sql file
    create table sa.QAUSER (
    username varchar(64) with null , fullname varchar(127) with null , descuser varchar(52) with null , phone varchar(24) with null , address varchar(172) with null , comments varchar(172) with null , selfadminpermitted varchar(10) with null , adminpermitted varchar(10) with null , suspended varchar(10) with null , wfmanager varchar(64) with null , wfbusinessapprover varchar(64) with null , wftechnicalapprover varchar(64) with null , wfdelegate varchar(64) with null , wfavailableworkflow char(1) with null, wfadmsecurity varchar(5) with null, wfsecurity varchar(5) with null, uadprofile varchar(250) with null )

    we note that these fields do not exist so we have to add them.
    We can find the definitions of these properties in the "eTrust Admin"\ Data\NamespaceDefinition\COSSchemaAbridged.txt file. Let us look at Title

    
        ATTRIBUTE (LDAP Name) eTGlobalUser::eTTitle
            User-friendly Name : Title
            Description: Title
            ProhibitedCharacters: (null)
            MinValue: (null)
            MaxValue: (null)
            DefaultValue: (null)
            MinLength: 0
            MaxLength: 40
            EditType: 0 [string]
            IsSpaceAllowedIn: yes
            IsAsciiOnly: no
            IsMultiValued: no
            Case: 0 [insensitive]
            Values: <NONE>
            ExcludedValues: <NONE>
            OrWords: <NONE>
            VerbReqs: <NONE>
            Group: (null)
            Label: (null)
            IsHidden: no
            IsRelationalOperatorAllowedWith: no
            IsEncrypted: no
            IsIndexed: no
            IsBaseAttribute: no
            Searchable: yes
            DataLocation: 2 [DATABASE]
            AuthOps: 0x00 (NONE)
            IsPropagationAllowed: yes

    MaxLength and EditType keys give us information we need to describe the table field

    Rather than modifying the "eTrust Admin"\reporting\config\ingrpt.sql, create our own table description file: "eTrust Admin"\reporting\config\ myingrpt.sql. We will update the reporting database with the "eTrust Admin"\reporting\config\myupdrpt.bat command file.

    Below is the content of the "eTrust Admin"\reporting\config\ myingrpt.sql file

    
    Drop table sa.QAUSER
    \g
    create table sa.QAUSER (
        username             varchar(64)     with null ,
        fullname             varchar(127)    with null ,
        descuser             varchar(52)     with null ,
        phone                varchar(24)     with null ,
        address              varchar(172)    with null ,
        comments             varchar(172)    with null ,
        department           varchar(50)     with null,
        title                varchar(40)     with null,
        userdata             varchar(128)    with null,
        selfadminpermitted   varchar(10)     with null ,
        adminpermitted       varchar(10)     with null ,    
        suspended            varchar(10)     with null ,
        wfmanager            varchar(64)     with null ,
        wfbusinessapprover   varchar(64)     with null ,
        wftechnicalapprover  varchar(64)     with null ,
        wfdelegate           varchar(64)     with null ,
        wfavailableworkflow  char(1)         with null,
        wfadmsecurity        varchar(5)      with null,
        wfsecurity           varchar(5)      with null,            
        uadprofile           varchar(250)    with null
    )
    \g

    Below is the content of the "eTrust Admin"\reporting\config\myupdrpt.bat command file.

    
    @echo off
    sql -usa RDMO20 < myingrpt.sql >> ..\SCRIPTS\myingrpt.log
    echo *
    echo *
    echo *--- See logfile in scripts folder
    echo *
    echo *


  • In order to tell eTrust Admin to return values for these fields, we must also modify in the "eTrust Admin"\reporting\report\extract.esp file the request corresponding to the global user (the request begin with the name of the corresponding database table) and add the new attributes. The "eTrust Admin"\ Data\NamespaceDefinition\COSSchemaAbridged.txt file will give us the names of these attributes.
    
    (QAUSER)\
         LDAP://localhost:20389/;\
          base:eTGlobalUserContainerName=Global Users,
                        eTNamespaceName=CommonObjects;\
               filter:(&(objectClass=*));\
             scope:LDAP_SCOPE_ONELEVEL;\
         attributes:etuserid,etglobalusername,etdescription,ettelephone,
                       etstreetaddress,etcomments,\
           eTSelfAdminPermitted,eTAdminPermitted,eTSuspended,
                       eTwfManager[eTGlobalUserName],\
           eTwfBusinessApprover[eTGlobalUserName],
                       eTwfTechnicalApprover[eTGlobalUserName],\
           eTwfDelegate[eTGlobalUserName],eTwfAvailableWorkflow,
                       eTwfAdmSecurity,eTwfSecurity,
           etAccessControlList,eTDepartment,eTTitle,eTCustomField;

    The original request is in black, the modification is in red.

  • At least we have to modify the "eTrust Admin"\reporting\config\ QAUSER.ing (This file contains the Ingres SQL statement COPY allowing to load the global user table with data stored in the "eTrust Admin"\reporting\report\temp\QAUSERnnn.bcp file. Each row of this file corresponds to a user and is ended by a carriage return, attributes values are separated by a tab)
    
    copy qauser(
        username= char(0)tab with null(''),
        fullname= char(0)tab with null(''),
        descuser= char(0)tab with null(''),
        phone= char(0)tab with null(''),
        address= char(0)tab with null(''),
        comments= char(0)tab with null(''),
        SelfAdminPermitted= char(0)tab with null(''),
        AdminPermitted= char(0)tab with null(''),
        Suspended= char(0)tab with null(''),
        wfManager= char(0)tab with null(''),
        wfBusinessApprover= char(0)tab with null(''),
        wfTechnicalApprover= char(0)tab with null(''),
        wfDelegate= char(0)tab with null(''),
        wfAvailableWorkflow= char(0)tab with null(''),
        wfAdmSecurity= char(0)tab with null(''),
        wfSecurity= char(0)tab with null(''),
        UADProfile= char(0)nl with null('')) from

    Each line of the copy statement must exactly match the attribute parameter's properties list. Only the last line of the copy statement must be describe with nl instead of tab.

    On the left side of the equal operator the value must match a QAUSER table's attribute.

    Below is the new content of the file

    
    copy qauser(
        username= char(0)tab with null(''),
        fullname= char(0)tab with null(''),
        descuser= char(0)tab with null(''),
        phone= char(0)tab with null(''),
        address= char(0)tab with null(''),
        comments= char(0)tab with null(''),
        SelfAdminPermitted= char(0)tab with null(''),
        AdminPermitted= char(0)tab with null(''),
        Suspended= char(0)tab with null(''),
        wfManager= char(0)tab with null(''),
        wfBusinessApprover= char(0)tab with null(''),
        wfTechnicalApprover= char(0)tab with null(''),
        wfDelegate= char(0)tab with null(''),
        wfAvailableWorkflow= char(0)tab with null(''),
        wfAdmSecurity= char(0)tab with null(''),
        wfSecurity= char(0)tab with null(''),
        UADProfile= char(0)tab with null(''),
        department= char(0)tab with null(''),
        title= char(0)tab with null(''),
        userdata= char(0)nl with null('')) from

    After the next "Reload Report Database", the QAUSER table will have desired information.

3. Brief synopsis of the filter parameter usage

LDAP allows to select returned data by using the filter parameter, each search element added must be put in brackets.


(QAUSER)\
      LDAP://localhost:20389/;\
        base:eTGlobalUserContainerName=Global Users,
               eTNamespaceName=CommonObjects;\
       filter:(&(objectClass=*));\
     scope:LDAP_SCOPE_ONELEVEL;\
  attributes: eTGlobalUserName, eTFullName,etdescription,ettelephone,
                      etstreetaddress,etcomments,\
              eTSelfAdminPermitted,eTAdminPermitted,eTSuspended,
                      eTwfManager[eTGlobalUserName],\
              eTwfBusinessApprover[eTGlobalUserName],
                      eTwfTechnicalApprover[eTGlobalUserName],\
              eTwfDelegate[eTGlobalUserName],eTwfAvailableWorkflow,
                      eTwfAdmSecurity,eTwfSecurity,\
              etAccessControlList,eTDepartment,eTTitle,eTCustomField;

Here are some samples


(&(objectClass=*))
     retrieve global user having a value in objectClass 
(&(objectClass=*)(eTTitle=engineer))
     retrieve global user having a value in objectClass and title=engineer 
(&(objectClass=*)(!(eTTitle=*)))
     retrieve global user having a value in objectClass and no value in title 
(&(objectClass=*)(|( eTFullName=*smith)( eTFullName=*jones*))
     retrieve global user having a value in object class and 
     the full name ending by smith or containing jones
(&(objectClass=*)(|( eTFullName=john*)( eTFullName=a*z*))
     retrieve global user having a value in object class and 
     the full name beginning with smith or containing any string
     between a and z 

4. New report

The report "Global user list 2.ret" display the fields newly added, it is a system report, its right place is the CA_APPSW\Reports\Enterprise Administration\ System folder.

To create a new report :

  • The easiest way is to duplicate a similar report and edit him with the tools provided by Report Explorer.

    Here are the steps to change the query of a report.

    Using report Explorer,

    1. select the report and edit it
    2. Export the query to a file by using the Export Query option of the dropdown Query menu ( queries are SQL queries )
    3. modify the order clause
    4. Import the modified query by using the Import Query option of the dropdown Query menu
    5. Set the new query as default query by using the Set Report Query option of the dropdown Query menu ( the current report query is the one selected in the displayed list)
    6. you can delete the unused query by using the Delete Query option of the dropdown Query menu or keep it for a later use


    if you want to reuse a query, perform the step 5


  • You can also in a command window use the following command:

    Caqr3lch.exe -q SQLReq.qbe -DRDMO20 -usa -w"" -e

    1. Choose the table you want to use
    2. In the Query menu, the "Columns...", "Condition..." and "Sort..." options allow you to customize your query.
    3. In File menu select the "New Report" option
    4. Choose the kind of report you want to do
      • A Tabular report if you have a few fields to display
      • A Form report if you have many fields to display