The Failed Hard Drive, the Toaster Oven, and a Little Faith

OK, everyone knows that heat kills electronic components, right?  Never subject any electronic component to heat.  Unless that makes the component work, that is…

Confession is good for the soul, they say, but bad for the reputation.  So I’ll tell the story this way.  You see, there was this “friend of mine” whose hard drive failed.  I mean, it was working fine the night before when I, er, he shut down his computer.  But the next morning he turned it on and all he got was “shicka, shicka, shicka, shicka, shicka,” then a pause, then five more attempts, then five more, and so on until the drive finally said “sorry…” and shut itself off.  Now this guy hasn’t been taking his own advice about backups for a while and – you guessed it – he hadn’t backed up his Quicken off drive since, oh, about December of 2008!

The first attempt was to unplug the power and let the hard drive sit and think about running all day long while he was at work.  One friend told him, “My drive does that all the time.  I just unplug it and plug it back in and it works.”  Sounds good, he thought.  So at the end of the work day, he came back home, plugged it in, held his breath, and powered up.  No joy!  Five more “shickas” and he powered it back off.  Now what?

The next thing, especially when one has theological training, is to have a prayer meeting with one’s wife!  (Seriously.)  He knew it was going to take a miracle at this point to avoid spending an inordinate amount of time and money to get this thing going.  After the prayer meeting, I decided…  ok, no more pretense – I’m the one that hadn’t been practicing what I preach when it comes to backing up…  I decided to try the freezer trick I keep hearing about.  Hadn’t really ever needed it before, but I was getting desperate.  So I put the drive in a zip lock with some silica gel, and stuck the drive in the quick freeze section of my freezer for about 30 minutes or so. 


Keep in mind, now, my electronics are good.  The BIOS recognizes and correctly identifies the drive as a 250 GB Western Digital model Wd2500jb-57gvc0.  And remember that I had NO trouble with it up until that morning.  I figured it was worth a shot, although I think Scott Moulton prefers freeze spray to icing down the whole drive.  This has got to work.  Perhaps with a little help from the almighty…  I plug it back in, turn on the computer, and – you guessed it – “shicka” some more.  So I power down.


Now, I start thinking – which is a very dangerous thing at times.  Last night when I shut down – when the drive was working just fine – it was warm.  Not cold.  Now, I am trying to get it to run but it is cold, not warm.  Do you see where this is going?


I set the toaster oven on about 150 degrees, or so – probably a normal running temp for a lot of devices – and let it warm up.  Then I took the drive and laid it on the rack with the top down and electronics up.  (I didn’t want to cook the circuit board from the bottom element!)  Then I stood by and opened the oven door every 30 seconds or so, and felt the drive with my hand to make sure it wasn’t getting too hot.  I know what a running drive feels like, so I figured I should be able to get it to the right temp without burning the casserole, if you know what I mean. 

After a few minutes, I determined that it felt just like a warm, running drive.  So I pulled it from the oven, took it to the computer, and let it sit for a couple of minutes to equalize.  I knew at that point that, while the outside was crisp, the inside was still warm and gooey.  After equalization of the heat, I plugged it in, powered up, and it ran PERFECTLY!  I was able to use Helix to image the entire 250GB drive over to a new 500GB drive without a single error!

So, here is my theory – If we are working with a drive that:

  • Has not given us a problem before
  • Its electronics are known to be good
  • Was running fine when it was warm and it powered down fine
  • Hasn’t been backed up since Christmas of last year
  • Desperately needs data recovery!

We may try this procedure with the following caveats –

  1. Preheat the oven
  2. Use bake, not toast or broil
  3. Turn the drive upside down when you put it on the rack
  4. Make certain that it does not get too hot!  (Too bad we can’t insert a meat thermometer)  It should never be hot enough to burn you!
  5. Let it sit and equalize the heat before you try plugging it in
  6. DISCLAIMER:  Neither the author of this blog, nor anyone the entire GCFA community or at SANS will be responsible if you end up with a burnt “casserole” and render your drive even worse than it was before you started!
  7. It might work

Please don’t tell my GCFA instructor Rob Lee, or SANS instructor Scott Moulton, that I suggested this.  I don’t want them to think I’ve totally lost my marbles!!!

By the way, Quicken is alive and well.  Oh, and I have 4 copies now of my data files.  (Just in case)  And, by the other way, what I always told my customers was:  a) “If you have done enough work that you would not want to have to do it over, it is time to make a backup,” and b) “You will never fully appreciate backups until you have a catastrophic failure.”  (And that isn’t “If” but “When” you have a catastrophe!)

OK, my soul is better now.  My reputation is probably back where it belongs.  And I have to thank God for the inspiration to even try this.  (For those who do not “believe,” I have to remind you that there are no atheists in foxholes.  At least, that is what I have always heard, although there are differing opinions on the topic.  For me, this computer stuff can be a real battle, sometimes.  I welcome all the help I can get!)

J. Michael Butler, GCFA Gold #00056 GSEC EnCE CISA, is an Information Security Consultant employed by a fortune 500 application service provider who processes approximately half of the $5 trillion of residential mortgage debt in the US. He is a certified computer forensics specialist. In addition, he authored the enterprise wide security incident management plan and information security policies for his corporation. Butler has authored a number of blogs and white papers.  He can be reached at jmbutler_1 at hotmail dot com.

Decrypting a PointSec Encrypted Drive Using Live View, VMWare, and Helix

Doing it the HARD way!

Perhaps you remember my previous blog on EnCase and PointSec, which included my plea for Guidance Software and CheckPoint to work together to create a seamless way to decrypt drives without having to go through 20 or 30 steps to get there.  I even wrote, out of desperation, A Case for Decryption of the Original, because it would save time consuming steps and not change the data relevant to an investigation.

Time for an update.  As noted in my last blog on decrypting the original, VMWare no longer recognizes a raw disk as a valid disk image.  Images have to be converted before VMWare will recognize them.

 Here is a new and “improved” method that will result in a COMPLETE decrypted image without changing the original.  It is more painful because more steps are involved, but it works.  (Today).  That being said, I STILL want PointSec, now called “End Point Security,” to work with Guidance to create a driver that could be used to directly access the disk image and decrypt it in EnCase.  This can’t be rocket science, right?  Let me add an encrypted image to the case, key in a password, and access the data.

In the mean time, gather your tools.  You will need the dcfldd for Windows, Live View application, VMWare Server, and Helix for imaging.  (Twice).

  1. Use Helix or your other favorite method to acquire a raw image of the drive to be decrypted.  (There is an open source version of Helix you can download for free, or you can purchase Helix Pro in order to have support, if your prefer.)  [Watch for my upcoming blog on using dcfldd to acquire a raw image.]
  2. Use Live View to convert the raw image to a VMDK file.  (You will have to have the correct versions of VMWare to read the VMDK.  Live View will inform you what version of VMWare you should be running.)
  3. Acquire the PointSec recovery file from the administrator.  (This whole process assumes that you have the administrator ID and password for an administrative install of PointSec.  If you don’t have that, you are reduced to a manual brute force attack.  Good luck!)
  4. Using the PointSec recovery file, create Recovery Media.  (Believe it or not, you need a real floppy disk to do this.  Can’t just create a raw floppy image.  Go figure.)
  5. Create a raw image of the floppy disk in a file on the Windows hard drive using the following command:
    dcfldd if=\\.\A: of=filename.img   
    (requires you have dcfldd installed – available from
    If you use linux, refer to the floppy drive device (if=/dev/fd0 or as appropriate for your system) as the input file instead of the above syntax.)
  6. Copy the resulting floppy image to your VMWare server where you intend to decrypt the image.
  7. Open VMWare
  8. Select the VM created by Live View, but do NOT start the machine.  (Note that you will not have to create a new virtual machine.  Live View handles all that.  But also note that Live View creates a snapshot and other files as well, which cannot be read directly into EnCase Forensic.  That is why we must do the final acquisition with Helix in this process.)
  9. Add a floppy drive to the VM configuration and select the image created above as the floppy virtual drive.  Make sure it will “Connect on Power On” so that the machine will boot to the floppy

10.  Edit the CD Rom settings and set it to use an ISO image.  Point to a copy of the Helix ISO image.  (This is for acquiring the decrypted drive later, but will not be used for the decryption step.)

11.  Start the Virtual Machine – it will boot to the floppy image.

12.  Enter the requested PointSec administrator credentials and start the decrypt process.  The VMDK image will be decrypted.

13.  Once you have entered the credentials, the program begins decrypting the hard drive image, posting a % complete message as it goes.

14.  Once decrypted, reboot the VM

15.  Hit escape ONE TIME during boot to get Boot Menu.  (If you hit escape too many times, VMWare will blow by the boot menu, but not to worry, because we have left the floppy image set up as the boot drive.  That way the decrypted image will not boot and will, therefore, remain unchanged for maintaining Chain of Custody.)

16.  Select CD-Rom from the boot menu to boot to the Helix CD-Rom.

17.  Run Helix from the CD.

18.  Insert a USB drive with enough spare space to receive the image from the “target” machine.  You will mount it later.  Helix is able to mount NTFS in read/write mode, so your portable drive can be formatted using NTFS.

19.  Once Helix has booted up, use the VMWare toolbar option:  VM/Removable Devices/USB Devices to select the USB drive for writing the acquired decrypted image.

20.  Open a Terminal Session by clicking on the terminal icon in the Helix tool bar.

21.  Execute the following command in order to get root prompt:  sudo su –

22.  Execute the following command in order to determine drive designations:  fdisk –l   [note that is dash lower case L, not I or 1]

23.  Once the USB drive has been added to the VM, if it is formatted using NTFS, use the following command to mount the drive:
mount –t ntfs-3g /dev/sdx1 /media/sdx1 –o force  
(substitute correct letter for x based on the results of your fdisk –l listing)

24.  Create a directory on the USB drive to receive the image.

25.  Change to the directory you just created.

26.  Execute the following command in order to record disk parameters for the case:  fdisk –l > fdisk.txt

27.  Use the following command to acquire the image:
dcfldd if=/dev/sdx of=filename.img conv=noerror,sync hash=md5 hashlog=filename.img.md5

28.  Once completed, for the record, do the following command to save the history of commands into file:
history > history.txt, then save the mount config in case anyone asks about that with:
mount > mount.txt

29.  Now you have a raw, decrypted image that can be read into EnCase and properly acquired for analysis.  Using this method, the original disk is untouched, and the only change to the disk image is that it was decrypted.  This preserves proper Chain of Custody and avoids contamination of the evidence.


Whew, that was way too painful.  In my next blog, I will share a method of “slaving” the target drive so that it can be acquired directly into EnCase with the hard disk left in its original state.  Still not as easy as it ought to be, but much easier than the VMWare method.  The only caveat is that the “Slave” method will allow us to image the decrypted partition(s), but will not allow decryption of the entire hard drive.  So at some point, it may be necessary to use the method in this post, not the “Slave” method.

 J. Michael Butler, GCFA Gold #00056, is an Information Security Consultant employed by a fortune 500 application service provider who processes approximately half of the $5 trillion of residential mortgage debt in the US. He is a certified computer forensics specialist. In addition, he authored the enterprise wide security incident management plan and information security policies for his corporation. He can be reached at jmbutler_1 at hotmail dot com.

Block Pornography – The Bane of Computer Forensics

By J. Michael Butler

What is more important?  Searching for porn on an organization owned asset, or looking for misuse of organization owned data?  Not even a trick question.  Too easy.  So why do organization’s computer forensic experts still find themselves searching for porn?  Because it is there. 

New problem?  I think not.  In T.h.e. Journal, there is an article written in 1997 addressing this same issue and suggesting a product called “Little Brother” to fix it.[1]  Today there are a plethora of software products for home and office use, ranging from free to more than $100 per workstation.  Some are more effective than others, but evaluation is outside the scope of this article.  Just know that software solutions exist.[2] 
Moreover, hardware solutions are also ubiquitous.[3]  Consider firewall and/or proxy type products.  With the correct settings, most porn can be stopped at the proxy.  I say most because there are so many new sites that pop up every day.  On the other hand, proxy software and hardware are constantly updated to add new blocked sites, so even if a new site is temporarily available, it will be blocked eventually.  So a good proxy will make it so much trouble to surf undesirable sites that one must assume users will eventually give it up.
I have included links to a few software and hardware tools at the end of this article.  This is not an endorsement of any of them; just a simple statement that there are many options available.  Nor should this list be considered all inclusive.  I assure you I am forgetting more than I listed. 

Not too long ago, the GCFA alumni e-mail list was hit with a brisk discussion of porn on corporate computers, to which one wag replied ‘just block the traffic.’  No porn – no problem.  Makes a lot of sense to me!  So, what’s stopping the companies from stopping porn surfing?  Money.

It takes money to block porn.  Not a huge amount, but enough to get the attention of management and the bean counters.  The problem is, like the old Fram oil filter ad used to say, ‘…you can pay me now, or you can pay me (a lot more) later.’

This is more than a “moral” issue or a policy issue.  Porn can be an addiction, just like booze and cigarettes.  So, do you really think you will solve the issue by writing a policy?  Perhaps you have heard the old saw:  “We can’t legislate morality.”  But, at the same time, we can take steps to keep porn out of the workplace. 
Let’s examine what happens when an organization has a user who spends work time on pornography.  Everybody loses.  Most of all, the organization!  First, there is the lost of productivity.  That costs money.  Whatever the user is supposed to be doing is not getting done.  Or it is taking a lot longer to complete.  Customers are not happy.  That costs money.  Coworkers are not happy.  In fact, Coworkers may well be running to HR if they are offended by what they see on the offender’s computer.  Has your company looked at the legal liabilities?  That costs a LOT of money!  Check out this article from the ABA:

Finally, what is the cost of losing an otherwise trained and capable employee?  When an employee makes poor choices that cost that person a job, not only does he or she take a hit, the organization loses thousands of dollars.  Possible costs include:

  • Exit costs
  • Recruiting
  • Interviewing
  • Hiring
  • Orientation
  • Training
  • Compensation & benefits while training
  • Lost productivity
  • Customer dissatisfaction
  • Reduced or lost business
  • Administrative costs
  • Lost expertise
  • Temporary workers[4]

Webpronews estimates “[Losing employees] costs you 30-50% of the annual salary of entry-level employees, 150% of middle level employees, and up to 400% for specialized, high level employees!”[5]  You can also calculate in the resource costs for the HR expert, Legal representative, and forensic analyst who have to spend time on such an issue.  There is another expense.[6]

A significant percentage of inappropriate web surfing can be stopped.  Period.  Access to anonymous proxies can be stopped.   Users can be blocked from wasting time at porn sites, as well as other sites considered undesirable by management.  By carefully and thoughtfully spending appropriate funds now, an organization can avoid loss of productivity and loss of good employees.

Why is this important enough to blog about?  Because it can be stopped.  There are software and hardware tools that, among other things, will effectively block a high percentage of porn sites.  Try entering “How to block porn” in a search engine, and you will have over 1 million hits.  How much should you spend to block porn?  Well, it depends.  How many employees do you want to make sure you keep?  How important is it that all your projects are completed on time?  So, stop it, already.  Make sure your resources spend their time on your priorities.



[2] Software options for blocking porn:

Aobo Porn Blocker:

Blog article on Proxy Auto Configuration files :



NetDog Filter:



SafeSquid Proxy:



[3] A few hardware vendors with solutions for blocking porn:

Blue Coat:






Sonic Guard:





[6] More on cost of losing/replacing employees:

Cost of losing an employee: by Tom Maydew

Average Cost of Bringing On a New Employee:

The Real Cost of Losing an Employee:

The Billion Dollar Cost of Lost Business Knowledge:

J. Michael Butler, GCFA Gold #00056, is an Information Security Consultant employed by a fortune 500 application service provider who processes approximately half of the $5 trillion of residential mortgage debt in the US. He is a certified computer forensics specialist. In addition, he authored the enterprise wide security incident management plan and information security policies for his corporation. He can be reached at jmbutler_1 at hotmail dot com.

The NOISY U3 Thumb Drive File Access behavior in Windows

So I have a timeline analysis. What file activity should I see when someone inserts a U3 type USB thumb drive in a computer? And why should I care?

I care because files accessed on the hard drive, or the “Recent Documents” history, may tie directly to the actual time the thumb drive was plugged in. It turns out that U3 thumb drives actually run programs and create logs when plugged in. This means you have file creation and/or modification all the time the drive is inserted. Not only that, but cleanup routines run after it is pulled out, whether you exit nicely or just jerk it out.

You may wish to corroborate other evidence you have, from the registry for example, concerning the insertion of a particular drive. Or you may find files or file remnants that will give you more information about the thumb drive that was inserted. To understand what happens on insertion, and to know where to look for files, I have used Filemon1 and recorded the file activity that occurred as the drive was inserted. Due to unrelated file activity going on at the same time you have to filter to find what you need.

For further flexibility for querying, filtering, or otherwise sorting it all out, you can load the Filemon log file into a database, such as MS Access, for further analysis. This gives you very flexible and quick sorting, filtering, viewing, and reporting capabilities. But let’s conduct the experiment and see what Filemon looks like first.

When you run Filemon, capturing starts immediately by default. Here is a screen shot taken after I clicked on the magnifying glass, which stopped the capture process on my computer.
Note that you have data fields that indicate a sequential number, time of access, process that caused the file access, type or “Request” of access, the full path of the file accessed, the result of the access attempt, and information about what part of the file was accessed.

The next step, after stopping the capture, is to clear the screen and get ready to record the results of our test. First, set the filter. I have discovered that all you need to use is “*U3*” for your filter to get relevant activity. So, click on the filter button and key in your filter string as in the example below.


Then, in this order, click on the capture button to start recording and insert the U3 thumb drive, let Filemon run for a minute until the activity slows down, then remove the drive. In the space between 8:05:24 and 8:06:32 on my computer, I had over 8500 entries in Filemon. Many are the same file(s) being accessed repeatedly, of course. Knowing this behavior will be helpful when examining a computer where the user inserted a U3 thumb drive, especially for your timeline analysis.

Here is a shot of what I came up with. The first entries show what happened immediately after insertion.


Later on in the data, there is actually a U3Launcher.log file created under the user’s directory in Local Settings\Temp that is regularly updated. In fact, the last usage of that log will remain in the temp directory unless the user deletes it. Here is a shot of a few lines from that log. Note the dates, times, serial numbers, and other relevant information.


Fortunately, Filemon has a “save as” capability where it creates a tab delimited text file that is easily imported into other software. You can see below that the file extension is .LOG. You may have to rename the file to .TXT or .CSV for your database or spreadsheet to see it.


I imported the file into Microsoft Access and did further queries, filtering, and sorting. I changed the name filename.log to filename.txt so Access would read it, then imported the file. However, you can tell a lot just from looking through the results of your Filemon experiment on screen.

Finally, and I find this interesting from a Forensics standpoint, the U3 cleans up after itself. It actually runs an application called “cleanup.exe.” Here is a shot of the tail end of my Filemon experiment showing the last entries of the cleanup routine.


In short, U3 drives are extremely noisy and leave a wide trail of file activity. Information is changed elsewhere, as well, of course, in the registry, and in memory. But that will have to wait for another article.  If you know the type of drive the user used in your case, you may wish to experiment with another thumb drive just like it to see what it does to another computer. Then you can compare data with the timeline analysis you pull from the subject computer.

J. Michael Butler, GCFA Gold #00056, is an Information Security Consultant employed by a fortune 500 application service provider who processes approximately half of the $5 trillion of residential mortgage debt in the US. He is a certified computer forensics specialist. In addition, he authored the enterprise wide security incident management plan and information security policies for his corporation.

1 (Link to Filemon.exe by Mark Russinovich)

Keeping Evidence Safe for Litigation

You have an incident. You have collected hard drives, USB drives, thumb drives, and PDAs. You made bit for bit images of all of them. Now, what do you do with the originals to ensure chain of custody?

First, make sure they are all stored inside static free bags, such as those in which hard drives are packaged when new. It is possible to obtain static free evidence bags, but the easiest thing to do is to use a plain static free bag to wrap the device, then store the device, bag and all, inside an ordinary plastic evidence bag. Such bags are available from companies that sell them to law enforcement.1 Just Google “Evidence Bags” for lots of choices. Here are the bags we use in my organization:

Evidence bags have a place to record what is in the bag along with the current and previous custodian information. This particular type of bag is sealed permanently by peeling a strip off and sticking the bag together. The only way to get the contents out is to tear or cut the bag open.

Once sealed, the bags need to be locked in a secure place. We selected a combination gun safe from which we removed the built in gun racks and installed shelves. Gun safes are a good option in that they are relatively inexpensive and they have some fire protection built in. They come in many sizes and, therefore, prices. Again, a quick Google search for “gun safe” will provide you with many options.2

The safe we acquired is a Winchester safe with a combination AND key lock. Here is a picture similar to the one we purchased:

These models of safes are available with combination and/or key, or electronic combination lock styles.

Finally – and possibly most important – keep a detailed log of when evidence is placed in the safe, and removed from the safe, including date, time, person responsible, and a reason. I recommend using a standard composition book with a specific number of pages so any missing pages will be obvious.  Store the book (and an ink pen) in the safe with the evidence to protect the log.  Such books are available from any office supply store and a host of other retailers.

Place the safe in a secure area with limited access, and limit the number of persons who know the combination and/or who have access to the key. (Only two persons have access in my organization.) Make sure you record the INs and OUTs of the evidence in a log. Then, when you get to court, you will be able to demonstrate in a defensible manner how you protected the chain of custody.

J. Michael Butler, GCFA Gold #00056, is a Information Security Consultant employed by a fortune 500 application service provider who processes over half of the approximately $5 trillion of residential mortgage debt in the US. He is a certified computer forensics specialist. In addition, he authored the enterprise wide information security policies for his corporation.

1. Examples of evidence bags available on line. (Google “Evidence Bags” to find many more options.)

2. Examples of gun safes available on line. (Google “gun safe” to find more options.)

Lawyers Aren't So Bad, After All

This sentiment may vary depending upon whose side of a case you choose.  I have had the good fortune to work with several capable lawyers.  It has been my experience that lawyers are good listeners when they need input from me concerning my field – forensics.  The important thing is to make sure you have a good relationship with legal.  The communication lines have to be open, no matter what you think of the “legal eagles” with whom you are dealing.

Just Push a Button…

I wrote code in a former life for a guy who ran a trucking firm.  He didn’t even know how to turn the computer on.  However, when he wanted some new feature, his comment was, invariably, “…you should just be able to push a button and get that!”  While today’s lawyers are typically more sophisticated, they may still expect us to be able to push a button and get their answers.  Just like that.  While they may be disappointed, they can learn that there is no instant gratification with forensics.  A lot of preparation is required to reach the point where we can just “push a button” to get answers.[1]

Consider cases that start with the pain of drive decryption.  Decryption will probably take a day or a night to complete.  If the drive is large enough, and if the lawyers have provided a large number of search words, it could be several days more before we have results.  We may need to reveal file structures and run a number of case initialization scripts before we can really begin the searches in earnest.  Searches can take days.  Once the searches are complete, there is a chance that something was not right in the search parameters, and we may have to start over.

Needle In a Needlestack Art or Science?

Computer forensics – art or science? My co-worker, Richard Newman, expressed recently that it was like looking for a needle in a needle-stack. When Legal understands that, they can be your best friend.  They can help buy you needed time in order to do a thorough job.

Provide timely status reports.  Explain everything fully.  Seek their advice.  Work closely with them throughout the entire case.  Maybe you, too, will believe that lawyers aren’t so bad, after all.

1. Computer Forensics for Lawyers Who Can’t Set A Digital Clock

J. Michael Butler, GCFA Gold #00056, is a Information Security Consultant employed by a fortune 500 application service provider who processes over half of the approximately $5 trillion of residential mortgage debt in the US. He is a certified computer forensics specialist. In addition, he authored the enterprise wide information security policies for his corporation.

Using a Database as a Forensics Tool – Part 2 of 2

In my first post…

I discussed the value of importing discovered flat files into a database in order to analyze them for the legal team. I showed two files of mock data based on an actual case where we were able to tie together relative fields of NPI/PII data to determine what the malicious user had stolen. We also discussed the need for legal to know what persons lost data and what type of data was exposed for each individual. Lawyers always want details!

In this post I will discuss the import procedure for Microsoft Access and some lessons learned regarding that database.

Get External Data

Microsoft Access Import Screen Shot for flat file 1
Microsoft Access Import Screen Shot

Microsoft Access has an Import or “Get External Data” routine.  If you are fortunate and most or all of your files and file fragments are in the same format (don’t hold your breath!) then you can create a single specification for reading the text files (see below), and just “suck” them in one right after the other.  They can even be added to a single table.  Otherwise, you may have to import these flat files in several tables to reconcile and link together later.

Import Specification Dialogue Box
Import Specification Dialogue Box

Here are some lessons learned while importing data in Microsoft Access:

1. Take the time to create a file specification (under the “Advanced” tab in the MS Access import function).  That way, if something doesn’t import correctly, you can go back and tweak the specification instead of having to start over from scratch.

Import Specification Dialogue Box
Import Specification Dialogue Box

2. Import all date/time fields as text. See DOB specification above. Access does not seem to be consistent in the way it handles an imported date/time. But you can import it as text, then change to a date/time type in the design of the table. That will automatically convert all of the data from text to date/time. Converting to actual date/time data is important for queries and sorting.

3. Watch for fields that may be mostly numeric, but that may occasionally have a bit of text in them. For example, a field may specify “None” or “N/A” when there is no applicable number. If that is the case, it is best to import the field as text, run some queries to move or delete the text data, then convert the original field to a number. This is important for correct sorting and in order to get totals.

Import Specification Dialogue Box
The Dreaded Import Errors Table

4. In fact, it is safest to import all fields as text and convert later. That way you avoid the dreaded “ImportErrors” table! Also, some numbers, such as employee numbers, may begin with a leading zero. Those zeroes will be dropped, if not imported as text, making it more difficult to use these numbers as keys.

5. Take careful notes on what you did to accomplish your purposes in the database, as you may be required to appear in court. Also, notes help you determine if you already tried an action, after several days of head-banging regarding some problem you are attempting to solve.

In short

A database can provide valuable analysis of the data you discover using your computer forensic tool(s).  That analysis can be valuable for investigators as well as the legal team.  If you do not possess the qualifications for working with a database, find someone who can assist you.  Perhaps a DBA could step in and provide this function for your investigation.  You will find the results invaluable when working with Legal.

J. Michael Butler, GCFA Gold #00056, is a Information Security Consultant employed by a fortune 500 application service provider who processes over half of the approximately $5 trillion of residential mortgage debt in the US. He is a certified computer forensics specialist. In addition, he authored the enterprise wide information security policies for his corporation.

Using a Database as a Forensics Tool – Part 1 of 2

What do you do, when your computer forensic tool of choice, Autopsy, EnCase, FTK, etc., helps you to find, say, 40 million data records containing credit card numbers, date of birth, SSN, checking account numbers or similar non-public personal information (NPI)? What if those data are in flat files created by an employee who pulled them from some data source belonging to your organization? What next?

Simulation of “discovered” flat files patterned after an actual case

Query from table 1
Query from table 1
Query from second table
Query from second table

(Data is entirely fictitious.  Any resemblance to real data, living or dead, is purely coincidental)

The legal team wants specifics.  How many unique credit card numbers linked to a name and address were on the laptop?  What are all the fields of data you have found?  Initially, these questions may be asked to validate action against the employee.  But ultimately, the company must report these losses to consumers to whom the information belongs. [1]

Lawyers Want More

They always want more!  They want to know if you can provide a list of names and addresses for mailing the information and exactly what data might have been compromised for each individual.  In one case, a company felt obligated to provide such granularity for every person compromised as these individuals all worked for that company.  An 800 number and help desk may even be set up for employees to call to get more information.

Unfortunately, computer forensic tools were not designed to link flat files and determine what data belongs to whom.  This is where I have found the answer in a database application.  I use Microsoft Access, though any robust database will do the trick, so long as you are familiar with it and can import and format flat files with it.

Very Tedious!

All of the data will likely be scattered throughout numerous files and file fragments.  One file may contain SSN with a Credit Card Number and date of birth.  A second file may have a name and a credit card number.  These two files must be imported and linked together based on the appropriate key, then you can report how many names can be linked with SSN, credit card number and/or date of birth.

For Example…

In the above data that the “perpetrator” delimited with double quote marks, the key in the first table would be the credit card.  The second table also provides a credit card as a key.  So, where we can link the credit card numbers together, we may have more NPI data tied to actual persons’ names.  In this case, name plus SSN, CC and DOB.  Any unique identifier can be a key to tie data together from multiple files or tables.

To further complicate things, file fragments can be located in unallocated space on the hard drive.  The examiner must determine the format of each fragment to make sure it matches the format of the other fragments like it.  Are there any additional fields?  How are the fields delimited; comma, space, tab, quotes, special characters or some combination thereof?  If you have concatenated data fragments, are you sure that the fields have not gone out of phase due to incorrect ordering of the file fragments on the disk?  Sometimes the pieces of files have pieces!  You have to do the best you can to clean them up and fit them together logically without distorting or jumbling the original data of record.

Next post…

I will go over some of the techniques and caveats pertaining to importing flat files into Access.

1 Interactive map and article indicating states with data breach disclosure laws:

2 Article about a national data breach disclosure law:

J. Michael Butler, GCFA Gold #00056, is a Information Security Consultant employed by a fortune 500 application service provider who processes over half of the approximately $5 trillion of residential mortgage debt in the US. He is a certified computer forensics specialist. In addition, he authored the enterprise wide information security policies for his corporation.