2 Replies Latest reply: Mar 31, 2009 7:18 AM by user6372894 RSS

    overquota notification...

    38104
      Hello,
      Does any know how can we settup OCS to send a notification to Admin and/or user when the user quota close to 100%... ex: 90%...?
      Thanks
        • 1. Re: overquota notification...
          user6372894
          This might help. I'm trying to found out how to find the workspace name from the ID in the content.ODM_CONTENTQUOTA table.

          column ALLOC_IN_MEG format 999,999,999.00
          column CONSUMED_IN_MEG format 999,999,999.00
          column USED_IN_MEG format 999,999,999.00


          select ID,
          ALLOCATEDSTORAGE / (1024 * 1024) as ALLOC_IN_MEG,
          CONSUMEDSTORAGE / (1024 * 1024) as CONSUMED_IN_MEG ,
          ( CONSUMEDSTORAGE / ALLOCATEDSTORAGE ) * 100 as USED_IN_MEG
          from content.ODM_CONTENTQUOTA order by 3 desc;
          • 2. Re: overquota notification...
            user6372894
            To check for email quota limits try this:

            NOTE: You can create a custom process to send out emails to users for email Quotas. The following examples will show you waat tables to use.



            Step 1
            =====


            The goal is to find the userid for username. For this example Lets say userid = 1234;


            column username format a20
            column domain format a15


            select userid, username, domain, usage / (1024 * 1024) as usage_MB from es_mail.es_user where username like '%cboman%'




            Step 2
            =====


            This will give you the space for a specific user.


            SELECT nvl(sum(nvl(i.msg_size, 0)), 0)
            FROM es_mail.es_instance i,
            es_mail.es_folder f
            WHERE i.folder_id = f.folder_id AND
            f.userid = 1235;





            Step 3 (optional)
            ============

            This shows the folders own by the user

            select folder_id,folder_name, userid from es_mail.es_folder where userid = 3122;




            Step 4 (optional)
            ============

            Show the users that usage and sum_usage that do not match



            column userid format 9999999
            column username format a20

            select a.userid,
            a.username,
            a.usage,
            b.SUM_USAGE
            from es_mail.es_user a,
            (SELECT f.userid,
            nvl(sum(nvl(i.msg_size, 0)), 0) as SUM_USAGE
            FROM es_mail.es_instance i,
            es_mail.es_folder f
            WHERE i.folder_id = f.folder_id
            group by f.userid) b
            where a.userid = b.userid and
            a.usage != b.SUM_USAGE;



            Step 5 (optional)
            ============

            Fix the usage when they do not agree (See MeatLink note: Note:395485.1)


            SELECT nvl(sum(nvl(i.msg_size, 0)), 0)
            FROM es_mail.es_instance i,
            es_mail.es_folder f
            WHERE i.folder_id = f.folder_id AND
            f.userid = 1235;


            UPDATE es_mail.es_user
            SET usage = 0
            WHERE userid = 1235;





            I hope this helps