Frequently Asked Questions
Frequently Asked Questions: M-Files
SQL query to find the name of a file in the vault based on the location of the .bin file in file data storage
Posted by Josh Repp (M-Files) on 11 October 2018 11:22 PM

Note: This example is only useful for vaults that use SQL for their database and where the 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

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 in the example above 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. 

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

declare @listOfIDs table (id int);
insert @listOfIDs(id) values(64392);
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 (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 you need.

 

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

Help Desk Software by Kayako case