FUJ00152167 - ICL Pathway Conducting Audit Data Extractions at CSR+ Ref: IA/PRO/003 Version: 1.0
FUJ00152167
FUJ00152167
ICL Pathway Conducting Audit Data Extractions at CSR+ Ref:IA/PRO/003
Version:1.0
Date:15/12/00
Document Title:
Document Type:
Abstract:
Status:
Distribution:
Author:
Comments to:
Comments by:
Conducting Audit Data Extractions at CSR+
Process
This document describes the process to be followed by
Post Office Internal Audit (POIA), and other groups
external to Pathway as defined in Schedule A03, when
requesting audit data extraction services from ICL
Pathway Internal Audit. It also describes those activities
carried out within ICL Pathway to handle the request,
manage the data extraction and despatch the results to
the original requester.
APPROVED
Richard Laking Martyn Bennett
Chris Paynter (POIA) Graham Hooper
Chris Billings
Library
Brian Mooney/Jan Holmes/Anthony Brown
Anthony Brown
22/12/00
© 2000 ICL Pathway Ltd
COMMERCIAL IN CONFIDENCE Page 1 of 1
FUJ00152167
FUJ00152167
ICL Pathway Conducting Audit Data Extractions at CSR+ Ref.IA/PRO/003
Version:1.0
Date:15/12/00
0 Document control
0.1 Document history
Version Date [Reason
0.1 12/04/00 _Iinitial draft based on CSR version IA/PRO/002
(0.2 18/04/00 [Following walk through on Audit Workstation and R-Query user guide
0.3 [21/07/00 [Review by Brian Mooney.
1.0 15/12/00 ‘ersion 1.0 for approval
0.2 Approval authorities
Name Position \Signature \Date
IM. Bennett {Quality Director
0.3 Associated documents
Reference \Vers ate iTitle Source
[1] IA/MAN/005 0.3 [21/07/00 [Horizon System Audit Manual (CSR+) IPWAY
[2] IA/REQ/004 0.2 \21/07/00 {Audit Data Retrieval Requirements (CSR+) IPWAY
[3] IA/SPE/015, 0.8 (10/07/00 Audit Data Catalogue (CSR+) IPWAY
[4] IRS/MAN/010 0.2 15/03/00 \SecurelD Normal Token User Guide IPWAY
COMMERCIAL IN CONFIDENCE Page 2 of 1
ICL Pathway Conducting Audit Data Extractions at CSR+
FUJ00152167
FUJ00152167
Ref:IA/PRO/003
Version:1.0
Date:15/12/00
0.4 Abbreviations
Acronym IMeaning
IAS Audit Server
JAW Audit Workstation
IAWO. [Audit Workstation Operator
ICD-W Writeable CD
ICSR+ \Core System Release +
IDLT Digital Linear Tape
IFTMS File Transfer Management System
iOBCS [Order Book Control System
IPA Pathway Auditor
PIN Personal Identification Number
iPLUI Pathway Legato User Interface
IPOCL Post Office Counters
IPOIA Post Office Internal Audit
IPWAY ICL Pathway
IRFI Request for Information
ISLUI \Standard Legato User Interface
TMS. [Transaction Management System
COMMERCIAL IN CONFIDENCE
Page 3 of 1
FUJ00152167
FUJ00152167
ICL Pathway Conducting Audit Data Extractions at CSR+ v Ref WPROIOOS
‘ersion:1.
Date:15/12/00
0.6 Table of content
1 INtrOGUCION..........eeeeeeeececee cece eeeeeeeeeeseseeeeeeesesesenessesesreteeeeees 6
2 SCOPE... eee eceeeeeescseseeeeeeececscseeceeereececeeeeereetacecseseersneecseseerees 6
3 Terminology. ............-ceceececeeseeseseeseeeeseeseeseeeeeceseecseeeeeseneeeeates 6
4 Audit Data Integrity.
5 Retrieval Schematic. :
6 OVErVIOW...... eee ececec cece cece eeeeseeeeeeeeeeeesteseseeniseteceseneeteseeesitete® 9
6.1 Request For Information..............0....0.0:esceeeeeceeeeeeeeeeeeeeeeee 9
6.2 Marking Files and Tapes. ..........0....:ccceeeceeeeeeceeeseeeeeteeeeeeee 9
6.3 Audit Track Retriever..
6.4 Audit Data Check Sea
6.5 Audit Trail Extractor...........c.cceeceesececeeeeseseseeeseeeeesrseseeereeees
7 Retrieving & Extracting Audit Data... eee
7.1 Receiving the RFI...
7.2 Interpreting the RFI.
7.3 Login Audit Workstation
74 Preliminary Housekeeping. ..............:ccccecceseeeeeeeeeeseeeeeeeeee
75 Counter Determinant.................:cececeeseeseeeeee eee eeeeeeeeeeeeeee
7.6 Targeting the Data Files.
7.7 Using the Pathway Legato User Interface.
78 Using the Standard Legato User Interface..
7.9 Targeting the DLTS....0...... cece cece e cee eeeeeeeeeeeeeeeeeeee
7.10 Reformatting Retrieved Data... eee
7.10.1 Reformatting TMS Journals.
7.10.2 Oracle Archive Tables
7.11 Checking the Seals.............cecececeeceecceeeseeseeseseeseeeeseeseesenees
7.12 Despatch of Audit Data... ieee eeeeeeeeeeeneeneeeees
8 Introduction to R-QUETY................ec eects eseeeeeeeeeeeeeeneeeeeee
8.1 Invoking R-Query and Connecting to a Correspondence
Server 18
8.2 Restoring Retrieval Scenarios.
8.3 Changing Retrieval Parameter:
8.4 Selecting TMS Fields for Display..............2..::eeeeeceeeeeeeeeeee
18
COMMERCIAL IN CONFIDENCE
Page 4 of 1
FUJ00152167
FUJ00152167
ICL Pathway Conducting Audit Data Extractions at CSR+ v Ref WPROIOOS
‘ersion:1.
Date:15/12/00
8.5 Order By Tab... eceeeeececeecseseseeeeeeeseseeeeeeeceteeeeeeeeeneees 22
8.6 Groups Tab.
8.7 Select Output Medium
8.8 Running the Query.................ccccceeceseeeseeeeeeeeeeseeeeceeeeseneneeee 24
A ANNEXES... 2. cece esses es eseeeeeeeeeeseseeeeneseeeeseneeseeeesesesseeeeisites 25
A Example RFI form — for POCL contact use
B Example OCP form — tape reload request
C Example OCP form — Correspondence Server rebuild
COMMERCIAL IN CONFIDENCE Page 5 of 1
FUJ00152167
FUJ00152167
ICL Pathway Conducting Audit Data Extractions at CSR+ Ref:IA/PRO/003
Version:1.0
Date:15/12/00
1 Introduction
The Horizon system generates significant amounts of data that is of interest
to Internal Audit and other groups. The Horizon System Audit Manual (CSR+)
[1], and the supporting <Product> Audit Trail Specifications provide further
information on the structure, form and content of this data, referred to in this
document as ‘audit data’.
Subject to certain constraints the audit data must be made available to POIA
or other authorised groups within timescales established in the Audit Data
Retrieval Requirements (CSR+) [2].
This document establishes the process for requesting audit data extractions
and subsequent activities undertaken to locate, retrieve, extract & filter and
prepare for despatch on behalf of authorised requesters.
2 Scope
Should future releases of Horizon bring about changes to the way that data is
extracted this process will be updated to reflect those changes.
This process applies to ALL audit data extraction requests from outside ICL
Pathway. Requests for audit data extraction from within ICL Pathway will also
be subject to this process although use of the Request For Information (RFI)
form is optional.
3 Terminology
Within this process certain terms are used which have specific meaning
within the Horizon Audit Solution. They are:
Gatherer: The module responsible for collecting the audit files from the
hosts, agents, correspondence servers and _ interface
mechanisms. This module is also responsible for the application
of the audit file naming policy.
Sealer : The module responsible for calculating the checksum seal of
each audit data file before it is written to DLT (tape) by the
Hoarder. This value is recalculated by the Retriever and
compared to the original value when first sealed. Used to
ensure data integrity during storage on DLT.
Hoarder : The module responsible for writing audit data files onto DLT at
pre-defined intervals.
Retriever: The module responsible for retrieving audit data from the buffer
file where it is placed by Legato when requested by the Audit
Workstation.
Extractor: Retriever brings back complete files or groups of files from the
DLTs. Further work may be required to filter out unwanted
COMMERCIAL IN CONFIDENCE Page 6 of 1
FUJ00152167
FUJ00152167
ICL Pathway Conducting Audit Data Extractions at CSR+ Ref:IA/PRO/003
Version:1.0
Date:15/12/00
information, especially true of the TMS files, using a number of
tools available on the Audit Workstation.
Legato : Legato Networker is the storage management application
selected by Pathway to store and manage audit data onto DLTs.
A more complete explanation of these modules can be found in [2].
4 Audit Data Integrity
The integrity of audit data must be guaranteed at all times from its origination,
storage and retrieval to subsequent despatch to the requester. Controls have
been established to provide assurances to Post Office Internal Audit that this
integrity is maintained.
During audit data extractions the following controls apply:
a.
Extractions can only be made through the three Audit Workstations,
which exist at Feltham and the 2 Data Centres. These are all subject to
rigorous physical security controls appropriate to that location.
Specifically, the Feltham AW — where most extractions will take place
— is located in a secure room subject to proximity pass access within a
secured ICL site
Logical access to the AW and its functionality is controlled by
dedicated Logins, password control and utilises the NT and Pathway
security features defined in the overall Horizon security policy.
All extractions are logged on the AW and supported by documented
RFls, authorised by nominated persons within POIA. This log can be
scrutinised on the AW.
Extractions will only be made by individuals previously notified to
POIA. Currently this is limited to the Pathway Audit Manager. Any
additions will be notified to POIA.
Agreement has been reached with POIA regarding their rights to
witness extractions without warning or to request repeat extractions
that they can witness.
Checksum seals are calculated for audit data files when they are
written to DLT and re-calculated when the files are retrieved.
COMMERCIAL IN CONFIDENCE Page 7 of 1
ICL Pathway Conducting Audit Data Extractions at CSR+
Ref:/A/PRO/003
Version:1.0
Date:15/12/00
5 Retrieval Schematic
Tyr (Full)
I
TS
LEGATO
LS).
Restored_AT
co
NTS (Full)
etrieve
Tapes
LEGATO TAPE Identity
contro. I “Tapes
iaman-24 ins
Seal DB j-
/ '
CHECK SEAL
avoir
ll RETRIEVER
Extracted_AT
‘OPERATIONAL ENVIRONMENT
Live Oracle
Database
-!
FUJ00152167
FUJ00152167
1e@@)
\
ive databases
LEGATO USER
INTERFACE
Identity & Mark
Required Files
‘COUNTER
DETERMINANT
-_—
AUDIT SERVER AUDIT WORKSTATION
t
integrity
—t —
I REFORMATTER TMS Pseudo
a
ean eo I 0
I TABLE ‘PI Purged Oracle
Patiway
Internal Aut
Interpret RFI to
‘Audit Points &
Files
ne
Post Offce
Internal Aut
Request For
Information
COMMERCIAL IN CONFIDENCE
Page 8 of 1
FUJ00152167
FUJ00152167
ICL Pathway Conducting Audit Data Extractions at CSR+ v Reh IA/PRO/008
‘ersion: 1.
Date:15/12/00
6 Overview
The process assumes that audit data has been Gathered, Sealed and
Hoarded onto DLTs by the Audit Archive Server. Files will be one of three
types :
a. Flattened and compressed TMS Journals from the Correspondence
Servers.
b. Flattened Oracle tables output from regular OBCS database purging
cycles.
c. Transaction files to and from PO systems and their associated FTMS
control files.
The process is invoked through the receipt of an RFI into Pathway Internal
Audit. Expressed in business terms, the RFI must be interpreted into its
component Audit Points and Sub-points. This then enables specific files to be
identified which, through the Legato index, targets a specific DLT. Data is
retrieved by the Audit Retriever, formatted as appropriate and then further
Extracted against the RFI criteria. Depending on the extraction method the
data can be extracted to standard MSOffice products before being placed
onto CD-W or floppy disc for despatch to the RFI originator.
The following paragraphs present an overview of each step in the extraction
process and are ordered to reflect the actual processing of a Request For
Information (RFI) by ICL Pathway Internal Audit.
6.1 Request For Information
All POCL requests for audit data must be made via the Request For
Information form. This will contain a description, in business terms, of the
times, outlets, events, items activities and required Excel reporting format that
the Auditors are interested in. This request has to be interpreted by Pathway
Internal Audit and mapped onto the Audit Points and Files described later in
this document.
Internal requests (e.g. from Pathway investigations personnel) will typically
be in the form of an internal e-mail.
6.2 Marking Files and Tapes
Based on this interpretation as many files of audit data that are needed to
satisfy the request are ‘marked’ for retrieval. Legato is notified of these files
and it in turn identifies the DLTs containing these files. Legato provides
system prompts for Operators to load tapes and it copies the data into a local
buffer area.
COMMERCIAL IN CONFIDENCE Page 9 of 1
FUJ00152167
FUJ00152167
ICL Pathway Conducting Audit Data Extractions at CSR+ v Reh IA/PRO/008
‘ersion: 1.
Date:15/12/00
6.3 Audit Track Retriever
Polls the Legato buffer area and retrieves any data files found into temporary
disk storage (Export File) on the Archive Server prior to the extraction of
relevant data for use by the auditors. The Retriever provides a second copy
of the file which is input to the Check Seal function.
6.4 Audit Data Check Seal
To assure the integrity of the audit data while on the DLT the checksum seal
for the file is re-calculated by the Audit Track Sealer and compared to the
original value calculated when the file was originally written to the DLT. The
result is maintained in a Check Seal Table.
6.5 Audit Trail Extractor
This is a facility that uses various tools to extract or reform the retrieved audit
data in accordance with the RFI. It also places the information onto a CD-W,
or other suitable media, for despatch to the RFI originator.
COMMERCIAL IN CONFIDENCE Page 10 of 1
FUJ00152167
FUJ00152167
ICL Pathway Conducting Audit Data Extractions at CSR+ v Reh IA/PRO/008
‘ersion: 1.
Date:15/12/00
7 Retrieving & Extracting Audit Data
7.1 Receiving the RFI
a) All POCL requests for audit data extractions must come to Pathway
Internal Audit in the form of a Request For Information. An example of this
form can be found at Annex A. The RFI may be mailed, faxed or e-mailed to
Pathway.
RFs will only be accepted from the following named individual :
Chris Paynter : PO Internal Audit : I
or one named delegate, to be confirmed in writing by POIA Internal Audit.
If other parts of the Post Office, or other organisations, require audit data
extractions they must be channelled through POIA to Pathway Internal Audit
at Feltham.
Contractual turnaround times for the provision of data apply.
b) Internal requests are recorded in file AUD/1/3/2. They should be logged
to record the following information: Requester name, address, justification
(e.g. PINICL no.) and their urgency assessed. Turnaround times are agreed
rather than covered by contract; a reasonable turnaround time will probably
be 5-10 days.
These requests will typically be in the form of an internal e-mail, allowing the
requestor’s identity to be verified. Requestors should state what media are
acceptable (e.g. CD-W, email of WinZipped file up to 500kB) and are bound
by company policy including over the despatch of confidential data. For TMS
files - also referred to as “message store” or “Correspondence Server’- they
should also specify the output file format(s): text, MS-Excel or MS-Access.
(See Section 8 for more information).
7.2 Interpreting the RFI
It is necessary to interpret the RFI by identifying the audit points and sub
points that generated the records that are required and, through the Audit
Data Catalogue [3], the files produced at those audit points and sub points.
An Enquiry Catalogue of often requested extractions will accumulate which
means that this part of the process would be simply about targeting the files
by the date spread.
7.3 Login Audit Workstation
Carry out following procedure to Login and obtain necessary shares
1. Login
2. Password =: *********
——__I3._ Domain :PWYDCS
COMMERCIAL IN CONFIDENCE Page 17 of
FUJ00152167
FUJ00152167
ICL Pathway Conducting Audit Data Extractions at CSR+ v Reh IA/PRO/008
‘ersion: 1.
Date:15/12/00
At this point the SecurelD Authentication is invoked. See [4].
Carry out the following procedure to authenticate yourself as an authorised
user
[ 1. Enter passcode : <personal 6 digit PIN and 6 digit SecurelD token display> ]
The AW will present a blank desktop with a START icon in the bottom left of
the screen. Using pullup <Programs> will reveal the extent of products
available for any subsequent extraction work.
7.4 Preliminary Housekeeping
It is highly likely that an average RFI will need a significant number of files to
satisfy it. To avoid the AW filestore becoming clogged with hundreds of files it
is strongly recommended that a working directory is established on the AW to
hold all files relevant to a particular RFI :
1. Select <Windows_NT_Explorer> from the drop down menu.
2. Set up <New Folder> as D:\audit\RFI Reference No.
7.5 Counter Determinant
Note that this step is only required if retrieving TMS Journal files.
It is recommended that this step is carried out BEFORE entering the Retrieval
GUI.
The architecture of the Correspondence Server has Post Office outlets
spread across 4 separate clusters. These clusters are separately archived
thus it is necessary to identify the particular cluster that services the outlet, or
outlets, for which audit data is being extracted.
3. Select <Counter_Determinant.CMD> from the C:\ drive
4 Type 6 character FAD code <enter>.
5. Type <ctrl & z> <enter>
6. Cluster identity will be displayed.
7. Note that multiple FAD codes can be entered at the same time.
7.6 Targeting the Data Files
At this stage of the retrieval procedure the AWO can choose to use the
Pathway Legato User Interface (Para 7.7), an ICL Pathway developed
intelligent front end, or the standard Legato User Interface (Para 7.8). While
there are no hard and fast rule around which interface to use the PLU! has
obvious benefits when attempting to identify and mark a large number of files
for retrieval.
Note that if you are using the standard Legato User Interface it is still
necessary to register the RFI on the RFI database.
COMMERCIAL IN CONFIDENCE Page 12 of 1
ICL Pathway
FUJ00152167
FUJ00152167
Conducting Audit Data Extractions at CSR+ Ref:IA/PRO/003
Version:1.0
Date:15/12/00
7.7 Using the Pathway Legato User Interface
The Audit Data Retrieval Service utilises a complex Graphical User Interface
(GUI) to help identify and mark files and also associate those files with the
originating RFI.
a Pon »
today)
10.
11.
12.
Select <Audit ExtractorClientStartup> from the <Shortcuts> folder.
At dialogue FORM1 select <Data Centre> required.
At dialogue AUDIT FILE EXTRACTOR enter <Requester> from drop down menu.
Select <New> if new RFI
Complete remaining selection fields :
<Date Received> Mandatory Date RFI received in Pathway.
<Date Required> Mandatory Date data required by requester.
<Catalogue Entry> Optional Enter search criteria into Search Catalogue
<Receipt Reference> Mandatory Original RFI reference
<Access Reason> Mandatory Reason for running retrievals
Select <Specify Selection Criteria>.
Complete remaining selection fields :
Time Period <Fromdate> Mandatory Start date of retrieval
<To date> Optional End date of retrieval (assumes
File Source <File name > Optional Wild card allowed
Wigan/Bootle <Select from drop down> Mandatory
<PO FAD> Optional FAD code for retrieval
<Audit Point> Optional
<Audit S Point>Optional
<Tape Pool> Optional
Tick <Generate volume information with file list> if you wish to see the associated
DLT names.
Select <Search for Files> or
<Save Selection Criteria> or
<Return to Menu>
A list of file names will be displayed in the response part of the dialogue
<Mark> the required files.
Select <Restore Selected Files>
It is highly unlikely that a single file will hold the information required by the
RFI. Indeed, the broader the date spread or complexity of request the greater
the number of files that will have to be retrieved from DLT.
COMMERCIAL IN CONFIDENCE Page 13 of 1
FUJ00152167
FUJ00152167
ICL Pathway Conducting Audit Data Extractions at CSR+ vl Reh IA/PRO/008
‘ersion: 1.
Date:15/12/00
7.8 Using the Standard Legato User Interface
The RFI must be registered on the RFI database before commencing the
retrieval activity.
Select <Audit ExtractorClientStartup> from the <Shortcuts> folder.
At dialogue FORM1 select <Data Centre> required.
At dialogue AUDIT FILE EXTRACTOR enter <Requester> from drop down menu.
Select <New> if new RFI
ao Peon
Complete remaining selection fields :
<Date Received> Mandatory Date RFI received in Pathway.
<Date Required> Mandatory Date data required by requester.
<Catalogue Entry> Optional Enter search criteria into Search Catalogue
<Receipt Reference> Mandatory Original RFI reference
<Access Reason> Mandatory Reason for running retrievals
6. Select <Return to Menu>.
7. <Exit>
The default Legato approach, where the primary search index is the instance
of a DLT hoard, does not allow for quick and easy identification of the
required files. If files to be retrieved are spread across more than 1 hoarding
instance then they have to be retrieved on a hoard instance basis. For
example, if 3 hoarding instances happened in a day and all 3 contained files
of interest to a particular RFI there would have to be 3 separate retrieval
runs.
. Select <Legato_Client_Bootle.CMD> from main program menu
. Select <Directed Recovery> from <Operations> drop down menu
ons
. Confirm <mboarc01> as Source Client in dialogue. <OK>
4. Confirm <mboarc01> as Destination Client dialogue. <OK>
Note that Bootle is assumed as the primary retrieval location. There is no difference in the
audit data held at each Data Centre. If Wigan is selected then the <Legato Wigan Client
CMD> should be selected and <mwiarc01> used to confirm Source and Destination
dialogues.
5. Select <Change Browse Time> from View drop down menu
6. Select appropriate date button
7. Select appropriate Hoard time (note 7:30p)
8. Locate files through Legato directory structure and naming convention [3]
9. <Mark> files using <“> button on toolbar
10.Select <Recover Options> from Options drop down menu
11. Enter d:\Archiveserver\INTERFACES\RESTORED_AT into dialogue box
12.Select <traffic lights> button on toolbar
COMMERCIAL IN CONFIDENCE Page 14 of 1
FUJ00152167
FUJ00152167
ICL Pathway Conducting Audit Data Extractions at CSR+ v Reh IA/PRO/008
‘ersion: 1.
Date:15/12/00
Note: The ‘View Versions’ facility on the Legato User Interface can be used
to identify when hoardings took place.
7.9 Targeting the DLTs
Most Retrievals will be made from the TMS18Mnth and NonTMS18Mnth tape
pools. DLTs in these pools are replaced every 5 days or when full and
despatched to the DataVault (DV) offsite storage facility for safekeeping.
Each file that is marked has an associated Volume Name and this must be
notified to OSD if the DLT has already been sent to DV.
In order to achieve next day delivery OSD must be notified before 1200hrs.
Emergency recoveries can be organised but these incur an extra charge and
should be avoided where possible.
Requests must be submitted in the form of an ‘OCP’ - a MS-Word file. This can be emailed
to the id: ‘OSD Pathway SM’. An example is given as Annex B
Note: The ‘View Versions’ facility on the Legato User Interface can be used
to identify whether the DLTs containing the data are in place.
For example, following the directory tree down as far as the ‘TMS Pool’
branch will show an entry ‘TMS’. Highlighting this enables ‘View Versions’ to
be used.
It may thus be possible to ensure that the DLT is retained on-site for long
enough for the data to be extracted, minimising turnaround time.
7.10 Reformatting Retrieved Data
Before detailed extractions can take place using R-Query, Wordpad,
Discoverer or other appropriate tools it is necessary to ‘re-format’ the
retrieved data into a format suitable for access. There are three options :
a. TMS Re-formatter to rebuild a pseudo Correspondence Server.
b. Winzip for flat files that were zipped prior to Hoarding.
c. Oracle Table Re-formatter to rebuild Oracle tables.
COMMERCIAL IN CONFIDENCE Page 15 of 1
FUJ00152167
FUJ00152167
ICL Pathway Conducting Audit Data Extractions at CSR+ vl Reh IA/PRO/008
‘ersion: 1.
Date:15/12/00
7.10.1 Reformatting TMS Journals
Once the TMS Archive files have been deposited in EXTRACTED_AT they
must be ‘built’ into a pseudo Correspondence Server for R-Query to access.
Further filtering is available to restrict the number of Outlet records that are
included in the re-build activity based on the original RFI.
This utility is invoked at the Data Centre. Before requesting prepare a text file containing the
FAD codes to be included [fads.txt]. If all Outlets are to be included the text file should
contain ‘ALL’.
Notify DC Operator of file name and location and ask for <Restore_Audit_Data> to be run. It
has a parameter that defines the date range :
Restore_Audit_Data —d 19990521:19990522 —f c:\temp\fads.txt
This module can be found in c:\Program Files\Pathway Agents on the Audit Server.
Requests must be submitted in the form of an ‘OCP’ — a MS-Word file. This can be emailed
to the id: ‘OSD Pathway SM’. Provide details of date range required and the FAD code(s) to
be included. An example is given as Annex C
Note: Running this utility deletes the previously generated Correspondence Server in the
working area.
Unzipping Zipped Flat Files
It is strongly recommended that files to be unzipped are transferred from the
AS to the AW in their zipped state and unzipped on the AW. This can
produce space savings of the order of 90%.
Select <Winzip.CMD> from main program Menu.
Select <Open> and identify zipped file through dialogue screen.
Select <Extract> and establish a new ‘Unzipped’ directory for unzipped datafiles.
Unzipped file will be placed into new Directory
fp Fenyn
Open unzipped files using the <Wordpad.CMD> utility from main program menu
7.10.2 Oracle Archive Tables
These are stored in text format.
7.11 Checking the Seals
When Legato recovers a file from DLT a copy is made and subjected to a re-
calculation of the integrity seal. This value is compared to the original value
on the Seal Database and an entry made in the Check Seal table of
MatchOK, MatchNOTOK or MatchFAIL. This activity carries on independently
of any further extraction or filtering activity on the part of the AW Operator.
COMMERCIAL IN CONFIDENCE Page 16 of 1
FUJ00152167
FUJ00152167
ICL Pathway Conducting Audit Data Extractions at CSR+ vl Reh IA/PRO/008
‘ersion: 1.
Date:15/12/00
1. Select <Microsoft_Access.CMD> from main program menu.
. Using File/Open Database... open the share’d ‘Audit_Seal_DB.mdb’ database that
exists on the mapped drive (F:)
w
. A list of 4 database ‘tables’ will be displayed.
. Position the mouse cursor on the <QUERIES> tab and click.
. A list of 2 database ‘queries’ will be displayed.
. Double click on the <Seals Match Check — Normal> icon.
Wo a fk Oo
. You will obtain an extract of the data that is in the <Check Seal Table> of the database.
Note: only 5 of the available fields from this table, will be displayed. These are:
Request ID Audit Track Match? On = At
8. From this point on, all of the ‘Access’ facilities to: sort, filter, export to spread sheet etc.
are available.
9. Should you need to examine the records in the ‘No Initial track table’ i.e. the exceptions,
then you will have to double click on the ‘Seals Match Check — Exceptions’ icon.
10.You will obtain an extract of the data that is in the ‘No Initial Track Table’ of the database.
(Note: as above, only 5 of the available fields from this table, will be displayed. These are:
Request ID Audit Track Match? On = At
11.From this point on, all of the ‘Access’ facilities to: sort, filter, export to spread sheet etc.
are available.
7.12 Despatch of Audit Data
Despatch of the extract data is by the most appropriate means depending on
the nature and volume of the extracted data, and subject to any special
requests made on the RFI.
For POCL requests, the return part of the RFI should be completed with
details of the media used for despatch as well as the date and time of
despatch.
The Audit Data Extraction Database must be updated to record the date that
the extraction activity was completed.
1. Select <CD_Writer_Software.CMD> from main program menu
. Maximise dialogue box
. Select files required in top dialogue box
. Drag & drop to bottom dialogue box
QrEON
. When complete select RED dot <Red>
6. Save layout as RFI_id
The media is despatched to the POCL contact using Royal Mail Special
Delivery. This ensures that a receipt is provided to Pathway confirming
delivery.
For internal requests, it will usually be convenient to email the extracted
data file to the recipient, although in the case of large files (>100kB) this is
ideally done at the end of the working day. The alternative is to arrange
despatch/collection with the recipient
COMMERCIAL IN CONFIDENCE Page 17 of 1
FUJ00152167
FUJ00152167
ICL Pathway Conducting Audit Data Extractions at CSR+ vl Reh IA/PRO/008
‘ersion: 1.
Date:15/12/00
8 Introduction to R-Query
R-Query is an interrogation tool used to extract data from a Correspondence
Server. It has powerful SQL type features which are used to define the
extraction scenarios and the ability to output the results to standard MS-
Office utilities.
It is a vital element in the Audit Workstation toolset and requires that a
Correspondence Server exists on one of the Audit Servers. Details on how to
achieve this pre-requisite can be found earlier in this procedure.
8.1 Invoking R-Query and Connecting to a Correspondence Server
[13.Select <Riposte-Query.CMD> from main program menu ]
w Fipaste Query (UK}VI.0 Connected to Sen (Group:0 Node: 61)
me] om [oe I et [oem] om Joe
Gormect fo Message Sto on.
‘Suggestions for enhancements or Bug fixes to Brian Orzel at Pathway.
Run Query
Select <Connect to Message Store on>
When asked type <mboarc01> if connecting to Bootle AS or
<mwiarc01> if connecting to Wigan AS.
When asked to justify the usage of the system type <RFI Reference> See [1].
9 fF ON a
You will be automatically transferred to the <Save and Restore> Tab
8.2 Restoring Retrieval Scenarios
The <Save and Restore> dialogue provides the opportunity to restore
scenarios that have already been scripted for further use.
COMMERCIAL IN CONFIDENCE Page 18 of 1
FUJ00152167
FUJ00152167
ICL Pathway Conducting Audit Data Extractions at CSR+ vl Reh IA/PRO/008
‘ersion: 1.
Date:15/12/00
mel I= I me] [oe
Message Stare
I Sere Quey
Delete Query
Queries can be caved and Rastored trom the Message store, or om fle
un Quow
Scenarios for re-use exist at two levels :
> Those that are associated with the current Correspondence Server.
> Those that have been saved to an external file or Catalogue.
Scenarios associated with the Correspondence Server exist only while that
particular CS exists. If you believe that an extraction scenario is likely to be re-
usable it's as well to remember that unless the scenario is saved to an
external file it will not be available if a new CS is built for another retrieval
exercise.
Use these steps to re-use scenarios associated with current Correspondence Server.
1. Go to <Message Store> window.
2. Select <Refresh> to list all scenarios associated with the current Correspondence Server.
3.__Highlight the required scenario and select <Retrieve Query>.
OR
Use this step if retrieving scenarios from the Catalogue.
1. Locate stored scenario from the Catalogue via the <Retrieve Query from File> button
using the <Change filename> to browse as required.
At this stage you will have retrieved the scenario complete with the parameter
setting used on the last retrieval activity. If you want to change any of the
parameters you will need to go to the <Where> tab.
Enter the required Post Office (FAD) code into the <Group ID:> field if it is not
shown.
COMMERCIAL IN CONFIDENCE Page 19 of 1
FUJ00152167
FUJ00152167
ICL Pathway Conducting Audit Data Extractions at CSR+ vl Reh IA/PRO/008
‘ersion: 1.
Date:15/12/00
8.3 Changing Retrieval Parameters
Sn
=e) = La ee I
[Exsts(Logon)
Group 1D: fiz3457
Messages will only be used if they match these criteria. See the documentation for
the Bpprapriete symten, (Note for UK: a Group = Post Office FAD code)
Fun Que
Note that the current version provides significant amounts of assistance with
regard to the structure of the query statement. An ‘Examples’ button allows
search parameters to be retrieved and tailored (e.g.):
(Date DGE “29-May-2000”) AND (Date DLE “01-Jun-2000”)
for all dates between 29 May-1 June 2000.
Date DEQ “31-May-2000”
for this day only.
Enter the required Post Office (FAD) code into the <Group ID:> field if it is not
shown. If you want to change the TMS fields that will be visible following the
retrieval you will need to go to the <Select Cols> Tab.
Note: Riposte Query can only work with one FAD code (GroupID) at a time. It
will need to be run separately for each Post Office, remembering that by
default it may delete the previous output file (see Section 8.7).
COMMERCIAL IN CONFIDENCE Page 20 of 1
FUJ00152167
FUJ00152167
ICL Pathway Conducting Audit Data Extractions at CSR+ vl Reh IA/PRO/008
‘ersion: 1.
Date:15/12/00
8.4 Selecting TMS Fields for Display
4 Riposte Query (UK]VI.0 Connected to Server:DESIGN (Group:0Node-61) Pa]
Lee [ee [oe] me [ome
F Export Ene Messages
TF Export to Tet File
Select the columns for your report. If desired, con also include the entire
contents of all matching records in the spreadsheet.
Run Quey
Note that the current version provides lists of available fields per Horizon
application which can be selected by highlighting and pressing <Add>.
Alternatively to reduce the numbers of fields displayed highlight field in the
window and press <Remove>.
If you want to retrieve the entire message for your given selection parameters
<Remove> all entries in the window and put a ‘x’ in the <Export Entire
Messages> field.
Optionally a field “Add CAP and BP rollover data” can also be checked.
You may now want to choose how the results of the retrieval will be
presented. To do this go to the <Automation> Tab.
Note: For TMS extractions, “Export Entire Messages’will normally be
checked; the field “GroupID” is typically the only one selected via the “Add”
button, ensuring that all rows are linked to a FAD code in the output file.
COMMERCIAL IN CONFIDENCE Page 21 of 1
FUJ00152167
FUJ00152167
ICL Pathway Conducting Audit Data Extractions at CSR+ v Ref: IAPRO/O08
‘ersion: 1.
Date:15/12/00
8.5 Order By Tab
Selecting the parameter “Sort By Date” is recommended to ensure ascending
time sequence (where appropriate).
[<Atnibute Date» <Type Dete><Order Forward>
Attribute Time><Type Sting><Order Forward>
8.6 Groups Tab
The Groups tab on the R-Query tool is a remnant from the aborted Benefit
Payment Card system. All of the fields should be blanked.
COMMERCIAL IN CONFIDENCE Page 22 of 1
ICL Pathway Conducting Audit Data Extractions at CSR+
FU,
Ref:IA/PRO/003
Version:1.0
Date:15/12/00
8.7 Select Output Medium
‘a Hiposte Ques (UK} V1.0 Connected to Serve
FUJ00152167
00152167
=I = l= — i I
TF Omit Expot to Excel
I T Aidematic Expat to Excel
Ip devascitvest ods
=I
I Server Director ce
[c\Audit I
I I Spreadsheets can be produced
I automatically. or the entire query
i eieeeeioeine i i I process can be automated.
‘Fun Query,
Note: By default a text output file is created as C:\Audit\test.txt. In the current
version there is also an option to export to an MS-Access database (default
name when selected: C:\Audit\test.mdb) and an Excel spreadsheet (default
name when selected: C:\Audit\test.xls).
If you want to export the retrieved message to either an Excel spreadsheet or
an Access database the enter ‘x’ in the <Automatic Export to Excel> or
<Automatic Export to Access> field. Using the template.qry file found in
d:\audit data gives the following report format:
‘Microsoft Excel [-[50d
1S) fle Edt View Inset Fomat Tools Data Window Help ld
‘Disha! gale! ¥ [elo] ol] =l6I gin islelaI f= = _lneI
— ra 2 af 2] a) =lslaley @]) sala 2 Of we]
ro
a (RAT BADD NOS 2 Pa TRS A ES PT a a
A fRiposte Message Query (UIQN I
zone ceoiar \
[atime 122610 PM I
rae i I
fs] iI
[i [seit Srist(togo) I
keys key <atibuie Dato><Type Date> <OrderFaward>> E I
[a] ‘kay Abt Tene>Type Sing><OrGerForard>>
fa ;
[30] ate Time Logon
i BMarar "152600 SETUPOI
[12] 2aMarS7 07 08-58 SETUPOI
fa demars? 1038.20 SETUPOI
[a4] 24Mac97__ 10.4801 BBANTI
15 deniers? 122018 BRANT!
[36] demars? 130701 BBANT
a7) Senaro? 135905 BEANTI
(38) 2enars? 170434 BRANT!
[33] Semaes? 09 1204 BAN
[20] Sars? 08.4957 BRANT!
[21] hana? 102852 BBANTI I
ef «I >I Sheets ta
Ready z
Details of the query statement used will appear on the spreadsheet and this
provide the evidence to POIA of the search criteria used, in other words, how
their RFI has been interpreted.
COMMERCIAL IN CONFIDENCE
Page 23 of 1
FUJ00152167
FUJ00152167
ICL Pathway Conducting Audit Data Extractions at CSR+ v Reh IA/PRO/008
‘ersion: 1.
Date:15/12/00
8.8 Running the Query
Normally you would not actually execute the retrieval scenario until such time
as you had built the query statement (Section 8.3), selected the fields
(Section 8.4) and chosen the output medium (Section 8.7). However, at any
time in this sequence you can run the query statement by selecting <Run
Query> using the button on the “Connection” tab screen.
Enter the required Post Office (FAD) code into the <Group ID:> field if it is not
shown.
Once this has been done an intermediate screen will be displayed, allowing
the file format to be confirmed — select the <Excel> or <Access> buttons or
the “text” icon, as appropriate to commence loading the package and
complete the data transfer. This will also allow the data format the be
checked on-screen.
Note: In the case of very large Correspondence Server files spanning a
number of days, an error may be generated on trying to save an Excel file.
This will be because the maximum number of rows (records) has been
exceeded. Should this occur, the range of dates should be covered, say one
or two days at a time, and a number of output files generated.
In rare cases it will theoretically be possible to produce a text output file that
is too big to be read by Wordpad. Should this occur, a possible response is to
produce output files for a smaller range of dates, or to initially create data as
an Excel working file which you can ‘Save As’ “Text, OS/2 or MS-DOS”.
It is good practice to check that all output files can be opened before they are
copied to floppy disk or CD-W for onward transmission.
COMMERCIAL IN CONFIDENCE Page 24 of 1
FUJ00152167
FUJ00152167
ICL Pathway Conducting Audit Data Extractions at CSR+ v Reh IA/PRO/008
‘ersion: 1.
Date:15/12/00
A Annexes
A Example RFI form — for POCL contact use
B Example OCP form — tape reload request (page for data extraction
user to complete)
Cc Example OCP form — Correspondence Server rebuild (ditto)
(Three unnumbered pages follow)
COMMERCIAL IN CONFIDENCE Page 25 of 1