Harvest SCM database query for user groups which are not assigned to any project related processs

Products

• CA Harvest Software Change Manager

Issue/Introduction

This article provides the query. The query works on both Oracle and SQL Harvest SCM database.

Environment

Harvest SCM version 14.x

Resolution

select hug.usrgrpobjid, hug.usergroupname, hug.creationtime, hu.username created_by, hug.modifiedtime, hug.note from HARUSERGROUP hug, harallusers hu
    where hug.creatorid = hu.usrobjid
        and not exists (select 1 from HARSTATEPROCESSACCESS hpa where hpa.executeaccess = 'Y' and hpa.usrgrpobjid = hug.usrgrpobjid) -- not in any process access list
        and not exists (select 1 from HARAPPROVELIST hal where hal.usrgrpobjid = hug.usrgrpobjid) -- not in any approver list
        and not exists (select 1 from HARENVIRONMENTACCESS hea where hea.usrgrpobjid = hug.usrgrpobjid) -- not in any project access list
        and not exists (select 1 from HARFORMTYPEACCESS hf where hf.usrgrpobjid = hug.usrgrpobjid) -- not in any form type access list
        and not exists (select 1 from HARITEMACCESS hia where hia.usrgrpobjid = hug.usrgrpobjid) -- not in any item access list
        and not exists (select 1 from HARNOTIFYLIST hnl where hnl.usrgrpobjid = hug.usrgrpobjid) -- not in any notification list
        and not exists (select 1 from HARREPOSITORYACCESS hra where hra.usrgrpobjid = hug.usrgrpobjid) -- not in any repository access list
        and not exists (select 1 from HARSTATEACCESS hsa where hsa.usrgrpobjid = hug.usrgrpobjid) -- not in any state access list
        and hug.usrgrpobjid > 20; -- exclude all HARVEST built-in user groups