[Power BI] Resource Counts - New metric for reporting?

[psilantropy]

📝 Scenario

I look after cloud optimisation for our business and report monthly to exec. This includes general cost information, but also new resources deployed, resources decommissioned and any future potential charges upcoming.

Even though we make a lot of savings, we generally stay quite static month-to-month for costs. This is due to constant deployments fighting against optimisation and saving strategies.

Some months I can tell the business I removed $30k pa in future charges by cleaning up backups and storage accounts, but the monthly invoice amount won't usually reflect that. The written report can become cumbersome for the reader if I have to constantly explain growth in other areas offset those changes.

There appears to be no way to get any historical resource counts directly from Azure, but it is very easy to get a resource count at the current time with the resource graph explorer;

Resources
| summarize count()

💎 Solution

Cost exports do contain historical data (my exports go back almost 2 years) and could be a great source. I don't think there is anything else available for this unless you had a log analytics workspace already ingesting the information required.

Any metric that can count resources or count usage emitting meters would be great. This can be tracked over time and may provide further insight into cost movement. I'm presuming the PowerBI CostSummary report would be a good destination for this metric.

These counts by would be very useful to track over the tenant, but also counts per subscription, service etc would provide insight into different areas of the business.

ℹ️ Additional context

Using PowerBI you can to a count(distinct) on a resource column. Some examples of this count below. I think this is number of resources that emitted usage data during the timeframe specified. It's difficult to explain to an exec anyway.

Some examples of this count by date range;

JAN 1-1: 2014
JAN 1-31: 4232
FEB 1-1: 2136
DEC 1-1: 2010
NOV 1-1: 1864

Note: My actual resource count today is 6500+. Never under 6000 from memory.

🙋‍♀️ Ask for the community

We could use your help:

1. Please vote this issue up (👍) to prioritize it.
2. Leave comments to help us solidify the vision.

If anybody knows of another count method that would be great. At the moment I can only think of using logic apps to query a resource count, and then append to csv, or send the count somewhere to manually input into reports.

[flanakin]

I love this idea! Here are a few thoughts:

1. Add a count KPI to the Resources page.
2. Consider adding a line to chart on the Resources page showing the resource count over time.
3. Add a new "Inventory" page that shows resource count broken down by service and resource type.
   • NOTE: This is partially based on other generic feedback I've heard about tracking a resource inventory, but I don't have enough details, so it would be immensely helpful to get feedback on what's most useful.

The biggest concern I have is that you cannot get an accurate representation of resource count from cost data alone. Here's a note on the Cost Management docs:

Cost Management data only includes the usage and purchases from services and resources that are actively running. The cost data you see is based on past records. It includes resources, resource groups, and subscriptions that might be stopped, deleted, or canceled. So, it might not match with the current resources, resource groups, and subscriptions you see in tools like Azure Resource Manager or Azure Resource Graph. They only display currently deployed resources in your subscriptions. Not all resources emit usage and therefore might not be represented in the cost data. Similarly, Azure Resource Manager doesn't track some resources so they might not be represented in subscription resources.

@psilantropy, let me know what you think about this or if you'd add anything else.

[psilantropy]

Inventory page and count KPI sounds great. Thank you 👍 I can't think of any splits I'd be interested outside of resource type, subscription and service. Basically, everything you already have for other items. I would likely report on resource type and subscription counts due to our structure.

I suspect this would be the only source of this information for MS customers outside of a point in time count. So, it's a great 'selling point' so-to-speak for your toolkit :) Hopefully brings in a few new users.

[prabha11-sys]

I had the exact same challenge in my organization, the savings that I was making through the optimization was offset by the constant increase in resources and its usage. I used the ResourceName columns and the Quantity column to explain the story to the management. Sum of "Quantity" would provide an accurate picture as the actual resource utilization(in hrs) dictates the cost. I have two Power BI reports now, one using Count(Distinct) of ResourceName against Cost, the other one using the Sum of Quantity against Cost in a Line chart.

[psilantropy]

Was this implemented in v5 CostSummary in the inventory tab? "Count of ResourceID"

It's quite funny how it looks, presumably based on cost usage data produced on the resourceId as discussed. Across 3 months it's hard to see a trend, but pushing out 4-12 months it definitely gives you some kind of visual on the resource numbers.

Filtering to just VMs, over 12 months however the data doesn't make any sense. ~130 VMs. But moves between 14 and 111 resources, and jumps all the time.

Let me know if you want me to look any further. It just jumped out to me now while I set up v5. Thank you :)