How to create a Summary
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.
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.
1Bulk 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
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
2Files 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
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
3Printed 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
select 1, eventTime, eventText from Events where eventText like '%has printed%' and eventTime > OldestRecordAge
4Number 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
SELECT count(distinct Upper(filename)) fileCount, username from [AuditRecords] where majorFunction = 3 and isDirectory = 0 and eventTime > OldestRecordAge group by username
5Number 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
SELECT count(distinct Upper(filename)) fileCount, username FROM [AuditRecords] where majorFunction = 0 and isDirectory = 0 and and eventTime > OldestRecordAge group by username
6Number 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
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
7Detailed 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
select 1 count, fileName, userName from AuditRecords where alertID = 10 and eventTime > OldestRecordAge
8Denied 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
select 1 count, userName, fileName from AuditRecords where extension <> "" and eventTime > OldestRecordAge and deniedOp = 1
9Files 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)
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)
10Files 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);
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);
11Summary 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;
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;
12Summary 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;
select 1 Count, UserName, Filename, renameTo from AuditRecords where isDirectory = 1 and (MajorFunction = 18 or MajorFunction = 6) and (operationContext & 1= 1) and eventTime > OldestRecordAge;
13File 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
SELECT count(distinct Upper(filename)) fileCount, username FROM [AuditRecords] where majorFunction = 0 and isDirectory = 0 and wasCreated = 1 and eventTime > OldestRecordAge group by username
14Reading 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;
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;
15Root 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;
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;
16Extension Summary by User
select count(extension) Count, userName, extension from AuditRecords where eventTime > OldestRecordAge group by Upper(extension), Upper(userName) order by Count desc;
17Program summary
select count(exeName) Count, exeName from AuditRecords where exeName <> "" and eventTime > OldestRecordAge group by Upper(exeName) order by Count desc;
18Extension summary
select count(extension), extension from AuditRecords where extension <> "" and eventTime > OldestRecordAge group by Upper(extension);
19Drive 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;
20Drive 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;
21Written 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;