- How to find the start date of an Azure subscription?
- How is the resource usage calculated?
Start date of Azure subscription will always depend on the date when the Azure subscription was created under the instance. This can be find at "Applications > Azure Cloud Solution Provider > Instances > Instance ID" in OA provider control panel.
If the subscription is imported from an existing Azure instance, the subscription start date can be different. In order to find the usage cost we need to find the exact date when the subscription was created on Azure side. To do this follow the steps below:
In Azure database execute the following command:
SELECT t.id, t.name, t.provider_commerce_id, t.client_commerce_id, t.account_id, t.login, t.temp_password, tri.tenant_oss_id, tri.tenant_id, tri.last_tracking_id, tri.state, tri.retail_rate, tri.total_cost FROM tenants t LEFT JOIN tenant_resource_instances tri ON t.id = tri.tenant_id WHERE t.name = <Name> ;
<Name>value should be equal to the Microsoft domain of corresponding subscription.
From the above query we will obtain the
"tenant_oss_id"value. Using this we can find the start date of the subscription on Azure side, which will be included in the resource usage calculation for the first billing order. For this example, let's assume that the first billing period ended on Jan 10 2017 (it is passed as the value to the end_time column):
select start_time from consumption_history where end_time <= '2017-01-10 16:00:00.0000000 +00:00' and tenant_oss_id = <tenant_oss_id>;
From the result of above query, we can obtain the start date
You can calculate the usage cost using the following query:
select sum(*) from consumption_history where end_time <= '2017-01-10 16:00:00.0000000 +00:00' and tenant_oss_id = <tenant_oss_id>;
This step is only valid for the first billing order generated for the subscription.
For the second billing order, the usage can be obtained from the query:
select SUM(cost) from consumption_history where end_time >= '2017-01-10 16:00:00.0000000 +00:00' AND end_time <= '2017-02-10 16:00:00.0000000 +00:00' and tenant_oss_id = <tenant_oss_id>;
Similar queries can be used to obtain usage for further periods.