10 Replies Latest reply on Nov 12, 2013 2:29 AM by MarcelloCentineo

    Script for Web Desk Personal Dashboard

    Jamie Cannon ITSMMVPGroup

      I have a script that will update the personal dashboard in webdesk for "UserTypes"  but does anyone have a modified one to update the personal for a Role or Group?  This would be awesome.  I'm not that good in SQL but I know a lot of you are.  Here is the existing script.

       

      declare

      @DashboardName nvarchar(200)

      declare

      @UserType nvarchar(200)

      set

      @DashboardName = 'Analyst' -- Replace with the name of the dashboard to use.

      set

      @UserType = 'Analyst' -- Replace with the user type to set the default dashboard for.

      --- do not modify below this line ---

      declare

      @DashboardGuid uniqueidentifier

      declare

      @UserTypeGuid uniqueidentifier

      select

      @DashboardGuid = ui_guid from ui_dashboard where ui_title = @DashboardName and ui_client_type > 1

      select

      @UserTypeGuid = tps_guid from tps_user_type where tps_name = @UserType

      if

      @DashboardGuid is null or @UserTypeGuid is null

      begin

       

      print 'Error: Cannot find dashboard or user type.'

       

      return

      end

      declare

      @PreferenceGuid uniqueidentifier

      select

      @PreferenceGuid = tps_guid from tps_system_preference where tps_fullname = 'WebAccess.DefaultDashboard'

      -- update existing personal default dashboard records

      update

      tps_user_preference set tps_value = lower(@DashboardGuid)

       

      where tps_system_preference_guid = @PreferenceGuid

       

      and tps_user_guid in (select tps_guid from tps_user where tps_user_type_guid = @UserTypeGuid)

      -- add personal default dashboard records for remaining users

      insert

      into tps_user_preference (tps_guid, tps_user_guid, tps_system_preference_guid, tps_value)

       

      (select NEWID(), tps_guid, @PreferenceGuid, lower(@DashboardGuid) from tps_user

       

      where tps_user_type_guid = @UserTypeGuid and tps_deleted = 0

       

      and tps_guid not in (select tps_user_guid from tps_user_preference where tps_system_preference_guid = @PreferenceGuid))

        • 1. Re: Script for Web Desk Personal Dashboard
          MarcelloCentineo Specialist

          let me know if someone send you a script to set it for a group and/or role!

           

          thanks

          marcello

          • 2. Re: Script for Web Desk Personal Dashboard
            Expert

            Hi Jamie,

             

            This script should work but as always - test, test, test and backup x10 :

             

             

            declare @DashboardName nvarchar(200)
            declare @UserRole nvarchar(200)

            set @DashboardName = 'Analyst' -- Replace with the name of the dashboard to use.
            set @UserRole = 'Analyst' -- Replace with the Role to set the default dashboard for.

            --- do not modify below this line ---

            declare @DashboardGuid uniqueidentifier
            declare @UserRoleGuid uniqueidentifier

            select @DashboardGuid = ui_guid from ui_dashboard where ui_title = @DashboardName and ui_client_type > 1
            select @UserRoleGuid = tps_guid from tps_role where tps_name = @UserRole

            if @DashboardGuid is null or @UserRoleGuid is null
            begin
            print 'Error: Cannot find dashboard or Role.'
            return
            end

            declare @PreferenceGuid uniqueidentifier
            select @PreferenceGuid = tps_guid from tps_system_preference where tps_fullname = 'WebAccess.DefaultDashboard'

            -- update existing personal default dashboard records

            update tps_user_preference set tps_value = lower(@DashboardGuid)
            where tps_system_preference_guid = @PreferenceGuid
            and tps_user_guid in (select tps_user_guid from tps_user_role where tps_role_guid = @UserRoleGuid)

            -- add personal default dashboard records for remaining users

            insert into tps_user_preference (tps_guid, tps_user_guid, tps_system_preference_guid, tps_value)
            (select NEWID(), tps_user_guid, @PreferenceGuid, lower(@DashboardGuid) from tps_user_role
            where tps_role_guid = @UserRoleGuid and tps_deleted = 0
            and tps_guid not in (select tps_user_guid from tps_user_preference where tps_system_preference_guid = @PreferenceGuid))


            Cheers,

            Hadyn

            • 3. Re: Script for Web Desk Personal Dashboard
              MarcelloCentineo Specialist

              Good morning,

               

              Thanks! Do you also have a script to set it for a specific group?

               

              Thanks and Regards,

              Marcello 

              • 4. Re: Script for Web Desk Personal Dashboard
                Jamie Cannon ITSMMVPGroup

                Invalid column name 'tps_role_guid'.

                 

                I get that when running it

                • 5. Re: Script for Web Desk Personal Dashboard
                  Expert

                  I suggest that it should be tps_role_id, rather than tps_role_guid, as far as the tps_user_role table goes.

                   

                  (Addendum - The tps_user_guid column is also just tps_user_id in this table for some reason.  Not sure why they seem to have changed their naming conventions...)

                   

                  Message was edited by: paul.enkelaar

                  • 6. Re: Script for Web Desk Personal Dashboard
                    Expert

                    Thanks for the added info Paul, I did the script from memory so I figured there would be some slight changes required

                     

                    Cheers,

                    Hadyn

                    • 7. Re: Script for Web Desk Personal Dashboard
                      Jamie Cannon ITSMMVPGroup

                      It doesn't like this part:


                      and tps_guid not in (select tps_user_guid from tps_user_preference where tps_system_preference_guid = @PreferenceGuid))

                      says tps_guid is an invalid column name
                      • 8. Re: Script for Web Desk Personal Dashboard
                        Expert

                        Hi Jamie

                         

                        I think this line should start with "and tps_user_guid not in..."

                         

                        Cheers

                         

                        Paul

                        • 9. Re: Script for Web Desk Personal Dashboard
                          smiddleton Expert

                          Hi All,

                           

                          As I have different user types (End User, Contact, Account Manager and Analyst) within some support groups for approval purposes  I decided I would expand the SQL script to allow me to set the personal default dashboard based on the group as well.

                           

                           

                          declare @DashboardName nvarchar(200)
                          declare @UserType nvarchar(200)
                          declare @UserGroup nvarchar(200)
                          set @DashboardName = 'Support Me' -- Replace with the name of the dashboard to use.
                          set @UserType = 'Analyst' -- Replace with the user type to set the default dashboard for.
                          set @UserGroup = 'IT Support Group' -- Replace with the group which the users must belong to.
                          --- do not modify below this line ---
                          declare @DashboardGuid uniqueidentifier
                          declare @UserTypeGuid uniqueidentifier
                          declare @UserGroupGuid uniqueidentifier
                          select @DashboardGuid = ui_guid from ui_dashboard where ui_title = @DashboardName and ui_client_type > 1
                          select @UserTypeGuid = tps_guid from tps_user_type where tps_name = @UserType
                          select @UserGroupGuid = tps_guid from tps_group where tps_title = @UserGroup
                          if @DashboardGuid is null or @UserTypeGuid is null or @UserGroup is null
                          begin
                               print 'Error: Cannot find dashboard or user type or the support group.'
                               return
                          end
                          declare @PreferenceGuid uniqueidentifier
                          select @PreferenceGuid = tps_guid from tps_system_preference where tps_fullname = 'WebAccess.DefaultDashboard'
                          -- update existing personal default dashboard records
                          update tps_user_preference set tps_value = lower(@DashboardGuid)
                               where tps_system_preference_guid = @PreferenceGuid
                               and tps_user_guid in (select tps_guid from tps_user where tps_user_type_guid = @UserTypeGuid)
                          and tps_user_guid in (select tps_user_id from tps_user_group where tps_group_id = @UserGroupGuid)
                          -- add personal default dashboard records for remaining users
                          insert into tps_user_preference (tps_guid, tps_user_guid, tps_system_preference_guid, tps_value)
                               (select NEWID(), tps_guid, @PreferenceGuid, lower(@DashboardGuid) from tps_user
                                    where tps_user_type_guid = @UserTypeGuid and tps_deleted = 0 and tps_guid in (select tps_user_id from tps_user_group where tps_group_id = @UserGroupGuid)
                                    and tps_guid not in (select tps_user_guid from tps_user_preference where tps_system_preference_guid = @PreferenceGuid))

                           

                           

                           

                           

                           

                           

                           

                           

                           

                           

                           

                           

                           

                           

                           

                           

                           

                           

                           

                           

                           

                           

                          However please note we are currently NOT running web access so haven't had a chance to test this script, please verify against a test system and backup before running in live.

                           

                          You can basically use this to set the dashbaord and the type of users you want it to apply to and where the users belong to a specific group as well.

                          • 10. Re: Script for Web Desk Personal Dashboard
                            MarcelloCentineo Specialist

                            Here is the version with the new columns

                            Worked for me

                             

                            ----------------------------------------------------------------------------------------------------------------------------------

                             

                            declare @DashboardName nvarchar(200)

                            declare @UserRole nvarchar(200)

                             

                            set @DashboardName = 'Dashboard' -- Replace with the name of the dashboard to use.

                            set @UserRole = 'Analyst' -- Replace with the Role to set the default dashboard for.

                             

                            --- do not modify below this line ---

                             

                            declare @DashboardGuid uniqueidentifier

                            declare @UserRoleGuid uniqueidentifier

                             

                            select @DashboardGuid = ui_guid from ui_dashboard where ui_title = @DashboardName and ui_client_type > 1

                            select @UserRoleGuid = tps_guid from tps_role where tps_name = @UserRole

                             

                             

                            if @DashboardGuid is null or @UserRoleGuid is null

                            begin

                            print 'Error: Cannot find dashboard or Role.'

                            return

                            end

                             

                            declare @PreferenceGuid uniqueidentifier

                            select @PreferenceGuid = tps_guid from tps_system_preference where tps_fullname = 'WebAccess.DefaultDashboard'

                             

                            -- update existing personal default dashboard records

                             

                            update tps_user_preference set tps_value = lower(@DashboardGuid)

                            where tps_system_preference_guid = @PreferenceGuid

                            and tps_user_guid in (select tps_user_guid from tps_user_role where tps_role_id = @UserRoleGuid)

                             

                            -- add personal default dashboard records for remaining users

                             

                            insert into tps_user_preference (tps_guid, tps_user_guid, tps_system_preference_guid, tps_value)

                            (select NEWID(), tps_user_id, @PreferenceGuid, lower(@DashboardGuid) from tps_user_role

                            where tps_role_id = @UserRoleGuid -- and tps_deleted = 0

                            and tps_user_id not in (select tps_user_guid from tps_user_preference where tps_system_preference_guid = @PreferenceGuid))