Clarity

Expand all | Collapse all

I want to find Get the list of portlets they(users) have been configured

  • 1.  I want to find Get the list of portlets they(users) have been configured

    Posted Nov 14, 2017 09:15 AM

    Want to Get the list of portlets they(users) have been configured ,Reason is we want to Publish the Portlets,So we want to limit the Impact to end users when we do this



  • 2.  Re: I want to find Get the list of portlets they(users) have been configured

    Posted Nov 14, 2017 10:23 AM

    muthukumaran.mohanarengan The below query will list all the portlets users have configured. You can apply additional filters for particular users or particular portlets

     

    select c.last_name, c.first_name, nls.name portlet_name from cmn_page_portlets pp
    inner join cmn_portlets p on pp.portlet_id=p.id
    inner join CMN_CAPTIONS_NLS nls on nls.pk_id=p.id and nls.language_code='en' AND nls.table_name='CMN_PORTLETS'
    inner join cmn_sec_users c on c.id=pp.principal_id
    where pp.principal_type='USER'



  • 3.  Re: I want to find Get the list of portlets they(users) have been configured

    Broadcom Employee
    Posted Nov 14, 2017 02:26 PM

    Thanks @gayathri for providing the code!  (I'm copying it as well;  have customers always looking for this!)



  • 4.  Re: I want to find Get the list of portlets they(users) have been configured

    Posted Nov 16, 2017 04:57 AM

    Gayathri,

     

    Sorry that query is not correct, cmn_page_portlets principal type = 'USER' contains where the portlet has been added to custom pages rather than where the portlet has actually been configured.

     

    Here's the NSQL you can copy/paste into a query and build a grid portlet, 

    This will return all portlets (graph/grid) configured and you'll be able to filter on user or portlet.

     

    I wrote some more portlets for object list config and page config, hope to share this dashboard at some point.

     

    SELECT @SELECT:DIM:USER_DEF:IMPLIED:DATA:x.unique_key:unique_key@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:DATA:x.created_date:created_date@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:DATA:x.portlet_id:portlet_id@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:DATA:x.portlet_name:portlet_name@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:DATA:x.email_address:email_address@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:DATA:x.user_name:user_name@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:DATA:x.full_name:full_name@
    FROM
    (
    SELECT
    gr.created_date||gr.id||u.id unique_key,
    gr.created_date,
    gr.portlet_id,
    cmn.name portlet_name,
    u.email_address,
    u.user_name,
    u.last_name || ', '|| u.first_name full_name
    FROM
    cmn_sec_users u
    INNER JOIN
    (SELECT DISTINCT g.id, g.principal_id, g.portlet_id, g.created_date FROM cmn_grids g WHERE g.principal_type ='USER' AND g.dal_type = 'nsql'
    UNION ALL
    SELECT DISTINCT g.id, g.principal_id, g.portlet_id, g.created_date FROM cmn_graphs g WHERE g.principal_type ='USER' AND g.dal_type = 'nsql'
    ) gr ON gr.principal_id = u.id
    INNER JOIN cmn_captions_nls cmn ON ( language_code = 'en' AND pk_id = gr.id AND table_name = 'CMN_GRIDS' )
    WHERE u.user_status_id !=201
    ORDER BY cmn.name) x
    WHERE
    @FILTER@

     

    As always you use the code at your own risk 



  • 5.  Re: I want to find Get the list of portlets they(users) have been configured

    Posted Nov 16, 2017 05:16 AM

    Theres some SQL in here TIP : Documenting your Configuration (via SQL)  regarding user's object configurations too.



  • 6.  Re: I want to find Get the list of portlets they(users) have been configured

    Posted Nov 16, 2017 05:42 AM

    Hi All,

     

    Thanks for your Response...

     

    Is it possible to find what are the Fields users has been configured in the Port lets.

     

    I want the list of Portlet  fields what user has been configured .



  • 7.  Re: I want to find Get the list of portlets they(users) have been configured

    Posted Nov 17, 2017 04:09 AM

    For grids this will work, need to wrap in NSQL.

    Recommend to read Dave's tip

    SELECT
    u.user_name,
    u.last_name || ', ' || u.first_name name,
    col.position,
    col.col_code,
    cmn.name,
    col.col_type
    FROM
    cmn_portlets p
    INNER JOIN cmn_grids gr ON gr.portlet_id = p.id
    INNER JOIN cmn_grid_cols col ON col.grid_id = gr.id AND col.position != -1
    INNER JOIN cmn_sec_users u ON u.id = gr.principal_id
    LEFT JOIN cmn_captions_nls cmn ON ( language_code = 'en' AND pk_id = col.id AND table_name = 'CMN_GRID_COLS' )
    WHERE p.portlet_code = 'PORTLET_CODE_HERE'
    AND gr.principal_type = 'USER'
    ORDER BY u.user_name, col.position


  • 8.  Re: I want to find Get the list of portlets they(users) have been configured

    Posted Nov 23, 2017 06:31 AM

    Hi Andrew,

     

    Thanks for your reply...

     

    Is it possible to find what are the Attributes assigned to Default values using tables.



  • 9.  Re: I want to find Get the list of portlets they(users) have been configured

    Posted Nov 23, 2017 09:29 AM

    You can find attribute defaults using the SQL in the referenced "tip" - look for default_value in the SQL

    You can then compare the values in the data-tables to this.

    (Manually or via simple SQL would be easiest, but if you need to automate a solution then that could be done too - semi-automated where you know all the attributes you are interested in, fully-automated would require some dynamic SQL which could get complicated)



  • 10.  Re: I want to find Get the list of portlets they(users) have been configured

    Posted Nov 23, 2017 10:40 AM

    Hi David,

     

    Thank you for your reply...

     

    Is it possible to get the below query to find the Default values assigned to which Attributes. I had checked unable to find the column like default. Please let me know on which table default attribute value is there.

     

    SELECT 
    u.user_name, 
    u.last_name || ', ' || u.first_name name, 
    p.portlet_code,
    cmn.name Attribute_name,
    col.position, 
    col.col_code Attribute_Id, 
    col.col_type
    FROM 
    cmn_portlets p
    INNER JOIN cmn_grids gr ON gr.portlet_id = p.id
    INNER JOIN cmn_grid_cols col ON col.grid_id = gr.id AND col.position != -1
    INNER JOIN cmn_sec_users u ON u.id = gr.principal_id
    LEFT JOIN cmn_captions_nls cmn ON ( language_code = 'en' AND pk_id=col.id and  table_name in('CMN_GRID_COLS'))
    WHERE 


     gr.principal_type = 'USER'



  • 11.  Re: I want to find Get the list of portlets they(users) have been configured

    Posted Nov 23, 2017 10:51 AM

    I'd recommend re-reading Dave's tip and looking at the tables.

    Your looking for "SYSTEM" and there wouldn't be a principal_id associated.

    i.e.

     

    SELECT
    col.position,
    col.col_code,
    cmn.name,
    col.col_type
    FROM
    cmn_portlets p
    INNER JOIN cmn_grids gr ON gr.portlet_id = p.id
    INNER JOIN cmn_grid_cols col ON col.grid_id = gr.id AND col.position != -1
    --INNER JOIN cmn_sec_users u ON u.id = gr.principal_id
    LEFT JOIN cmn_captions_nls cmn ON ( language_code = 'en' AND pk_id = col.id AND table_name = 'CMN_GRID_COLS' )
    WHERE p.portlet_code = 'PORTLET_CODE_HERE'
    AND gr.principal_type = 'SYSTEM'
    ORDER BY col.position


  • 12.  Re: I want to find Get the list of portlets they(users) have been configured

    Posted Nov 30, 2017 06:00 AM

    Hi Andrew,

     

    I want to find the Default values listed in Filter using the below Query.

    Ex:

    In portlet filter am using this Default value as 'Yes' below field Is_Active, I want this kind of Information . Is it Possible.

    As Dave suggested am trying the below tables but Default values can't to find.

     

    select portlet_code,is_default,c.attribute_code,c.attribute_value,user_name
    from cmn_portlets a ,
    cmn_sec_users u,
    CMN_ATTRIBUTE_VALUE_SETS b,
    cmn_attribute_values c
     where  
     cast(b.VIEW_CODE as nvarchar2(255)) = cast(a.ID as nvarchar2(255)) 
    and portlet_code='pmo_ewo_hours'
    and b.business_object_id=c.id
    and user_status_id=200
    and user_name='X663686'
    and is_default=1  

      

     

     

    Attribute_code



  • 13.  Re: I want to find Get the list of portlets they(users) have been configured

    Posted Nov 30, 2017 07:04 AM

    Your join between CMN_ATTRIBUTE_VALUE_SETS and CMN_ATTRIBUTE_VALUES is very wrong.

     

    i.e. not

    and b.business_object_id=c.id

    but

    and b.id=C.attribute_set_id



  • 14.  Re: I want to find Get the list of portlets they(users) have been configured

    Posted Nov 30, 2017 07:26 AM

    Hi David,

    Thank You So Much.....Now the records are coming.

    But there are so many Duplicate Attribute_code are coming...Is there anything I missed the join condition.

     

    select portlet_code,is_default,c.attribute_code,c.attribute_value,user_name
    from cmn_portlets a ,
    cmn_sec_users u,
    CMN_ATTRIBUTE_VALUE_SETS b,
    cmn_attribute_values c
     where  
     cast(b.VIEW_CODE as nvarchar2(255)) = cast(a.ID as nvarchar2(255)) 
    and portlet_code='pmo_ewo_hours'
    and b.id=C.Attribute_Set_id
    and user_status_id=200
    and user_name='X663686'
    and is_default=1 

      select * from cmn_attribute_value_sets
     



  • 15.  Re: I want to find Get the list of portlets they(users) have been configured

    Posted Nov 30, 2017 07:40 AM

    Well you are not joining CMN_SEC_USERS to anything 

     

    and u.id = b.user_id



  • 16.  Re: I want to find Get the list of portlets they(users) have been configured

    Posted Nov 30, 2017 07:47 AM

    Hi David,

     

    Yes I tried that one, no record exists using this and u.id = b.user_id . If I remove the below condition

    and u.id = b.user_id

     records are fetching mentioned already duplicate attribute codes are repeating.

     

    select portlet_code,is_default,c.attribute_code,c.attribute_value,user_name
    from cmn_portlets a ,
    cmn_sec_users u,
    CMN_ATTRIBUTE_VALUE_SETS b,
    cmn_attribute_values c
     where  
     cast(b.VIEW_CODE as nvarchar2(255)) = cast(a.ID as nvarchar2(255)) 
    and portlet_code='pmo_ewo_hours'
    and b.id=C.Attribute_Set_id
    and u.id = b.user_id
    and user_status_id=200
    and user_name='X663686'



  • 17.  Re: I want to find Get the list of portlets they(users) have been configured

    Posted Nov 30, 2017 08:27 AM

    You need the join to cmn_sec_users otherwise what you are seeing is all the data relating to other users (other than the X663686 user).

     

    If you are getting no data then there is not default filter set for that user.

     

    Run this;

     

    select portlet_code,is_default,c.attribute_code,c.attribute_value,u.user_name 
    from cmn_portlets a ,
    cmn_sec_users u,
    CMN_ATTRIBUTE_VALUE_SETS b,
    cmn_attribute_values c 
     where   
     cast(b.VIEW_CODE as nvarchar2(255)) = cast(a.ID as nvarchar2(255))  
    and portlet_code='pmo_ewo_hours' 
    and b.id=C.Attribute_Set_id
    and u.id = b.user_id

     

    and you will see the data and username (for other users)