Your company's ad could live here and reach over 50,000 people a month!

Share This Post

Getting to Know Normalize CM Data

If you are reading this you’ve most likely read part one and/or part two of this series. In not, I suggest you go back and read them. In this, part three of the series; I am going to show you a quick tip about writing a report using normalized data.

BDNA Normalize CM ships with many built-in reports, queries and collections that you will find useful. I’ll discuss those in a later blog. For now let’s clear up some confusion about writing our own custom reports.

Normalize CM puts everything in two easy to find views for you. They are v_GS_BDNA_Software_Data0 and v_GS_BDNA_Hardware_Data0. I bet you can guess what’s in each of those so I don’t feel like there’s any more explanation needed here.

If you’ve been working in SCCM for long at all I’m sure you’ve written some SQL queries before. Most likely you started your query something like this:

SELECT v_R_System.Name0, v_GS_ADD_REMOVE_PROGRAMS.DisplayName0
FROM v_R_System
INNER JOIN v_GS_ADD_REMOVE_PROGRAMS ON v_R_System.ResourceID = v_GS_ADD_REMOVE_PROGRAMS.ResourceID
WHERE (v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE ‘%foo%’)

Pay special attention to that join statement. From the first day I started learning SMS I’ve always been told that in almost all SQL queries I should select from v_R_System and join other views to that using the ResourceID. So let’s try that using v_GS_BDNA_Software_Data0.

SELECT v_R_System.Name0, v_GS_BDNA_Software_Data0.SoftwareName0
FROM v_R_System
INNER JOIN v_GS_BDNA_Software_Data0 ON v_R_System.ResourceID = v_GS_BDNA_Software_Data0.ResourceID

So here are the results of that query.

screenshot

What the heck? I don’t see any of my computer names listed there. All I see is BDNAClient_x. This thing must be broken. Has anyone blogged how to uninstall this yet?

Never fear, it’s not broken, you don’t need to find the uninstaller. Normalize CM takes all of your hardware and software data and processes it against their Technopedia database, normalizes it, then injects it back into SCCM. That injection is done via their proxy clients. This means you can’t use ResourceID to join their views to other views. Instead you need to use the SMS_Unique_Identifier0 to join the views. Here’s an example of a how a query using Normalize CM data would look:

SELECT v_R_System.Name0, v_GS_BDNA_Software_Data0.SoftwareName0
FROM v_R_System
INNER JOIN v_GS_BDNA_Software_Data0 ON v_R_System.SMS_Unique_Identifier0 = v_GS_BDNA_Software_Data0.SMSUniqueIdentifier0
WHERE (v_GS_BDNA_Software_Data0.SoftwareName0 = ‘foo’)

Sorry… No screenshot here because this is running in my production environment and I can’t post that info in a blog but trust me, it works.

I hope this clears up some confusion for people. I know the first time I tried writing a query using the data I was frustrated. A quick call to BDNA Support cleared this up for me.

Share This Post

Leave a Reply