Automation of routine work in forensics: extracting file time attributes by list

My name is Maxim Antipov, I am a computer forensic expert and a teacher at CyberEd. In this guide, I will show you how to automate the process of extracting file attributes using a VBA script. We will set up Excel so that it collects data on the size, creation date, modification date, and last access date of files by itself.

Problem

When conducting research on information carriers, it is often necessary to extract the timestamp attributes of files from the file table. These can be parameters such as file size, creation date, or last modification date. Less often, but still, there is a task to extract the date of last access to a file or group of files. If there are few files — a dozen or two — you can leisurely manually transfer the necessary attributes or use special software. Solving this problem is a typical operation that is not worth talking about in detail: an internet search will instantly give you a list of programs for creating file registries (file listers).

However, in practice, computer forensics specialists face tasks that require a deeper approach. For example, what if there are thousands of files? And it is necessary to extract attributes not for all files in the directory, but only for a specific list selected according to some criterion? In such situations, standard utilities for compiling file lists are not always effective.

Examples of such tasks:

1. A list of potentially malicious files detected by antivirus. Sometimes there may be several dozen such files, and they are scattered throughout the file system in the most unexpected places.

2. A list of executable files from the event audit log. Events with codes 4688 and 4689 can provide information about processes running in the system. If Sysmon was installed, its events with code 1 will provide a comprehensive list of executable files. These files need to be checked if the system protection did not detect suspicious actions, but anomalies are observed in the system's operation. In addition, you can extract hashes of executed files, but this is a topic for a separate discussion.

3. A list of documents with specific keywords after indexing. What if there are several thousand such documents and they are scattered across different folders?

4. A list of files selected according to certain criteria. Out of tens of thousands of files, only a few thousand that meet certain criteria need to be extracted. These files may be unevenly distributed throughout the file system.

Standard solutions: disadvantages

You can try to extract the attributes of all files and then write a script to filter by the desired list. I initially thought of going this route, but it is cumbersome and multilayered. First, you need to create a list of selected files in Excel, and then manually add the necessary attributes to the table. This solution is suitable for users whose work is measured in hours at the monitor, but there is a high probability of errors.

We, however, are not ordinary users. We need a more efficient solution — process automation. When the task arises regularly, for example, as in the case of several thousand files selected according to specified criteria, you start to wonder: is it possible to automate this process in Excel? After all, manual copying of attributes is a routine that can and should be avoided. The only question is how to organize this automation.

The solution is to write a VBA script for Excel that will perform three simple tasks:

1. Go through all the cells in the list with file paths.

2. Automatically extract the necessary attributes from the file table.

3. Insert these attributes into the specified cells of the table.

Solution

So, we have an Excel file with a table where the list of required files is already specified in column "A". The task is to extract the following attribute values from the file system for each file:

• Column "B" — file size in bytes.

• Column "C" — file creation date.

• Column "D" — last modification date.

• Column "E" — last access date.

The first step is to determine how many rows the column "A" occupies. This will give us the number of files we need to work with.

To do this, we use the line:

LastRow = Cells.SpecialCells(xlLastCell).Row

Now we need an object to work with files — FileSystemObject. With its help, we can access the file attributes. The variable "i" will serve as a counter in the FOR loop, through which we will go through each cell of column "A", reading the file path, and then extracting its attributes.

To do this, add the following lines:

Set FSO = CreateObject("Scripting.FileSystemObject")
Set File = FSO.GetFile(Range("A" & CStr(i)))

Writing the script

Now let's present the final VBA script with comments for each part. This script will go through all the rows in column "A", check for the presence of the file, and extract the necessary attributes.

Sub ATTR1()
    ' Determine the number of rows in the column of the active sheet
    LastRow = Cells.SpecialCells(xlLastCell).Row

    ' Start the loop through the cells from the first to the last row
    For i = 1 To LastRow
        ' Initialize the FileSystemObject to work with files
        Set FSO = CreateObject("Scripting.FileSystemObject")

        ' Try to access the file whose path is specified in cell "A"
        If FSO.FileExists(Range("A" & CStr(i))) Then
            ' If the file exists, extract its attributes
            Set File = FSO.GetFile(Range("A" & CStr(i)))
            ' Write the file size in bytes to column "B"
            Range("B" & CStr(i)).Value = File.Size
            ' Write the file creation date to column "C"
            Range("C" & CStr(i)).Value = File.DateCreated
            ' Write the file's last modification date to column "D"
            Range("D" & CStr(i)).Value = File.DateLastModified
            ' Write the file's last access date to column "E"
            Range("E" & CStr(i)).Value = File.DateLastAccessed
        Else
            ' If the file is not found, display a message in column "B"
            Range("B" & CStr(i)).Value = "File not found"
        End If
    Next i
End Sub

What this script does

1. Determines the number of rows in column "A" to know how many files need to be processed.

2. FOR loop iterates through each row starting from the first one.

3. FileSystemObject checks if the file exists at the specified path.

4. If the file is found, its attributes are extracted:

   • The file size is recorded in column "B".

   • The creation date is recorded in column "C".

   • The last modification date is recorded in column "D".

   • The last access date is recorded in column "E".

5. If the file is not found, the message "File not found" appears in column "B".

Tests, where would we be without them

We launch the test machine, look for suitable files, and start testing.

We transfer the list of files to Excel.

We open the VBA editor, create a new macro, and insert our script for automating the extraction of file attributes. We try to run it.

The script worked successfully, and we now have a ready table to which we can add headers and transfer it to the report.

For confidence, we open the properties of one of the files and compare the attribute values to ensure they are correctly extracted.

Features of the "Last Accessed" attribute

Tests show that the "Last Accessed" attribute (last access date) is quite unreliable. This explains why it is rarely used. In some cases, this attribute may not be updated, especially if the file was opened in "read-only" mode. If the last access date is not required for the study, it can simply be excluded from the script by commenting out or deleting the corresponding line.

If the files are on a mounted image in "read-only" mode, it makes sense to investigate these files as well by adding their attributes to the table.

Points to Consider

It would seem that the task is solved: attributes are extracted, routine work is done, and you can move on. However, as is often the case, the difficulties do not end there.

1. Checking the success of file reading
   For the stability of the script, it is important to provide for checking the success of reading each file. This is necessary so that the script does not stop when errors occur, for example, if the file is damaged or unavailable.

2. Time zone consideration
   Both Excel and Windows Explorer extract file time attributes based on the current time zone settings. For the average user, this usually does not raise questions. However, for forensic experts, it is important to remember that file systems such as NTFS store timestamps in UTC+00:00 format. Many forensic utilities work with this time or allow you to set the desired time zone in reports. If this point is not taken into account, you can get confused when analyzing timestamps, especially if the devices are in different time zones.

Conclusion

After I started teaching, students often ask the question: do you need to know programming to work as a computer forensic expert? The answer to this question is obvious: such skills are not just useful, they become necessary. This task is an example of how programming helps to solve even routine tasks efficiently, saving time and reducing the likelihood of errors.

In the next article, we will try to take into account the presence of a time zone shift in the attributes and automatically convert the timestamps to UTC+00:00. Ask your questions in the comments and stay tuned for updates — it will be interesting!