720-913-5000 auditor@denvergov.org

Audit Report

Download PDF

Public Works Asset Management Software Applications

Objective: To evaluate the design and the operating effectiveness of the information technology general controls, specific application controls, and financial reporting for infrastructure assets tracked in the Cartegraph and dTIMS asset management systems. Also, to assess the shared responsibility between the Department of Public Works and the city’s Technology Services agency to define and implement information technology general controls.

The Department of Public Works uses asset management application software to track and report on the city’s $2.8 billion investment in streets, alleys, traffic signals, sewers, and other related infrastructure assets it manages. The city’s Technology Services agency provides technical support services to keep these applications fully functional.

The Lack of Formal Citywide Policies for Information Technology Processes to Guide Agency Procedures Creates Cybersecurity and Operational Risks

  • Technology Services does not have the explicit authority to create and enforce citywide information technology policies.
  • The lack of documented information technology controls places the Department of Public Works’ infrastructure asset data at risk.
  • The lack of a formal citywide best practice for spreadsheet controls results in errors in Public Works’ fixed asset accounting and reporting data.

Poor Vendor Oversight Puts Asset Management Data at Risk

  • There is no effective review of cloud-based vendors’ information technology general controls.
  • Public Works did not obtain and review vendors’ insurance coverage for dTIMS and Cartegraph.

The City’s Contracting Process Failed to Include Technology Services’ Required Review and Approval of a Technology Purchase

  • Technology Services did not review the Cartegraph contract, which resulted in the city purchasing unneeded licenses.
  • The Cartegraph contract did not clearly specify who is responsible for backup of city data in line with best practices.

Technology Services Should Expand its Existing Business Relationship Management Program to Include the Public Works Department to Provide Better Customer Service

  • Technology Services failed to respond to requests from Public Works to ensure appropriate security processes were established.
  • Technology Services has not communicated the process for updating citywide geographic information system data.

The Public Works Department Does Not Have a Formal Strategy for Asset Management

Public Works identified the need to expand its asset management plan, but it lacks a comprehensive asset management plan.

1.1 Update Necessary Laws – The Technology Services agency should have the necessary laws updated as soon as possible to enable the agency to establish and enforce standardized citywide information technology policies.

Agency Response: Agree, Implementation Date – April 15, 2020

1.2 Create Information Technology Policies – When the laws are updated as noted in Recommendation 1.1, the Technology Services agency should create citywide information technology policies and establish the means to ensure all city agencies are complying with these policies.

Agency Response: Agree, Implementation Date – July 15, 2020

1.3 Develop User Access Process – The Department of Public Works, with guidance from the Technology Services agency, should develop a formal process as soon as possible to grant, change, and remove user system access.

Agency Response: Agree, Implementation Date – June 30, 2020

1.4 Develop Administrator User Review Process – The Department of Public Works, with guidance from the Technology Services agency, should develop a formal process as soon as possible for approving administrator user accounts.

Agency Response: Agree, Implementation Date – June 30, 2020

1.5 Develop an Access Removal Process – The Department of Public Works should develop a process to notify Workday administrators to remove user access for all users, including unpaid interns, when their employment or internship is completed.

Agency Response: Agree, Implementation Date – June 30, 2020

1.6 Develop User Access Review – The Department of Public Works, working with the Technology Services agency, should develop a process as soon as possible to establish periodic user access review listings to ensure user access remains appropriate for all applications and that appropriate segregation of duties is maintained.

Agency Response: Agree, Implementation Date – June 30, 2020

1.7 Develop Succession Plan – The Department of Public Works, with guidance from the Technology Services agency, should ensure there is a succession plan as soon as possible for all system administrator roles.

Agency Response: Agree, Implementation Date – March 31, 2020

1.8 Create Spreadsheet Best Practice – When the laws are updated as noted in Recommendation 1.1, the Technology Services agency should establish standard citywide best practices for key spreadsheet controls that address the following areas:

  1. Change controls – develop controls to highlight changes made to the spreadsheet calculations or reporting
  2. Version control – set up automated version control of all files when they are updated to allow tracking of changes made
  3. Access control – restrict users’ access to the folders where the critical files are stored and set up password protection of individual files
  4. Input controls – set up “checksum” totals to confirm the accuracy of data entered, and lock cells with formulas to prevent them from being accidentally changed
  5. Documentation – create documentation for each spreadsheet to describe its purpose, methodology, source of data, and outputs
  6. Backups – ensure folders where spreadsheets are stored are regularly backed up to a different location

Agency Response: Agree, Implementation Date – April 15, 2020

1.9 Implement Spreadsheet Controls – Working with the Technology Services agency, the Department of Public Works should, as soon as possible, move to a solution with appropriate information technology controls or implement spreadsheet control procedures that address the following areas:

  1. Change controls – develop controls to highlight changes made to the spreadsheet calculations or reporting
  2. Version control – set up automated version control of all files when they are updated to allow tracking of changes made
  3. Access control – restrict users’ access to the folders where the critical files are stored and set up password protection of individual files
  4. Input controls – set up “checksum” totals to confirm the accuracy of data entered, and lock cells with formulas to prevent them from being accidentally changed
  5. Documentation – create documentation for each spreadsheet to describe its purpose, methodology, source of data, and outputs
  6. Backups – ensure folders where spreadsheets are stored are regularly backed up to a different location

Agency Response: Agree, Implementation Date – 180 days after completion of Recommendation 1.8

2.1 Implement Vendor Management System – The Technology Services agency should continue to implement the ServiceNow Vendor Management module to fully document the review process and schedule recurring reviews for System and Organization Controls for Service Organizations, or SOC, reports. The agency should follow up with the vendor on control gaps identified in the report.

Agency Response: Agree, Implementation Date – Dec. 31, 2020

2.2 Implement Periodic Cloud-Based Vendor Security Reviews – The Technology Services agency should implement a process to review cloud-based vendors’ ongoing adherence to the Cloud Security Alliance security controls. If gaps in the vendors’ security controls are identified, Technology Services should implement sufficient additional controls to mitigate the lack of security or decommission the noncompliant vendor service until the security issues can be adequately addressed.

Agency Response: Agree, Implementation Date – Dec. 31, 2020

2.3 Obtain System and Organization Controls for Service Organizations Reports – The Department of Public Works should obtain its vendors’ System and Organization Controls for Service Organizations, or SOC, reports or other attestation documentation and review to determine whether backups are scheduled and tested on a periodic basis. The department should follow up with the vendor on control gaps identified in the report and mitigate any risks identified.

Agency Response: Agree, Implementation Date – Oct. 30, 2020

2.4 Review Vendor Disaster Recovery Controls – The Department of Public Works should obtain the vendor System and Organization Controls for Service Organizations, or SOC, reports or other attestation documentation and review the disaster recovery information contained in these reports to ensure the vendors’ disaster recovery processes meet Public Works’ needs. The department should follow up with the vendor on control gaps identified in the report and mitigate any risks identified.

Agency Response: Agree, Implementation Date – Oct. 30, 2020

2.5 Review Vendor Change Management Controls – The Department of Public Works should obtain the vendors’ System and Organization Controls for Service Organizations, or SOC, reports or other attestation documentation and review the change management information contained in these reports to ensure the vendors’ change management controls meets Public Works’ needs. The department should follow up with the vendors on control gaps identified in the report and mitigate any risks identified.

Agency Response: Agree, Implementation Date – Oct. 30, 2020

2.6 Implement Complementary User Controls – The Department of Public Works should ensure it evaluates the complementary user controls as identified in each vendor’s System and Organization Controls for Service Organizations, or SOC, report and implements those controls that are feasible.

Agency Response: Agree, Implementation Date – 180 days after receipt of SOC reports

2.7 Review Insurance Coverage – The Department of Public Works should develop a process to receive, track, and review all insurance coverage certificates from technology vendors to ensure they are maintaining compliance with the city’s insurance requirements.

Agency Response: Agree, Implementation Date – June 30, 2020

3.1 Improve Contracting Process – The Technology Services agency should work with all parties involved in the contracting process to improve the contract routing and approval process to ensure Technology Services is included in all technology purchases.

Agency Response: Agree, Implementation Date – July 15, 2020

3.2 Clarify Contract Language – The Department of Public Works should clarify the Cartegraph contract language regarding the responsibility for performing backups and for how frequently those backups should occur.

Agency Response: Agree, Implementation Date – Oct. 30, 2020

4.1 Improve Customer Service – The Technology Services agency should improve its customer service for the Department of Public Works’ technology issues.

Agency Response: Agree, Implementation Date – Immediately

4.2 Establish Service-Level Agreements – The Technology Services agency should establish and communicate a standard process with expected response times and escalation path for handling customer requests and disagreements.

Agency Response: Agree, Implementation Date – March 31, 2020

4.3 Improve dTIMS Password Settings – The Technology Services agency should work with the Department of Public Works to ensure dTIMS meets password security requirements as soon as possible by integrating dTIMS into the city’s active directory or ensuring that a vendor-provided solution meets Technology Services’ requirements.

Agency Response: Agree, Implementation Date – March 15, 2020

4.4 Communicate Update Process – The Technology Services agency should, as soon as possible, communicate its process to the Department of Public Works for updating the city’s geographic information system database.

Agency Response: Agree, Implementation Date – Jan. 31, 2020

5.1 Formalize Asset Management Strategy – The Department of Public Works should continue its efforts to develop an asset management strategy by formalizing its asset management approach and:

  • Developing a comprehensive charter;
  • Creating specific deliverables and objectives;
  • Identifying a business case and requirements; and
  • Conducting an analysis of stakeholder needs.

Agency Response: Agree, Implementation Date – Sept. 30, 2020

5.2 Consult with the Chief Data Officer – The Department of Public Works should continue its efforts to develop an asset management strategy by consulting with the chief data officer to leverage their experience in developing data standards to ensure consistency between the various asset management platforms.

Agency Response: Agree, Implementation Date – Ongoing

5.3 Engage Technology Services – The Department of Public Works should continue its efforts to develop an asset management strategy by engaging the Technology Services agency as soon as possible to assist with the asset management initiative to ensure information technology and project management best practices are followed.

Agency Response: Agree, Implementation Date – Sept. 30, 2020

The objective of our audit of the Department of Public Works’ asset management software applications was to determine whether the information technology general controls are effective for two of the asset management software applications managed by Public Works and the city’s Technology Services agency. I am pleased to present the results of this audit.

The audit revealed the city needs to establish citywide policies for information technology general controls and spreadsheet controls and both Public Works and Technology Services need to improve their oversight of cloud-based vendors. The controls over information technology contracts need strengthening, and Technology Services needs to improve its customer service. Additionally, Public Works needs to formalize its strategy for managing its infrastructure assets.

Creating standardized citywide information technology policies, improving the contracting processes for technology purchases, and strengthening vendor oversight will create a stronger cybersecurity approach for protecting the city’s data. Developing a best practice to establish spreadsheet controls will protect critical financial and operational data. Improving the way Technology Services supports other city agencies will lower costs and help streamline overall operations. Finally, formalizing Public Works’ infrastructure asset management will prevent wasted efforts and provide leadership with meaningful data to improve services provided to Denver’s residents. Our report lists several related recommendations.

This performance audit is authorized pursuant to the City and County of Denver Charter, Article V, Part 2, Section 1, “General Powers and Duties of Auditor,” and was conducted in accordance with generally accepted government auditing standards. Those standards require we plan and perform the audit to obtain sufficient, appropriate evidence to provide a reasonable basis for our findings and conclusions based on our audit objectives. We believe the evidence obtained provides a reasonable basis for our findings and conclusions based on our audit objectives.

We extend our appreciation to the Public Works and Technology Services personnel who assisted and cooperated with us during the audit. For any questions, please feel free to contact me at 720-913-5000.

Follow-up report

A follow-up report is forthcoming. 

Audit Team: Dawn Wiseman, Kevin Sear, Brenda Berlin, Karin Doughty, Joe Ebiziem