HomeMy WebLinkAboutCS 50-07
REPORT TO
EXECUTIVE COMMITTEE
Report Number: CS 50-07
5 0 Date: Novembl9r 12, 2007
From: Gillis A. Paterson
Director, Corporate Services & Treasurer
Subject: Response to Executivl3 Committee Requests for Information with Respect
to the 2006 Audit
Recommendation:
That Report CS 50-07 of the Director, Corporate Services & Treasurer re!~arding the
Response to Executive Committee Requests for Information with Respect to the 2006
Audit be received for information.
Executive Summary: At the Executive Committee meeting of Septembl3r 10, 2007
when discussing Report CS 29-07 on the results of the 2006 Audit, several questions
were put to staff. Discussion took. place and staff were requested to provide further
information. That information can be found in the attachments to this report.
Financial Implications: None
Sustainability Implications:
implications.
This rl3port does not contain any sustainability
Background: As mentioned above this report and its attachments are in response to
questions raised at a recent meeting of the Executive Committee. Rather than rewrite
the response prepared by the respective Managers in report format it was felt most
expedient just to attach the information they prepared.
During the review and discussion of the management letter prepared by Deloitte &
Touche, a number of questions/concerns were raised with respect to the Information
Technology comments included in the letter. The responses to these
questions/concerns raised have been provided by the Manager, Information
Technology in Attachment 1.
Further during the review of the 2006 draft Financial Statements presented, questions
arose with respect to the tax write-offs, specifically the write-off related to OPG, and
general government accounts for purchased services.
neport CS 50-07
Date; November 12; 2007
Subject: Response to the Executive Committee Requests for
Information with Respect to the 2006 Audit
Page 2
51
Attachment 2 provides a summary of the budget and actuals for the General
Government category presented in the financial statements.
Attachment 3 provides a general explanation of the tax write-off account and what
makes up the balance. A brief summary of the nature of the tax write-off related to
OPG has also been provided. In addition Report CS 52-07 is also being submitted
which provides greater detail and status of the OPG tax write-off.
Attachment 4 provides the detail of what makes up the General Government Purchased
Services account and I believe is self-explanatory.
Attachments:
1. Memorandum dated October 15, 2007 from the Manager, Information
Technology
2. General Government Expenditures per Financial Statements, December 31,
2006
3. Tax Write-Offs 2134-0000-0000
4. General Government Purchased Services for 2006
Prepared By:
~~
~it i:.P ' :~
. risine Senior
Manager, Accounting Services
Approved I Endorsed By:
,x~,.~,."",.~"...... .... ~ "... "..,,_,..,_,. ':'... .,,"';;,r'o.......'....... .,,' ...."....,. ,..'..,....,. .....v.-......"."........',.."....."....."..
c._ ""_"'~~~'.-" ~ ..".."......
"!1''''''''' '"".,
~' -t? '~.,-~~"" .... . ~
" . ,~".,"." . '/",.... , "
Gillis A. Paterson
Director, Corporate Services & Treasurer
GAP:vw
Attachments
Copy: Chief Administrative Officer
52
ATTACHMENT#.L_ TO REPORT#,~tD~fn
CORPORATE SERVICES DEPARTMENT
INFORMATION TECHNOLOGY SECTION
MEMORANDUM
October 15, 2007
To:
Gillis A. Paterson
Director, Corporate SeNices & Treasurer
From:
Jon G. Storms
Manager, Information Technology
Subject:
Response to September 10, 2007 Audit Minutes
This memorandum attempts to address the questions that Members of Council put forth
regarding Information Technology (IT). The responses have been grouped by subject
matter.
Disaster Recoverv/Business Continuit,~
First, let us identify what IT has accompHshed. A fire suppressant system was installed
within the computer room in December 2005. Weekly data backup tapes are bein!)
stored offsite at the Recreation Complex, and month-end tapes are stored at Claremont.
In 2004, the City contracted Deloitte to perform a corporate study to identify the computer
systems that were deemed to be the most critical to the City. This was pha.se one of a
proposed three-phase Disaster Recovery/Business Continuity (DRBC) project. Thl3
systems were identified from the most critical to those of lesser importance. The list was
provided to Council.
IT budgeted for phase two of the DRBC project. The amount budgeted was about
$25,000. This was the same amount budgeted for phase one, and was the amount
provided to IT by Deloitte. The budget amount was approved, but when the time came to
start the project, Deloitte had tripled the project cost for phase two. Based on thl3
adjusted cost for phase two, and the projected cost of phase three, it was determined
that the project management costs alone would be approximately $170,000.
The results from phase one of the DRBC project indicated that the most critical system
within the City was the telephone system, followed by the email system. Telcorps
provided the City with a cost of $40,000 to have a refurbished NEC PBX ready for
installation should a disaster strike the computer room - the most secure and protected
room within the corporation. Councillor Pickles deemed the cost excessive and the
remaining councillors agreed. The number one critical system identified within the City
was deemed not to have a value of $40,000. That, plus the revised phase two cost from
Deloitte, ended the effort to implement a formal DRBC plan for IT.
I must fully concur with senior managemHnt that the cost to implement a corporate DRBC
plan is currently prohibitive. The cost of just developing the plan for IT was deemed to
~Qgpomm to SQPtQmbQf 101 2007 Audit MinutQg
OCtOb8f 15, 2007
Page 2
53
be too expensive. Costs for standby equipment and possible offsite agreements would
have pushed the final cost much higher.
During the previous three years, IT has identified the need for a Storage Area Network
(SAN), which has been deferred each year. However, costs have decreased at the same
time. A SAN system would replace tape backups, and provide a further measure of
DRBC. The SAN has been cut or deferred each year. The SAN will be resubmitted for
the 2008 budget. Tapes are proving undependable when attempting to recover data.
Phase one of the SAN project would have nightly backups written onto disk located
within the computer room. Weekly and monthly tape backups would still occur if only
phase one were implemented. Phase two of the project would have a second SAN
system located at the Recreation Complex. The two systems would be connected by an
existing fibre connection. Phase two would eliminate the need for weekly backups, as
the two data storage systems would virtually always be synchronized. Only the monthly
tape backups that would be stored at Claremont would still be required. Once phase one
and phase two were implemented, absolutely no data would be lost should a disaster
strike the computer room. The estimated cost for both phases is $120,000, and once
again, will be included within the 2008 IT budget submissions.
The question has previously been asked why the City cannot develop reciprocal
agreements with other municipalities to use their resources should a disaster strike. All
municipalities are in the same position as Pickering. The City has just enough resources
to accommodate its own requirements. During the night, backups are being performed;
therefore, the City's data could not be offloaded and another municipality's data loaded.
The 2007 IT budget submissions also included the following DRBC related items. All
were cut or deferred. They will be resubmitted within the 2008 budget.
Virtual software designed to eliminate two-thirds of the City's physical servers. The
software, if installed on the remaining servers, would have resulted in fewer servers
having to be restored should a disaster strike, and the remaining servers could have
been restored much quicker. The cost was about $19,000.
The O'Brien meeting room within the Recreation Complex was identified as a backup
computer site should the computer room or a section of the Civic Complex be damaged
and become unusable. The target was to have the room fully wired to allow for the quick
insertion of both servers and PCs for employee usage. The network switches recently
replaced within the Civic Complex were to be reused within the O'Brien room. The cost
to accommodate the additional electrical load for the servers and PCs was approximately
$15,000. The budget item will be resubmitted within the 2008 IT budget. The item
previously appeared within O&ES's budget.
A proposal was put forth to connect the four electrical closets within the Civic Complex
to the diesel generator, along with a number of electrical sockets used by PCs.
Currently, if a power failure occurred within the City, only certain PCs within IT would
c_,.~",^~~~.___,~<~.~~_,~~~"",,u~_~:,~,_.,.........~~,~;..:..,.;';~';;:";"..o.;,--.-~~ ""';j-~_
~~;;",~;,lIC~~""",iL "~",,":'.."' '
'., ,', ~_.', "~"',--.. '"'",~..~ ,'.~,",,.,'.~, .',"" - '.'
Response to September 10, 2007 Audit Minutes
Octobl3r 15, 2007
54
Page 3
function. The 2007 budget submission was in O&ES's budget but will be resubmitted in
IT's 2008 Capital Budget at an estimated cost of $60,000.
Computer Security
First, let it be known that there has yet to be a security breach of the network.
Suggestions and statements have been expressed in the past by both elected officials
and employees, but after investigations were completed, the supposed infractions were
people-based rather than network related.
IT has developed a very comprehensive PC Policy that incorporates a numbHr of security
issues. All employees received mandatory training on the policy within tlhe past two
years, and every two months a course is provided by IT for new staff, alon!~ with thosls
staff who require a refresher. The last refresher course was provided during the week of
September 17. The next course will be offered in November. Each new employee who
has been given access to the network is provided with a copy of the policy by Human
Resources.
One of the most fundamental requirements for computer networks to be secure involves
the cyclical changing of passwords. Within the City, password changes are forced every
30 days. The only network accounts that do not change their passwords are Members of
Council.
In 2005, IT contracted the services of a sBcurity firm to test both the internal and external
security measures in place. Attempts to remotely access the network by means of
'hacking' failed; thus, demonstrating the quality of the security systems. Tho attempt to
breach the network from within was succHssful. Although employees had just completed
the mandatory seminar involving the PC Policy, and the fact that employees were clearly
warned that passwords were not to be shared, a number of employees freely gave out
their passwords by email through a method called 'phishing.' The security firm was ablEl
to compromise the network as a result. Although IT knew the identity of the employees,
HR directed that they could not be approach for reasons of confidentiality; therefore, no
disciplinary action was taken.
The only known method of stopping successful 'phishing' attacks is by implementin9
login tokens to all users accessing the nEltwork. IT submitted a 2007 budget request o;f
approximately $50,000 to purchase these tokens. The amount was reduced to phase in
the project. In 2007, 47 tokens have been purchased, at a cost of approximately
$11,000and IT is in the process of implElmenting these; however, 47 is not enough to
protect against network attacks. Login tokens work by displaying a 4 - 8 number, which
changes every 60 seconds. Before a person can log into the network, the person must
enter a fixed password, plus the current number being displayed on the token. Even if
the password were shared, by the time thE~ other person tried to access it, the login-token
number would have changed. It needs to be stated that the bulk of computer networks
still rely on passwords to access the network, the same method as is currently beinQl
used within the City. To be 100c10 effective, each person accessing the network would
Response to September 10, 2007 Audit Minutes
October 15, 2007
Page 4
55
require a token. The current number of network accounts is approximately 320. These
tokens, less than 47purchased, will cost approximately $65,300.
Before employees are permitted to log into the network, they must acknowledge a
security message that is displayed on their monitor.
Finally, the services of Net Cyclops are employed on a monthly basis to review the log
files of the computer network firewall. Four of the main servers within the computer
rooms are further protected by Intrusion Detection Systems (IDS). Before December 31,
2007, the number of IDS protected servers will be six out of a total of 32 servers.
However, there is not a need to place IDS on the non-critical servers.
Best Practices
While the offer from P. Jesty of Deloitte is most certainly appreciated, it is not known how
IT would implement these 'best practices.' IT does not have sufficient resources to
implement non-expensive DRBC initiatives. The above statements are not to be
misconstrued. IT is fully aware of the City's financial predicament, and is also fully aware
that it now receives its fair share of the available resources. Nonetheless, it is difficult to
implement 'best practices' under such circumstance.
I believe the development of an IT Strategic Plan would be of benefit to fully discern the
requirements and expectations of the Corporation. IT attempted to develop such a plan
in both 1999 and 2000, but although approved by the Information Technology Steering
Committee (ITSC), the plans did not have the support of Council during budget
allocations. Many IT installations have such a plan, and Ajax just had one developed.
However, the cost for the consultants to develop the plan was approximately $85,000. It
resulted in, among other things, additional staff for IT. But once again, the issue of
Pickering's financial constraints must be realized.
Security Policies
It is readily acknowledged that with the exception of the PC Policy, written security
policies have not been developed. Deloitte makes reference to 'password management.'
This subject is already covered within the PC Policy. A policy regarding the
characteristics of the password is not required, as the server's operating system enforces
the password structure, that being 8 characters, etc. The PC Policy and the security-
login message clearly state that all data residing on the network is the property of the
City.
For those security issues not addressed, IT simply does not have the resources to
develop such policies. With the security policy and procedures currently in place,
perhaps the development of still more corporate polices would not be the best use of IT's
limited resources.
Server System LOQS
The log files are being captured with the files being saved to DVD and then deleted from
thA SArvers due to disk snace constraints. The files are not beina reviewed. As a areat
Response to September 10, 2007 Audit Minutes OctobHr 15, 2007
5 6 Page 5
deal of data is being captured within tht9 log files, it would be very time consuming to
review the files, and certainly not a goocl utilization of IT's limited resources. With the
automated security procedures in place, there is limited need to constantly review the
logs. Furthermore, the intent of installin!~ the software was to search for activities if we
suspected a problem, not to continually monitor the contents of the logs. With the
resources available, we simply could not do anything else. However, I would suggest
that if an additional resource were obtained, the City could make better use of it
elsewhere in IT.
Chanqe Manaqement - Application Sv!;tems
With the exception of the Corporate Customer Care Tracking System (CCCTS), and a
few smaller systems, all application systl3ms in use within the City are purchased. The
changes are fully tested by the vendors before reaching the City. The City is then sent
".exe" files that are self loading. The changes are first installed within test databases,
and in fact, are tested by City staff prior to the changes being installed in the production
databases. Changes to the CCCTS is once again fully tested within the test databasi9
environment by IT, and then by staff prior to the changes being implemented into
production. To the best of my recollection, no problems have ever been experienced
that were the result of inadequate testing.
Chanqe Manaqement - Computer Room Servers
This is where difficulties are sometime experienced. IT does not have enough servers to
create 'test' servers where new softwarH installs can be tested. Even if such servers
were available, the lack of staff presents itself again. Where problems have been
experienced, and they have mainly been associated with the Canaveral servers, IT has
quickly secured the services of specializHd consultants to rectify the problems that wem
encountered. Therefore, any problems that have been experienced have been minimized
or rectified.
Summary
While the issues identified within Oeloitte's audit are perhaps legitimate for larger IT
environments, they are not for Pickerin~l, especially if budgets and staffing levels am
considered. Council is probably unaWarE! that the City is still using PCs that schools wil!1
not accept as donations because they ana too obsolete. If the City cannot afford to fully
maintain a basic element such as the currency of pes, how can IT be expected to
positively respond to the list of concerns that have been expressed.
{;l"lC'trl 'Hif':,~"" 'j
j'" f'\\.niVltl'. #-:-6...,_
'~~':"'A~......j /',"-,-',,(11 i"i-
~\.~;~\,,__q\! 11'~ _k~~,';)dJ (j
57
GENERAL GOVERNMENT EXPENDITURES per FINANCIAL STATEMENTS
December 31, 2006
Budget Actual
Mayor, Council & Council Support 886,182 783,418
General Government 3,137,378 3,442,830
Administration 2,423,219 2,283,099
Corporate Services (1) 4,675,811 4,281,248
Municipal Building 462,036 424,911
City Property Maintenance 532,605 330,274
12,117,231 11,545,781
Tax Write-off - OPG Assessment Appeals (2) 4,238,294
PSAB - Decrease in Non-Financial Asset (229,227) } Adjustments for
PSAB - Increase in Post Employment Benefits 10,700 } Financial Statement
PSAB - Decrease in WSIB benefit Liabilities (262,567) } Presentation only
General Government per Financial Statements 12,117,231 15,302,981
(1) excludes By-law $578,582 & Animal Control $316,471 as reported under Protection to Persons & Property
instead of General Government
(2) The City of Pickering with the other two nuclear host municipalities (Clarington & Kincardine) filed an assessment
appeal in 1999 regarding OPG's nuclear facilities. The appeal was filed claiming that the assessments for these
properties are too low. MPAC increased the assessment value of these properties with the understanding
that there would still be some adjustment downward for the final assessed value. The City was required to tax on
the higher assessment value per the returned tax roll from MPAC.
Each year a portion of the City's share of taxes for the property under appeal were transferred to the Contingency
Reserve to offset the impact of the final adjustment downward when the appeal was eventually settled with OPG,
These transfers commenced in 2001 and continued each year through to 2005 for a total amount of approximately
$9.3 million at the end of 2005 in the Contingecy Reserve,
With the settlement of the appeal, the write-off represents the property tax adjustment up to and including 2006.
This expense was required to be recorded in the City's books in 2006 in conformance with Generally Accepted
Accounting Principles (GAAP), The corresponding transfer from the Contingency Reserve to the Current Fund
was also recorded to offset this expense.
. ..- -... .-....., ",.~_.~""-~~,,,..~-"',"--"""_..,~,,-,...~
,.....,'_."__..,~-M,.;......~~'I,;i_""-..1;}..'~~!!IIl!r.l!O'$!Iffl'...."_T.-
II
58
A TTACHMEI\rr # _TO REPORT # ('"~':;(').. D 7
Tax Write-ofts - 2134.0000.0000
Property owners in Ontario have the right to appeal the assessment value on their property.
Assessment appeals arise from taxpayers believing their assessment value is too high,
clerical errors by Municipal Property Assessment Corporation (MPAC) or a reduction in
assessment from demolitions due to fire.
Tax write-off expense represents the City's share of the reduction in taxes as a result of successful
assessment appeals related to Minutes of Settlement from Requests for Reconsideration to MPAC,
Assessment Review Board Decisions and Council approved S. 357's & 358'8.
Taxable Properties
240,004
Payment in Lieu Properties
(taxes on Federally, Provincially or Regionally owned property)
159,580
OPG Assessment Appeal
4,238,294 (1)
4,637,878
(1) The City of Pickering with the other two nuclear host municipalities (Clarington & Kincardine) filed an assessment
appeal in 1999 regarding OPG's nuclear facilities. The appeal was filed claiming that the assessments for these
properties are too low. MPAC increased the assessment value of these properties with the understanding
that there would still be some adjustment downward for the final assessed value. The City was required to tax on
the higher assessment value per the returned tax roll from MPAC.
Each year a portion of the City's share of taxes for the property under appeal were transferred to the Contingency
Reserve to offset the impact of the final adjustment downward when the appeal was eventually settled with OPG.
These transfers commenced in 2001 and continued each year through to 2005 for a total amount of approximately
$9.3 million at the end of 2005 in the Contingecy Resarve.
With the settlement of the appeal, the write-off represents the property tax adjustment up to and including 2006.
This expense was required to be recorded in the City's books in 2006 in conformance with Generally Accepted
Accounting Principles (GAAP). The corresponding transfer from the Contingency Reserve to the Current Fund
was also recorded to offset this expense.
~ . 1 ;,j_.tt.", #1. s ..50 "\)7
59
Continuing
Studies
Budget Actual Reserve
(see Note)
20,000 14,832
30,000 7,209
15,000 2,150 12,850
35,000 20,550 14,450
100,000 41,141
40,000 40,000
200,000 45,239 135,000
115,000 77,304
555,000 208,426 202,300
General Government Purchased Services for 2006
Strategic Planning Sessions
Unanticipated needs
Development Charge Appeal
Compensation Study
Litigation Matters
Seaton Fin Impact Assmt
Seaton - Provincial & OMS Matters
Arbitration Hearings
Purchased Services (2126.2392)
Note Quite often, consulting projects are not completed within a calendar year and may continue into future
budget years due to timing or complexity of issue involved.
As a result, a Continuing Studies Reserve was established in order that the unspent funds for an
incomplete consulting project could be carried over to the following year to allow completion of the project.
The carryover column represents an estimate of the unspent funds required to complete the project and are
transferred to the Continuing Studies Reserve. The amount then shows in the following year budget funded
by a transfer from the Continuing Studies Reserve so that there is no impact on future year tax levies.