Basic Filtering

In addition to using filters to scope the range of scan data, basic filtering can also be used to limit the results to only records of interest.

The following is a list of basic filtering examples that may be used as starting templates for queries.

Filter by Path Type

In cases where aggregation or calculations against a discrete set of files is desired, it may be necessary to filter out any directories or shares first, since those entries contain size and name data that may skew the desired results.

SELECT * 
FROM srs.current_fs_scandata_ad 
WHERE path_type = 1             -- Note: 1 = file entry
  AND server='Server1';

Filter by File Extension

This example filters the set of file entries within a given directory structure to just those defined as media types.

SELECT * 
FROM srs.current_fs_scandata_ad 
WHERE path_type = 1
  AND filename_extension IN ('mp3', 'mp4', 'avi', 'ogg', 'png', 'jpg', 'jpeg');

Note that for filename_extension, all values should be lower case.

Filter by Date Range

This example selects all files on the specific server from November 1, 2013 midnight, through November 2, 2013 11:59 PM.

SELECT *
FROM srs.current_fs_scandata_ad
WHERE modify_time BETWEEN '2013-11-01 00:00:00' AND '2013-11-02 23:59:59'
  AND server='dbdev.db.dtest.lab'
  AND path_type = 1   -- Files only

We can also use the familiar >= and <= comparison operators to accomplish the same:

SELECT *
FROM srs.current_fs_scandata_ad
WHERE modify_time >= '2013-11-01 00:00:00'
  AND modify_time <= '2013-11-02 23:59:59'
  AND server='dbdev.db.dtest.lab'
  AND path_type = 1   -- Files only

Note that the behavior of the BETWEEN operator is inclusive, not exclusive, to the parameters given.

It is important to note with date-time ranges, that a simple date such as ‘2013-11-02’ actually represents ‘2013-11-02 00:00:00’, so be careful to include 23:59:59 to the ending date as appropriate.

Finally, it is important to remember that all timestamps stored in the database are stored as UTC values, so consideration for time zone offsets may be needed.

Filter by File Name

This example shows how to filter by a given file name.

SELECT * 
FROM srs.current_fs_scandata
WHERE LOWER(name) = 'document1.txt';

Note the use of the LOWER operator to force a case-insensitive search. Depending on the collation of the database instance and the database itself, this operator may be required.

For wildcard matches, the standard SQL flags _ and % can be used to represent single or multiple characters.

SELECT *
FROM srs.current_fs_scandata
WHERE LOWER(name) LIKE 'document1.%';

See the following links for database specific info regarding wildcards and other search patterns: