retrieving temporary file attributes from a list

Imagine: you have several thousand files, and for each you need to extract metadata – creation dates, modifications and last access. You can, of course, sit and manually copy this data from Explorer. One file, another… After an hour of work, my head is already swimming, and there are still hundreds of files ahead. But all this can be avoided.

My name is Maxim Antipov, I am a computer forensics specialist and teacher at CyberEd. In this tutorial, I'll show you how to automate the process of extracting file attributes using a VBA script. We will configure Excel so that it itself collects data about the size, date of creation, modification and last access of the files.

Problem

When conducting storage media research, it is often necessary to extract file attribute timestamps from a file table. These may be parameters such as file size, date of creation or last modification. Less often, but still, the task of retrieving the date of last access to a file or group of files arises. If there are only a few files—a dozen or two—you can manually transfer the necessary attributes without haste or use special software. The solution to this problem is a typical operation, which is not worth discussing in detail: a search on the Internet will instantly produce a list of programs for creating file listers.

However, in practice, computer forensics specialists face tasks that require a more in-depth approach. For example, what if there are thousands of files? And you need to extract attributes not for all files in a directory, but only for a specific list selected by some criterion? In such situations, standard utilities for compiling file lists are no longer always effective.

Examples of such tasks:

1. List of supposedly malicious files detected by the antivirus. Sometimes there can be several dozen such files, and they are scattered throughout the file system in the most unexpected places.

2. List of executable files from the event audit log. Event codes 4688 and 4689 can provide information about processes running on the system. If Sysmon was installed, its event code 1 will provide a comprehensive list of executable files. These files require scanning if system protection has not detected any suspicious activities, but anomalies are observed in the operation of the system. 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 to do if there are several thousand such documents and they are scattered in different folders?

4. A list of files selected based on certain criteria. Out of tens of thousands of files, only a few thousand need to be extracted that meet certain criteria. These files may not be evenly 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 about going this route, but it's cumbersome and multi-layered. 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 behind the monitor, but there is a high probability of errors.

We are, however, not ordinary users. We need a more efficient solution – process automation. When a task arises regularly, for example, as is the case with several thousand files selected according to given criteria, you begin to wonder: is it possible to automate this process in Excel? After all, manually copying attributes is a chore 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. Iterates through all cells of the list with file paths.

2. Automatically extracts the required attributes from the file table.

3. Inserts these attributes into the specified table cells.

Solution

So, we have an Excel file with a table, where column “A” already contains a list of required files. The task is to extract the values ​​of the following attributes from the file system for each file:

• Column “B” is the file size in bytes.

• Column “C” is the date the file was created.

• Column “D” is the date of the last modification.

• Column “E” is the date of last access.

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

To do this we use the line:

LastRow = Cells.SpecialCells(xlLastCell).Row

Now we need an object for working with files – FileSystemObject. With its help we can access file attributes. The variable “i” will serve as a counter in the FOR loop where we will iterate through each cell in 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 a script

Now we 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 desired attributes.

Sub ATTR1()
    ' Определяем количество строк в столбце активного листа
    LastRow = Cells.SpecialCells(xlLastCell).Row
    
    ' Запускаем цикл перебора ячеек от первой до последней строки
    For i = 1 To LastRow
        ' Инициализируем объект FileSystemObject для работы с файлами
        Set FSO = CreateObject("Scripting.FileSystemObject")
        
        ' Пробуем получить доступ к файлу, путь к которому указан в ячейке "A"
        If FSO.FileExists(Range("A" & CStr(i))) Then
            ' Если файл существует, извлекаем его атрибуты
            Set File = FSO.GetFile(Range("A" & CStr(i)))
            ' Записываем размер файла в байтах в столбец "B"
            Range("B" & CStr(i)).Value = File.Size
            ' Записываем дату создания файла в столбец "C"
            Range("C" & CStr(i)).Value = File.DateCreated
            ' Записываем дату последней модификации файла в столбец "D"
            Range("D" & CStr(i)).Value = File.DateLastModified
            ' Записываем дату последнего доступа к файлу в столбец "E"
            Range("E" & CStr(i)).Value = File.DateLastAccessed
        Else
            ' Если файл не найден, выводим сообщение в столбец "B"
            Range("B" & CStr(i)).Value = "File not found"
        End If
    Next i
End Sub

What does this script do

1. Defines the number of lines in column “A” to know how many files are to be processed.

2. FOR Loop goes through each line, starting from the first.

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

4. If the file is foundits attributes are extracted:

   • The file size is entered in column “B”.

   • The creation date is in column “C”.

   • The date of the last modification is in column “D”.

   • The date of last access is in column “E”.

5. If the file is not foundthe 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.

Transferring the list of files to Excel.

Open the VBA editor, create a new macro and insert our script into it to automate the extraction of file attributes. Let's try to launch it.

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

To be sure, open the properties of one of the files and check the attribute values ​​to make sure that they are extracted correctly.

Features of the “Last Accessed” attribute

The tests performed show that the attribute “Last Accessed” (date of last access) 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 research, 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 examine these files too, adding their attributes to the table.

Points to consider

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

1. Checking whether files were read successfully
   For the stability of the script, it is important to ensure that each file is read successfully. This is necessary so that the script does not stop when errors occur, for example, if the file is damaged or inaccessible.

2. Accounting for time zones
   Both Excel and Windows Explorer retrieve temporary file attributes based on the current time zone settings. For the average user, this usually does not raise any questions. However, it is important for forensic scientists 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 configure the desired time zone in reports. If you do not take this point into account, you can get confused when analyzing timestamps, especially if the devices are in different time zones.

Conclusion

Since I started teaching, students often ask: Do you need to know programming to work as a computer forensics specialist?m? The answer to this question is obvious: such skills are not just useful, they are becoming necessary. This task is an example of how programming helps solve even routine tasks efficiently, which saves time and reduces 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 timestamps to UTC+00:00. Ask your questions in the comments and stay tuned for updates – it will be interesting!