Managing users and devices is always fun; a major challenge for me was always assigning assets without unnecessarily purchasing or depleting the device stock. An unused or under utilised device in an organisation can potentially be money down the drain. As configMgr admins, we have the data already to determine whats inactive and potentially reusable devices through client reporting and dashboards. Because the business doesn’t always value or fully understand the figures in ConfigMgr. Adding the potential cost can give the business something they can relate too but also allows us to gain an understanding of the value of our estate and ask questions about where and how you potentially use devices.
Another challenge was managing hardware refresh projects, every year going through manually exporting lists and trying to work out which devices needed replacing. This tool is designed to help all those admins out there who struggle with this! Now you can confidently show your management or staff a report that actually puts figures behind the dataset :).
What is the ConfigMgr Device Cost & Renewal Estimator?
The ConfigMgr Device Cost & Renewal Estimator is a free Power BI report which you connect to your ConfigMgr database and using BI visuals, show the calculated potential device cost and the renewal date for refreshing devices. The reports can help with device cost planning and forecasting.
How does it work?
Device Cost Estimator
The Device Cost Estimator gives a really interesting insight into your environment device cost. The report utilizes a calculated creation date from the device information. This date is based on the OS install date and the ConfigMgr creation date, it will by default take the earliest date. The Creation Date is then linked to a financial year calendar; this is calculated by month number and is specified in a parameter. Finally there is a drop down which allows you to specify your estimated average device cost, all the visual calculations are then based from this value.
Device Renewal Cycle Estimator
The Device Renewal Cycle Estimator is really cool! Based on a parameter for your estimated device recycle period (in years), it will show you which devices need renewing and indicate any potential cost. This allows you to map out when you need to replace devices and plan ahead. With the Manufacturer and Model matrix, you can also see what device type actually needs replacing.
How to Configure the Report
Prerequisites
- ConfigMgr Current Branch (1802 or above)
- Minimum SQL data reader rights to connect to the ConfigMgr Database
- The latest Power BI Desktop
- Download the Template
Setup
- Download the latest Power BI Desktop
- Before attempting to open the template file, open Power BI Desktop first
- Go to File > Options and Settings > Options > Security
- Under Native Database Queries, untick Require user approval for new native SQL queries
- Close the open Power BI desktop and open the template file
- Enter the SQL server or instance name where the Configuration Manager site database is located
- Enter the SQL database name for the Configuration Manager site
- Supply the Finance End Month number, for example entering 3 would be March as the year end month
- Drop the Renewal Cycle (Years) and select your renewal period for swapping devices.
Changing the Currency
The currency by default is set to GBP (£), to change the currency:
- Go the Fields\Cost table on the right panel.
- Select the “Currency Symbol” to whatever your currency is (This is only used for visual text formatting).
- For all the other measures (The fields with a calculator symbol next to it), click each measure individually. Finally, go to the modelling tab and drop the $ drop-down to select your currency.
DOWNLOAD
The template is downloadable from the Technet Gallery 🙂
Finally, I hope you enjoying using this report. If you have any feedback or suggestions comment below as would love to hear them.
Cheers,
Raf