Whilst this post is targeted at retrieving archived BigHand dictation file locations, it does demonstrate how we can use RES Automation Manager parameters and its Microsoft SQL integration to good effect.
Locating a Dictation File
BigHand holds dictations in a proprietary format with the extension .BHF. This is essentially just a WAV file with minor modifications. Once a user has completed a dictation it will disappear from their view after a pre-determined number of days. If you have a reason to retrieve one of these files once it is removed or if a system corruption means you need to re-import dictations there is no easy way to identify the file, as the files have GUID filenames and can be sitting on any file store server in your BigHand estate. Using a combination of SQL and Automation Manager you can make everyone’s life easier.
Create SQL View
First you will need to create a SQL view on your BigHand SQL instance called Find_Dictation_Location as per the below script:
SELECT a.BH_FirstName, a.BH_LastName, a.BH_UserName, b.BH_Title, CONVERT(varchar(10), b.BH_CreationDate, 103) AS BH_CreationDate,
b.BH_CompletionDate, b.BH_Deleted AS BH_Deleted_Task, b.BH_Destination, b.BH_Description, b.BH_MatterNumber, b.BH_DocumentType,
b.BH_Confidential, b.BH_FileRequired, b.BH_Open, c.BH_FileGuid, c.BH_Location, c.BH_Version, CAST(d.BH_URL AS nvarchar(4000))
+ CAST(SUBSTRING(c.BH_Path, 2, LEN(c.BH_Path)) AS nvarchar(4000)) AS Path, c.BH_Deleted AS BH_Deleted_File
FROM dbo.BH_Users WITH (nolock) a INNER JOIN
dbo.BH_Tasks WITH (nolock) b ON a.BH_UserGuid = b.BH_Author INNER JOIN
dbo.BH_FileLocations WITH (nolock) c ON b.BH_TaskGuid = c.BH_FileGuid INNER JOIN
dbo.BH_Locations WITH (nolock) d ON c.BH_Location = d.BH_Location
b.BH_CompletionDate, b.BH_Deleted AS BH_Deleted_Task, b.BH_Destination, b.BH_Description, b.BH_MatterNumber, b.BH_DocumentType,
b.BH_Confidential, b.BH_FileRequired, b.BH_Open, c.BH_FileGuid, c.BH_Location, c.BH_Version, CAST(d.BH_URL AS nvarchar(4000))
+ CAST(SUBSTRING(c.BH_Path, 2, LEN(c.BH_Path)) AS nvarchar(4000)) AS Path, c.BH_Deleted AS BH_Deleted_File
FROM dbo.BH_Users WITH (nolock) a INNER JOIN
dbo.BH_Tasks WITH (nolock) b ON a.BH_UserGuid = b.BH_Author INNER JOIN
dbo.BH_FileLocations WITH (nolock) c ON b.BH_TaskGuid = c.BH_FileGuid INNER JOIN
dbo.BH_Locations WITH (nolock) d ON c.BH_Location = d.BH_Location
Automation Manager SQL Query
Next you need to run a SQL query against that view to retrieve the URL of the file, but the SQL query needs to be edited every time you use it, so let’s create an AM job to do this.
1. Create a Module and Add a SQL Statement (Query) task from the advanced folder
2. On the settings tab add the following SQL query:
1. Create a Module and Add a SQL Statement (Query) task from the advanced folder
2. On the settings tab add the following SQL query:
select BH_UserName,BH_Title,BH_CreationDate,path,BH_Deleted_File from Find_Dictation_Location
where bh_username like ‘%$[Username]%’ and
BH_Title like ‘%$[Title]%’ and
BH_CreationDate like ‘%$[Date]%’
where bh_username like ‘%$[Username]%’ and
BH_Title like ‘%$[Title]%’ and
BH_CreationDate like ‘%$[Date]%’
Note the parameter references in the SQL query ($[Username], $[Title] and $[Date]). These are the fields required to identify the dictation. Obviously these can be changed to any of the fields available in the SQL view.
So that AM can insert the correct variables into the script you will need to create parameters for:
a. Username (Username)
• This will allow a full or part entry of the user who created the dictation
b. Dictation title (Title)
• This will allow a full or part entry of the Dictation title
c. Time and Date (Date)
• This must be the exact creation date; you can set a mask for the parameter on the input tab in the format 00/00/0000.
a. Username (Username)
• This will allow a full or part entry of the user who created the dictation
b. Dictation title (Title)
• This will allow a full or part entry of the Dictation title
c. Time and Date (Date)
• This must be the exact creation date; you can set a mask for the parameter on the input tab in the format 00/00/0000.
Once these are created, you will be able to run the query against your SQL instance. It will collect information on the dictations you specified by looking up the relevant GUIDs in the database and create a field entry (Path) that has the URL built up from the separate BH_Locations and BH_FileLocations tables. This will then be presented in the Job results for the query.
