Situation
Bank of America had acquired many small banks, and Y2K compliance turned that into a real problem. To build an inventory, the bank had sent out questionnaires asking employees, IT staff and individuals to enter what they had. The result became the system of record: an MS SQL asset database of roughly a quarter million entries of inconsistent, free-text data. Some entries were vendor part numbers, something like compaq cpq-1292349125491459ad; others were just text. Some were hardware, some software. A staff of twelve to sixteen people was chasing clarifications and vendor compliance declarations, and the backlog was large.
Objective
Document every item in the inventory as Y2K compliant or not, and document the vendor engagements and the dispensations of compliance for items that could not otherwise be verified.
Approach
To know what a messy inventory string actually was, I needed a database of the universe of possible things to match it against. I built that, then built the matching on top.
- A reference universe. I wrote tools to ingest three sources into a new normalized database: Microsoft’s hardware compatibility catalog, which shipped on the server and desktop OS install disks and covered a huge number of vendors with SKU and description; scraped online product catalogs from the two or three vendors the bank commonly sourced from; and third-party published Y2K compliance lists, plus vendor records that expanded on all of it.
- Fuzzy matching at scale. I built a front-end app in MS Access with a fuzzy-matching engine that walked the inventory and, wherever it found a strong match, updated the record with vendor, product and compliance information automatically. For everything else, it showed unmatched records with a ranked list of possible matches, so I could pick one or search further.
- Closing the loop with testing. IT had a bootable-disk tool that staff could boot into to run a compliance test on a machine. Its results updated records we had no compliance data for and flowed back into the master inventory. When the test flagged a very common 90 MHz PC, I found it was a software issue, not hardware, and wrote an extension that dropped the PC to a lower clock speed to validate it.
- A vendor-engagement tracker. I designed it by hand in pseudocode first, then built it as a classic-ASP site in a day or so. It walked staff through engaging a vendor, reporting product details, and capturing either compliance documentation or a finalized exemption. It filtered by last-review date, open and newly closed, which made the weekly open-issue reviews manageable. A closed issue got a big red “CLOSED” stamp on the record, which the VP liked.
Net results
- Accomplished everything asked of a six-to-twelve-month contract in about four weeks, then stayed on to roughly six weeks total for support and handed the codebase off to a colleague.
- The twelve-plus person follow-up pool was reduced to a core of two or three.
- The bank and I ended the contract early by mutual agreement, at a large savings against its budget.
Takeaway: design the solution in the abstract first, then implement.
Scope and technologies
MS SQL Server; MS Access front end with a custom fuzzy-matching engine; data-ingestion and catalog-scraping tools; normalized reference database design; a bootable compliance-test disk integration; a low-level extension to down-clock a PC for validation; and a classic-ASP vendor-engagement tracking site.