11 Replies Latest reply on Feb 5, 2020 1:59 AM by Abhay Bagalkoti

    Report writer role to all smart reporting users.

    Neha Patel
      Share This:

      Hi All,

       

      I would like to know if i can assign report writer role to any users or it depends on permissions it holds at ITSM end?  Is there any way to assign Report Writer role to all users at once (before or after installation)?

        • 1. Re: Report writer role to all smart reporting users.
          Mohammad Rehman

          You can assign report writer role to any user you (as administrator), it does not depends on ITSM end.

          • 2. Re: Report writer role to all smart reporting users.
            Neha Patel

            Thank you for your reply. I tried keeping this default role but not all users were assigned as Report Writer. We can assign this role one by one to users but is there any other way to assign this role to all users at once?

            • 3. Re: Report writer role to all smart reporting users.
              Mohammad Rehman

              Neha,

              I doubt until BMC folks share direct sql update on db level. As far as i know you have to add them manually.

              But you should be able to set default in future. I will try to test in the morning when get to office and share.

              • 4. Re: Report writer role to all smart reporting users.

                Hi
                I'm interested in this as well. If a customer has a need to give 500 users the "Report Writer" role, for example: Is the customer expected to do this MANUALLY?

                I sent a request to support and asked if I can get the DB-tables where the user-role relationships are stored. Will report back when I hear something.

                • 5. Re: Report writer role to all smart reporting users.

                  OK, my colleague Maximilian Jost did some digging around in the SmartReporting-DB and found something:

                   

                  - The table "OrgRole" seems to contain a list of all roles which can be assigned to a user.

                   

                  - The table "Person" seems to contain all users. On our system, most users are listed more than once, but I'm not sure if that's normal or not.

                  (We screwed around with this particular environment a while ago, so that may or may not be the cause ).

                   

                  - The table "IpClass" seems to contain users' Logon-IDs (more on that later).

                   

                  - The table "IpContact" seems to contain users' email addresses.

                   

                   

                  Now, let's combine all those findings and look up the current role of a user based on the user's first and last name

                  and display his Logon-ID and email address as well:

                   

                  (All SQL-queries listed here are for MSSQL. If you're on Oracle, adapt where needed.)

                   

                  select c.EmailLeft, d.EmailAddress, b.FirstName, b.LastName, a.*

                  from StaffMemberRole a

                  join Person b

                  on a.IpEmployee = b.IpPerson

                  join IpClass c

                  on a.IpEmployee = c.IpId

                  join IpContact d

                  on a.IpEmployee = d.IpId

                  where b.LastName = 'Smith' and b.FirstName = 'John'

                  AND a.EndDate = '9999-12-31 00:00:00.000';

                  Note: You could replace the "where"-line with the following line if you want to look up the user based on the ID in the "Person"-table:

                   

                  where a.IpEmployee = '12345'

                   

                  or replace it with the following if you want to look up the user based on the Logon-ID:

                   

                  where c.EmailLeft = 'jsmith'

                  (On my environment, the "EmailLeft"-value is the Logon-ID. I'm not sure if that's the case on every system or if this is truly always the

                  left portion of the email address)

                   

                   

                  or look it up based on the email address:

                   

                  where d.EmailAddress = 'jsmith@domain.com'

                   

                   

                  Now the question is: What happens on the DB when you change a user's role through the Smart-Reporting-Admin-GUI? (If we can find that out, then we could easily script the role-assignment and do bulk-updates)

                   

                  Answer:

                   

                  Two things happen!

                   

                  A - The currently active entry in "StaffMemberRole" (which you can easily identify: It has a value of "9999-12-31 00:00:00.000" in the

                  "EndDate" column) is deactivated by having the "EndDate" value changed into the current date.

                   

                  B - A new entry is created in the "StaffMemberRole" table with the new role and an EndDate of "9999-12-31 00:00:00.000".

                   

                   

                  So let's say you'd like to give user "jsmith" the role "Report Writer". Here are two queries which perform steps A and B:

                   

                  We deactivate the current role-assignment:

                  UPDATE StaffMemberRole

                  SET EndDate = CONVERT(varchar(10), GetDate(), 120) + ' 00:00:00.000'

                  WHERE IpEmployee = (

                    select IpId from IpClass a

                    where a.EmailLeft = 'jsmith'

                  )

                  AND EndDate = '9999-12-31 00:00:00.000';

                   

                  We create a new role-assignment with the role we want to assign now:

                  INSERT INTO StaffMemberRole

                  (IpEmployee, IpEmployer, RoleCode, StartDate, EndDate, IpAccessOrg)

                  VALUES ((select IpId from IpClass a where a.EmailLeft = 'jsmith'), '1', 'YFREPORTWRITER', CONVERT(varchar(10), GetDate(), 120) + ' 00:00:00.000', '9999-12-31 00:00:00.000', '1');

                   

                   

                  And that's it!

                   

                   

                  All of this is, of course, absolutely unsupported.

                  3 of 3 people found this helpful
                  • 6. Re: Report writer role to all smart reporting users.
                    Mohammad Rehman

                    Thanks for sharing, please make sure it will not break anything, well test the solution. But I will say this is not the way customer find the solution, I have always said Smart Reporting was premature project which was released. It does not have proper users administration, Personaly would be ideal when we create or update user and assign permissions there should be report roles to assign to user and sync job should sync them, also check which users need to be sync (initially everyone , now only with fixed and floating license users).

                    Smart Reporting User, User Groups, Roles management and Misc.  I had this idea but no one voted and BMC ignore the good ideas as usual when they integrate third part software like in this case YellowFin called Smart Reporting.

                    • 7. Re: Report writer role to all smart reporting users.

                      Hi

                       

                      I have some news from BMC support. Their reponse:

                       

                      We have performed couple of tests regarding this case and you can use the below query to update your Roles using DB Query.

                       

                      -- Below query will update Roles for all the Users Except Administrators

                       

                      Update StaffMemberRole

                      set RoleCode = '<ROLE CODE>'

                      where RoleCode != 'YFADMIN'

                       

                       

                      Available Role Codes to update:

                      -- Corp Writer - YFCORPWRITER

                      -- Report Writer - YFREPORTWRITER

                      -- Report Consumer - YFREPORTCONSUMER

                      -- Dashboard Consumer - YFDASHBOARDCONSUMER

                       

                      Development has tested it inhouse and it is working correctly.

                       

                      This is a better answer than I expected.

                       

                      When I pointed them to this thread and asked them for their opinion, they responded:

                       

                      It is not officially supported or recommended by BMC.

                      So you can test these details on own risk by taking DB backup.

                       

                      I created a new idea, let's see if we can get some traction on this:

                      Smart-Reporting: Allow role-changes in bulk and/or via interface

                      3 of 3 people found this helpful
                      • 8. Re: Report writer role to all smart reporting users.
                        Kelly Logan

                        Update for Smart Reporting 18.05:

                         

                        The Role names (OOB in SR Admin console)  and StaffMemberRole RoleCodes are:

                        1. Consumer & Collaborator - YFREPORTCONSUMER
                        2. Dashboard Consumer - YFDASHBOARDCONSUMER
                        3. Personal Content Writer & Collaborator - YFREPORTWRITER
                        4. Public Content Writer & Collaborator - YFCORPWRITER
                        5. Public Content Writer & Collaborator - Advanced - PUBLICCONTENTWRITERCOLLABORATORADVANCED
                        6. System Administrator - YFADMIN

                         

                        Also, the date format seems different, or is differently displayed on my Oracle SQL Developer.

                         

                        This was the format I had to use to find a current role value:

                             to_date(enddate) = '31-DEC-99'

                         

                         

                        3 of 3 people found this helpful
                        • 9. Re: Report writer role to all smart reporting users.
                          Kelly Logan

                          To change all current Dashboard User access to another role (in this case Public Report Writer & Collaborator):

                           

                          update StaffMemberRole

                          set rolecode = 'YFCORPWRITER'

                          where rolecode = 'YFDASHBOARDCONSUMER' and

                          to_date(enddate) = '31-DEC-99';

                           

                          Note the use of to_date to translate the date value and the check for enddate = '31-DEC-99'. This makes sure you only change the current values.

                          Also the current rolecode is checked specifically so you only change the values you want to change (rather) than any non-admin, as in the BMC suggested statement.

                          1 of 1 people found this helpful
                          • 10. Re: Report writer role to all smart reporting users.
                            Meera Hande

                            Hi Mohammad Rehman,

                             

                            I have one question regarding same context.

                            Right now we are working on BMC Smart reporting 19.02, created custom role which is actually copied and advanced version of Personal Content Writer and Collaborator and keep it as Default.

                            I observed, whenever a new user got added in Smart Reporting, by default user getting Dashboard Consumer role though the default one is custom role.

                            Please suggest, how can I make it possible custom role as Default one for all upcoming new users in Smart Reporting

                            Thanks in Advanced,

                             

                             

                            Regards,

                            Meera

                            • 11. Re: Report writer role to all smart reporting users.
                              Abhay Bagalkoti

                              Hello Meera,

                               

                              User sync utility i.e. RSR:UserSync does a API call which is set to default to Dashboard Consumer. I think this is not publicly open to change it.

                              In such case, you will need to perform steps as mentioned by Kelly Logan.

                               

                              Regards,

                              Abhay

                              2 of 2 people found this helpful