通过

用于商业市场的编程分析的示例查询

  • 项目

本文提供针对 Microsoft 商业市场“订单”、“使用量”和“客户”报表的示例查询。 可以通过调用 “创建报表查询 API”终结点，根据用例引用这些查询并创建更多查询。

有关列名称、属性和说明的详细信息，请参阅以下文章：

“客户”报表查询

这些示例查询适用于“客户”报表。

查询说明 示例查询
列出所选日期之前合作伙伴的活动客户的客户详细信息 SELECT DateAcquired,CustomerCompanyName,CustomerId FROM ISVCustomer WHERE IsActive = 1
列出所选日期之前合作伙伴的流失客户的客户详细信息 SELECT DateAcquired,CustomerCompanyName,CustomerId FROM ISVCustomer WHERE IsActive = 0
过去 6 个月来自特定地理位置的新客户列表 SELECT DateAcquired,CustomerCompanyName,CustomerId FROM ISVCustomer WHERE DateAcquired <= ‘2020-06-30’ AND CustomerCountryRegion = ‘United States’

“使用情况”报表查询

这些示例查询适用于“使用量”报表。

查询说明 示例查询
列出使用情况详细信息，包括过去 6 个月“通过 Azure 计费”市场许可证类型的虚拟机 (VM) 规范化使用情况 SELECT MonthStartDate, NormalizedUsage FROM ISVUsage WHERE MarketplaceLicenseType = ‘Billed Through Azure’ AND OfferType NOT IN (‘Azure Applications’, ‘SaaS’) TIMESPAN LAST_6_MONTHS
列出使用情况详细信息，包括过去 12 个月“通过 Azure 计费”市场许可证类型的 VM 原始使用情况 SELECT MonthStartDate, RawUsage FROM ISVUsage WHERE MarketplaceLicenseType = ‘Billed Through Azure’ AND OfferType NOT IN (‘Azure Applications’, ‘SaaS’) TIMESPAN LAST_1_YEAR
列出使用情况详细信息，包括过去 6 个月“自带许可证”市场许可证类型的 VM 规范化使用情况 SELECT MonthStartDate, NormalizedUsage FROM ISVUsage WHERE MarketplaceLicenseType = ‘Bring Your Own License’ AND OfferType NOT IN (‘Azure Applications’, ‘SaaS’) TIMESPAN LAST_6_MONTHS
列出过去 6 个月“自带许可证”市场许可证类型的 VM 原始使用情况的详细信息 SELECT MonthStartDate, RawUsage FROM ISVUsage WHERE MarketplaceLicenseType = ‘Bring Your Own License’ AND OfferType NOT IN (‘Azure Applications’, ‘SaaS’) TIMESPAN LAST_6_MONTHS
列出上个月付费计划的使用情况详细信息，包括使用日期、每日总规范化使用量和“估算的扩增费用(PC/CC)” SELECT UsageDate, NormalizedUsage, EstimatedExtendedChargePC FROM ISVUsage WHERE SKUBillingType = ‘Paid’ ORDER BY UsageDate DESC TIMESPAN LAST_MONTH
列出上个月付费计划的使用情况详细信息，包括使用日期、每日总原始使用量和“估算的扩增费用(PC/CC)” SELECT UsageDate, RawUsage, EstimatedExtendedChargePC FROM ISVUsage WHERE SKUBillingType = ‘Paid’ ORDER BY UsageDate DESC TIMESPAN LAST\_MONTH
列出使用情况详细信息，包括套餐名称、过去 6 个月“通过 Azure 计费”市场许可证类型的 VM 规范化使用情况 SELECT OfferName, NormalizedUsage FROM ISVUsage WHERE MarketplaceLicenseType = ‘Billed Through Azure’ AND OfferName = ‘Example Offer Name’ TIMESPAN LAST_6_MONTHS
列出使用情况详细信息，包括套餐名称、过去 6 个月的计量使用情况 SELECT OfferName, MeteredUsage FROM ISVUsage WHERE OfferName = ‘Example Offer Name’ AND OfferType IN (‘SaaS’, ‘Azure Applications’) TIMESPAN LAST_6_MONTHS
列出过去 6 个月所有专用产品/服务的所有套餐使用情况详细信息 SELECT OfferType, OfferName, SKU, IsPrivateOffer, UsageReference, UsageDate, RawUsage, EstimatedPricePC FROM ISVUsage ORDER BY UsageDate DESC TIMESPAN LAST_MONTH
列出过去 6 个月专用产品/服务的所有套餐使用情况详细信息 SELECT OfferType, OfferName, SKU, IsPrivateOffer, UsageReference, UsageDate, RawUsage, EstimatedPricePC FROM ISVUsage WHERE IsPrivateOffer = '1' ORDER BY UsageDate DESC TIMESPAN LAST_MONTH

“订单”报表查询

这些示例查询适用于“订单”报表。

查询说明 示例查询
列出过去 6 个月“Azure 许可证类型”为“企业”的订单详细信息 SELECT AssetId, PurchaseRecordId, PurchaseRecordLineItemId, OrderPurchaseDate FROM ISVOrder WHERE AzureLicenseType = 'Enterprise' TIMESPAN LAST_6_MONTHS
列出过去 6 个月“Azure 许可证类型”为“即付即用”的订单详细信息 SELECT OfferName, AssetId, PurchaseRecordId, PurchaseRecordLineItemId, OrderPurchaseDate, OrderStatus, OrderCancelDate FROM ISVOrder WHERE AzureLicenseType = 'Pay as You Go' TIMESPAN LAST_6_MONTHS
列出过去 6 个月特定产品/服务名称的订单详细信息 SELECT AssetId, PurchaseRecordId, PurchaseRecordLineItemId , OrderPurchaseDate FROM ISVOrder WHERE OfferName = Contoso test Services' TIMESPAN LAST_6_MONTHS
列出过去 6 个月活动产品/服务的订单详细信息 SELECT OfferName, AssetId, PurchaseRecordId, PurchaseRecordLineItemId, OrderPurchaseDate FROM ISVOrder WHERE OrderStatus = 'Active' TIMESPAN LAST_6_MONTHS
列出过去 6 个月已取消产品/服务的订单详细信息 SELECT OfferName, AssetId, PurchaseRecordId, PurchaseRecordLineItemId, OrderPurchaseDate FROM ISVOrder WHERE OrderStatus = 'Cancelled' TIMESPAN LAST_6_MONTHS
列出订单详细信息，包括过去 6 个月的数量、期限开始日期、期限结束日期和估计费用、货币 SELECT AssetId, Quantity, PurchaseRecordId, PurchaseRecordLineItemId, TermStartDate, TermEndDate, BilledRevenue, Currency from ISVOrder WHERE OrderStatus = 'Active' TIMESPAN LAST_6_MONTHS
列出过去 6 个月活动试用订单的订单详细信息 SELECT AssetId, Quantity, PurchaseRecordId, PurchaseRecordLineItemId from ISVOrder WHERE OrderStatus = 'Active' and IsTrial = 'True' TIMESPAN LAST_6_MONTHS
列出过去 6 个月内活动的所有产品/服务的订单详细信息 SELECT OfferName, SKU, IsPrivateOffer, AssetId, PurchaseRecordId, PurchaseRecordLineItemId, OrderPurchaseDate, BilledRevenue FROM ISVOrder WHERE OrderStatus = 'Active' TIMESPAN LAST_6_MONTHS
列出过去 6 个月内活动的专用产品/服务的订单详细信息 SELECT OfferName, SKU, IsPrivateOffer, AssetId, PurchaseRecordId, PurchaseRecordLineItemId, OrderPurchaseDate, BilledRevenue FROM ISVOrder WHERE IsPrivateOffer = '1' and OrderStatus = 'Active' TIMESPAN LAST_6_MONTHS

收入报表查询

这些示例查询适用于“收入”报表。

查询说明 示例查询
列出合作伙伴过去 1 个月的计费收入 SELECT BillingAccountId, OfferName, OfferType, Revenue, EarningAmountCC, EstimatedRevenueUSD, EarningAmountUSD, PayoutStatus, PurchaseRecordId, LineItemId,TransactionAmountCC,TransactionAmountUSD, Quantity,Units FROM ISVRevenue TIMESPAN LAST_MONTH
列出过去 3 个月处于已发送状态的所有交易的估计收入（以美元为单位） SELECT BillingAccountId, OfferName, OfferType, EstimatedRevenueUSD, EarningAmountUSD, PayoutStatus, PurchaseRecordId, LineItemId, TransactionAmountUSD FROM ISVRevenue where PayoutStatus='Sent' TIMESPAN LAST_3_MONTHS
基于订阅的计费模型的非试用交易列表 SELECT BillingAccountId, OfferName,OfferType, TrialDeployment EstimatedRevenueUSD, EarningAmountUSD FROM ISVRevenue WHERE TrialDeployment=’False’ and BillingModel=’SubscriptionBased’

服务质量报表查询

此示例查询适用于服务质量报表。

查询说明 示例查询
列出过去 6 个月的产品/服务的部署状态 SELECT OfferId, Sku, DeploymentStatus, DeploymentCorrelationId, SubscriptionId, CustomerTenantId, CustomerName, TemplateType, StartTime, EndTime, DeploymentDurationInMilliSeconds, DeploymentRegion FROM ISVQualityOfService TIMESPAN LAST_6_MONTHS

客户保留报表查询

此示例查询适用于客户保留报表。

查询说明 示例查询
列出过去 6 个月的客户保留详细信息 SELECT OfferCategory, OfferName, ProductId, DeploymentMethod, ServicePlanName, Sku, SkuBillingType, CustomerId, CustomerName, CustomerCompanyName, CustomerCountryName, CustomerCountryCode, CustomerCurrencyCode, FirstUsageDate, AzureLicenseType, OfferType, Offset FROM ISVOfferRetention TIMESPAN LAST_6_MONTHS
列出过去 6 个月内所有客户的使用活动和收入详细信息 SELECT OfferCategory, OfferName, Sku, ProductId, OfferType, FirstUsageDate, Offset, CustomerId, CustomerName, CustomerCompanyName, CustomerCountryName, CustomerCountryCode, CustomerCurrencyCode FROM ISVOfferRetention TIMESPAN LAST_6_MONTHS

后续步骤