Article ID: 129355, created on Aug 31, 2016, last review on Aug 31, 2016

  • Applies to:
  • Operations Automation

Question

SPLA Reports are inaccurate in OA Control Panel. How to validate the data from the report?

Answer

Microsoft Service Provider Licensing Agreement (SPLA) enables service providers with a hosted offering to license Microsoft products on a monthly basis and provide services and hosted applications to their end customers.

SPLA report is generated based on the amount of currently enabled Exchange mailboxes distributed among the active subscriptions. Mailboxes of the disabled subscriptions along with the mailboxes disabled by the customers are excluded from this value.

Services > E-mail > Mailboxes shows the list of all Exchange mailboxes currently located in the Exchange environment regardless of the subscription and mailbox status. The following query is used to get all the mailboxes:

plesk=> select count(*) from exch_mailboxes;

As the SPLA report is generated, the following query is used to obtain the mailboxes:

plesk=> select count(*) from exch_mailboxes d left join domain_services ds on (d.ds_id = ds.ds_id) inner join subscriptions s on (ds.subscription_id = s.sub_id) where s.is_active=1 and d.mailbox_id not in ( select entity_id from actdir_user_props where enabled =  'n' );

The same figures are displayed in OA control panel.

SPLA usage is calculated by the Create Exchange SPLA report periodic task, which by default is executed once a month.

Details about how SPLA report is calculated:

  1. It fetches all mailboxes that have active status and belong to subscriptions in active status (i.e. disabled mailboxes or mailboxes on disabled subscriptions are not counted):

    plesk=> SELECT mbx.mailbox_id AS mailbox_id, mbx.ds_id AS ds_id, mbx.active_sync_status AS active_sync_status, mbx.mapi_status AS mapi_status, mbx.http_status AS http_status, outl.license_res_id AS license_res_id, gms.account_id AS gms_access, bes.account_id AS bes_access, splaf.name AS spla_feature_name FROM exch_mailboxes mbx  INNER JOIN exch_domain_services ds ON mbx.ds_id = ds.ds_id LEFT JOIN exch_outlook_licenses outl ON mbx.mailbox_id = outl.mailbox_id LEFT JOIN exch_gms_accounts gms on mbx.mailbox_id = gms.mailbox_id LEFT JOIN exch_bes_accounts bes on mbx.mailbox_id = bes.mailbox_id LEFT JOIN exch_spla_usage splau on mbx.mailbox_id = splau.mailbox_id LEFT JOIN exch_spla_features splaf on splau.feature_id = splaf.feature_id WHERE ds.enabled = 'y' AND mbx.mailbox_id NOT IN ( SELECT entity_id FROM actdir_user_props WHERE enabled =  'n' ) ORDER BY ds_id, mailbox_id;
    
  2. For all these mailboxes it fetches the following properties:

    active_sync_status   --  is active sync support enabled for mailbox or not
    mapi_status          --  is MAPI (Outlook) access enabled for mailbox or not
    license_res_id       --  does mailbox have outlook license associated with it
    
  3. OA compares the list of features the mailbox has with the features included in a particular SKU. Checks are performed in order from the lowest SKU (Hosted Exchange Basic SAL) to the highest (Hosted Exchange Enterprise Plus SAL).

  4. OA checks the list of features included in an SKU and then check the list of features in the mailbox. If the mailbox has a feature that is not included in the Hosted Exchange Basic SAL SKU, then the comparison is made with the Hosted Exchange Standard SAL SKU and so on.

Example

There is a mailbox that has:

MAPI access = enabled
Outlook license = no
Public folder access permissions = yes
Active sync access = no

OA checks this mailbox against Hosted Exchange Basic SAL, sees that the mailbox has Public folder permissions. This feature is not included in Hosted Exchange Basic SAL so it checks it against the next SKU.

It checks the mailbox against Hosted Exchange Standard SAL SKU - all features that the mailbox has are included in this SKU so the mailbox is counted as Hosted Exchange Standard SAL, even if it does not have active sync access enabled.

If the mailbox also had Outlook license enabled, then it would be counted as Hosted Exchange Standard Plus SAL because Hosted Exchange Basic SAL and Hosted Exchange Standard SAL do not include Outlook license feature, but Hosted Exchange Standard Plus SAL does.

The following queries can be used to approximately estimate the SPLA report according to the algorithm used by OA:

  • Checking the amount of Hosted Exchange Basic SAL (i.e. mailboxes that do not have Outlook access, Outlook license and active sync access enabled and have no access to any public folder):

    plesk=> SELECT count(mbx.mailbox_id) FROM exch_mailboxes mbx  INNER JOIN exch_domain_services ds ON mbx.ds_id = ds.ds_id LEFT JOIN exch_outlook_licenses outl ON mbx.mailbox_id = outl.mailbox_id WHERE ds.enabled =  'y' AND mbx.mailbox_id NOT IN ( SELECT entity_id FROM actdir_user_props WHERE enabled =  'n' )  AND mbx.active_sync_status=0 AND mbx.mapi_status=0 AND outl.license_res_id is null;
    
  • Checking the amount of Hosted Exchange Standard SAL (i.e. mailboxes that do not have Outlook license, but have either Outlook access or active sync access or permission on a public folder or all 3 of the above):

    plesk=> SELECT count(mbx.mailbox_id) FROM exch_mailboxes mbx  INNER JOIN exch_domain_services ds ON mbx.ds_id = ds.ds_id LEFT JOIN exch_outlook_licenses outl ON mbx.mailbox_id = outl.mailbox_id WHERE ds.enabled =  'y' AND mbx.mailbox_id NOT IN ( SELECT entity_id FROM actdir_user_props WHERE enabled =  'n' )  AND (mbx.active_sync_status=1 OR mbx.mapi_status=1 OR mbx.mailbox_id in (SELECT entity_id from exch_public_folders_permissions)) AND outl.license_res_id is null;
    
  • Checking the amount of Hosted Exchange Standard Plus SAL (i.e. mailboxes that have Outlook license and can also have Outlook access or active sync access or permission on a public folder or all 3 of above):

    plesk=> SELECT count(mbx.mailbox_id) FROM exch_mailboxes mbx  INNER JOIN exch_domain_services ds ON mbx.ds_id = ds.ds_id LEFT JOIN exch_outlook_licenses outl ON mbx.mailbox_id = outl.mailbox_id WHERE ds.enabled =  'y' AND mbx.mailbox_id NOT IN ( SELECT entity_id FROM actdir_user_props WHERE enabled =  'n' )  AND (mbx.active_sync_status=1 OR mbx.mapi_status=1 OR mbx.mailbox_id in (SELECT entity_id from exch_public_folders_permissions)) AND outl.license_res_id is not null;
    

Search Words

SPLA Report

Mailboxes deleted

SPLA reports

SPLA report differs to total mailboxes

5356b422f65bdad1c3e9edca5d74a1ae caea8340e2d186a540518d08602aa065 e12cea1d47a3125d335d68e6d4e15e07

Email subscription for changes to this article
Save as PDF