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'))