Frequently Asked Questions
Frequently Asked Questions
SQL QUERY TO FIND THE NAME OF A FILE IN THE VAULT BASED ON THE LOCATION OF THE .BIN FILE
Posted by Josh Repp (M-Files) on 30 August 2019 05:31 PM

Overview

Note: This example is only useful for vaults that use SQL for their database and where the file data for the vault is stored outside of the database itself.

This is useful if, for example, you have a virus scanner that detects a virus in one of the files in your vault. The virus scanner will likely point to file in a location like this...

C:\Program Files\M-Files\Server Vaults\<vaultname>\FileData\0\6\43\64392-4_1.bin

Solution

If a virus scanner detects a virus in one of the .bin files that resides in the vault, you will want to destroy that file as soon as possible to avoid spreading that virus if/when that file is opened at a later date. The key piece of information you need from the .bin is the File ID which is part of the path and filename of the file. In the example above, the File ID would be 64392. Once you have your File ID, you can use SQL Server Management Studio to query the vault database and locate the actual title of the file as it appears within M-Files. Using the query below, you simply need to replace the ID in the query with the ID inserted into the value list below. If you have multiple files to look for, you can use a comma separated list of IDs like (11223,44556,77889).

******QUERY*****

declare @listOfIDs table (id int);
insert @listOfIDs(id) values(64392); --<place File ID(s) here

with objs as
(select
  df.DV_OBJECTTYPE as 'ObjType'
, df.DV_DOCUMENTPART as 'ObjID'
, max(df.DV_VERSIONPART) as 'Version'
, df.DFV_DOCUMENTFILEPART as 'FileID'
, df.DFV_VERSIONPART as 'FileVersion'
from [dbo].[DV_DF] as df
where df.DFV_DOCUMENTFILEPART in (select * from @listOfIDs) group by df.DV_OBJECTTYPE, df.DV_DOCUMENTPART, df.DFV_DOCUMENTFILEPART, df.DFV_VERSIONPART ),
objDetails as ( select objs.*, dfv.TITLE as 'Title' from objs left join [dbo].[DOCUMENTFILEVERSION] as dfv on dfv.ID_DOCUMENTFILEPART = objs.FileID and dfv.ID_VERSIONPART = objs.FileVersion)

select * from objDetails

******QUERY*****

When the results of the query return, you will see the Object Type ID (document objects are object type '0' by default), Object ID (this is the internal id number that appears on the metadata card of all objects), the Version Number of the object as well as the Title of the object. Once you know the Object ID or the Title of the object in question, you can use that information to search within the vault and locate the file.

 

(0 vote(s))
This article was helpful
This article was not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please enter the text you see in the image into the textbox below. This is required to prevent automated registrations and form submissions.

Help Desk Software by Kayako case