A couple weeks ago there was a thread on the SMS mailing list discussing AI, add/remove programs data and third party normalization products. Personally I’ve been using BDNA Normalize CM for a couple years now. I find the normalized data easier to use than any of the out of the box data. Using the normalized data I am able to create queries for collection and reports that are much more lightweight thereby running faster and producing less load on my SQL server.
In a previous blog I described a process, using a script, that allows one to run the same report against many computers producing an individual pdf of the report for each computer. This report, which many people have been using for years, is commonly known as the All in One Machine Details report. It contains everything a person could possibly want to know about the hardware and software for a specific machine. In order for the report to be usable we must exclude a lot add/remove programs data from being displayed in the report. Traditionally we did that directly in the SQL query itself.
Running the machine details report that has been around as long as I’ve been doing SMS isn’t all that bad if you are just running one of them. In my case though I was running hundreds of them at a time. That report contains 5 separate select statements, at least 7 joins and at least 5 not like statements required to excluded the add/remove programs ‘”junk” that nobody cares about. Even with all of that the report still produces less than optimal results due to the inconsistent way publishers write entries into add/remove programs; however for many years we just had to accept that because it was the best we could do.
In case you aren’t familiar with the report I’m talking about and/or the SQL behind that report I’ve attached it here as a Word document. My intentions were to paste it directly into the blog but it’s so much text that simply was no feasible.
Generating the same style report using normalized data is much simpler. You may ask why. After the data from hardware and software inventory is ran through the normalization process it is then injected directly into the CM database, in a supported manner, into two views one being hardware and the other software. That process eliminates many of the joins and individual select statements used in the original all-in-one report. For instance the same report I described above can be written using only two select statement, two joins and zero specific exclusions using not like statements. This time the queries will actually fit directly into my blog post.
SELECT r.Name0, r.Resource_Domain_OR_Workgr0, r.User_Name0 as Username, r.AD_Site_Name0, hw.Isvirtual0, hw.Manufacturer0, hw.Product0, hw.Model0,hw.SerialNumber0, hw.OS_Name0, hw.OS_MajorVersion0, OS_Edition0, hw.OS_ServicePack0, hw.RAM_TotalPhysicalMemory_KB0, hw.HDD_TotalSpace_MB0,hw.HDD_FreeSpace_MB0, hw.HDD_UsedSpace_MB0,hw.category20 as Type
FROM v_R_System r
INNER JOIN v_GS_BDNA_HARDWARE_data0 hw ON r.SMS_Unique_Identifier0 = hw.SMSUniqueIdentifier0
WHERE r.Name0 = ‘@compname
SELECT DISTINCT Publisher0, SoftwareName0, MajorVersion0, MinorVersion0, Edition0
FROM v_R_System r
INNER JOIN v_GS_BDNA_SOFTWARE_data0 hw ON r.SMS_Unique_Identifier0 = hw.SMSUniqueIdentifier0
ORDER BY Publisher0, SoftwareName0
What does that mean to you? It means the normalized queries will run faster, produce less load on your SQL server and are easier to write.The query using normalized data produced a clean two page report. The original query, without the 5 not like statement produced 14 pages of useless junk on my test computer and even with those statements still produces 134 rows of add/remove programs data that I need to sort through. Compared the the 60 rows of data returned when I use my normalized data because Normalize CM is automatically filtering out the ‘junk’ for me.
If you are going to be attending MMS 2013 next week stop by the BDNA booth and those guys will be happy to show you some demos of just how powerful their product is. You may catch me there in the booth, I’ve agreed to be there during certain hours to provide real-time customer referrals and notes from the field. Be aware though, they aren’t going to be as easy to find as they have been in years past because the all too familiar skull tattoo logo is no more.