FUJ00152176 - Conducting Audit Data Extractions at Live - ICL Pathway Ltd - v2.0
FUJ00152176
FUJ00152176
ICL Pathway Ltd CONDUCTING AUDIT DATA EXTRACTIONS AT Ref: TA/PRO/003
LIVE
Version: 2.0
COMMERCIAL IN_CONFIDENCE Date: 27-Nov-01
Document Title: Conducting Audit Data Extractions at Live
Document Type: Process
Release: N/A
Abstract: This document describes the process to be followed by Consignia
Group Internal Audit (CGIA), and other groups external to Pathway
as defined in Schedule A03, when requesting audit data extraction
services from ICL Pathway CS Security. 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.
Document Status: APPROVED
Originator & Dept: Jan Holmes/ Jane Bailey
Contributors: Brian Mooney/Anthony Brown
Reviewed By: Jan Holmes, Richard Laking, Graham Hooper, Chris Billings
Comments By:
Comments To: Jane Bailey (& Pathway Document Controller)
(Note: Non Contract Controlled Documents — Originator Only, Contract
Controlled Document — Originator & Pathway Document Controller)
Distribution: ICL Pathway Document Management
Richard Laking Martin Riddel
Rashpal Dhesi (CGIA) Graham Hooper
Chris Billings Graham Ward (CGIA)
Library
© 2001 ICL Pathway Ltd COMMERCIAL IN-CONFIDENCE Page: 1 of 29
FUJ00152176
FUJ00152176
ICL Pathway Ltd CONDUCTING AUDIT DATA EXTRACTIONS AT Ref: TA/PRO/003
LIVE
Version: 2.0
COMMERCIAL IN_CONFIDENCE Date: 27-Nov-01
0 Document Control
0.1 Document History
\Version No. Date Reason for Issue Associated
CP/PinICL
0.1 12/04/00 Initial draft based on CSR version IA/PRO/002
0.2 18/04/00 Following walk through on Audit Workstation and R}
uery user guide
03 21/07/00 IReview by Brian Mooney.
1.0 15/12/00 ersion 1.0 for approval
1.1 08/09/01 [Update to reflect organisational changes
2.0 27/11/01 ersion 2.0 for approval
0.2 Approval Authorities
Name Position Signature Date
Jan Holmes Audit Manager
Graham Hooper Security Manager
0.3. Associated Documents
Reference ersion [Date Title Source
IPA/TEM/001 ICL Pathway Document Template PVCS
I[A/MAN/005 Horizon System Audit Manual (CSR+) WAY
[[A/REQ/004 Audit Data Retrieval RequirementsPWAY
CSR+)
IA/SPE/18 Audit Data Catalogue - ADC (ConsigniaI
SIS)
I[A/SPE/19 Audit Data Catalogue (Consignia AP)
‘lients)
I[A/SPE/20 Audit Data Catalogue (System
lanagement)
I[A/SPE/21 Audit Data Catalogue (Internal Audit)
IRS/MAN/010 ecureID Normal Token User Guide PWAY
Unless a specific version is referred to above, reference should be made to the current
approved versions of the documents.
© 2001 ICL Pathway Ltd COMMERCIAL IN-CONFIDENCE Page: 2 of 29
ICL Pathway Ltd
CONDUCTING AUDIT DATA EXTRACTIONS AT Ref:
LIVE
Version:
COMMERCIAL IN_CONFIDENCE Date:
FUJ00152176
FUJ00152176
TA/PRO/003
2.0
27-Nov-01
0.4 Abbreviations/Definitions
Abbreviation Definition
JAS Audit Server
JAW IAudit Workstation
IAWO Audit Workstation Operator
(CD-W IWriteable CD
DLT [Digital Linear Tape
IFTMS File Transfer Management System
BCS IOrder Book Control System
IPA Pathway Auditor
PIN Personal Identification Number
PLUI Pathway Legato User Interface
ICGIA. \Consignia Group Internal Audit
IPWAY ICL Pathway
IRFI [Request for Information
SLU Standard Legato User Interface
[TMS [Transaction Management System
0.5 Changes in this Version
Version
Changes
0.6 Changes Expected
Changes
© 2001 ICL Pathway Ltd
COMMERCIAL IN-CONFIDENCE
Page: 3 of 29
FUJ00152176
FUJ00152176
ICL Pathway Ltd CONDUCTING AUP ee EXTRACTIONS AT Ref: TA/PRO/003
Version: 2.0
COMMERCIAL IN_CONFIDENCE Date: 27-Nov-01
0.6 Table of content
I Introduction. 6
2 Scope 6
3 Terminology 7
4 Audit Data Integrity 8
5 Retrieval Schematic. 9
6 Overview 10
6.1 Request For Information. 10
6.2. Marking Files and Tapes. 10
6.3 Audit Track Retriever. at
6.4 Audit Data Check Seal. iW
6.5 Audit Trail Extractor. iW
7 Retrieving & Extracting Audit Data 12
7.1 Receiving the RFL 12
7.2 Interpreting the RFI 12
7.3. Login Audit Workstation. 13
7.4 Preliminary Housekeeping 13
7.5 Cluster Determinant 13
7.6 Targeting the Data Files 13
7.7 Using the Pathway Legato User Interface. 14
7.8 Using the Standard Legato User Interface 15
7.9 Targeting the DLTs. 16
7.10 Reformatting Retrieved Data 16
7.10.1 Reformatting TMS Journals. 17
7.10.2 Oracle Archive Tables 17
7.11 Checking the Seals. 18
7.12 Despatch of Audit Data 18
8 Introduction to R-Query 20
8.1 Invoking R-Query and Connecting to a Correspondence Server. 20
8.2 Restoring Retrieval Scenarios 21
8.3 Changing Retrieval Parameters. 22
8.4 Selecting TMS Fields for Display. 23
8.5 Order By Tab. 24
8.6 Groups Tab 24
© 2001 ICL Pathway Ltd COMMERCIAL IN-CONFIDENCE Page: 4 of 29
FUJ00152176
FUJ00152176
ICL Pathway Ltd CONDUCTING AUDIT DATA EXTRACTIONS AT Ref: TA/PRO/003
LIVE
Version: 2.0
COMMERCIAL IN_CONFIDENCE Date: 27-Nov-01
Select Output Medium 25
8.8 Running the Query. 26
A Annexes 27
A. Example (RFI) REQUEST FOR INFORMATION form 28
B. Example OCP form 29
© 2001 ICL Pathway Ltd COMMERCIAL IN-CONFIDENCE Page: 5 of 29
FUJ00152176
FUJ00152176
ICL Pathway Ltd CONDUCTING AUDIT DATA EXTRACTIONS AT Ref: TA/PRO/003
LIVE
Version: 2.0
COMMERCIAL IN_CONFIDENCE Date: 27-Nov-01
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 CGIA 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.
© 2001 ICL Pathway Ltd COMMERCIAL IN-CONFIDENCE Page: 6 of 29
ICL Pathway Ltd
FUJ00152176
FUJ00152176
CONDUCTING AUDIT DATA EXTRACTIONS AT Ref: TA/PRO/003
LIVE
Version: 2.0
COMMERCIAL IN_CONFIDENCE Date: 27-Nov-01
3 Terminology
Within this process certain terms are used which have specific meaning within the
Horizon Audit Solution. They are:
Gatherer :
Sealer
Hoarder
Retriever :
Extractor :
Legato
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.
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.
The module responsible for writing audit data files onto DLT at pre-
defined intervals
The module responsible for retrieving audit data from the buffer file
where it is placed by Legato when requested by the Audit Workstation.
Retriever brings back complete files or groups of files from the DLTs.
Further work may be required to filter out unwanted information,
especially true of the TMS files, using a number of tools available on
the Audit Workstation.
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].
© 2001 ICL Pathway Ltd COMMERCIAL IN-CONFIDENCE Page: 7 of 29
ICL Pathway Ltd
FUJ00152176
FUJ00152176
CONDUCTING AUDIT DATA EXTRACTIONS AT Ref: TA/PRO/003
LIVE
Version: 2.0
COMMERCIAL IN_CONFIDENCE Date: 27-Nov-01
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 Consignia Group 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 RFIs,
authorised by nominated persons within CGIA. This log can be scrutinised on
the AW.
Extractions will only be made by individuals previously notified to CGIA.
Currently this is limited to Pathway Audit and Pathway CS Security personnel.
Any additions will be notified to CGIA.
Agreement has been reached with CGIA 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.
© 2001 ICL Pathway Ltd COMMERCIAL IN-CONFIDENCE Page: 8 of 29
ICL Pathway Ltd
CONDUCTING AUDIT DATA EXTRACTIONS AT Ref:
TA/PRO/003
LIVE
Version: 2.0
COMMERCIAL IN_CONFIDENCE Date: 27-Nov-01
5 Retrieval Schematic
AUDIT AUDIT
LEGATO SERVER WORKSTATION
iow hed
——l
Tan
i 1@@"
I I aor ruecopy I], § Notepaa O ayp
T™S CD-ROM Consignia Group
ORACLE
EBUILD meee
wera
Retrieve SJ -~ ive databases
“tapes
Identify. LEGATO USER] Identify & Mark
“conrroe I[" Tees inrenrace II Reaured ies oe OUNTER Paway nt] Request Fox
© 2001 ICL Pathway Ltd COMMERCIAL IN-CONFIDENCE Page: 9 of 29
FUJ00152176
FUJ00152176
FUJ00152176
FUJ00152176
ICL Pathway Ltd CONDUCTING AUDIT DATA EXTRACTIONS AT Ref: TA/PRO/003
LIVE
Version: 2.0
COMMERCIAL IN_CONFIDENCE Date: 27-Nov-01
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 four 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.
d. AP Client Files
The process is invoked through the receipt of an RFI into Pathway CS Security.
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 CS Security.
6.1 Request For Information
All CGIA 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 CS Security 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 a PinICL on the ‘Dataextraction’ stack for CS Security.
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.
© 2001 ICL Pathway Ltd COMMERCIAL IN-CONFIDENCE Page: 10 of 29
FUJ00152176
FUJ00152176
ICL Pathway Ltd CONDUCTING AUDIT DATA EXTRACTIONS AT Ref: TA/PRO/003
LIVE
Version: 2.0
COMMERCIAL IN_CONFIDENCE Date: 27-Nov-01
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.
© 2001 ICL Pathway Ltd COMMERCIAL IN-CONFIDENCE Page: 11 of 29
FUJ00152176
FUJ00152176
ICL Pathway Ltd CONDUCTING AUDIT DATA EXTRACTIONS AT Ref: TA/PRO/003
LIVE
Version: 2.0
COMMERCIAL IN_CONFIDENCE Date: 27-Nov-01
7 Retrieving & Extracting Audit Data
7.1 Receiving the RFI
a) All CGIA requests for audit data extractions must come to Pathway CS Security 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.
RFIs will only be accepted from the following named individual :
Graham Ward : CG Internal Audit : Tel
or one named delegate, to be confirmed in writing by CGIA Internal Audit.
If other parts of the Post Office, or other organisations, require audit data extractions
they must be channelled through CGIA to Pathway CS Security at Feltham.
Contractual turnaround times for the provision of data apply.
b) Internal requests are recorded on the Data extraction Spreadsheet. 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 be in the form of a PinICL, 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 SO0kB) 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.
© 2001 ICL Pathway Ltd COMMERCIAL IN-CONFIDENCE Page: 12 of 29
FUJ00152176
FUJ00152176
ICL Pathway Ltd CONDUCTING AUDIT DATA EXTRACTIONS AT Ref: TA/PRO/003
LIVE
Version: 2.0
COMMERCIAL IN_CONFIDENCE Date: 27-Nov-01
7.3 Login Audit Workstation
Carry out following procedure to Login and obtain necessary shares
1. Login 1 RRR
2. Password; he
3. Domain :PWYDCS
At this point the SecureID 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 pull up <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 :
Ll. Select <Windows_NT_Explorer> from the drop down menu.
2. Set up <New Folder> as D:\audit data\RFI Reference No.
7.5 Cluster 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.
Access the Secure Id Admin workstation and use Tivoli Event Console which links in
to the oracle database, to identify the cluster id of a particular FAD.
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 PLUI 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.
© 2001 ICL Pathway Ltd COMMERCIAL IN-CONFIDENCE Page: 13 of 29
ICL Pathway Ltd
FUJ00152176
FUJ00152176
CONDUCTING AUDIT DATA EXTRACTIONS AT Ref: TA/PRO/003
LIVE
Version: 2.0
COMMERCIAL IN_CONFIDENCE Date: 27-Nov-01
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.
1.
ae ey
6.
10.
Select <Audit ExtractorClient.CMD> from the main program menu.
At dialogue SELECT DATA CENTRE select <Data Centre> required.
At dialogue AUDIT EXTRACTOR select <Request> from title menu.
Select <New> if new RFI
Complete selection fields :
<Requester> Mandatory From drop down menu.
<Date Received> Mandatory Date RFI received in Pathway.
<Date Required> Mandatory Date data required by requester.
<Catalogue Entry> OptionalEnter search criteria into Search Catalogue
<Receipt Reference> Mandatory Original RFI reference
<Access Reason> Mandatory Reason for running retrievals
Select <Specifyy Selection Criteria>.
Complete remaining selection fields :
Time Period required <From date> Mandatory Start date of retrieval
<To date> OptionalEnd date of retrieval (assumes today)
File Source required
<Legato Server> Select from drop down Wigan/Bootle
<Tape Pool> Optional
<Filename Template> — Optional
<Update button>
<Audit Point> Optional
<Audit S Point>Optional
<PO FAD> OptionalFAD code for retrieval
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
© 2001 ICL Pathway Ltd COMMERCIAL IN-CONFIDENCE Page: 14 of 29
ICL Pathway Ltd
FUJ00152176
FUJ00152176
CONDUCTING AUDIT DATA EXTRACTIONS AT Ref: TA/PRO/003
LIVE
Version: 2.0
COMMERCIAL IN_CONFIDENCE Date: 27-Nov-01
1.
12.
<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.
7.8 Using the Standard Legato User Interface
The RFI must be registered on the RFI database before commencing the retrieval
activity.
vr en
6.
7.
Select <Audit ExtractorClient.CMD> from the main program menu.
At dialogue SELECT DATA CENTRE select <Data Centre> required.
At dialogue AUDIT EXTRACTOR select <Request> from title menu.
Select <New> if new RFI
Complete selection fields :
<Requester> Mandatory
<Date Received> Mandatory
<Date Required> Mandatory
<Catalogue Entry>
<Receipt Reference>
<Access Reason>
Select <Return to Menu>.
<Exit>
From drop down menu.
Date RFI received in Pathway.
Date data required by requester.
OptionalEnter search criteria into Search Catalogue
Mandatory Original RFI reference
Mandatory Reason for running retrievals
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 I 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.
wo
4
lL.
Select <Legato_Client_Bootle.CMD> from main program menu
. Select <Directed Recovery> from <Operations> drop down menu
. Confirm <mboarc01> as Source Client in dialogue. <OK>
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 Client Wigan CMD> should be
selected and <mwiarc01> used to confirm Source and Destination dialogues.
[5
Select <Change Browse Time> from View drop down menu
© 2001 ICL Pathway Ltd
COMMERCIAL IN-CONFIDENCE Page: 15 of 29
FUJ00152176
FUJ00152176
ICL Pathway Ltd CONDUCTING AUDIT DATA EXTRACTIONS AT Ref: TA/PRO/003
LIVE
Version: 2.0
COMMERCIAL IN_CONFIDENCE Date: 27-Nov-01
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 <v> 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
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, NonTMS18Mnth and NON
TMS7Yr tape pools.
In order to achieve next day delivery OSD must be notified before 1200hrs.
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.
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.
© 2001 ICL Pathway Ltd COMMERCIAL IN-CONFIDENCE Page: 16 of 29
FUJ00152176
FUJ00152176
ICL Pathway Ltd CONDUCTING AUDIT DATA EXTRACTIONS AT Ref: TA/PRO/003
LIVE
Version: 2.0
COMMERCIAL IN_CONFIDENCE Date: 27-Nov-01
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.
The utility is evoked with the use of the Pathway Audit Extractor GUI
1. Select <Audit Extractor Client> from the programs menu
2. Select <Message store>, <Reset Message Store>
When it has reset successfully
3. Select <Message store>, <Generate Message Store>
4. Enter start date for messages
End date for messages
PO FAD
5. Generate Message Store
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%.
1. 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 datatiles,
Unzipped file will be placed into new Directory
a)
Open unzipped files using the <Wordpad.CMD> utility from main program menu
7.10.2 Oracle Archive Tables
These are stored in text format.
© 2001 ICL Pathway Ltd COMMERCIAL IN-CONFIDENCE Page: 17 of 29
FUJ00152176
FUJ00152176
ICL Pathway Ltd CONDUCTING AUDIT DATA EXTRACTIONS AT Ref: TA/PRO/003
LIVE
Version: 2.0
COMMERCIAL IN_CONFIDENCE Date: 27-Nov-01
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.
1. Select <Microsoft_Access.CMD> from main program menu.
2. Using File/Open Database... open the share’d ‘Audit_Seal_DB.mdb’ database that exists on the
mapped drive ’AS_db on ‘mboarc01’ or mwiarc01”
3. A list of 4 database ‘tables’ will be displayed.
4. Position the mouse cursor on the <QUERIES> tab and click.
5. A list of 2 database ‘queries’ will be displayed.
6. Double click on the <Seals Match Check — Normal> icon.
7. 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 CGIA 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 Spreadsheet must be updated to record the date that the
extraction activity was completed.
I. Select <CD_Writer_Software.CMD> from main program menu
2. Maximise dialogue box
3.
Select files required in top dialogue box
4. Drag & drop to bottom dialogue box
© 2001 ICL Pathway Ltd COMMERCIAL IN-CONFIDENCE Page: 18 of 29
FUJ00152176
FUJ00152176
ICL Pathway Ltd CONDUCTING AUDIT DATA EXTRACTIONS AT Ref: TA/PRO/003
LIVE
Version: 2.0
COMMERCIAL IN_CONFIDENCE Date: 27-Nov-01
5. When complete select RED dot <Red>
16. Create ‘Closed’ CD
7. Save layout as RFI_id
The media is despatched to the CGIA 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.
© 2001 ICL Pathway Ltd COMMERCIAL IN-CONFIDENCE Page: 19 of 29
FUJ00152176
FUJ00152176
ICL Pathway Ltd CONDUCTING AUDIT DATA EXTRACTIONS AT Ref: TA/PRO/003
LIVE
Version: 2.0
COMMERCIAL IN_CONFIDENCE Date: 27-Nov-01
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
cece] we [owe [ee [ome] we I om
Conn to Message Store on.
Ripodte dens
Suggastions for enhancements or Bug fixes to Brian Orzel at Pathway.
1. 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].
wr yes
You will be automatically transferred to the <Save and Restore> Tab
© 2001 ICL Pathway Ltd COMMERCIAL IN-CONFIDENCE Page: 20 of 29
ICL Pathway Ltd
LIVE
COMMERCIAL IN_CONFIDENCE
CONDUCTING AUDIT DATA EXTRACTIONS AT Ref:
FUJ00152176
FUJ00152176
TA/PRO/003
2.0
27-Nov-01
8.2 Restoring Retrieval Scenarios
The <Save and Restore> dialogue provides the opportunity to restore scenarios that
have already been scripted for further use.
‘5. Riposte Query (UK} V3.3. Connected to Server MWIARCO! (Group-0 Node-41}
sce I wm [ow [et [ame] oe I om
Saving and restoring quses using LOCAL dk les
{Save and Retieve Quevies using Message Store
: fe\audittestay a
Se raion I cones he a
jie I
Fotesh
Queries can be in the Riposte ; Clsboad
store, or in disk files. tn I Pane I GiouiD for Fiposte
osaeae II
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
ifa new CS is built for another retrieval exercise.
1. Go to <Message Store> window.
3._ Highlight the required scenario and select <Retrieve Query>.
Use these steps to re-use scenarios associated with current Correspondence Server.
2. Select <Refresh> to list all scenarios associated with the current Correspondence Server.
OR
Use this step if retrieving scenarios from the Catalogue.
<Change filename> to browse as required.
1. Locate stored scenario from the Catalogue via the <Retrieve Query from File> button using the
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.
8.3 Changing Retrieval Parameters
© 2001 ICL Pathway Ltd COMMERCIAL IN-CONFIDENCE
Page: 21 of 29
FUJ00152176
FUJ00152176
‘=. Riposte Query (UK) V3.3 Connected sec MWIARCO! (Group:0 Node:41) 3]
ICL Pathway Ltd i CaaS IE a oy I Sgead af
an
‘Add Atte
Eiineies Group 1D:
Messages will only be used if they match those criteria. See the documentation for
esculenta (Note for UK: a Group = Post Office FAD code)
Fun Quey
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).
© 2001 ICL Pathway Ltd COMMERCIAL IN-CONFIDENCE Page: 22 of 29
FUJ00152176
FUJ00152176
ICL Pathway Ltd CONDUCTING AUDIT DATA EXTRACTIONS AT Ref: TA/PRO/003
LIVE
Version: 2.0
COMMERCIAL IN_CONFIDENCE Date: 27-Nov-01
8.4 Selecting TMS Fields for Display
T Export Erte Mescapes
7 Add CAP and BP Rollover Data
Select the columns for your report. tf desired. can also include the entire
contents of all matching records in the spreadsheet. (Double click to edit)
Fun gue
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.
© 2001 ICL Pathway Ltd COMMERCIAL IN-CONFIDENCE Page: 23 of 29
ICL Pathway Ltd
Version:
CONDUCTING AUDIT DATA EXTRACTIONS AT Ref:
LIVE
COMMERCIAL IN_CONFIDENCE Date:
FUJ00152176
FUJ00152176
TA/PRO/003
2.0
27-Nov-01
8.5 Order By Tab
Selecting the parameter “Sort By Date” is recommended to ensure ascending time
sequence (where appropriate).
‘=. Riposte Query (UKJV3.3 Connected
jecMWIARCO! (Group: 0 Node:41) 3)
nl oe
[ee I = I
Ag >> [<Amibute TwnData Start Date><Type:Date><OrderForwarc>
Atribute TanDta Start Time><Type'String><OrderForward>
Resmove <<
Scat By Date
‘The messages in the report will be sorted in the order specified, Note that if
the report covers multiple offices. each office will produce its' own sorted
feport. There is no sorting performed across offices.
Fun Quay)
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.
ste Query (UK}V3.3 Connected to S
AARCOI (Group:0 Node:41)
suc I Where i ome I Ses! I wenn I (
A Gree Coding
Card ID: 533200330905475500 Cgindex NINO
Payment [iaiiNASSTOO — Ostngox EODDate [———
SING: tsest4 CSindexHDTraniD— fizwettze
CSindex HDPaymentID
_Seachat I Indexed search across all offices }+4000NA38647600002
(bits I Bevo] Ptr Mule Gusies
Can search a single group, specified groups. or all groups with records for a
given Card or Payment (Note for UK: a Group - Post fice FAD code)
© 2001 ICL Pathway Ltd COMMERCIAL IN-CONFIDENCE
Page: 24 of 29
ICL Pathway Ltd
INCE
[Tomtom
tone Boat Er
CONDUCTING AUDIT DATA EXTRACTIONS AT Ref:
Version:
Date:
FUJ00152176
FUJ00152176
TA/PRO/003
2.0
27-Nov-01
I Factors tum I
I om temtioseces
TF Aone Boaters
I Peseeses
F fom Tae
ony
8.7 Select Output Medium
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:
cel
Ta ter beet fone oe Bon ren Hee
‘Dlele) alale) sltielo} ole] rll gla wlelsl Fle
be oo =) wl zi ul & lela] oF I 3] a
A
ar
Bales
[Riposte Messane Query UY
Date Time Logon
23MaeS7 152402 SETUPOI
24Mar97__07.0868 SETUPOI
24-Mar97 10:38:30 SETUPOI
2t-Mac97 10.4501 BBANT
2kMac97 1220-18 BBANT?
24-Mar97__1307.01 BBANTT
2k-MarS7 1359.05 BBANT!
24MacS7_ 17.04'34 BBANT
25MarS7 08:12:04 BBANTT
25MacS7 09.43.37 BBANT
25Mac7 10.2652 BBANT!
HM Sheett {Shea 7 Sheed Shewld X Shade [Shae I «
Ready ‘Sure
PREBSEEEFESEEI
7
zon woie7
EF fim 210M
ees sei
[5
Fe fseec EsL.g00)
Tlieys kay Anite Date><Type Dsl Order Forward>
[ ‘kay Arte Tne Type Sting» Order Fenvnds>
Details of the query statement used will appear on the spreadsheet and this provide the
evidence to CGIA of the search criteria used, in other words, how their RFI has been
interpreted.
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
© 2001 ICL Pathway Ltd
COMMERCIAL IN-CONFIDENCE
Page: 25 of 29
FUJ00152176
FUJ00152176
ICL Pathway Ltd CONDUCTING AUDIT DATA EXTRACTIONS AT Ref: TA/PRO/003
LIVE
Version: 2.0
COMMERCIAL IN_CONFIDENCE Date: 27-Nov-01
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 to 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.
© 2001 ICL Pathway Ltd COMMERCIAL IN-CONFIDENCE Page: 26 of 29
FUJ00152176
FUJ00152176
ICL Pathway Ltd CONDUCTING AUDIT DATA EXTRACTIONS AT Ref: TA/PRO/003
LIVE
Version: 2.0
COMMERCIAL IN_CONFIDENCE Date: 27-Nov-01
A Annexes
A Example RFI form — for CGIA contact use
B Example OCP form — tape reload request (page for data extraction user to
complete)
(Three unnumbered pages follow)
© 2001 ICL Pathway Ltd COMMERCIAL IN-CONFIDENCE Page: 27 of 29
FUJ00152176
FUJ00152176
ICL Pathway Ltd CONDUCTING AUDIT DATA EXTRACTIONS AT Ref: TA/PRO/003
LIVE
Version: 2.0
COMMERCIAL IN_CONFIDENCE Date: 27-Nov-01
A. Example (RFI) REQUEST FOR INFORMATION form
(Originator: internal Crime Policy & Date: IDD/MM/CCYY
Standards Manager
Post Office Counters Ltd.
1" Floor, Impact House
2 Edridge Road
(CROYDON CR9 I1PJ
ITelephone: H GRO I IRef No. (originator) #H/01
Priority: rgent [Ref No. (Pathway)
Routine x
(Other
Information Requested
[Date range: [Post Office ids IFAD ***/***
ame of PO
IGeneral Description/ IA report of all transactions and events for the office for the relevant
‘ \days, including remittances received, transfers between stock units and
[Format requirements:
error notices.
IWe would like the following format for logs (in Excel format with each
category in a separate column):
[Balancing Period; Cash Accounting Period;
Session Type - i.e. Serve Customer, Reversal. Rem In etc. Transaction
0; Session Indicator; Date; Time; Stock; User ID; Transaction
[Type; Amount £p
Session Indicator is whatever way the system has of indicating that
individual transactions are linked
Specific Details: Sorted by time within days
Signed [Date IDD/MM/YY
B. Example OCP form
© 2001 ICL Pathway Ltd COMMERCIAL IN-CONFIDENCE Page: 28 of 29
FUJ00152176
FUJ00152176
ICL Pathway Ltd CONDUCTING AUDIT DATA EXTRACTIONS AT Ref: 1A/PRO/003
LIVE
Version: 2.0
COMMERCIAL IN_CONFIDENCE Date: 27-Nov-01
PATHWAY TAPE LOADING REQUEST : __TELNRnnnnn
[Requested By: **** ****** (C§ Security/ Audit)
Date Raised: dd/mm/yy
System Id: m**arc01
[REQUESTED TAPE SERIAL No(s): Tape serial nos. are:
ITMS 000 (A00000),TMS 000 (A00000), TMS 000 (A00000), TMS 000 (A00000),
ITMS 000 (A00000)
Date Required: dd/mm/yy \Required Until:
(if known) dd/mmyy provisionally
[Reason for tape load request:
To satisfy Pathway Internal Audit (PO**/00).
To be Implemented by: OSD
Technical Support Completion Agreed
DATE
Signature
(OSD Service Management Completion Agreed:
© 2001 ICL Pathway Ltd COMMERCIAL IN-CONFIDENCE Page: 29 of 29