Tips for using SQL filters with OMPM

  • Article

A consultant recently asked us whether OMPM supports using wildcards for scanning files. Turns out, OMPM doesn’t support this, but our OMPM guru Curtis Sawin says it’s better to scan everything and then filter the results instead. Here he shares his tips for some advanced filtering techniques using SQL filters.

Does OMPM support wildcards when scanning documents?
Sorta, but not really. 

Meaning, when scanning documents (using offscan.exe), you can define:

  • folders to include and exclude
  • file extensions to include
  • whether or not to scan files that haven’t been modified or accessed in “a while.” 

That’s it. 

These settings are defined in the offscan.ini file.  However, the recommended strategy to scan and evaluate documents is to use offscan.exe to scan your local or network drives, then use the OMPM reporting tool (OMPM.accdr) to filter your results.  In other words, use offscan.exe to determine “what you have,” and then use OMPM.accdr to determine “what you need.” 

To do this, create a custom SQL filter in OMPM.accdr, which is just a SQL WHERE clause.

For example, if I want to exclude all files that begin with specific characters, I would add a WHERE clause in OMPM.accdr that excludes these files.  The below screen shot shows the Selected Filter SQL box in OMPM.accdr with a WHERE clause that includes only files that start with “pr”

After selecting the Apply Filter button, the screen shot above shows that only 7% of my scanned files meet my filter criteria.

Note: For this query to work, you’ll need to have the Scanned Files tab opened in OMPM.accdr.

A slightly more useful example is to exclude all “owner files.”  Ya know, those docs that start with a tilde (~) followed by a dollar sign ($), followed by the remainder of the document name (eg, “~$ile name.doc”).  These files get created when you open a saved file, and get deleted when you close the file.  If you’re scanning a network drive, you may pick up thousands of these files, which indicate that there are thousands of documents currently open by your customers.  The following query excludes such files:

Using this filter, I was able to reduce the number of files I need to evaluate.

In summary, you can get very granular in your filtering when using the OMPM reporting tool.  Thus, while you may not be able to scan files based on wildcards, you can certainly filter using wildcards. Using the approach of “scan what you have…report what you need” also reduces the potential for scanning the same location more than once.

Curtis