Posted on August 31, 2016 · Posted in Powershell, Windows Server 2012 R2

Tracking Files Deletion using Audit Policy and MSSQL

Any Windows administrator has come across the situation when angry users want to know who has deleted a very important file of the annual report from the share on the file server. This information can be obtained only if you have enabled the audit of the deleted files and folders on your file server, otherwise you will only have to recover the file from the backup (are you making it? ; ) ).

However, even if the audit of the deleted files is enabled, it can be troublesome to find something in the logs. Firstly, it is quite hard to find a specific entry among thousands of events (in Windows there are no convenient means to search an event with a flexible filter), secondly, if a file was deleted a long time ago, this event may be absent in the logs, since it was overwritten by more recent events.

In this article, we’ll show how to make a simple system to audit the deleted files and folders on a  network share (on a file server) using the integrated Windows tools so that the events are written to the separate MySQL database.

With the help of the database containing the information about all deleted files, the administrator can answer the following questions:

  • Who deleted the file and when it happened
  • What application was used to delete the file
  • What is the date of the backup to be restored

First of all, you should  enable audit object access  and track file deletion  events on Windows file server. The audit can be enabled in gpedit.msc console using the general policy Audit Object Access in Security Settings -> Local Policy -> Audit Policy section.

Audit File System - GPO

Or (preferably) using the advanced audit GPO : Security Settings -> Advanced Audit Policy Configuration -> Object Access -> Audit File System.

GPO - Audit File System

Tip. Audit is resource-consuming. Use it carefully, especially in case of heavy loaded file servers.

In the properties of the network share (Security -> Advanced -> Auditing), in which we want to track the deleted files, enable the audit of deleted files and folders (Delete subfolders and files) for Everyone.

Audit file and folder deletion

Tip. The audit of deleted files in the specific folder can also be enabled using PowerShell:

$Path = "E:\Public"
$AuditChangesRules = New-Object System.Security.AccessControl.FileSystemAuditRule('Everyone', 'Delete,DeleteSubdirectoriesAndFiles', 'none', 'none', 'Success')
$Acl = Get-Acl -Path $Path
$Acl.AddAuditRule($AuditChangesRules)
Set-Acl -Path $Path -AclObject $Acl

If a file has been deleted successfully, Event ID 4663 from Microsoft Windows security auditing appears in the security log. There is the information about the name of the deleted file, the account, which has deleted it, and the process name in the description.

Event ID 4663

So, the necessary events are logged, and it’s time to create a table on MySQL server with the following columns:

  • Server name
  • Name of the deleted file
  • Date and time
  • Name of the user who has deleted the file

The MySQL query to create this table looks like that:

CREATE TABLE deleted_items (id INT NOT NULL AUTO_INCREMENT, server VARCHAR(100), file_name VARCHAR(255), dt_time  DATETIME, user_name VARCHAR(100),  PRIMARY KEY (ID));

Note. An example  of how to  access a MySQL database with PowerShell  was considered earlier  in the article Querying MySQL from Powershell.

Here is the script to collect the information from the event log. We are filtering the log by the event with ID 4663 for the current day.

$today = get-date -DisplayHint date -UFormat %Y-%m-%d
Get-WinEvent -FilterHashTable @{LogName="Security";starttime="$today";id=4663} | Foreach {
$event = [xml]$_.ToXml()
if($event)
{
$Time = Get-Date $_.TimeCreated -UFormat "%Y-%m-%d %H:%M:%S"
$File = $event.Event.EventData.Data[6]."#text"
$User = $event.Event.EventData.Data[1]."#text"
$Computer = $event.Event.System.computer
}
}

Windows Security audit - event 4663 properties

The next script will write the data you get to the MySQL database on a remote server (10.1.1.13):

Set-ExecutionPolicy RemoteSigned
Add-Type –Path ‘C:\Program Files (x86)\MySQL\MySQL Connector Net 6.9.8\Assemblies\v4.5\MySql.Data.dll'
$Connection = [MySql.Data.MySqlClient.MySqlConnection]@{ConnectionString='server=10.1.1.13;uid=posh;pwd=P@ssw0rd;database=aduser'}
$Connection.Open()
$sql = New-Object MySql.Data.MySqlClient.MySqlCommand
$sql.Connection = $Connection
$today = get-date -DisplayHint date -UFormat %Y-%m-%d
Get-WinEvent -FilterHashTable @{LogName="Security";starttime="$today";id=4663} | Foreach {
$event = [xml]$_.ToXml()
if($event)
{
$Time = Get-Date $_.TimeCreated -UFormat "%Y-%m-%d %H:%M:%S"
$File = $event.Event.EventData.Data[6]."#text"
$File = $File.Replace(‘\’,’|’)
$User = $event.Event.EventData.Data[1]."#text"
$Computer = $event.Event.System.computer
$sql.CommandText = "INSERT INTO deleted_items (server,file_name,dt_time,user_name ) VALUES ('$Computer','$File','$Time','$User')"
$sql.ExecuteNonQuery()
}
}
$Reader.Close()
$Connection.Close()

Now, to find out who has deleted the file “document1 — Copy.DOC“, it is enough to run the following script in PowerShell:

$DeletedFile = "%document1 - Copy.DOC%"
Set-ExecutionPolicy RemoteSigned
Add-Type –Path ‘C:\Program Files (x86)\MySQL\MySQL Connector Net 6.9.8\Assemblies\v4.5\MySql.Data.dll'
$Connection = [MySql.Data.MySqlClient.MySqlConnection]@{ConnectionString='server=10.1.1.13;uid=posh;pwd=P@ssw0rd;database=aduser'}
$Connection.Open()
$MYSQLCommand = New-Object MySql.Data.MySqlClient.MySqlCommand
$MYSQLDataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter
$MYSQLDataSet = New-Object System.Data.DataSet
$MYSQLCommand.Connection=$Connection
$MYSQLCommand.CommandText="SELECT user_name,dt_time    from  deleted_items where file_name LIKE '$DeletedFile'"
$MYSQLDataAdapter.SelectCommand=$MYSQLCommand
$NumberOfDataSets=$MYSQLDataAdapter.Fill($MYSQLDataSet, "data")
foreach($DataSet in $MYSQLDataSet.tables[0])
{
write-host "User:" $DataSet.user_name "at:" $DataSet.dt_time
}
$Connection.Close()

We can see the user name and the time when the file was deleted.

select file deletion event from mysql db

Note. Since an issue has been found that the symbol “\” is not written to the database, we have replaced it for “|”. So if you have to display the full path to the file, when extracting back from the database, you can replace it back $DataSet.file_name.Replace(‘|’,’\’).

The script of writing the information from the log to the database can be run at the end of the day using Task Scheduler or attach to the event of deletion (On Event), which is more resource-consuming. It depends on the requirements to the system.

Tip. Make sure that the security log has enough space to log all events per day. Otherwise, you will have to run the task of exporting the data to the database more often than once a day or even on the trigger. As a rule, the Maximum Log Size on the workstations has to be at least 64 MB, and 262 MB on the servers. Enable the overwrite option (Overwrite events as needed).

If necessary, you can create a simple web-page on PHP  to get the information about the users who have deleted files in a more convenient form.

Important tip. If there is an entry of deleting a file by a user in the log, do not hurry to interpret it as a deliberate or malicious action. Many programs (especially MS Office) create a temporary file, save the document to it and then delete the old version of the file. In this case, logging the name of the process (ProcessName column), with which the file has been deleted, makes sense, and you can analyze the events on the base of it. Or you can filter the events from such processes, like winword.exe, excel.exe, etc.

So, we have suggested an idea and the general model of the system to audit and store the information about the deleted files in the network shares, and if needed, it can easily be modified to meet your requirements.

Previous:
Next:
Related Articles