Clarity

  • Private Community
Back to discussions
Expand all | Collapse all

CA Clarity Tuesday Tip  - Querying User's Notification Options

System

SystemNov 14, 2012 02:44 PM

Thanks for the tip Connie!

  • 1.  CA Clarity Tuesday Tip  - Querying User's Notification Options

    Posted Nov 07, 2012 08:36 PM
    This is a quick tip to remind you that there is a way to see what a certain user has enabled for notification under their Account Settings, which can only be seen if you log in as that user.

    This can be useful if you need to make sure email option is checked off for executives so that they don't get unwanted emails generated by Clarity

    See KB TEC572247
    Clarity: How Can I See What Notifications Users Have Enabled Under Account Settings

    URL: https://support.ca.com/irj/portal/kbtech?docid=572247

    Basically you can expand this query to look for specific user(s), and if you need to DISABLE a notification type for a user, when you have to, you would ADD a row to the table. Open an issue with Support if you need assistance with a specific situation. Remember that adding a row checks off the option; removing a row enable the option.

    SELECT NOTIFICATION_TYPE, METHOD
    FROM CLB_NOTIFICATION_PREFS

    The KB is completed with screenshots to illustrate an example. Check it out!


  • 2.  RE: CA Clarity Tuesday Tip  - Querying User's Notification Options

    Posted Nov 08, 2012 02:56 AM
    Thank you Connie for the great tip and opening the can of notifications.
    Basically you can expand this query to look for specific user(s), and if you need to DISABLE a notification type for a user, when you have to, you would ADD a row to the table. Open an issue with Support if you need assistance with a specific situation.
    That sounds like an authorization for anybody to turn off the email notifications for all types or alerts for all users in his or her organization with 10, 100, 1000, 10 000, 100 000 .... users - right?


    Is there an equally simple way to delete the notifications which are older than a week, a month, a year, 10 years ...?


    Martti K.


  • 3.  RE: CA Clarity Tuesday Tip  - Querying User's Notification Options

    Posted Nov 13, 2012 05:35 PM
    Hey Martti
    Yes I know this is opening up a can of protein... I will have faith that if anyone wants to use the trick for changing account settings for users, he/she will test and verify first, and open Support issues if he/she doesn't feel 100% sure -_-

    And no currently we haven't tried or know of a way to do the similar update for removing old notifications. It would make a good ERQ though

    Regards
    Connie


  • 4.  RE: CA Clarity Tuesday Tip  - Querying User's Notification Options

    Posted Nov 14, 2012 02:36 PM
    Thanks Connie,

    Just looked at a system with 3 000 000 records in the CLB_NOTIFICATIONS.

    Martti K.


  • 5.  RE: CA Clarity Tuesday Tip  - Querying User's Notification Options

    Posted Nov 15, 2012 01:29 PM
    We have almost 5 000 000 records in CLB_NOTIFCATIONS.

    Getting sub-1 second screen response times, so doesn't appear to be causing a user performance issue.

    Expect a cleanup is in order. Other than housekeeping, are there any other reason why one might worry about such a large number of notifications?

    Dale

    another_martink wrote:

    Thanks Connie,

    Just looked at a system with 3 000 000 records in the CLB_NOTIFICATIONS.

    Martti K.


  • 6.  RE: CA Clarity Tuesday Tip  - Querying User's Notification Options

    Posted Nov 15, 2012 01:56 PM
    Just housekeeping.
    The system I looked at has 3000 users so that is an average of 1000 per user. How many has yours?
    Just wondering in this lean and mean world what is the justification?
    If that is not under control what else is not?

    Martti K.


  • 7.  RE: CA Clarity Tuesday Tip  - Querying User's Notification Options

    Posted Nov 15, 2012 03:42 PM
    Almost 7000 users, on Niku/Clarity web app since 2002.

    Justification is that the number of notifications doesn't appear on any of our Pareto charts regarding performance or maintenance problems, nor is it causing us to add SAN drives. So, no cost.

    We have other issues with DB size that are causing real mainteance issues, adding to our maintenance costs. The size of the DMS is number one on the Pareto chart. Can't fix everything at the same time, and everything that could be fixed doesn't have the same payback.

    another_martink wrote:

    Just housekeeping.
    The system I looked at has 3000 users so that is an average of 1000 per user. How many has yours?
    Just wondering in this lean and mean world what is the justification?
    If that is not under control what else is not?

    Martti K.


  • 8.  RE: CA Clarity Tuesday Tip  - Querying User's Notification Options

    Posted Nov 15, 2012 04:01 PM
    Your system is then better both for per user and over time for the system I am looking was installed in 2007.

    Can you tell more about
    " Pareto charts regarding performance or maintenance problems"
    or point me to somewhere to read about those.

    What kind of queries are you using to determine what items are using the biggest space in the database?

    Are the tables expected to use more space than the indices?

    Martti K.


  • 9.  RE: CA Clarity Tuesday Tip  - Querying User's Notification Options

    Posted Dec 03, 2012 11:42 AM
    Hi Dale,

    We can delete the old notifications from the CLB_NOTIFICATIONS table...

    DELETE clb_notifications
    FROM clb_notifications
    WHERE clb_notifications.created_date <(GETDATE()-15)

    this can be done in a loop and we can limit the number of records deleted at a time to 5000.

    Thanks and Regards,
    Rajini


  • 10.  RE: CA Clarity Tuesday Tip  - Querying User's Notification Options

    Posted Dec 17, 2012 01:55 PM
    Supported?

    Martti K.


  • 11.  RE: CA Clarity Tuesday Tip  - Querying User's Notification Options

    Posted Dec 19, 2012 10:45 AM
    Hi Martti,

    This was suggested by CA for one of the issues we had. May need to check with CA if it is supported in your environments too.

    Thanks and Regards,
    Rajini


  • 12.  RE: CA Clarity Tuesday Tip  - Querying User's Notification Options

     
    Posted Nov 14, 2012 02:44 PM
    Thanks for the tip Connie!


  • 13.  Re: RE: CA Clarity Tuesday Tip  - Querying User's Notification Options

    Posted Aug 07, 2014 12:48 AM

    Hi Friends,

     

    We have almost 16 million records in our CLB_NOTIFICATIONS table. I see from this thread that CA suggested Rajini to just do a SQL delete from this table. But the Tech Ref Guide shows that CLB_NOTIFICATION_ASSOCS table is also linked to this notifications table as below (CLB_NOTIFICATION_ASSOCS.INSTANCE_ID=CLB_NOTIFICATIONS.ID).

     

    We are not sure if any other tables are also referring the records in CLB_NOTIFICATIONS table. Any ideas? I guess once we get the complete list of linked tables, we would be more confident to do this SQL delete

     

    Regards,

    Georgy



  • 14.  Re: CA Clarity Tuesday Tip  - Querying User's Notification Options

    Posted Aug 07, 2014 04:24 AM

    Open a case with support and request for the delete query.



  • 15.  Re: CA Clarity Tuesday Tip  - Querying User's Notification Options

    Posted Aug 07, 2014 04:58 AM

    Hi Urmas,

     

    The query seems to be the same. Please see more discussion on this here - Re: RE: How to delete Notification in Organizer Tab

     

    Regards,

    Georgy



  • 16.  Re: CA Clarity Tuesday Tip  - Querying User's Notification Options

    Posted Aug 07, 2014 07:36 AM

    Well, if it is approved by support or even more so given by support through the official channel it takes some of the responsibility off you.

    At the end it is still you who are responsible restoring the system if you get into a mess. Bear that in mind.

    Also the posts on these message boards are opinions of the posters some from more experienced users than others these are not official from support.

    Having said that, you may want to do better than what the support instructions lead you to. (I don't like the orphans in the investment hierarchy tables either). If you know what you are doing and bearing in mind that at the end you are responsible of the consequences who is stopping you?



  • 17.  Re: CA Clarity Tuesday Tip  - Querying User's Notification Options

    Posted Feb 16, 2016 11:34 AM

    We will need to soon update the KB article and this post to reflect pre and post 14.3 behaviours.

     

    Reference: Re: Notifications in Clarity 14.3