Query - Missing Tech to User Role

 SELECT NAME,

       ROLE_NAME,
       ENTITLEMENT_VALUE AS 'Tech Role will be assigned through Role',
       APPLICATION
FROM   (SELECT NAME,
               ROLE_NAME,
               ENTITLEMENT_VALUE,
               USERGROUP,
               APPLICATION,
               (SELECT 'Tech Role exist in Account' AS StatusinAccount
                FROM   ACCOUNT_ENTITLEMENTS1 AE
                WHERE  D.ACCOUNTKEY = AE.ACCOUNTKEY
                       AND D.ENTITLEMENT_VALUEKEY = AE.ENTITLEMENT_VALUEKEY) AS
               'Tech_Role_missing_according_to_assigned_Role'
        FROM   (SELECT RE.ENTITLEMENT_VALUEKEY,
                       (SELECT ACCOUNTKEY
                        FROM   ACCOUNTS A1,
                               ENDPOINTS E1
                        WHERE  A1.ENDPOINTKEY = E1.ENDPOINTKEY
                               AND E1.ENDPOINTNAME IN ( 'OneHealthIT' )
                               AND A.NAME = A1.NAME
                               AND A.STATUS IN (
                                   1, 'Active', 'Manually Provisioned' )
                               AND ETE.ENDPOINTKEY = E1.ENDPOINTKEY) AS
                       accountkey,
                       A.NAME,
                       R.ROLE_NAME,
                       EV.ENTITLEMENT_VALUE,
                       ETE.ENDPOINTNAME                              AS
                       application,
                       A.USERGROUP
                FROM   ROLE_USER_ACCOUNT RUA,
                       ROLES R,
                       ENDPOINTS E,
                       ROLE_ENTITLEMENTS RE,
                       ENTITLEMENT_VALUES EV,
                       ACCOUNTS A,
                       ENTITLEMENT_TYPES ET,
                       ENDPOINTS ETE
                WHERE  R.ROLEKEY = RUA.ROLEKEY
                       AND R.ENDPOINTKEY = E.ENDPOINTKEY
                       AND E.ENDPOINTNAME = 'OneHealthIT'
                       AND RUA.ROLEKEY = RE.ROLEKEY
                       AND EV.ENTITLEMENT_VALUEKEY = RE.ENTITLEMENT_VALUEKEY
                       AND RUA.ACCOUNTKEY = A.ACCOUNTKEY
                       AND A.STATUS IN ( 1, 'Active', 'Manually Provisioned' )
                       AND EV.ENTITLEMENTTYPEKEY = ET.ENTITLEMENTTYPEKEY
                       AND ET.ENDPOINTKEY = ETE.ENDPOINTKEY
                       AND ET.ENDPOINTKEY = ETE.ENDPOINTKEY
                       AND ETE.ENDPOINTNAME IN ( 'Rushi1' )) D) DD
WHERE  TECH_ROLE_MISSING_ACCORDING_TO_ASSIGNED_ROLE IS NULL
ORDER  BY 1 ASC 

Comments

Post a Comment

Popular posts from this blog

Enhanced Query - To Update Account Status and Name

select accountkey as accounts__primarykey, 'SUSPENDED FROM IMPORT SERVICE' as accounts__status, CONCAT(name,'-Deleted on-', DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'))as accounts__name from accounts where endpointkey=527 and name='CN=INRVCH,OU=User Accounts Vista,OU=User Accounts,OU=User Directory,DC=asia-pac,DC=xx,DC=com'  and status not in ('SUSPENDED FROM IMPORT SERVICE') limit 1;
Read more

Connection Config - STATUS_THRESHOLD_CONFIG

 {   "statusAndThresholdConfig": {    "inactivateAccountsNotInFile": true    "statusColumn":"userlock",    "activeStatus":["0","128","192"],     "accountThresholdValue": 500,     "deleteLinks": false,     "correlateInactiveAccounts": true,        } } ----------------------------------------------------------------------------- When account import run above STATUS_THRESHOLD_CONFIG is used to check. ----------------------------------- inactivateAccountsNotInFile  ==> True/false true ---> Account status 1/2 ( Active/Inactive) Day 1 --> 100 Accounts import Day 2 --> 90 Diff= 10  ( 90= Active , 10 Inactive)  ------------------------------------------------------------------------------ ----------------------------------- inactivateAccountsNotInFile  ==> True/false false ---> Account status 1/SFIS ( Active/SUSPENDED FROM IMPORT SERVICE.) Day 1 --> 100 Ac...
Read more

SQL - Connection table

 select EXTERNALCONNECTIONKEY,CONNECTIONDESCRIPTION,CONNECTIONNAME,CONNECTORMS,CREATEDBY,CREATEDON,CREDENTIALCHANGECONFIG,EMAILTEMPLATE,EXTERNALCONNECTIONTYPE,MSCONNECTORVERSION,NODE_MAC_ID,SSLCERTIFICATE,STATUS s,STATUSFORENABLEDISABLE,TEMPLATE_MANDATORYDATA,TEMPLATENAME,UPDATEDBY,UPDATEDON,VAULTCONFIG,VAULTCREDENTIALCONNECTION,CONNECTORTYPE  from externalconnection  SELECT   eh . connectionname ,         CASE           WHEN   eh . operation   =   'Remove'                AND   (   eh . newvalue   IS   NULL                       OR   eh . newvalue   =   ''   )   THEN   'ROLE_ADMIN'           ELSE   Conca...
Read more