Troubleshooting Windows Azure Pack & Usage (Part 1)

Hello Readers.

My original plan was to release the troubleshooting blog posts in listed order, but as there have been a few questions around Usage, I have decided to move this topic up the list and share some common challenges installing and configuring Usage for IaaS.

As mentioned in a previous blog post ( Installing & configuring Service Reporting for IaaS usage and metering ), Usage for IaaS uses the following main components:

    • Virtual Machine Manager
    • Operations Manager
    • Service Provider Foundation
    • Windows Azure Pack
    • Service Reporting
    • PerformancePoint & PowerPivot (Optional)

These previous blog posts describe how usage can be installed end to end and can be helpful if you are starting from scratch:

The goal of this blog post is not to explain how usage should be installed, but to help troubleshooting if something goes wrong during the install. Some things might be repeated from these previous blog posts, but this is to save the reader jumping around between blog posts to understand if things are working or not.

As with SPF and VMM it’s important to make sure that each component above works as expected since Service Reporting will be impacting with any of these components not working correctly.

clip_image001

The illustration above shows how data flows from the different components in the process of usage, metering & reporting.

To avoid the blog post from being to long it will be split into two parts:

First I will walk over how you can verify if things are working as they should (Part 1)

    • How to verify that Usage is working end to end.

Next, I will describe the potential issues you might run into (Part 2):

    • SPF cannot connect to OM DW.
    • WAP Usage is failing: Error: 404 (Not Found), 401 (Authentication failed).
    • Service Reporting shows “401 Authentication Failure” from WAP Usage API.
    • Service Reporting SQL Job is failing.
    • Processing Cubes are failing.
    • Databases or Cubes are not showing when connecting PerformancePoint to Service Reporting DW.

Let’s get started with verifying if everything is working as expected.

How to verify that Usage is working end to end:

If any of the verification steps below fails or no data is shown, there is really no need to check the next one in the list, as there is no data to pass on in the data flow.

Understanding where it fails in the flow will give you the right place to start troubleshooting and not waste time troubleshooting in the wrong place.

Data flows from VMM to OM:

We want to verify that data from VMM flows into OpsMgr DW.

The fastest way to do this is to run a Report from OM for VMs and see if we can extract any data:

To verify that performance data is flowing from Virtual Machine Manager to Operations Manager Data Warehouse perform the following steps:

Note: Data will not show in OM DW until at least 60 mins after the integration is completed between OM and VMM.

1. Open Operations Manager Console.

2. Select Reporting > Microsoft System Center Virtual Machine Manager 2012 R2 Reports.

3. In the Reports view Open “Virtual Machine Utilization”.

4. Click Add Object.

5. Click Search.

6. Mark one or more items from the search result and click Add and Click OK.

7. Click Run from the ribbon bar.

8. Verify that data is shown in the report; it should look similar to the example below:

   

If no data is shown in the report you want to check the following:

  • VMM and dependent Management Packs have been imported into Operations Manager.
  • OM and VMM service accounts needs to be added as Admins in VMM and OM.
  • Configure the right OM settings in VMM.

Please see this blog post on how to perform these steps:

Data flows from OM to SPF DB:

SPF is used to extract data from OM into WAP Usage Service, SPF service only keeps “buffer” data for a short period of time. In most cases SPF will store a buffer of data for limited time, when getting data from OM until this data is tranfered to WAP Usage database. Normally you will only see a few rows of data in the table, as this keeps the buffer between Operations Maanger and WAP Usage database.

To verify that IaaS data is being stored in the SPF Database do the following:

1. Logon the SPF Database server as Administrator.

2. Start SQL Management Studio.

3. Select Databases > SCSPFDB. (Default name)

4. Right Click on SCSPFDB and select “New Query

5. Paste the following Query into the Query window on the left side of the console:

    SELECT TOP 10 [Id]
       ,[RecordIndex]
       ,[ResourceId]
       ,[ServiceType]
    FROM [SCSPFDB].[scspf].[SpfUsageRecord]

6. Click Execute in the ribbon bar to execute the Query.

7. Verify that at least one record shows in the output

   

    • Notice the RecordIndex number (57010) as we will use this number to verify sync with WAP Usage Database.

8. Close the SQL Management Studio Console.

Data flows from SPF to WAP Usage DB:

WAP Usage Collector Database is used to extract data from SPF into WAP Usage Service. This data is kept by default for 40 days in the WAP Collector Database.

To verify that IaaS data is being stored in the WAP Usage Database do the following:

1. Logon the WAP Database as Administrator.

2. Start SQL Management Studio.

3. Select Databases > Microsoft.MgmtSvc.Usage.

4. Right Click on SCSPFDB and select “New Query

5. Paste the following Query into the Query window on the left side of the console:

    SELECT TOP 5 [RecordId]
         ,[ExternalRecordId]
         ,[ResourceId]
         ,[StartTime]
         ,[EndTime]
         ,[ResourceProviderId]
         ,[ServiceType]
    FROM [Microsoft.MgmtSvc.Usage].[usage].[Records]
    order by ExternalRecordId desc

6. Click Execute in the ribbon bar to execute the Query.

7. Verify that at least one record shows in the output

   

  • Note that the ExternalRecordId matches the number from the SPF Database.
  • These IDs should never get out of sync, if they do it’s recommended to reinstall SPF and delete the Resource Provider in WAP.

8. Close the SQL Management Studio Console.

Data flows from WAP Usage database to Service Reporting UsageStaging database

We want to verify that data gets transferred from WAP and processed in the Service Reporting Databases. To do this, do the following:

1. Logon to the Database Server that hosts Service Reporting Databases as an administrator.

2. Start SQL Management Studio and connect to the Database Instance holding the databases.

3.  Go to Databases folder, expand it and verify you can see the following Databases:

   

4. Expand UsageStagingDB > Tables

5. Right clink on MT_Microsoft$SystemCenter$VirtualMachineManager$2012$VirtualMachine and select “Select top 1000 Rows”

6. Verify that data shows in the table, that is similar to the below picture.

   

This confirms that data is being transferred from the WAP Usage Database to the Service Reporting UsageStaging database. This means that the extract job is working and data can be extracted from WAP Usage API to Service Reporting

7. Expand UsageDatawarehouseDB

8. Right Click on UsageDatawarehouseDB and select “New Query

   SELECT TOP 10 [VirtualMachineDimKey]
         ,[DisplayName]
         ,[CPUCount]
         ,[State]
         ,[TotalSizeOfVirtualDisks]
         ,[Cloud]
         ,[Owner]
     FROM [UsageDatawarehouseDB].[dbo].[VirtualMachineDim]
     order by Cloud desc

9. Verify that data is returned. As an example of running this query in my testing environment, please see the picture below.

   

Based on this output we know that data travels from the UsageStagingDB database to the UsageDatawarehouseDB, which indicates that the ETL process is Work for Service Reporting.

 

Hopefully this blog post will help you verify if usage and reporting is working as expected in your environment.

I would like to thank Kevin Hughes for helping me put this blog post together.

Please let me know if you have run into other challenges and I’ll be happy to add it to the list of things that can cause challenges with WAP & Usage.

Happy Usage and Metering with Windows Azure Pack & System Center.

Next blog post will be:

Troubleshooting Windows Azure Pack & Usage (Part 2)