Worksite iManage History SQL Search Query

July 24, 2015
Code
0 Comments
paris

Worksite/Imanage Monitor can export a document list based on History using the History search but it’s limited in the way it can display comments as well as duplicate Document Numbers. Below is SQL of how to get this direct in SSSM

Change the WorksiteDB to your DB and APPName to your application

Source code

SELECT DISTINCT [WORKSITEDB].[MHGROUP].[DOCHISTORY].[DOCNUM]
     ,[WORKSITEDB].[MHGROUP].[DOCHISTORY].[VERSION]
	 ,[WORKSITEDB].[MHGROUP].[DOCMASTER].DOCNAME
	 ,[WORKSITEDB].[MHGROUP].[DOCMASTER].T_ALIAS
      ,[ACTIVITY]
      ,[ACTIVITY_CODE]
      ,[ACTIVITY_DATETIME]
      ,[DURATION]
      ,[DOCUSER]
      ,[APPNAME]
      ,[LOCATION]
	  , CAST([WORKSITEDB].[MHGROUP].[DOCHISTORY].[COMMENTS] AS NVARCHAR(MAX))
  FROM [WORKSITEDB].[MHGROUP].[DOCHISTORY] JOIN [WORKSITEDB].[MHGROUP].[DOCMASTER] ON [WORKSITEDB].[MHGROUP].[DOCHISTORY].[DOCNUM]=[WORKSITEDB].[MHGROUP].[DOCMASTER].[DOCNUM] WHERE (APPNAME = 'APPLICATIONNAME') AND (ACTIVITY = 'Checkin')

(No Ratings Yet)