How do you automate software inventory updates and reports in Excel?

SAM data is abundant these days. In most cases, it's already (a least partially) present in existing non-SAM tools, like SCCM, Lansweeper, etc. So it's not really a matter of what discovery tool to use, as you probably already have one, and the data is already there. The most important question is what you do with it after. So, instead of thinking of a discovery tool, I would think of a tool that can analyze my data. Or at least do some of the heavy lifting. Modern IT tools support API integrations with most modern SAM tools.

Microsoft Power Query, which is integrated into Excel, is a powerful tool for automating data extraction and transformation. Additionally, various third-party tools and solutions, such as Lansweeper and Spiceworks, can also help automate software inventory updates and reports. The choice of tool may depend on the complexity of your inventory and reporting needs. Be sure to evaluate and choose the one that best fits your organization's requirements and budget.

When organization does not have access to any comprehensive SAM tool for data discovery and raw signature normalization, then either SCCM tool or software inventory extraction VB script which can extract data from add remove program and program files can be utilized to capture raw software report. The key is here to have access to a comprehensive software normalization list ( software recognition services or application recognition library). But all these will for windows based systems. For Linux systems you need a tool like Snow inventory or FNMS or manual data extraction process needs to be deployed. The approach should be utilize a discovery tool to get a list of endpoints and then deploy inventory agents to capture the inventory.

VB scripts or macros can be used for exporting , mapping of software inventory data , but frankly, the amount of manual effort it requires and cost of making manual errors will outweigh the investment for a Software asset Management tool. Now a days organization even with less than 5000 endpoints are investing for a SAM tool. Using Excel for a SAM discovery only make business sense if the number of endpoints are less than 1000 and the software portfolios are not very complex. But VB script and Excel can be a useful tool to perform reporting for SaaS based products where the compliance rules are straight forward and company does not want to invest for separate SaaS connectors.

Excel is the bread & and butter of any analytical work. However, it comes with some serious limitations. It has a 1M rows limitation, so if your data is bigger, it won't fit. You'll need a lot of RAM memory on your device, too, as Excel seems to be very shaky if they're big. You'll be spamming the save button all the time. :) Tip: Don't use the web version. It's not the same in terms of performance and usability. Excel is great as long as it's the right tool for the job.

While a consultant in SAM many of the customers I was advising admitted that they don't really get most of it, but they trust it. Story and presentation are key. All those features are great, but it's easy to get sucked in the race for the ultimate spreadsheet master. Simplicity is key. Be short. Concise. Keep the details away from the big picture. Customers don't buy fancy spreadsheets, they buy outcomes. And I highly recommend using a versioning system, and always create a new copy when updating a report ("save as" version 1.0 -> 1.1 etc.). This way you avoid loosing your work due to a corrupted file. Happened to me. Never again.

I know a few SAM teams using Power Automate with some good results. But you'd obviously need a highly specialised team to get an overall effective outcome. My honest opinion is that you're still worried about automating spreadsheets with scripts, most likely your SAM program is outdated.

Most companies have started SAM programs in an Excel spreadsheet. And some of them may not get to outgrow it. And that's fine. But the companies that scale fast or complex companies in general need more than a few tidy Excel sheets to keep things in check.