1 2 Previous Next 21 Replies Latest reply on Feb 24, 2020 7:49 AM by Jeffrey Jordan Go to original post
      • 15. Re: Control-M Authorization
        Haw Kor

        Lakshmi,

         

        There is no need for you to copy the details to v9 client.  Start with a new v9 client and only make the addition to the installed-versions.txt for this new client. 

         

        For the record, this is what was recommended by BMC support as a workaround for our needs as well.

         

        Thanks,

         

        Haw

        • 16. Re: Control-M Authorization
          David Fernandez

          Apologies for my prevoius email. I thought manually overwritting a file which is supposed to be modifyed only by the install/upgrade packages was not recommended, but if support has provided that workaround please do so.

           

          I will send a note to the R&D team in charge of the Reporting tool for them to look at this thread.

          • 17. Re: Control-M Authorization
            Raghul Thiruvaimozhi

            Hi Lakshmi, This will help you i hope..Run the query in EM db. Replace group names per requirement.

             

            select USERGROUP, USERNAME from USERSGROUPS WHERE USERGROUP like 'UpdateGroup' ORDER BY USERGROUP DESC, USERNAME;

            • 18. Re: Control-M Authorization
              Erez Gordon

              Hi,

               

              Authorization report is planned to be available with V20.

               

              Regards,

              Erez

              1 of 1 people found this helpful
              • 19. Re: Control-M Authorization
                Lakshminarasimhan Santhanam

                Thank you All .

                I did the same even engaged BMC but it did not worked . I will try once again on my test machine and see how it goes .

                I did tried the query but was not giving much details and was forced to give them the screenshot of all 1400 users and group

                 

                Let me test that and see how it goes . Will keep you posted .

                 

                Once again thank you for your help .

                • 20. Re: Control-M Authorization
                  Sergio Blanco

                  Hi Santhaman,

                   

                  We have an audit request on a quarterly basis and we have established a procedure approved by our CISO team to avoid thousands of screenshot.

                  We run a script with md5 evidences and among other steps the following query (v9.0.19.xxx) to export groups and orphan users privileges.

                  I hope that helps you.

                   

                  SELECT

                   

                      ISGROUP||';'||

                      USERNAME||';'||

                      DESCRIPTION||';'||

                      (CASE

                          WHEN "CCM-Privileges" LIKE '%FULL%' OR "CCM-Privileges" LIKE '%UPDATE%'

                              OR "CONTROLM-FOLDERS-Filters" LIKE '%FULL%' OR "CONTROLM-FOLDERS-Filters" LIKE '%UPDATE%'

                              OR "CONTROLM-ACTIVE-Actions" NOT LIKE '%CONTROL: null, UPDATE: null%'

                          THEN 'NOT'

                          ELSE 'YES'

                      END)||';'||

                      "ACCESS-CONTROLM"||';'||

                      "ACCESS-ALERTS"||';'||

                      "ACCESS-SELFSERVICE"||';'||

                      "ACCESS-CLI"||';'||

                      "ACCESS-CCM"||';'||

                      "CCM-Privileges"||';'||

                      "CONTROLM-FOLDERS-Filters"||';'||

                      "CONTROLM-ACTIVE-Actions"||';'||

                      "CONTROLM-ACTIVE-Filters"||';'||

                      "SELFSERVICE-Filters"

                  FROM

                      (SELECT

                          (CASE

                              WHEN tGENERAL.ISGROUP = 1 THEN 'YES'

                              WHEN tGENERAL.ISGROUP = 0 THEN 'NOT'

                          END) AS "ISGROUP",

                          tGENERAL.USERNAME,

                          tGENERAL.DESCRIPTION,

                          (CASE

                              WHEN tGENERAL.LOGIN_AUTH_ARR LIKE '%1' THEN 'NONE'

                              WHEN tGENERAL.LOGIN_AUTH_ARR LIKE '%4' THEN 'FULL'

                          END) AS "ACCESS-CONTROLM",

                          (CASE

                              WHEN tGENERAL.ALERTACCESS = 1 THEN 'NONE'

                              WHEN tGENERAL.ALERTACCESS = 2 THEN 'BROWSE'

                              WHEN tGENERAL.ALERTACCESS = 3 THEN 'UPDATE'

                              WHEN tGENERAL.ALERTACCESS = 4 THEN 'FULL'

                          END) AS "ACCESS-ALERTS",

                          (CASE

                              WHEN tGENERAL.LOGIN_AUTH_ARR LIKE '1%' THEN 'NONE'

                              WHEN tGENERAL.LOGIN_AUTH_ARR LIKE '4%' THEN 'FULL'

                          END) AS "ACCESS-SELFSERVICE",

                          (CASE

                              WHEN tGENERAL.CLIACCESS = 1 THEN 'NONE'

                              WHEN tGENERAL.CLIACCESS = 2 THEN 'BROWSE'

                              WHEN tGENERAL.CLIACCESS = 3 THEN 'UPDATE'

                              WHEN tGENERAL.CLIACCESS = 4 THEN 'FULL'

                          END) AS "ACCESS-CLI",

                          (CASE

                              WHEN tGENERAL.CMC_LOGIN = 1 THEN 'NONE'

                              WHEN tGENERAL.CMC_LOGIN = 2 THEN 'BROWSE'

                              WHEN tGENERAL.CMC_LOGIN = 3 THEN 'UPDATE'

                              WHEN tGENERAL.CMC_LOGIN = 4 THEN 'FULL'

                          END) AS "ACCESS-CCM",

                          '{Authorizations: ' || (CASE

                              WHEN tGENERAL.AUTHORIZACCESS = 1 THEN 'NONE'

                              WHEN tGENERAL.AUTHORIZACCESS = 2 THEN 'BROWSE'

                              WHEN tGENERAL.AUTHORIZACCESS = 3 THEN 'UPDATE'

                              WHEN tGENERAL.AUTHORIZACCESS = 4 THEN 'FULL'

                          END)

                          || ', Configuration: ' || (CASE

                              WHEN tGENERAL.COMMACCESS = 1 THEN 'NONE'

                              WHEN tGENERAL.COMMACCESS = 2 THEN 'BROWSE'

                              WHEN tGENERAL.COMMACCESS = 3 THEN 'UPDATE'

                              WHEN tGENERAL.COMMACCESS = 4 THEN 'FULL'

                          END)

                          || ', Operation: ' || (CASE

                              WHEN tGENERAL.CMC_OPER = 1 THEN 'NONE'

                              WHEN tGENERAL.CMC_OPER = 2 THEN 'BROWSE'

                              WHEN tGENERAL.CMC_OPER = 3 THEN 'UPDATE'

                              WHEN tGENERAL.CMC_OPER = 4 THEN 'FULL'

                          END)

                          || ', Database Maintenance: ' || (CASE

                                  WHEN tGENERAL.CMC_DB = 1 THEN 'NONE'

                                  WHEN tGENERAL.CMC_DB = 2 THEN 'BROWSE'

                                  WHEN tGENERAL.CMC_DB = 3 THEN 'UPDATE'

                                  WHEN tGENERAL.CMC_DB = 4 THEN 'FULL'

                          END)

                          || ', Security: ' || (CASE

                              WHEN tGENERAL.CMC_CTM_SECURITY = 1 THEN 'NONE'

                              WHEN tGENERAL.CMC_CTM_SECURITY = 2 THEN 'BROWSE'

                              WHEN tGENERAL.CMC_CTM_SECURITY = 3 THEN 'UPDATE'

                              WHEN tGENERAL.CMC_CTM_SECURITY = 4 THEN 'FULL'

                          END) || '}' AS "CCM-Privileges",

                          (CASE

                              WHEN t2FOLDERS.FILTERS IS NULL AND tGENERAL.LOGIN_AUTH_ARR LIKE '%1' THEN 'SIN ACCESO A CONTROL-M'

                              WHEN t2FOLDERS.FILTERS IS NULL AND tGENERAL.LOGIN_AUTH_ARR LIKE '%4' THEN 'SIN ACCESO A FOLDERS'

                              ELSE t2FOLDERS.FILTERS

                          END) AS "CONTROLM-FOLDERS-Filters",

                          (CASE

                              WHEN t2ACTIVE.FILTERS = '{MEMNAME: [*]}' THEN 'FULL'

                              WHEN t2ACTIVE.FILTERS IS NULL AND tGENERAL.LOGIN_AUTH_ARR LIKE '%1' THEN 'SIN ACCESO A CONTROL-M'

                              WHEN t2ACTIVE.FILTERS IS NULL AND tGENERAL.LOGIN_AUTH_ARR LIKE '%4' THEN 'FULL'

                              ELSE t2ACTIVE.FILTERS

                          END) AS "CONTROLM-ACTIVE-Filters",

                          t2ACTIVEACTIONS.ACTIONSTYPE AS "CONTROLM-ACTIVE-Actions",

                          (CASE

                              WHEN t2SERVICES.SERVICE_FILTER IS NULL AND tGENERAL.LOGIN_AUTH_ARR LIKE '1%' THEN 'SIN ACCESO A SELF-SERVICE'

                              WHEN t2SERVICES.SERVICE_FILTER IS NULL AND tGENERAL.LOGIN_AUTH_ARR LIKE '4%' THEN 'SIN FILTROS CONFIGURADOS'

                              ELSE t2SERVICES.SERVICE_FILTER

                          END) AS "SELFSERVICE-Filters"

                      FROM

                          GENERALAUTHORIZATIONS tGENERAL

                          LEFT JOIN (

                              SELECT

                                  GENERALAUTHORIZATIONS.USERNAME,

                                  LISTAGG(NVL(FILTERS, 'SIN ACCESO A FOLDERS'), ', ') WITHIN GROUP (ORDER BY t1FOLDERS.USERNAME) AS "FILTERS"

                              FROM

                                  GENERALAUTHORIZATIONS

                                  LEFT JOIN (

                                      SELECT

                                          USERNAME,

                                          '[' || LISTAGG(FILTER, ',') WITHIN GROUP (ORDER BY USERNAME) || ']' AS "FILTERS"

                                      FROM

                                          (SELECT

                                              t1FOLDERSFILTER.USERNAME,

                                              '{AccessLevel: ' || t1FOLDERSFILTER.ACCESS_LEVEL || ', AllowOrder: ' ||

                                              (CASE

                                                  WHEN t1FOLDERSFILTER.FILTERFOLDER = t2ALLOWORDER.FILTERORDER THEN 'YES'

                                                  WHEN t2ALLOWORDER.FILTERORDER IS NULL THEN 'NOT'

                                                  ELSE 'REVIEW_ERROR'

                                              END) || ', Filter: ' || t1FOLDERSFILTER.FILTERFOLDER AS "FILTER"

                                          FROM

                                              (SELECT

                                                  USERNAME,

                                                  TERMINDEX,

                                                  (CASE

                                                      WHEN AUTHORIZATIONLEVEL = 2 THEN 'BROWSE'

                                                      WHEN AUTHORIZATIONLEVEL = 3 THEN 'UPDATE'

                                                      WHEN AUTHORIZATIONLEVEL = 4 THEN 'FULL'

                                                  END) AS ACCESS_LEVEL,

                                                  '{Control-M: [' || LISTAGG(FIELDVALUE, '], Folder: [') WITHIN GROUP (ORDER BY TERMINDEX,FIELDNAME) || ']}' AS FILTERFOLDER

                                              FROM RESOURCESAUTHORIZATIONS

                                              WHERE (ISGROUP = 1

                                                  OR (ISGROUP = 0 AND USERNAME NOT IN (SELECT USERNAME FROM USERSGROUPS)))

                                                  AND RESOURCETYPE = 5

                                                  AND (FIELDNAME = 'SCHED_TABLE' OR FIELDNAME = 'DATA_CENTER')

                                              GROUP BY USERNAME,TERMINDEX,AUTHORIZATIONLEVEL

                                              ) t1FOLDERSFILTER

                                              LEFT JOIN (

                                                  SELECT

                                                      USERNAME,

                                                      TERMINDEX,

                                                      '{Control-M: [' || LISTAGG(FIELDVALUE, '], Folder: [') WITHIN GROUP (ORDER BY TERMINDEX,FIELDNAME) || ']}' AS FILTERORDER

                                                  FROM RESOURCESAUTHORIZATIONS

                                                  WHERE (ISGROUP = 1

                                                      OR (ISGROUP = 0 AND USERNAME NOT IN (SELECT USERNAME FROM USERSGROUPS)))

                                                      AND RESOURCETYPE = 10

                                                      AND (FIELDNAME = 'SCHED_TABLE' OR FIELDNAME = 'DATA_CENTER')

                                                  GROUP BY USERNAME,TERMINDEX

                                              ) t2ALLOWORDER ON t1FOLDERSFILTER.USERNAME = t2ALLOWORDER.USERNAME

                                                  AND t1FOLDERSFILTER.FILTERFOLDER = t2ALLOWORDER.FILTERORDER

                                          )

                                      GROUP BY USERNAME

                                  ) t1FOLDERS ON t1FOLDERS.USERNAME = GENERALAUTHORIZATIONS.USERNAME

                              WHERE ISGROUP = 1 OR (ISGROUP = 0 AND GENERALAUTHORIZATIONS.USERNAME NOT IN (SELECT USERNAME FROM USERSGROUPS))

                              GROUP BY GENERALAUTHORIZATIONS.USERNAME,ISGROUP

                          ) t2FOLDERS ON tGENERAL.USERNAME = t2FOLDERS.USERNAME

                          LEFT JOIN (

                              SELECT

                                  GENERALAUTHORIZATIONS.USERNAME,

                                  (CASE

                                      WHEN t1ACTIVE.FILTERS IS NULL THEN 'SIN ACCESO A ACTIVE-JOBS'

                                      ELSE t1ACTIVE.FILTERS

                                  END) AS FILTERS

                              FROM

                                  GENERALAUTHORIZATIONS

                                  LEFT JOIN (

                                      SELECT

                                          USERNAME,

                                          LISTAGG(FILTERS, ', ') WITHIN GROUP (ORDER BY TERMINDEX) AS "FILTERS"

                                      FROM

                                          (SELECT

                                              USERNAME,

                                              TERMINDEX,

                                              '{' || LISTAGG(FIELDNAME || ': [' || FIELDVALUE, '], ') WITHIN GROUP (ORDER BY TERMINDEX) || ']}' AS "FILTERS"

                                          FROM ACTIVEJOBSAUTHORIZATIONS

                                          WHERE

                                              USERNAME IN (SELECT USERNAME FROM GENERALAUTHORIZATIONS

                                                  WHERE JOBS_FILTER_FOR_GROUP = 1 AND (LOGIN_AUTH_ARR LIKE '%4' OR LOGIN_AUTH_ARR LIKE '4%')

                                                  )

                                          GROUP BY USERNAME, TERMINDEX

                                          )

                                      GROUP BY USERNAME

                                  ) t1ACTIVE ON t1ACTIVE.USERNAME = GENERALAUTHORIZATIONS.USERNAME

                              WHERE ISGROUP = 1 OR (ISGROUP = 0 AND GENERALAUTHORIZATIONS.USERNAME NOT IN (SELECT USERNAME FROM USERSGROUPS))

                          ) t2ACTIVE ON tGENERAL.USERNAME = t2ACTIVE.USERNAME

                          LEFT JOIN (

                              SELECT

                                  GENERALAUTHORIZATIONS.USERNAME,

                                  (CASE

                                      WHEN t1SERVICES.SERVICE_FILTER = '*' THEN 'FULL'

                                      ELSE t1SERVICES.SERVICE_FILTER

                                  END) AS "SERVICE_FILTER"

                              FROM

                                  GENERALAUTHORIZATIONS

                                  LEFT JOIN (

                                      SELECT

                                          USERNAME,

                                          LISTAGG(SERVICE_FILTER, ' || ') WITHIN GROUP (ORDER BY USERNAME) AS SERVICE_FILTER

                                      FROM

                                          SERVICE_AUTHORIZATIONS

                                      WHERE

                                          USERNAME IN (SELECT USERNAME FROM GENERALAUTHORIZATIONS WHERE LOGIN_AUTH_ARR NOT LIKE '1%')

                                      GROUP BY USERNAME

                                  ) t1SERVICES ON t1SERVICES.USERNAME = GENERALAUTHORIZATIONS.USERNAME

                              WHERE ISGROUP = 1 OR (ISGROUP = 0 AND GENERALAUTHORIZATIONS.USERNAME NOT IN (SELECT USERNAME FROM USERSGROUPS))

                          ) t2SERVICES ON tGENERAL.USERNAME = t2SERVICES.USERNAME

                          LEFT JOIN (

                              SELECT

                                  GENERALAUTHORIZATIONS.USERNAME,

                                  '{' || LISTAGG(

                                  NVL(t1BrowseActions.BROWSE_ACTIONTYPE, 'BROWSE: null') || ', ' ||

                                  NVL(t1ControlActions.CONTROL_ACTIONTYPE, 'CONTROL: null') || ', ' ||

                                  NVL(t1UpdateActions.UPDATE_ACTIONTYPE, 'UPDATE: null')) WITHIN GROUP (ORDER BY GENERALAUTHORIZATIONS.USERNAME)

                                  || '}' AS ACTIONSTYPE

                              FROM

                                  GENERALAUTHORIZATIONS

                                  LEFT JOIN

                                      (SELECT

                                          USERNAME,

                                          'BROWSE: [' || LISTAGG(

                                          (CASE

                                              WHEN ACTIONTYPE = 18 THEN 'Properties'

                                              WHEN ACTIONTYPE = 0 THEN 'Documentation'

                                              WHEN ACTIONTYPE = 3 THEN 'Log'

                                              WHEN ACTIONTYPE = 19 THEN 'Statistics'

                                              WHEN ACTIONTYPE = 6 THEN 'View Output List'

                                              WHEN ACTIONTYPE = 1 THEN 'View JCL'

                                              WHEN ACTIONTYPE = 5 THEN 'Why'

                                          END), ', ') WITHIN GROUP (ORDER BY USERNAME) || ']' AS BROWSE_ACTIONTYPE

                                      FROM JOBACTIONS

                                      WHERE ACTIONTYPE IN (18,0,3,19,6,1,5)

                                      GROUP BY USERNAME

                                      ) t1BrowseActions ON GENERALAUTHORIZATIONS.USERNAME = t1BrowseActions.USERNAME

                                  LEFT JOIN

                                      (SELECT

                                          USERNAME,

                                          'CONTROL: [' || LISTAGG(

                                          (CASE

                                              WHEN ACTIONTYPE = 7 THEN 'Hold'

                                              WHEN ACTIONTYPE = 8 THEN 'Free'

                                              WHEN ACTIONTYPE = 10 THEN 'Confirm'

                                              WHEN ACTIONTYPE = 11 THEN 'Rerun'

                                              WHEN ACTIONTYPE = 15 THEN 'React'

                                              WHEN ACTIONTYPE = 12 THEN 'Restart'

                                              WHEN ACTIONTYPE = 37 THEN 'Kill'

                                              WHEN ACTIONTYPE = 41 THEN 'Bypass'

                                          END), ', ') WITHIN GROUP (ORDER BY USERNAME) || ']' AS CONTROL_ACTIONTYPE

                                      FROM JOBACTIONS

                                      WHERE ACTIONTYPE IN (7,8,10,11,15,12,37,41)

                                      GROUP BY USERNAME

                                      ) t1ControlActions ON GENERALAUTHORIZATIONS.USERNAME = t1ControlActions.USERNAME

                                  LEFT JOIN

                                      (SELECT

                                          USERNAME,

                                          'UPDATE: [' || LISTAGG(

                                          (CASE

                                              WHEN ACTIONTYPE = 9 THEN 'Delete'

                                              WHEN ACTIONTYPE = 17 THEN 'Undelete'

                                              WHEN ACTIONTYPE = 16 THEN 'Set to OK'

                                              WHEN ACTIONTYPE = 14 THEN 'Edit Properties'

                                              WHEN ACTIONTYPE = 2 THEN 'Edit JCL'

                                          END), ', ') WITHIN GROUP (ORDER BY USERNAME) || ']' AS UPDATE_ACTIONTYPE

                                      FROM JOBACTIONS

                                      WHERE ACTIONTYPE IN (9,17,16,14,2)

                                      GROUP BY USERNAME

                                      ) t1UpdateActions ON GENERALAUTHORIZATIONS.USERNAME = t1UpdateActions.USERNAME

                              WHERE

                                  ISGROUP = 1 OR (ISGROUP = 0 AND GENERALAUTHORIZATIONS.USERNAME NOT IN (SELECT USERNAME FROM USERSGROUPS))

                              GROUP BY GENERALAUTHORIZATIONS.USERNAME

                          ) t2ACTIVEACTIONS ON tGENERAL.USERNAME = t2ACTIVEACTIONS.USERNAME

                      WHERE

                          ISGROUP = 1 OR (ISGROUP = 0 AND tGENERAL.USERNAME NOT IN (SELECT USERNAME FROM USERSGROUPS))

                      )

                  ORDER BY ISGROUP,USERNAME

                  ;

                  1 of 1 people found this helpful
                  • 21. Re: Control-M Authorization
                    Jeffrey Jordan

                    I wish they'd hurry up & add this report back in. I lost it as well but had that ability to login to my old 9.0 to get to crystal reports but a few other operators here lost the ability when they got new pcs & had to do a new web client install to set controlm access back up on their pcs. One company I know, this report was something fed auditors needed from them but for us it was if auditors needed it but otherwise we were only doing it for one of the bosses & he was fine if it he didn't get it. We thought about trying to do the report running some kind of sql db query to get same report info but just never has become priority due to still in compatability mode.

                    1 2 Previous Next