Poor man's inventory with PowerShell, DocumentDB and Power BI

Roger Zander

Dec 28, 2016 • 5 min read

There are many Tools to inventory Assets like Windows-Computers. I want to present a simple way to collect asset inventory data and generate custom reports with "free" tools...

Goals

agentless (just run a PowerShell script)
customizable inventory classes (you can collect whatever you want)
customizable reporting
centralized "database" (in the cloud or onPrem)
inventory offline devices (copy the inventory data to an USB stick..)
scalable

Summary

The solution is using DocumentDB to store all the inventory data. DocumentDB is a noSQL Database, a Database without a schema (no tables). You can just upload JSON-Files and DocumentDB will index these files to get a fast response when you search/query the content.
DocumentDB can run in Microsoft Azure (Cloud) as a hosted Service (not for free !) or you can use the DocumentDB Emulator to run it on premise (it's fine for testing).
To generate reports or charts based on the inventory data, we can use Power BI as it supports DocumentDB as DataSource.

Prerequisites:

Installation

Install DocumentDB Emulator
Open DocumentDBStudio and create a new Account.
select the Checkbox: "Use local emulator" and change the port on the AccountEndpoint to 8081 (DocumentDB emulator is running on port 8081)
right click on "https://localhost:8081) and select: Create Database
Give the database a name ("Inventory" in this example) and click on Execute..
Right click the "Inventory" Database and select "Create DocumentCollection"
Give the DocumentCollection an Id ("Computers" in this example) and click on Execute..

Collect Inventory with PowerShell

PowerShell can convert all kind of Objects to a JSON string by using ConvertTo-Json. Most of the interesting data for inventory is coming from WMI, so we can just query WMI for a specific class, remove unwanted information and generate a JSON string:

Get-WmiObject -Namespace "root\cimv2" -ClassName "win32_Processor" `
 | Select * -ExcludeProperty Scope,Options,ClassPath,Properties,SystemProperties, `
 Qualifiers,Site,Container,PSComputerName, Path, __* `
 | Sort | ConvertTo-Json

as we have many of the WMI Queries, we can create a Function to simplify the logic. To allow filtering WMI data, the function is using WMI queries instead of the full class:

function WMIInv ([string]$Name, [string]$query, [string]$namespace = "root\cimv2")
{
    $jsonout = ",`n `"$($Name)`":" 
    $val += Get-WmiObject -Namespace $namespace -Query $query -ea SilentlyContinue| select * -ExcludeProperty Scope,Options,ClassPath,Properties,SystemProperties,Qualifiers,Site,Container,PSComputerName, Path, __* | ConvertTo-Json
    if($val -eq $null) { $val = "null" } 
    $jsonout += $val
    return $jsonout
}

Each JSON-Document must have a unique identifier in DocumentDB, so we have to create this part before we generate the File. As id, we are using the SMBIOS UUID of the device and for identification, we also add the Hostname and the current Date/Time...

#generate the JSON Header
$json = " { `"id`": `"" + (Get-WmiObject Win32_ComputerSystemProduct uuid).uuid + "`","
$json += "`n `"hostname`": `"" + $env:COMPUTERNAME + "`","
$json += "`n `"InventoryDate`": `"" + $(Get-Date -format u) + "`""

#add the Inventory data...
$json += WMIInv "Battery" "Select * FROM Win32_Battery" "root\cimv2"
$json += WMIInv "BIOS" "Select * FROM Win32_Bios" "root\cimv2"
$json += WMIInv "ComputerSystem" "Select * FROM Win32_ComputerSystem" "root\cimv2"
#... more Inventory classes here

#close the JSON format
$json += "`n } "

#Save the File   
$json > "$((Get-Location).Path)\$($env:COMPUTERNAME).json"

to get a more detailed script, check out the Inventory-Script on GitHub. It also provides the logic to directly upload the data to DocumentDB in Azure.

You can now generate Inventory-Files on your favorite machines...

If you want to have an .EXE File that runs Inventory, just convert your Script with PS2EXE into an executable...

Upload Data

The Inventory-Script on GitHub has the capability to automatically upload the data to DocumentDB. If you want to do this manually, you can use DocumentDBStudio to upload the Files...

Open DocumentDB Studio
right Click on the "Computers" Document Collection and click on "Create Document from File"
Select and open the JSON-File
Click on "Execute" to start the import process

You will get a document entry with the UUID of your Device

You can now run queries against you documents. Just right click "Computers" and select "Query Documents". As an example, you can get a List of all hostnames, UUID's and SerialNumbers:
SELECT root.hostname, root.id, root.BIOS.SerialNumber FROM root

Reporting

Power BI Desktop is a free tool to generate Reports from several Datasources such as DocumentDB.

In PowerBI just click on "Get Data" to configure the datasource
URL:https:\\localhost:8081
Database: Inventory
Collection: Computers
Account Key: C2y6yDjf5/R+ob0N8A7Cgv30VRDJIWEHLM+4QDU5DE2nQ9nDuVTqobD4b8mGGyPMbIZnqyMsEcaGQy67XIw/Jw==
You will get some records in a GridView. To configure the details, click on "Edit Queries" in the ribbon and another Wizard will be displayed:

I want to create a Report that just count's all installed Software, so I only check "Software" as the only class. The result is a simple list of items. Click on the "Expand" Icon in the right upper corner ans select all Attributes from the Software class

You can now click on "Close and Apply" from the ribbon to go back to the initial screen.
Switch to the "Report" Node and add a "New Visual" from the ribbon.
Some "dummy" bars will be displayed. Keep the visualization active and configure the chart like:

as a Result, you will get nice chart with all the installed Software: