Asset Management with Power Bi and vRops

I was recently reading a blog post by Bob Plankers on the official vSphere Blog about setting up asset management with some free to use tools.  This time last year I was in a heated discussion about how our biggest security hole was a lack of asset management.  Now, we weren't with out it 100%.  I track our servers with a spreadsheet.  We also own vRops and various other tools that help track management of assets.  There are a couple of problems.

1. Unreliable Data:  We have long ago out grown the "Server Spreadsheet" as a good means of tracking servers.  We are a large company, but not an enterprise, with over 2000 servers all over the world.  My boss is a firm believer that anything inputted manually has a chance of being unreliable.  Items may be missed and changes may occur and not tracked.  I was good at entering data as I made changes, but I was not the only person making changes.  
2. Painful Reporting:  We have plenty of management tools with good data.  But reports took a long time to generate because they all had to be formatted for viewing.  I was spending hours on every report just formatting it to show things like servers being replicated per team or patches not installed by team.
3. Lack of Application Data:  The server team was pretty good at updating information in our "spreadsheet", but sometimes the applications team makes changes that we aren't told of that affects the data in the "spreadsheet".  We had no idea what the servers did outside of a quick description that we entered when we built it originally.

Why didn't we buy a proper Asset Management software?  Lets see if you've heard this before?  

• Why do we need it, you have a spreadsheet.
• That's a lot of money, don't we have something already for this?
• Who is going to manage it?  Not us.
• We don't need it, you guys are doing fine.
• We don't have time to take on a project of that scale.

What did I have?

• A "Server Spreadsheet" with server names, brief description and application owners.  Plus a lot of other data that was unreliable due to changes.
• A server environment where 98% of it is virtualized on VMware.  It means most of the server's configuration can be tracked back to VMware and only a few need to be tracked manually.
• vRealize Operations Manager.  This contains all the VM and host configuration and performance data.  It is "reliable" data as it updated dynamically.
• A heavy Microsoft Windows environment.  This includes Active Directory and popular Microsoft Applications. 
• A large assortment of common management tool with SQL databases (This is important).
• An On-Prem version of Power BI.  I was lucky here.  Some of the queries I do would be a lot more difficult to do in the O365 version of it.

How did I get here?  I was complaining to a co-worker about my need for a better way to track assets and mentioned that I was about to just setup a larger spreadsheet with vlookups.  Then he showed me what he was doing in Power Bi.

What is Power Bi?  The description says its a  business analytics service by Microsoft that provides interactive visualizations.

How I use something like that for Asset Management?  The secret sauce behind Power Bi is the Power Query and the built in ability to link data sources together.  

I can pull in multiple data sources:

• Lets say a spreadsheet with server names, application description and application team owner
• Then I pull a vRops report with server names, vCPU's, Memory, disk size and power state. (Plus a ton of other data, just think about what vRops can get).

I put that data together:

• The server names are the same on both reports.  In Power BI, I link the two columns together.  Power BI then knows that all the data rows in each data source is related.

• What if the server names are not exactly the same?  Like one uses FQDN and the other is a simple name.  That's were Power Query is your friend.  It changes the data as it pulls it from the data source dynamically.  For example, you can drop the ".company.com" from the FQDN and leave the rest.  In the same way, you can capitalize it as well.  Lots of ways to solve problems like this.

Output:

• The Power BI virtualization allows me to make a list with whatever data I want together as long as it is linked.
• Once uploaded to a Power BI server, it can be set to automatically update every day with new data.  If your queries are setup right, the server compiles all the data automatically.
• Now, I have a server name and description side by side with the current vCPU's, memory and disk space.  The virtual configuration is as current as the latest vRops report.
• The built-in visualizations allow for filters (slicers), so you can easily filter based on location of the servers, owners, OS versions, whatever you can imagine and\or fit on a page.
• The list automatically removes those servers that are powered off, based on the power state from vRops.

Is that all?  Hell No!  Now that I have this data formatted, what else can I do with it?

• Do you want to track the removal of Server 2008 from your environment?  That's an easy page with the data I've already uploaded.

• The page filters the Server OS to Server 2008 and filters out those that are "powered off".  Lists the totals by teams.  All the values are interactive.  If you click on a slice of the pie, it filters the list below to that team only.
• All lists are exportable to CSV for those that don't have time to click on a web page and a tab.  I suggest formatting these lists in a way that is easy to read for management.  

Other Examples:

• VM Tools & VM Hardware versions:

• Host Hardware\OS Version:

• Do you want to track which OU the server is located in AD?  Add Active Directory as a data source.  Its already built-in to Power BI.
• Do you want to track if a specific patch is installed?  Add your CMDB or Patching server DB as a source.

The Best Part!!!  Self Service REPORTS!

I save hours a work a week by building common reports in Power BI.  Instead of spending the time gathering the data manually, it does it for me.  It updates every day dynamically from multiple sources.  No longer do I need to pull information from VMware and merge it with Physical servers information, its already there.

Does a director keep asking for the same information?  Tell him to visit the page and get it himself, anytime.

Future Projects:

• Build an application page, like the server asset page and link the two together.  This is a struggle because the application information is not mine.  I have to work with other teams to get this information updated and formatted the way we need.
• Use the application data to check on VMware configurations like DRS anti-affintiy rules to make sure we are properly set up for High Availability.  We want to make sure that paired Web servers are on separate hosts and datastores.  This will prove tricky since this data is not in vRops yet.  I've asked for it.
• Add performance data.  I don't want to re-create the graphs or dashboards that vRops already has.  But I would like people to be able to get to vRops, from here to look at the performance data.  One dashboard to rule them all!
• Add Costing in Power Bi.  The costing in vRops is very helpful.  But I doubt it will be complete for my environment.  I can export and manipulate the costing from vRops and make it look the way I want within Power BI.