​

“Find those Beta machines or find another job!” The broadcast executive’s ultimatum was an unwelcome challenge. I had been security manager at the Fox Television Center in Hollywood for less than a year, and the prospects of unemployment, or returning to graveyard shift patrols in a police cruiser, were not appealing. I was desperate for a lead that could help solve the mystery of where two $75,000 Sony BVW-75 tape machines had gone.

I conducted the usual follow-up: looking for witnesses, checking pawn shops, reviewing videotapes, checking phone records, filing a police report. Since the machines had disappeared overnight when only guards were around, I even checked out my entire guard staff, strangely hoping that I might have a criminal working for me. I came up empty on all accounts. With my options narrowing, I had to try something less orthodox: I turned to data mining—and found what I needed.

The lesson of that experience has been reinforced time and again: Traditional investigative techniques, while important and necessary, do not always deliver the goods. But when a security professional with investigative skills teams up with an information technology expert, they can form a highly successful and powerful partnership.

Digital data helps detectives go well beyond physical evidence at the scene, such as fingerprints and clothing fibers, to less obvious relational information that can reveal the guilty party through telling patterns of behavior. I have learned that an investigative file should never be marked “case closed” until a data-mining probe has been conducted. The following cases illustrate how it can be done successfully.

Not Caught on Tape

The case of the missing videotape machines became my first foray into datamining detective work. It was the natural outgrowth of many years of getting to know computers, which began when, as a young police officer in 1984, I purchased a Televideo 8086—a basic unit that didn’t even have a hard drive, but merely used two floppy disks.

Fast forward a dozen years to the theft of the two Sony BVW-75 Beta machines—essentially giant Beta tape decks. Total value: $150,000. The machines had been taken from the Fox broadcast operations repair shop. Unfortunately, no one saw or remembered anything unusual the evening of the theft. There were no workable leads.

I was sitting in my office mulling my misfortune when the phone rang. It was the broadcast executive calling—I could tell from the caller ID. I was gazing at the number when I had an epiphany: A BVW- 75 was used for broadcast operations to view and edit tapes for on-air commercials, TV shows, or other broadcast users. It was something you would not steal to watch movies at home.

Therefore, I theorized the suspect might take the equipment to a reseller, but that would take time and effort and could raise suspicion. The broadcast industry does not normally buy $75,000 tape machines out of the trunk of someone’s car. The thief might decide instead to try to sell it through classified ads that commonly listed used broadcast equipment. Thus, I reasoned, the ads might lead me to the suspect’s phone number.

Getting started. I figured my best bet was to look in the places where legitimate used broadcast equipment was offered for sale. I began by checking used equipment ads listed in newspapers and online bulletin boards. Each week I searched dozens of sources, gathering everything I could find on BVW-75 tape machines: model numbers, prices, seller phone numbers, and so on.

At first I started calling the ads and asking about the equipment, hoping to discover some flaw in the seller’s story about where the machines had been obtained. I thought I could follow up the calls by looking at all the equipment offered for sale and matching that data to our serial numbers.

The problem was, advertisements ran for only a week or two, and there were hundreds of ads for this sort of equipment. I needed to speed up the process.

To do this, I compiled the ad information into a Microsoft Access database, listing equipment type, price, description, geographic location, and phone number of the seller. (Microsoft Access is a relational database software program that helps to analyze and reveal patterns in data compiled in multiple tables.) After several weeks I had saved 1,500 distinct ads listing equipment similar to the stolen BVW-75 tape machines I was looking for.

Since our studio was essentially a closed lot, I knew it was likely that the theft was an inside job. Working on that premise, I hypothesized that if someone working at the studio was buying and selling stolen equipment on the side, they just might be foolish enough to do it during Fox studio business hours. If so, I might have an in-house phone record of their call to a phone number in the newspaper, or to their own message machine, to check on the response to their ad.

I then collected eight million of our company phone call records from the phone switch—the phone closet computer in modern buildings that records phone numbers from incoming and outgoing calls. I gathered the phone records in simple comma-delimited text format and dumped them into the same Microsoft Access database that contained my ad information. The records contained the date, time, caller ID, phone location, dialed digits, and length of call.

I tried to visually match the phone numbers from the newspaper ads with the phone numbers from our studio phone records, but with eight million records to review, using simple visual comparisons of data was like looking for a needle in a haystack.

Enter the programmer. I needed a programmer who understood database management and could write some custom code to fit my search parameters. I contacted our IT department but found no one with a database background. Fortunately, I was able to locate a database programmer through a temp agency.

Note that I did not have to be the computer expert myself. The match-up of the investigator with the programmer is key to data-mining. Most security experts and law enforcement officials have investigative know-how, but for the most part they lack advanced computer skills. Computer geeks with database programming capabilities can fill that void. Together, IT and security can form a formidable team.

After listening to my theories about the case, my programmer wrote a database query consisting of a series of questions and commands. His query asked the database to match phone numbers from the ads with phone numbers from the company phone switch. Result: Jackpot! Contained within the phone records was a match with an employee who worked in the broadcast building where the tape decks had been stolen.

The staffer had used his office phone to call a phone number listed in the newspaper that offered to sell a Sony BVW-75 tape machine. In fact, there were dozens of phone calls from the employee’s phone to the number listed in the newspaper.

I discovered that many of the calls to the number listed in the newspaper ad had been made months before the ad was placed. A quick check with the Fox human resources department confirmed that the phone number listed in the newspaper ad—which offered to sell similar equipment—matched the employee’s home phone number. We had a suspect.

Burning shoe leather. My next step was to find someone who could place the stolen BVW-75 tape machines in the possession of our suspect. At first I checked traditional physical records, such as visitor and employee security logs, time cards, and guard patrol logs. Nothing.

In addition, no one had seen this particular employee the night of the theft, nor were there electronic access records to place him at the scene. I needed more evidence, so I decided to identify some of the other numbers the suspect called from his office phone.

I started to call some of the telephone numbers from the studio phone records that coincided with the date and time of the equipment theft. I was able to identify the phone numbers that were businesses because people usually answered the phone by stating the company name. But again, this manual process was too slow—I needed to identify a lot of numbers quickly.

At the time of the investigation one could call the phone company and ask them for a phone listing based on a name, but you could not give the phone company a phone number and get an address. For this I needed to purchase a software version of a reverse phone directory that provided addresses and subscriber information based on phone numbers.

Reverse phone directories cost upwards of $1,500, but I was able to justify the purchase to my boss by showing him what had been achieved so far in matching the studio phone records to the newspaper ads. Armed with another new tool for our investigation, my programmer and I then downloaded the accumulated data and wrote a query asking my database to match phone numbers from the newspaper ads and the studio records with names and addresses in the reverse directory.

Of the thousands of numbers checked, one stood out. There were several calls between the suspect’s phone and a company that specialized in buying, selling, and repairing used broadcast equipment.

I contacted the Fox engineer who had originally reported the theft. He was familiar with the local company, so he and I took a trip to the broadcast repair shop. There we met with the owner, who coincidentally had a Sony BVW-75 tape machine for sale for only $25,000. My engineer checked the machine’s serial number. It matched our stolen equipment.

I persuaded the business owner to cooperate with the police in lieu of filing charges against him for receiving stolen property. The police showed the owner a photo lineup, and he identified our employee as the suspect. With this information we were later able to identify the suspect’s accomplice, a former employee of Fox and the half brother of the primary suspect. He had been fired several months earlier for—you guessed it—stealing.

I eventually used additional data mining techniques to dredge up more evidence that was used to convict the suspects. A final twist to an already labyrinthine investigation was that the live-in girlfriend of the suspect turned out to be the sister of the police detective working the case, something the officer had chosen to keep quiet.

The Missing Wallets

Another incident that proved the value of data mining involved stolen wallets. This case also occurred at Fox, which by then had moved to West Los Angeles.

Over a period of approximately six months, one of Fox’s buildings was plagued by a series of office-creeper thefts. There were 10 victims and 13 events—one unfortunate target had been hit three times in a row. The victims worked in separate business divisions and were not familiar with each other. They worked in open cubicles and private offices. Some were female; some were male.

The thief targeted purses or briefcases whether they were left out in the open or tucked away inside desks. Only cash or credit cards were stolen, however; everything else was left intact. The thefts were sporadic and ranged across all hours of the day and night, weekends, and even one holiday. The only commonality among the crimes was that they occurred in the same five-story restricted office building. Entering the building required and authentication through a computerized access control system.

As in the case of the BVW machines, extensive traditional investigations were conducted, but no witnesses or workable leads were generated. On the surface, it looked grim. I didn’t yet realize that the identity of the suspect was right under my nose.

What I did know was the following: The victims were all located in the same building complex, which housed approximately 5,000 employees. The building was open 24 hours a day, 365 days a year, but could only be accessed with an ID card. In addition to the external access points, the building had 65 restricted entry points within. All movements were recorded in the access control computer. More than

250,000 access control records were generated per month. In addition, more than 125 video cameras recorded employee movements within the complex.

I also knew that nonemployees entering the building had to present a photo ID, such as a driver’s license. That information was recorded by the guards on handwritten logs each time that an individual guest came and went from the facility.

Collating data. The next step was to consider what evidence might be provided by the electronic access controls and surveillance system. Cameras were allowed only in common areas, and since the thefts occurred within employee workspaces, I had no videotape leads. The real gold mine would be the access control records. Database mining was again the key.

I started by pulling the access control data from the system and dumping it into a Microsoft Access database. That yielded 1.5 million access control records.

I then created a table in Microsoft Access containing the distinct dates, times, victims, and building locations of the 13 thefts. There were millions of records to review but only one query for the database: Was any employee in the building for all 13 thefts? The program spit out a lone name. I had my suspect.

Proof positive. As convincing as my data-mining results were to me, they might be viewed as circumstantial evidence by others. I wanted more conclusive proof. I set up an office cubicle with a big fat wallet for bait. I installed hidden cameras above and around the cubicle, and settled in to wait.

A few days later, my suspect approached the area, checked out the unattended wallet, and left. He returned on several occasions over the next few weeks and continued to scope out the area, looking around.

Finally, one day the suspect approached the cubicle and reached for the wallet. His hand was only inches away from grabbing it when he inexplicably stopped, then abruptly turned heel and left. Perhaps someone had tipped him off, or the criminal sixth sense kicked in. Whatever the cause of his suspicion, it was the last time he got near the cubicle, and the last time we had a theft in the building.

Shortly afterward, the suspect changed jobs and took a position in another division as an executive assistant. The executive suffered identity theft only a month after the suspect starting working for him. What a coincidence.

I began to monitor the suspect’s computer and discovered he was browsing pornographic Web sites four hours per day. That was all the evidence we needed to get him fired. I turned the case over to the police, and they later were able to prosecute the suspect on a few of our thefts, as well as several other burglaries and identity thefts.

Building Your Database

It takes someone with advanced knowledge of Microsoft Access to write queries of the complexity used to solve the two cases discussed herein. Not to worry. Somewhere in your organization, there is a database guru. Seek that person out and chat about processing information to solve crimes. You may be surprised at how willingly he or she assists you.

After you have identified the right database person, ask for help in starting to save data from some of the following sources: access control; PBX; digital phone recordings; blogs; ID systems; report writing systems; Internet logs; e-mail; calendars; video; delivery logs; PeopleSoft; SAP; travel and expense records; point of sales data; time clocks; wireless networks; Webcams; Bluetooth personal area networks (PANs).

Make a backup of the data, never delete it, and save all new information that comes along. (You may first want to consult with legal counsel to make sure that there will not be any questions about privacy violations or other issues as you store this information.)

As it happens, several years ago I was part of a civil case where a former employee served as a hostile witness in a lawsuit against the Fox studio. He claimed that he had heard management making derogatory remarks about staffers over the building’s radio system. The testimony could have potentially cost Fox $25 million.

The legal department asked me to assist. I checked my database and dug up a copy of both the purchase receipts and the Federal Communications Commission (FCC) license for the building’s two-way radio system. The records proved that the employee was lying.

To begin with, the Fox radio system and the FCC license had not been purchased until almost a year after the employee claimed to have heard the lascivious comments over the radio. The coup de grace was even better: The witness had been fired for drinking on duty before the studio purchased the radio system.

Asking the right questions has always been at the heart of any good investigation, but in the past, investigators were limited to coaxing answers from reluctant suspects and witnesses, if any existed. Today, we can query powerful computers capable of seeing patterns that even the best witness wouldn’t be able to discern.

If there are any security professionals still reluctant to join the ranks of the computer savvy, I would suggest to them that this is a brave new world indeed, but it is one that only criminals need fear.

Charles A. Harold runs Charles A. Harold & Associates, a private investigation & consulting firm in Burbank, California. From 2002 to 2005 he served as senior manager of security operations at The Walt Disney Company.