How to troubleshoot Windows Azure Pack and System Center Usage Pipeline?

We have been observing that when we deploy the Usage and Billing solution to the clients, System Center records are not updated in the 'Microsoft.MgmtSvc.Usage' table. Following are some of the possible fixes for the issues that we found in existing deployments of our clients. 

PRE-CHECKS:

1. Before deploying the Usage and Billing solution make sure you can find 'System Center' records        in 'Microsoft.MgmtSvc.Usage' database. Execute the following SQL query.

  SELECT count(*)
  FROM [Microsoft.MgmtSvc.Usage].[usage].[Records]
  where ResourceProviderId = (select ProviderId  FROM 
           [Microsoft.MgmtSvc.Usage].[usage].[ProvidersConfiguration]
            where ProviderName='systemcenter')

2. If the result is greater than '0', then you can go ahead deploying the Usage and Billing.  

3. If not, please refer the following links to see if the 'VMM --> OperationManager --> OperationsManagerDW --> SPF  --> WAP’ integration is setup fine. 

Step 1: IaaS Usage and Service Reporting using System Center 2012 R2 and Windows Azure Pack

Step 2: Configuring VMM and OM for IaaS usage and metering.

Step 3: Configuring SPF and Windows Azure Pack for IaaS usage and metering.

If you are facing issues even after following all the steps in the above article, then please go through the following steps.

MORE TROUBLESHOOTING STEPS:

Please make use of the troubleshooting steps on known issues from our client deployments.

FINDING THE PARTICULAR STAGE IN PIPELINE ON WHERE THE ISSUE IS:

a) Checking OperationManager and OperationsManagerDW Integration:

Execute the following query against 'OperationsManagerDW'  database.

SELECT TOP 1000 [ManagedEntityRowId]
      ,[ManagementGroupRowId]
      ,[ManagedEntityGuid]
      ,[ManagedEntityTypeRowId]
      ,[TopLevelHostManagedEntityRowId]
      ,[FullName]
      ,[Path]
      ,[Name]
      ,[DisplayName]
      ,[ManagedEntityDefaultName]
      ,[DWCreatedDateTime]
  FROM [OperationsManagerDW].[dbo].[ManagedEntity] where DisplayName = 'xxxxx'

xxxx -> Replace it with any VM Name that was recently created from 'WAP'.

If you get some records, then OperationManager and OperationsManagerDW integration is fine. 

b) Troubleshooting the OperationsManagerDW and SPF integration:

1. Please use the following URL replacing with your 'spf-server-name':

   https://<spf-server-name>:8090/Usage/Usage?lastId=0&batchSize=100

2. Provide the respective credentials in the window that prompts.

3. A JSON will be downloaded. Open the file and check if it contains some records. If it does not, then following might be the issues. Incase if you can see the records in JSON file, then please skip this section and go to the section 'Troubleshooting SCSPF DBs'.

Check if you have added any SCSPF setting more than once: 

1. Go to SPF machine.

2. Open the powershell with an administrator privilege.

3. Execute the following command.

   Get-SCSPFSetting

4. Result is like below.

5. 

5.Make sure if there is only one setting with the name 'SCOM-DB-SERVER-NAME'.

6. If it is more than once, then remove the duplicate 'DB connection string' entry by using the following command (But do not remove the EndpointUrl setting).

 Get-SCSPFSetting -Id 'xxxx' | Remove-SCSPFSetting 

xxxx-> Replace with the actual Id of the setting that you need to remove.

Troubleshooting in SCSPF and WAP Integration:

1. Execute the following query against SCSPF database.

SELECT TOP 1000 [Id]
      ,[RecordIndex]
      ,[ResourceId]
      ,[StartTimeTickCount]
      ,[EndTimeTickCount]
      ,[ServiceType]
      ,[SubscriptionId]
  FROM [SCSPFDB].[scspf].[SpfUsageRecord]

 order by RecordIndex desc

2. Note the RecordIndex column of the first row from the result. If there are no records then replace 'XXXX' in the following query with '0'.

3.  Update the following query with the noted RecordIndex.
Update [Microsoft.MgmtSvc.Usage].[usage].[ProvidersConfiguration] set LastUsageEventId = 'XXXX' where ProviderName = 'systemcenter'

XXXX - RecordIndex taken from the previous query.

4. Execute the following query to update the ‘DefaultTimeOut’  setting in [Microsoft.MgmtSvc.Usage]  database.

  Update [Microsoft.MgmtSvc.Usage].[usage].[Configuration]  set Value = 600 where    [Microsoft.MgmtSvc.Usage].[usage].[Configuration].[Key] = 'OutgoingCallsDefaultTimeoutInSeconds'

5. Please recycle the usage collector app pool.

6. Check for the System Center records in Microsoft WAP Usage database.

If still usage records are not coming up try the following section. 

401 UnAuthorized Exceptions Stops pulling Usage data:

1. In the same machine where Usage Collector runs check for the following Event Log Name.

2. Under that, check for the logs with the similar Error message.

Provider read error occurred for provider 'systemcenter' instance id: '{04a0de02-6ab6-4e51-9620-df04e41b427a}'. Error: System.Net.Http.HttpRequestException: Response status code does not indicate success: 401 (Unauthorized).

   at System.Net.Http.HttpResponseMessage.EnsureSuccessStatusCode()

   at Microsoft.WindowsAzure.Server.Usage.DefaultUsageRecordsReader.<GetRecordsAsync>d__0.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)

   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()

   at Microsoft.WindowsAzure.Server.Usage.CycleRunner.<ProcessProviderInternal>d__19.MoveNext()

3. If you don't find any event logs, then please execute the following query against Microsoft.MgmtSvc.Usage database.

  SELECT 
  [ResourceProviderID]
      ,[RecordsCount]
      ,[DownloadStartTime]
      ,[Error]
  FROM [Microsoft.MgmtSvc.Usage].[UsageDiagnostics].[ProviderCollectionCycles]
  where ResourceProviderId = (select ProviderId  FROM 
           [Microsoft.MgmtSvc.Usage].[usage].[ProvidersConfiguration]
            where ProviderName='sqlservers') 
   order by ProviderCycleID desc

4. Now check for the Error column and see if you have similar '401 (Unauthorized)' Error message given in Step2. 

5. Incase if you find '401 (Unauthorized)' message, please go to Admin Portal --> VM Clouds.

 a. click on the Cloud icon.

 b. Click on the link under 'Register Service Provider Usage' and set the appropriate username and password.

 c. Now restart the UsageCollector service and check for the System Center usage records in Microsoft.MgmtSvc.Usage database.

We will keep updating this article, as we came across new issues in our U&B customer deployments.