Alert Summaries

Free Trial

How to create a Summary   (Jump to Predefined Summaries)

Creating a Summary is one of the more complicated aspects of configuring FileSure. I hope to explain how to do it.

Summaries are SQL select statements that are executed by the FileSure service after every consolidation phase and published on named pipe to be consumed by the alerting engine.

The FileSure data store is based on the public domain SQLite SQL database engine, so Summary queries are pretty close to standard SQL format, but there are some small differences. In most cases, these differences won’t be noticed.

While it is possible to enter any valid select statement into a query definition, to be a summary the first return value must be a number, typically this is the threshold value.

For example:

select count(*) Count from AuditingRecords;

would be a valid, albeit not very useful, summary. This summary would just count up all the records.

Here is a more useful summary:

select count(exeName) Count, exeName from AuditRecords where exeName <> "" group by Upper(exeName) order by Count desc;

This summary would return a list of programs used to access audited files and how many times they were used. While more useful, still not very useful since we don’t have a time constraint so the counts wouldn’t be quantitative.

Up to this point, things make sense since everything is standard SQL.

But we need to set how much data we need; for this we use the built-in variable OldestRecordAge, like this:

‘and eventTime > OldestRecordAge’

We use the eventTime to limit the dataset that is processed. When the summary is processed by the FileSure service, OldestRecordAge will be replaced with eventTime of the first record that should be processed.

Here is the final statement:

select count(exeName) Count, exeName from AuditRecords where exeName <> "" and eventTime > OldestRecordAge group by Upper(exeName) order by Count desc;

You can add other fields to be published as part of the summary that might be of interest. In the above example, I was also interested in the name of the program that was used when the file was audited.

 

Skip to this section:



Useful Summaries

  • Bulk copy size

    This summary will pick up the most common ways that a file is copied. Windows Explorer, XCopy and Cmd.

    SELECT sum(fileSize) totalSize, username from [AuditRecords] where majorFunction = 3 and (Upper(exeName) = 'EXPLORER.EXE' or Upper(exeName) = 'CMD.EXE'or Upper(exeName) = 'XCOPY.EXE') and upper(extension) <> "LNK" and upper(extension) <> "db" and eventTime > OldestRecordAge group by username

  • Files copied

    This summary will pick up the most common ways that a file is copied. Windows Explorer, XCopy and Cmd.

    SELECT count(distinct Upper(filename)) fileCount, username from [AuditRecords] where majorFunction = 4 and (Upper(exeName) = 'EXPLORER.EXE' or Upper(exeName) = 'CMD.EXE'or Upper(exeName) = 'XCOPY.EXE') and upper(extension) <> "LNK" and upper(extension) <> "db" and eventTime > OldestRecordAge group by username

  • Printed documents

    This summary can be used if you want to alert whenever a document is printed.

    select 1, eventTime, eventText from Events where eventText like '%has printed%' and eventTime > OldestRecordAge

  • Number of files read

    Bulk file reads occur when a users do a bulk copy or a 'find in files.' Whatever the reason, this is a nice little summary that you can use to set up an alert for when a reads a bunch of files.

    SELECT count(distinct Upper(filename)) fileCount, username from [AuditRecords] where majorFunction = 3 and isDirectory = 0 and eventTime > OldestRecordAge group by username

  • Number of files opened grouped by user

    Probably one of common summaries is to know how many files each user has opened. This summary is useful for detecting bulk read operations, like when someone copies an entire folder.

    SELECT count(distinct Upper(filename)) fileCount, username FROM [AuditRecords] where majorFunction = 0 and isDirectory = 0 and and eventTime > OldestRecordAge group by username

  • Number of files deleted

    Bulk file deletes rarely occur unless performed by an administrator or a disgruntled employee.
    Whatever the reason, this is a nice little summary that you can use to set up an alert for when a user deletes a bunch of files.

    SELECT count(distinct Upper(filename)) fileCount, username from [AuditRecords] where majorFunction = 18 and isDirectory = 0 and eventTime > OldestRecordAge group by username

  • Detailed summary by Alert ID

    This summary is useful for alerting on a rule match.
    Change the ‘alertID = 10’ in the SQL below to match what is defined in the rule.

    select 1 count, fileName, userName from AuditRecords where alertID = 10 and eventTime > OldestRecordAge

  • Denied Operations

    This summary is useful for detecting when someone is attempting to do something that FileSure was configured to block, for example someone attempting to stealing files or installing MalWare.

    select 1 count, userName, fileName from AuditRecords where extension <> "" and eventTime > OldestRecordAge and deniedOp = 1

  • Files being sent by web mail.

    This summary will list every instance of a protected file being read by either Internet Explorer or FireFox. Protected data being read by a internet browser typically means that someone is stealing data.

    Select 1, * from AuditRecords where eventTime > OldestRecordAge and ((lower(exeName) = 'iexplore.exe' or lower(exeName) = 'firefox.exe' or lower(exeName) = 'chrome.exe') and deniedOp = 0)

  • Files written to a removable drive

    This summary will list every instance of a protected file being written to a removable drive.

    Select 1, * from AuditRecords where eventTime > OldestRecordAge and (majorFunction = 0 and WriteAccess = 1 and driveType = 2 and deniedOp = 0) or (majorFunction = 4 and driveType = 2 and deniedOp = 0);

  • Summary for subfolder moves

    This summary is useful for getting alerts based on folder moves. This summary will only pick up subfolders moves.
    The SQL makes use of the column OperationalContext, this column is a catch-all field that FileSure uses to store additional information about the operation…in this case, OperationalContext is used to determine a rename vs a move.

    select 1 Count, UserName, Filename, renameTo from AuditRecords where isDirectory = 1 and (MajorFunction = 18 or MajorFunction = 6) and ((FileName not like '%\%' and FileName like '%\%\%') or (renameTo not like '%\%' and renameTo like '%\%\%')) and (operationContext & 1 = 1) and eventTime > OldestRecordAge;

  • Summary for ANY folder moves

    This summary is useful for getting alerts based on any folder moves.
    The SQL makes use of the column OperationalContext, this column is a catch-all field that FileSure uses to store additional information about the operation…in this case, OperationalContext is used to determine a rename vs a move.

    select 1 Count, UserName, Filename, renameTo from AuditRecords where isDirectory = 1 and (MajorFunction = 18 or MajorFunction = 6) and (operationContext & 1= 1) and eventTime > OldestRecordAge;

  • File Creation Count

    Many administrators are constantly battling users chewing up disk space on the server by doing crazy things like, copying their personal music library to their homeshare or copying a entire share to another folder, so "it'll be easier to find."
    Whatever the reason, this is a nice little summary that you can use to set up an alert for when a user creates a bunch of files.

    SELECT count(distinct Upper(filename)) fileCount, username FROM [AuditRecords] where majorFunction = 0 and isDirectory = 0 and wasCreated = 1 and eventTime > OldestRecordAge group by username

  • Reading Someone Else’s Home Share.

    This summary can be used when someone, other than the home share owner, reads a home share. Very handy if you have a multiple Domain Administrators.
    It is a little more complicated since it requires you to change the query to reflect your setup.
    You'll need to change DOMAIN to be user domain name and you'll need to change 'C:\HOME' to be wherever your home shares are.
    First set up a rule to audit reads on the 'C:\home\*' for all users (*)
    Then create a summary with the following SQL:

    select 1, fileName, username from AuditRecords where filename like 'C:\HOME\%' and userName like 'DOMAIN\%' and not lower(SubStr(userName,length('DOMAIN\') + 1 ,length(username) - length('DOMAIN\') )) = lower(SubStr(filename, length('C:\HOME\') +1, length(SubStr(userName,length('DOMAIN\') + 1 ,length(username) - length('DOMAIN\'))))) and eventTime > OldestRecordAge;

  • Root Folder Moves

    This summary is useful for getting alerts based on folder moves. Typically, only root folders moves are interesting since subfolder moves are under the purview of the folder owner.
    The SQL makes use of the column OperationalContext, this column is a catch-all field that FileSure uses to store additional information about the operation…in this case, OperationalContext is used to determine a rename vs a move.

    select 1 Count, UserName, Filename, renameTo from AuditRecords where isDirectory = 1 and (MajorFunction = 18 or MajorFunction = 6) and ((FileName like '%\%' and FileName not like '%\%\%') or (renameTo like '%\%' and renameTo not like '%\%\%')) and (operationContext & 1 = 1) and eventTime > OldestRecordAge;

  • Extension Summary by User

    select count(extension) Count, userName, extension from AuditRecords where eventTime > OldestRecordAge group by Upper(extension), Upper(userName) order by Count desc;

  • Program summary

    select count(exeName) Count, exeName from AuditRecords where exeName <> "" and eventTime > OldestRecordAge group by Upper(exeName) order by Count desc;

  • Extension summary

    select count(extension), extension from AuditRecords where extension <> "" and eventTime > OldestRecordAge group by Upper(extension);

  • Drive Type summary

    select count(driveType) Count, case driveType when 0 then 'Unknown' when 1 then 'No Root' when 2 then 'Removable' when 3 then 'Hard drive' when 4 then 'Network' when 5 then 'CD/DVD' when 6 then 'RAMDisk' end Type from AuditRecords where eventTime > OldestRecordAge group by driveType;

  • Drive type usage by user

    select count(driveType) Count, case driveType when 0 then 'Unknown' when 1 then 'No Root' when 2 then 'Removable' when 3 then 'Hard drive' when 4 then 'Network' when 5 then 'CD/DVD' when 6 then 'RAMDisk' end Type, userName UserName from AuditRecords where eventTime > OldestRecordAge group by driveType, userName;

  • Written to removable drives by extension

    select count(extension) Count, machineName, userName, extension from AuditRecords where eventTime > OldestRecordAge and len(extension) > 0 and (majorFunction = 0 and WriteAccess = 1 and driveType = 2 and deniedOp = 0) or (majorFunction = 4 and driveType = 2 and deniedOp = 0) group by Upper(extension), Upper(machineName), Upper(userName) order by Count desc;