Check out my PowerShell module for pulling custom reports from MDE.

Have you benchmarked the API query performance at scale? Pulling installed software inventories from every endpoint can hammer your MDE instance if you're not careful.

We ended up building a pipeline: MDE Advanced Hunting -> Kusto -> scheduled export to blob -> transformed in Azure Functions. The raw data hits a Power BI dashboard for finance, with a separate alert threshold set for license compliance drift.

Your approach works for one-off reports, but you'll need a caching layer once you cross a few hundred machines.

That caching layer point is something I wouldn't have considered. We're just starting to scale up our endpoint monitoring, so hitting performance limits hasn't been an issue yet.

> scheduled export to blob -> transformed in Azure Functions
Is the transformation mostly for reshaping the data, or are you doing things like deduplication or merging with other asset systems there? I'm trying to figure out where the 'compliance logic' should live.

That's a really interesting use case, thanks for sharing it. I hadn't considered using MDE data specifically for reconciling invoices. In my work, we've connected HRIS data with access logs for onboarding/offboarding audits, but linking security inventory to finance is a clever angle.

I'm curious, when you say "a format our accounting system can digest," are you doing things like normalizing publisher names or matching versions to license SKUs directly in the PowerShell script? That seems like the most brittle part, given how messy software inventory data can be.

How do you handle edge cases, like different versions of the same application showing up under slightly different display names?

I actually ran into that exact issue when I first tried a similar export, but for a different system. Normalizing the publisher names became a nightmare because, like you said, the display names are all over the place. "Microsoft Corporation," "Microsoft," "Microsoft Corp." - same vendor, three different strings.

What finally worked for me was building a small lookup table in a separate CSV file. I map all the messy display name variations to a single, clean vendor name and the official license SKU. My script just references that file. It's a pain to maintain at first, but it breaks the logic out of the script itself. Did you find a better way to handle that mapping, or do you just keep tweaking the script every time a new variation pops up?

Nice approach. That gap between MDE's raw data and something finance can actually use is a huge pain point. We tackled it for audit reports.

Did you have to add any logic to filter out Windows components or system-level software that shouldn't be part of a license count? That's where we got tripped up at first.

That's an excellent point, and it's a critical step that often gets overlooked in the initial enthusiasm of getting the data pipeline working. Relying solely on the data from `DeviceTvmSoftwareInventoryVulnerabilities` or a similar table will include a massive amount of system-level clutter.

We implemented a two-tiered filtering strategy. First, we use a blocklist of known publisher names like "Microsoft Windows", "Microsoft Corporation" for core components, and common redistributables. Second, and more importantly, we apply a heuristic based on the `SoftwareVendor` and `SoftwareName` to exclude items with clear system roles.

For example, we filter out anything where the name contains strings like "service pack", "update for", "security update", "driver", "runtime", or "redistributable". This is done with a regex pattern in the transformation layer, not in the initial Kusto query, to keep the logic maintainable. The blocklist is stored as a config file, separate from the code.

Even with this, edge cases slip through, like certain Adobe or Oracle runtime components that finance doesn't care about. We found that adding a final manual review stage for any software title that appears on more than 80% of our endpoints catches most of the remaining system-level items before the data is handed off.

The two-tiered approach with a regex filter and a separate blocklist is sound. It mirrors what we do for audit scoping, where excluding operating system components is a non-negotiable for clean evidence.

I'd add a note of caution about the blocklist of "Microsoft Corporation" as a publisher. That will inadvertently filter out nearly all commercial Microsoft applications, like Office 365 ProPlus or Visio, which are absolutely relevant for license reconciliation. We had to split our Microsoft blocklist into two distinct categories: one for operating system publishers (e.g., "Microsoft Windows Publisher") and another for application publishers we actually need to track. Maintaining that distinction became its own configuration file.

You also mentioned edge cases slipping through. We introduced a simple confidence scoring mechanism in our pipeline. Software titles matched by the blocklist get a score of 100 (definitely exclude). Titles matched by regex heuristics get a score of 80 (probably exclude). Anything below a threshold of, say, 60, gets flagged for that manual review stage you mentioned. It helps prioritize the human review effort.

Splitting the Microsoft blocklist is the critical detail everyone misses early on. Your confidence scoring is a solid evolution. It moves the problem from a pass/fail gate to a triage system.

We found the review stage still choked on volume until we added a simple cooldown: any title flagged for manual review but not touched in 30 days gets auto-approved with a low confidence score. It forces action or accepts the noise.

That also creates an audit trail for why something slipped through.

Interesting that you could make that work with just PowerShell. I've seen similar attempts, and the first invoice reconciliation tends to be a rude awakening when finance sends back a report with a thousand line items for "Microsoft Windows Update" and "Adobe Reader 32-bit" counted as separate licenses.

How are you dealing with the fact that MDE's software inventory includes fifty entries for every Visual C++ redistributable? Finance teams usually consider that noise, not a licensable asset.

Lookup tables just create another maintenance problem. You're trading script edits for CSV edits.

We switched to a simple regex normalization function. Turns "Microsoft Corporation", "Microsoft Corp", etc., into "Microsoft". Works for about 90% of cases. The other 10% get a manual override list, which is way smaller.

If you need perfect mapping, you need a proper asset management system, not a better CSV.

Regex is still string twiddling, just in a more brittle wrapper. Wait until your finance team asks why "Adobe Systems, Incorporated" didn't match "Adobe Inc." across a new dataset.

You're right about needing a real system. But regex and a tiny override list is just a lookup table with extra steps and false confidence.

When that 10% manual list grows, you'll be right back at square one. Seen it a dozen times.

Oh, totally. I've been down that exact road pulling data from MDE for revenue ops forecasting. That gap between the raw feed and something finance can use is massive.

We hit the same wall, but for us it was about forecasting SaaS renewals based on actual installed seats. The biggest snag was the data duplication - a single "Zoom" install could show up a dozen times across different versions and architectures, inflating our counts. We had to add a deduplication layer that looked at the core product name and ignored version numbers and bitness before the data was even sent to accounting.

How are you handling versioning in your license counts? I've seen some teams get burned counting every minor patch as a separate license.

Interesting you went straight to PowerShell. I hit the same gap between MDE data and our finance reports, but I started in Power BI with a live connector.

A problem I ran into early was the timestamp format in some MDE tables causing mismatches with our billing cycles. Your module doesn't handle date transformations, does it? I had to add a separate step to align the data pull dates with our monthly invoice schedule.