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 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.
Here are some lessons learned while importing data in Microsoft Access:
- 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 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.
- 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.
- 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.
- 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.