iManage Security queries
--iManage Security Queries declare @client varchar(12) declare @matter varchar(12) declare @matterShort varchar(12) declare @groupID varchar(40) set @client = '3011' --CHANGE CLIENT NUMBER HERE set @matter = '' --CHANGE MATTER NUMBER HERE set @groupID = 'ZZINT_'+@client+'_'+@matter --Users in Group select du.USERID, du.FULLNAME, g.GROUPID, g.GROUPNUM, g.FULLNAME, g.ENABLED from MHGROUP.GROUPS g join MHGROUP.GROUPMEMS gm on gm.GROUPNUM = g.GROUPNUM join MHGROUP.DOCUSERS du on du.USERNUM = gm.USERNUM where g.GROUPID = @groupID --Documents Being Secured: Inclusionary select distinct dm.EDITPROFILEWHEN as LastEditedDate , dm.DOCNUM, dm.DOCNAME, du.FULLNAME , dm.C1ALIAS as ClientNum, dm.C2ALIAS as MatterNum , g.GROUPID from MHGROUP.DOCMASTER dm join MHGROUP.DOCUSERS du on du.USERID = dm.AUTHOR join MHGROUP.DOC_ACCESS da on da.DOCNUM = dm.DOCNUM join MHGROUP.GROUPS g on g.GROUPNUM = da.USER_GP_ID where g.GROUPID = @groupID --For inclusionary walls, verify that *only* ZZINT groups appear select distinct g.GROUPID, du.USERID from MHGROUP.DOC_ACCESS da left join MHGROUP.GROUPS g on g.GROUPNUM = da.USER_GP_ID left join MHGROUP.DOCUSERS du on du.USERNUM = da.USER_GP_ID where da.DOCNUM in ( select dm.DOCNUM from MHGROUP.DOCMASTER dm where dm.c1alias = @client and dm.c2alias = @matter ) --Documents Being Secured: Exclusionary select dm.EDITPROFILEWHEN as LastEditedDate , dm.DOCNUM, dm.DOCNAME, du.FULLNAME , dm.C1ALIAS as ClientNum, dm.C2ALIAS as MatterNum , g.GROUPID from MHGROUP.DOCMASTER dm join MHGROUP.DOCUSERS du on du.USERID = dm.AUTHOR join MHGROUP.DOC_DENIAL dd on dd.DOCNUM = dm.DOCNUM and dd.DOCVER=dm.VERSION join MHGROUP.GROUPS g on g.GROUPNUM = dd.USER_GP_ID where g.GROUPID = @groupID --Check Doc_Access for specified Matter declare @Client varchar (12), @Matter varchar (12) set @Client = '061189' set @Matter = '001' select * from MHGROUP.DOCMASTER DM join MHGROUP.DOC_ACCESS DA on DA.DOCNUM = DM.DOCNUM join MHGROUP.GROUPS G on G.GROUPNUM = DA.USER_GP_ID join MHGROUP.GROUPMEMS GM on GM.GROUPNUM = G.GROUPNUM join MHGROUP.DOCUSERS DU on DU.USERNUM = GM.USERNUM where dm.C1ALIAS = @Client and dm.C2ALIAS = @Matter --Check what users have access to specified matter (on document level) declare @Client varchar (12), @Matter varchar (12) set @Client = '2000' set @Matter = '001' select DM.DOCNAME, Dm.DOCNUM, DM.C1ALIAS, DM.C2ALIAS, DM.DEFAULT_SECURITY, DA.OBJECT_TYPE, DA.USER_GP_ID, DA.ACCESS_RIGHT, G.GROUPID, G. FULLNAME, G.GROUPNUM,DU.USERID, DU.FULLNAME, DU.USERNUM from MHGROUP.DOCMASTER DM join MHGROUP.DOC_ACCESS DA on DA.DOCNUM = DM.DOCNUM join MHGROUP.GROUPS G on G.GROUPNUM = DA.USER_GP_ID join MHGROUP.GROUPMEMS GM on GM.GROUPNUM = G.GROUPNUM join MHGROUP.DOCUSERS DU on DU.USERNUM = GM.USERNUM where dm.C1ALIAS = @Client and dm.C2ALIAS = @Matter
iManage Incremental Repair Queries
--If LastRepair Time is NOT NULL: --1 SELECT TOP (5000) [t2].[DOCNUM] AS [DocNum], [t2].[DOCUSER] AS [DocUser], [t2].[VERSION] AS [Version], [t2].[value] AS [ActivityCode], [t2].[ACTIVITY_DATETIME] AS [Time] FROM ( SELECT [t0].[DOCNUM], [t0].[DOCUSER], [t0].[VERSION], [t0].[ACTIVITY_CODE] AS [value], [t0].[ACTIVITY_DATETIME], [t0].[ACTIVITY_CODE], [t1].[TYPE] FROM [MHGROUP].[DOCHISTORY] AS [t0] INNER JOIN [MHGROUP].[DOCMASTER] AS [t1] ON [t0].[DOCNUM] = ([t1].[DOCNUM]) ) AS [t2] WHERE ([t2].[ACTIVITY_DATETIME] > '2015-08-30 17:28:42.963') AND (([t2].[ACTIVITY_CODE]) IN (7,4,6,8)) AND (NOT ([t2].[DOCUSER] = '')) AND ([t2].[TYPE] = 'D') ORDER BY [t2].[ACTIVITY_DATETIME] --2 SELECT TOP (1) [t1].[value] AS [docnum], [t1].[VERSION] AS [version], [t1].[DOCNAME] AS [name], [t1].[C1ALIAS] AS [clientID], [t1].[C2ALIAS] AS [matterID] FROM ( SELECT CONVERT(Int,[t0].[DOCNUM]) AS [value], [t0].[VERSION], [t0].[DOCNAME], [t0].[C1ALIAS], [t0].[C2ALIAS], [t0].[DOCNUM] FROM [MHGROUP].[DOCMASTER] AS [t0] ) AS [t1] WHERE ([t1].[DOCNUM] = '9') AND ([t1].[VERSION] = '1') --3 - LastRepairID looks like it is the DocNum? UPDATE [ScheduledSecurityRepairStatus] SET [LastRepairTime] = '2016-05-25 04:10:29.523', [LastRepairId] = '1', [Status] = 'Idle' WHERE ([ExtensionType] = 'INTERWOVEN') AND ([LibraryName] = 'iManage') AND ([ObjectType] = 'Documents') AND ([ScheduledSecurityRepairStatusId] = '1') AND ([LastRepairTime] = '2016-04-25 04:10:40.523') AND ([LastRepairId] = '19') AND ([Status] = 'Busy') SELECT [t1].[ScheduledSecurityRepairStatusId] FROM [ScheduledSecurityRepairStatus] AS [t1] WHERE ((@@ROWCOUNT) > 0) AND ([t1].[ExtensionType] = 'INTERWOVEN') AND ([t1].[LibraryName] = 'iManage') AND ([t1].[ObjectType] = 'Documents') --If LastRepair Time is NULL: --Check if activity more recent than scheduledSecurityRepairStatus datetime: SELECT TOP (1) [t0].[ACTIVITY_DATETIME] FROM [MHGROUP].[DOCHISTORY] AS [t0] ORDER BY [t0].[ACTIVITY_DATETIME] DESC --2. UPDATE [ScheduledSecurityRepairStatus] SET [LastRepairTime] = '2016-04-25 04:10:29.523', [Status] = 'Idle' WHERE ([ExtensionType] = 'INTERWOVEN') AND ([LibraryName] = 'iManage') AND ([ObjectType] = 'Documents') AND ([ScheduledSecurityRepairStatusId] = '1') AND ([LastRepairTime] IS NULL) AND ([LastRepairId] IS NULL) AND ([Status] = 'Busy') SELECT [t1].[ScheduledSecurityRepairStatusId] FROM [ScheduledSecurityRepairStatus] AS [t1] WHERE ((@@ROWCOUNT) > 0) AND ([t1].[ExtensionType] = 'INTERWOVEN' AND ([t1].[LibraryName] = 'iManage') AND ([t1].[ObjectType] = 'Documents'))