Troubleshooting the Windows Azure Pack Usage Service

Article
08/24/2015

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

Share via