This post is part of an ongoing series of videos on how Microsoft Excel can help CJA practitioners (including attorneys, paralegals, investigators, and mitigation specialists) in their CJA cases.
Today’s Post: Filename Lists
When working with discovery, investigative documents, or other case-related materials, it is often helpful to have a list of filenames in an Excel table.
There are times when the government produces to defense counsel digital files where the name of the file indicates something about the file content without a user having to open each file individually.
For example, the government may produce a list of investigative reports in PDF format which, as part of the file name, has the date of the report, the type of report (e.g. FBI 302) and the author. In those instances, it can be beneficial to create a spreadsheet of the filenames and information about the files for later review and organization. Even in instances where the filename is only the Bates number of the file, it can be useful to have a spreadsheet of those numbers.
Microsoft Excel is a useful tool for generating such a filename list. When properly setup, Excel allows users to sort, filter and search for specific files based on different criteria. Fields can be created and associated such as comments, document type, review status, dates and related issues. Additionally, hyperlinks to a specific file or folder can be created for quick and easy access to an item. We will examine these functions in greater detail in future videos.
This video will demonstrate how lists can quickly be created and recommendations to follow when setting up a file list. The video looks at three methods for creating filename lists:
Method 1: Creating a query table by running the “Get Folder Data” process that is currently available in the newer “Office 365” version of Excel.
Method 2: Using the “Copy Path” process available in Windows File Explorer.
Method 3: Using a “File List Program” specifically designed for creating a list of files in Excel format (ex: Directory List and Print).
[Editor’s Note: John C. Ellis, Jr. is a National Coordinating Discovery Attorney for the Administrative Office of the U.S. Courts, Defender Services Office. In this capacity, he provides litigation support and e-discovery assistance on complex criminal cases to defense teams around the country. Before entering private practice, Mr. Ellis spent 13 years as a trial attorney and supervisory attorney with Federal Defenders of San Diego, Inc. He also serves as a digital forensic consultant and expert.]
CJA panel attorneys frequently ask me for strategies for how to manage and review computer forensic images they receive in discovery. It is a great question. Forensic images are often difficult for CJA panel attorneys to access, and they can contain an immense amount of information (often much more than the rest of the discovery production). Without opening them, they already know that a lot of the information in the forensic image is irrelevant. But they also know that often crucial information is in the forensic image that is important for them to know so they can prepare their client’s defense.
Short technical background:
There are two ways data from a computer is provided in discovery:
Duplicates, which refers to “an accurate and complete reproduction of all data objects independent of the physical media”; or
Usually the government provides forensic images. The forensic image is created using specialized software such as opentext EnCase or AccessData Forensic Toolkit (FTK). These forensic images cannot be opened without specialized software. Although there are free viewer programs, such as AccessData’s FTK Imager, which enable users to review the contents of forensic images, the process can be time-consuming and difficult.
Computer Forensic Reports
Isn’t there a better way? Yes, there is. Computer Forensic Reports (there are caveats). But first, why are they important and relevant to you?
Besides the forensic image that the government provides you, they may also provide you something called a Forensic Report (or forensic program generated report). Two common examples for computers will be an EnCase Report or an FTK Report. These reports, generated through the forensic software program, can allow you to see and review the information extracted from the image in a more user-friendly way. This can frequently mean you won’t need to use a forensic image viewer or a computer expert to assist you.
FTK HTML Report
Now these computer forensic reports are not the same as a law enforcement report written by an agent discussing what information was on a computer and describing the evidence they think may be relevant to the criminal investigation. These forensic reports are generated through the forensic tool that was used to examine the data found on the device.
So, the first thing you should do when the government provides a forensic image to you is to ask the government if they have a forensic report as well and request a copy.
Forensic reports are useful because they can make it much easier for a legal professional to review data extracted from the device without having to use a forensic tool. Since most forensic examiners work with law enforcement, they typically create these reports for case agents and prosecutors. The information in the report can include information about documents, images, emails, and web browsing history. These reports often show both the content of a file as well as the metadata (such as the date the document was created). These reports are limited to the data extracted from the original device, the parameters of the forensic program, and the choices made by the forensic examiner.
The forensic reports can be provided in a several formats, including PDF, Excel and HTML. Many forensic tools also include a reader or viewer program that is proprietary to the forensic too, such as Magnet’s AXIOM Portable Case, opentext’s EnCase and AccessData’s FTK also have reader or viewer programs. These forensic reports allow legal professionals to search, review, sort and filter information in ways that can be superior to reviewing the reports in PDF, HTML or Excel formats.
Axiom Portable Case
These reports are valuable and frequently provide most of the information that a legal team will need to understand the contents of a forensic image. It should be noted that forensic reports may not contain all data that was on the original digital device. Therefore, counsel should consider engaging a forensic expert or consultant when he or she does not understand the forensic report or image.
[NOTE: Law enforcement will frequently generate a forensic report after completing an extraction from a mobile device. A common forensic report seen in federal criminal cases is a Cellebrite Reader Report. See the Mobile Forensic Reports post for more details.]
Whether it is on media (CD Rom, USB drive, or hard drive) or through the internet (email or USAfx) it is becoming common practice that discovery files will be “encrypted.” Encryption adds a layer of protection by scrambling the data, so files cannot be seen unless a digital “key” (password) is provided. The goal is to protect the data while it is being shipped in case it is lost or stolen.
“Decryption” is the process of unscrambling an encrypted file so it is readable. The first step you should take when you receive encrypted files is to create a decrypted copy of the files. The decrypted copies will allow you to search, review and work with them on your computer that the encrypted files will not, and you will not need to enter a password each time to open them.
When receiving encrypted case related materials:
Look for cover letters and associated correspondence that mention password protection or encryption. Often the sender will tell you that the files are encrypted and provide instructions on how to obtain the key (password). If the media contains encrypted files you cannot work with them unless you have that password.
Use a Windows computer. Most decryption programs included on the media are designed to work with Windows computers. Sometimes decryption can be done on Mac computers, but often it requires additional software not included with the media.
Insert the media and look for either a “password” prompt or a decryption program. Certain encryption programs (like Microsoft “Bitlocker“) will automatically prompt for a password when the media is inserted. Other times the media will include Windows-based software programs that needs to be run.
Create decrypted copies of the files. When you open a file that is encrypted a computer will typically only temporarily decrypt it. The file may be in a “read-only” mode that will not work well with most software programs and will continue to need a password when reopening. Making a decrypted copy of the file will allow it to be correctly recognized by the programs on your computer and will no longer need a password when opening the copy.
McAfee Removable Media Protection
“McAfee Removable Media Protection” is a common encryption program used by the USA’s when delivering discovery on thumb drives and CD/DVD discs. The media usually includes an executable file that when run will allow users to make decrypted copies of the files. To create decrypted copies:
Create a destination. Open File Explorer (the file browser on your computer) and navigate to a destination on your computer (or external drive) with enough room to hold a copy of the files. Create a folder that will keep the decrypted copy of the files.
Open McAfee. Insert the media and look for a McAfee program executable file (the file is usually called “MfeEERM” and will have the “.exe” extension).
Run the executable and look for a dialog window prompting for a password. Enter the password and click “OK”.
Copy the files or folders. From within McAfee:
Select the “Top Level” folder from the left-hand navigation pane.
From the main window (on the right side), select all the files and folders listed, right-click on them and choose “Copy”.
Paste the copies into the destination. Switch back to File Explorer. Right-click on an empty space within the destination location and choose “Paste”. For larger sets of data (over 10,000 files/folders), try dividing the copy process into smaller batches of about 1,000 files / folders each. Verify the copied files can be opened by closing McAfee and opening a few of the copied files.
Here is a quick video demonstration of the process:
To assist federal defender offices and CJA panel attorneys who need to share and transfer e-discovery in their cases, the National Litigation Support Team (NLST) has obtained (“cloud”) space from Box.com for the short-term storage and transfer of data.
Box.com is a simple cloud-based collaboration program that allows users to store, access, share, and transfer electronic files and documents. The service encrypts all data and has additional security features. Users can store an unlimited number of files, for their own use or to share with others, without having to use remote access to office computers. Defense teams can use different devices (such as computers, tablets, or smartphones) to access case data anywhere they can connect to the internet. This allows CJA panel attorneys to share discovery and work product easily and efficiently in a secure environment.
Box.com is being used by the Department of Justice (DOJ) as their cloud service to distribute e-discovery to the defense. DOJ evaluated it against other similar products and concluded it best met their security standards.
Box.com is committed to ensuring that your data will remain as secure as possible, and providing strong customer support. They have worked closely with the NLST in designing a cloud service that effectively addresses CJA counsels’ growing problem of moving and sharing large volumes of data. The NLST will work directly with each defense team to set up their cloud case folders, and to provide ongoing support of their use of Box.com.
The NLST will manage:
creating case folders to hold electronic information on a case in the cloud,
inviting team members (“collaborators”) to help them get access to the cloud data, and,
granting rights of different team members to get into specific folders.
Because cloud contracts like this store case information on servers owned by Box.com, attorneys remain ultimately responsible for the use of this service. Before using it, CJA members should review their local bar opinions regarding the use of cloud computing and storage.
Once approved, the NLST will send you a form asking for the case details including who will serve as the “point of contact” for each defense team, and who on the team should be given access to the what files that have been stored on the cloud. Note that additional team members can be added later. The NLST will set up a short session to show all those who will use this cloud service how to navigate the system, and how to upload and download data. The NLST will be the team’s first point of contact if there are any questions about using Box.com, technical questions, or any concerns regarding using this
cloud-based case information repository.
Please note that Box.com does not offer advanced e-discovery features found in online document review programs such as Relativity, Summation, or Catalyst. It does not have a database and other advanced tools for organizing, reviewing, and analyzing e-discovery. Rather, its purpose is for short-term storage and transfer of information in the “cloud.”
When the case has concluded, (or sooner if counsel no longer needs this service), the CJA lawyer must delete all case materials from Box.com. The NLST will help ensure the case files are deleted, and the case is properly closed. Counsel should always maintain a copy of all files on their office computer system (besides the information stored in the cloud), as only duplicate files should be stored on Box.com.
Below are some answers to Frequently Asked Questions (FAQ’s) in regards to this service:
What is the difference between Box.com and Dropbox?
Box.com and Dropbox are both cloud based repositories. The Department of Justice is using Box.com, renamed USAfx, to distribute discovery to defense counsel in many districts. Since the DOJ has approved of the security protocols of Box.com, we felt that it would be helpful to make Box.com available to federal defender offices and CJA panel attorneys on a national level. For that reason, the National Litigation Support Team (NLST) has a national contract with Box.com and not with DropBox. The NLST assists in creating and managing case folders on Box.com for the sharing of work product and discovery but we do not support the use of DropBox in any way.
Since USAfx is just Box.com rebranded, can I use my USAfx user ID to log in to a case folder that I have asked the NLST to create on Box.com?
Unfortunately, no. Your user ID and password for USAfx is unique to USAfx and will only work on USAfx. You will need to set up a regular Box.com account and use that user ID and password to access any case folder created by the NLST.
Every person invited to work within a folder on Box.com is known as a collaborator. Each collaborator needs to have their own Box.com account and needs to be invited to the folder by the NLST. If you receive an invitation to collaborate on a folder and you don’t have a Box.com account yet, you will first need to set one up.
Can I invite other users to collaborate on a case folder myself?
Only the NLST can invite collaborators to a folder to ensure that only those who should have access to a folder are granted access.
We have an expert on our case. Can we give them access to just a specific folder under our case folder on Box.com
Box.com works well for sharing a subset of information with an expert. Each sub-folder can have a different set of collaborators so you can set up a folder that only you and your expert can access.
Can access to a folder be limited to “read only” for certain users?
Each person invited to collaborate on a folder can be set up with their own unique permission level. The permission levels options for Box collaborators are:
How do I setup a Box.com account?
To set up a free, personal Box.com account, which is all you need to access any case folder created by the NLST, simply go to https://app.box.com/signup/n/personal and follow the instructions.
Can I access my Box.com folder on my phone or tablet?
Box.com is mobile device friendly. You can download the Box app to your phone or tablet and access your folders and documents using the same log in credentials you do on Box.com when sitting at your computer.
Why am I being asked verify my account with a text code?
We want to make sure that the data being shared is done so in a secure way. Asking for a text code in addition to your user name and password is one way of ensuring that the person who is logging in is in fact the person authorized to see the data. This two factor authentication process is just one of the many security measures that makes Box.com a safer way to transfer data between legal teams, clients and experts.
How do I upload items?
There are two ways to upload items into your case folder. You can either (1) drag and drop a file or folder from your computer into the folder or (2) click on the “Upload” button at the top of the page and browse to the filer or folder you want to upload.
How do I download items?
There are two ways to download items into your case folder. You can either (1) right click on the file or folder and choose the download option or (2) click on the ellipses […] next to the file or folder and choose download. Folders are downloaded as .zip files so you have to extract the files to your computer once the download is complete.
Can I get notified when another collaborator adds or deletes documents from a folder?
You can set your user preferences to receive email notifications when another collaborator downloads, uploads, makes comments, previews or deletes items from your case folder. Click on the down arrow next to your name and select account settings. Then click on Notifications along the menu bar. From there, you can select when you receive email notifications based on the actions of other collaborators.
How do I setup a sub-folder within a case folder?
If you have a folder on your computer that you want to make a sub-folder in your Box.com case folder, drag and drop the folder from your computer into your case folder. If you want to create a new sub-folder, click on the “New” button and a sub-folder will appear.
What happens when something is deleted?
Items that are deleted are moved to your Box.com Trash folder. Deleted items will stay in the Trash folder for 90 days, during which time you can go into your Trash folder and restore those items to your case folder. After 90 days, they will be permanently deleted.
Is there a maximum amount of data that I can use Box.com to share? What if I have 75 gigs or 1 terabyte?
There is no limit to the number of files or folders that can be shared on Box.com. For most users, there is a 250MB per file upload limit. If you need to upload files larger than 250MB, contact the NLST for assistance.
How do I edit a Microsoft Office document that has been shared on Box.com and track each version on Box.com?
Collaborators can use Box Edit to make changes to Microsoft Office documents. The changes will be saved directly back to Box.com along with access to prior versions of the document (see: https://app.box.com/services/box_edit for details and requirements).
Why is “NLST Admin” the Owner of the folder I requested to be created?
The NLST has a national contract with Box.com and is responsible for the creation and management of case folders in order to ensure sure that the appropriate security settings and collaborator permissions are used. We are responsible for the security of our hosted space on Box.com and we want to make sure that nobody is accidentally allowed access to any case data.
Can I use Box.com to store old case files?
While your personal Box.com space can be used for any purpose, the case folders set up on Box.com by the NSLT is not designed for the storage of old files long term. Case folders are meant for the short term sharing and transfer of files and to allow for teams to collaboratively edit documents while tracking each version.
Editor’s Note: Penny Marshall is currently in private practice, focusing on Law and Technology. Previously she was the Federal Defender for the Federal Public Defender Office for the District of Delaware. Her practice has also included the federal and local level in the District of Columbia and a year and a half stint in the state of Georgia. She has served as President of the Association of Federal Defenders and Chair of the Third Circuit Lawyers Advisory Committee. In addition, she is an adjunct faculty member at Widener Law School and has served as guest faculty at both Harvard Law School and Benjamin Cardoza School of Law.
Imagine that the government has provided you with 50 DVD’s, a stack of paper amounting to more than a 100,000 documents, an ample number of CD’s and a list several hundred witnesses. If you instinctively start to prepare by hiring enough paralegals to print out all of documents on the DVD’s, put them all in manila folders, and then hope that you or your smart energetic personnel will remember, in the middle of cross-examination, exactly where a particular impeaching statement is located, then this blog is certainly for you.
Unfrozen Caveman Lawyer
Even in the less complex cases, there is increasing reliance by prosecutors on digital discovery rather than forwarding a stack of reports and pictures. And certainly the video and audio of our clients providing visual and audio support for the government case will be represented in a digital fashion.
In the new technological age more and more the government is able to “over paper” a case by putting any and all documents on electronic media and challenge YOU to find what is truly relevant. More and more the government is following the way of our civil counterparts, who have long used technology as a way to organize and present their case. We, as defense lawyers are prime to catch up.
At different stages of litigation there are several advantages to the use of technology:
Generally, the first advantage is that technology allows all of your information to be stored and organized in a compact easy to find location. Almost gone are the days of moving numerous boxes from one location to the other to be copied and filed.
The next advantage is that the digital approach allows for your documents to be searched, either by looking in the digital file or by a program that blitzes through numerous documents to find one name or one crucial word. Tiny print, upside down lettering and even handwriting can be deciphered.
A third advantage is that technology is a less costly way of presenting evidence. For example: compare for example a FBI model versus using a computer program to reconstruct a crime scene. Also think of the flexibility!
Fourth, technology organization requires you to focus on your case in advance. Rather than place the paper in an accordion file and bringing it out close to trial, electronics says you must consider the parts of the case in advance.
The fact that we are in a visual age cannot be understated. TV, Text, Laptops, PCs, Phones, Tablets all require us to stare at electronic screens. Each of these compete for our attention by making more and more exciting bells and whistles. Check out the lines in front of an Apple store once a new “iDevice” is revealed.
Lining up for new technology
Even though jury duty is a diversion from the normal life for our citizenry, many jurors are regular consumers who expect theatrics in the courtroom. I must admit that, at first, I went kicking and screaming that I was not fully comfortable with tech in the courtroom, but having tried complex cases where it was an absolute necessity and experienced the impact of it in even the more modest case, I am an absolute convert. Think about it, even if you are one of the great lawyers of the day, jurors may tire of your voice in a long case with significant documents, especially if you are asking the Court’s indulgence to find your trial evidence!!
We all have our favorite lines from the Quentin Tarantino movie, Pulp Fiction. Mine is from the scene where Winston Wolfe, played by Harvey Keitel, arrives to clean up a mess caused by the accidental discharge of John Travolta’s handgun. As lawyers, we’re called upon to “solve problems” and help clean up messes. For me, it includes addressing how to handle terabytes of data that may include hundreds of thousands of pages of documents, tens of thousands of emails, hundreds of email attachments, tens of thousands of wire taps, body wires, GPS longitude and latitude data, hundreds of photos and many hours of video.
But as investigative methods become more sophisticated, so do the means to cull through and organize massive amounts of discovery. Picking the right tool is the key to “solving problems”. It might mean creating sortable spreadsheets or retaining the services of state-of-the art web-based document repositories.
For example, on multiple-defendant drug cases, we recommend using Excel spreadsheets to create sortable indices. One of the spreadsheets is for the line sheets and corresponding wiretap audio files. The other spreadsheet is for the remainder of the discovery and can include documents, photos, videos and body wire recordings. Counsel can sort by defendant name, date, call number or any other subject for which we have entered information. Each discovery item is hyperlinked to the spreadsheet; just sort down to a particular grouping and click on the hyperlink. The document displays, audio plays or photograph opens.
For fraud cases, which often include hundreds of thousands of pages of documents including emails, discovery can be hosted and accessed using an online document database. Multiple defense team members can access, search, sort and identify documents simultaneously using sophisticated search features. Online database programs have capacities to manage huge amounts of discovery – far greater than any desktop application. They also have features to help find key documents, tag them for their importance and even save them for later review.
I can help solve your problems. I am a CJA Panel Attorney in Seattle, Washington. I am under contract with the Administrative Office of U.S. Court, Office of Defender Services as a Coordinating Discovery Attorney (“CDA”) to support your work on multi-defendant prosecutions involving large amounts of discovery. My job is to help you strategize and implement ways to use technology to create cost effective ways to better represent clients in massive discovery cases for CJA panel attorneys and FDO staff across the country.
I evaluate each lawyer’s level of computer sophistication; identify the types of discovery involved; assist in determining how best to distribute the discovery; determine what technology and other resources are necessary for discovery review and management; and help in maintaining quality control of the discovery review process.
I focus on a limited number of cases each year that have been identified by the National Litigation Support Team (“NLST”) as needing a CDA, whether due to the complexity of the matter, the number of parties involved, or the nature and/or volume of the discovery. After an initial consultation with the NLST, and a second one with me, a decision will be made about the use of my services.
The factors that are considered in determining whether a CDA should work on a particular case are:
Whether the number of co-defendants is so large as to create a risk of costly duplicative efforts, which could otherwise be eliminated or reduced upon the appointment of a CDA, or whether there are other factors that create a likelihood that the CDA’s participation would enable costs to be contained;
Whether the volume of discovery is so large that addressing the Organizational needs in the case would interfere with defense counsel’s ability to address the legal and factual issues in a case;
Whether unusual organizational or technological issues exist, not commonly found even in complex cases, that would interfere with defense counsel’s ability to address the legal and factual issues in a case;
Whether the case is prosecuted in a region that lacks experts who can provide necessary technology support and document management expertise in addressing the factors described above;
Whether the timing of the request, which preferably should be made early in a case, is such that the CDA’s participation is likely to be of assistance to defense counsel, promote efficiency, and contain costs; and,
The CDA’s workload.
All these factors need not be present. Any final determination will be made by the National Litigation Support Administrator. In determining how much weight to provide each factor, the seriousness of the alleged offense will be factored into any decision.
If approved, CJA panel counsel then petitions the court for my appointment. By having the court appoint, I will have standing to confer directly with the prosecution on issues of discovery, which allows for better coordination and overall cost-efficiencies regarding information exchange. I will examine the discovery and propose a plan of action. If counsel agrees, we’re on our way. If outside services are necessary, the proposed services of vendors will be evaluated and competitive price quotes obtained. I will recommend to the court the proposed strategy and petition for the necessary funds. Throughout the project, work will be monitored to make sure it is being performed properly and in an expeditious manner.
Russell M. Aoki,
Coordinating Discovery Attorney
If you have any questions regarding the services of a CDA, please contact either: