Troubleshooting the Windows Azure Pack Usage Service
Applies To: Windows Azure Pack
Troubleshooting information is available for various aspects of the Windows Azure Pack Usage Service.
Troubleshooting Poor Usage Data Flow in Windows Azure Pack
In general, check the table [usage].[ProvidersConfiguration] in the [Microsoft.MgmtSvc.Usage] schema first. This table lists all the resource providers that have been correctly registered with Windows Azure Pack to provide usage data. If a resource provider is missing from that table, it will not be contacted for usage data by the usage collector service.
To diagnose incorrect registration of a resource provider, view the [mp].[ResourceProviders] table in the [Microsoft.MgmtSvc.Store] schema. This will list all the resource providers in the system. Inspect the values for usage forwarding address and credentials to see what the resource provider has been registered with. This should always be your first stop in validating that a resource provider is correctly configured.
Usage Diagnostics Tables
Beyond the steps mentioned above, the following usage diagnostic tables are provided in the Microsoft.MgmtSvc.Usage database. Each table has a column ‘Error’ where exceptions are logged.
Important
By default, diagnostics data is kept for three days. This can be configured by DiagnosticDataDetationDays in the table Usage Configuration Table
Table |
Log Data |
|---|---|
UsageDiagnostics.CollectionCycles |
Contains logs for all calls by the Usage Collector. |
UsageDiagnostics.ProviderCollectionCycles |
Contains logs for all calls made by the Usage Collector to resource providers in the system. |
UsageDiagnostics.FrontEndRequests |
Contains logs for all calls made to the Usage REST API (Usage Service) from an outside Billing Service. |
UsageDiagnostics.MaintenanceCycles |
There are maintenance operations that run on the core usage tables, for example, purging usage records based on the purge cycle. This table has logs related to the maintenance cycles. |
SQL Queries
The following SQL queries provide information useful troubleshooting information
Table [UsageDiagnostics].[ProviderCollectionCycles]
Errors Encountered Collecting Usage Data.
SELECT TOP 1000 [ProviderCycleID],[CollectionCycleID],[ResourceProviderID],
[ResourceProviderStatus],[RecordsCount],[DownloadStartTime],
[DownloadDurationMilliSeconds],[ResourceProviderHostName],[DownloadStatus],[Error]
FROM [Microsoft.MgmtSvc.Usage].[UsageDiagnostics].[ProviderCollectionCycles]
WHERE Error NOT LIKE 'NULL'
Total Collection Attempts in last (v1) hours.
DECLARE @v1 int = 5
SELECT count(*) as 'Total Collection Attempts in last (v1) hours',
sum([RecordsCount]) as 'Total Records Collected in last (v1 hours)',
sum(DownloadDurationMilliSeconds) as 'Time spent collecting (ms)',
[ResourceProviderID],[ResourceProviderHostName]
FROM [Microsoft.MgmtSvc.Usage].[UsageDiagnostics].[ProviderCollectionCycles]
WHERE DownloadStartTime > DATEADD(HOUR,-@v1,GETDATE())
GROUP BY ResourceProviderID,ResourceProviderHostName
ORDER BY ResourceProviderID
Total Collection Attempts and Records in last (v2) days for Resource Provider (v3).
DECLARE @v2 int = 5
DECLARE @v3 int = 1
SELECT cast(DownloadStartTime As Date) as 'Day',[ResourceProviderID],
[ResourceProviderHostName],count(*) as 'Total Collection Attempts this day',
sum([RecordsCount]) as 'Total Records Collected this day',
(sum(DownloadDurationMilliSeconds)/60000) as 'Time spent collecting ~(min)'
FROM [Microsoft.MgmtSvc.Usage].[UsageDiagnostics].[ProviderCollectionCycles]
WHERE cast(DownloadStartTime As Date) > DATEADD(DAY,-@v2,GETDATE())
AND ResourceProviderID = @v3
GROUP BY cast(DownloadStartTime As Date),ResourceProviderID,ResourceProviderHostName
ORDER BY cast(DownloadStartTime As Date)
Errors Encountered Collecting Usage Data.
SELECT TOP 1000 [ProviderCycleID],[CollectionCycleID],[ResourceProviderID],
[ResourceProviderStatus],[RecordsCount],[DownloadStartTime],
[DownloadDurationMilliSeconds],[ResourceProviderHostName],[DownloadStatus],[Error]
FROM [Microsoft.MgmtSvc.Usage].[UsageDiagnostics].[ProviderCollectionCycles]
ORDER BY ProviderCycleID DESC
Table [Usage].[Records]
All Resource Providers Configured for Usage Data.
SELECT ALL [ProviderId],[ProviderName]
FROM [Microsoft.MgmtSvc.Usage].[usage].[ProvidersConfiguration]
Total Records from each Resource Provider.
SELECT count(*) as 'Total Records Received',ResourceProviderId
FROM [Microsoft.MgmtSvc.Usage].[usage].[Records]
GROUP BY ResourceProviderId
Total Records from each Resource Provider within last (v1) hours.
DECLARE @v1 int = 5
SELECT count(*) as 'Total Records in last (v1) hours',ResourceProviderId
FROM [Microsoft.MgmtSvc.Usage].[usage].[Records]
WHERE EndTime > DATEADD(HOUR,-@v1,GETDATE())
GROUP BY ResourceProviderId
Usage Records from Resource Provider (v2) within last (v3) hours.
DECLARE @v2 int = 1
DECLARE @v3 int = 5
SELECT [RecordId],[ExternalRecordId],[ResourceId],[StartTime],[EndTime],
[ResourceProviderId],[ServiceType],[SubscriptionId],[Properties],[Resources]
FROM [Microsoft.MgmtSvc.Usage].[usage].[Records]
WHERE ResourceProviderId = @v2 AND EndTime > DATEADD(HOUR,-@v3,GETDATE())
ORDER BY StartTime desc
Usage Records for Subscription (v4) within last (v5) hours.
DECLARE @v4 varchar(50) = 'E6F86A02-3D89-44E9-AE8E-17C77223676E'
DECLARE @v5 int = 5
SELECT [RecordId],[ExternalRecordId],[ResourceId],[StartTime],[EndTime],
[ResourceProviderId],[ServiceType],[SubscriptionId],[Properties],[Resources]
FROM [Microsoft.MgmtSvc.Usage].[usage].[Records]
WHERE SubscriptionId = @v4 AND EndTime > DATEADD(HOUR,-@v5,GETDATE())
ORDER BY StartTime desc