20141008_105129

Introduction

The evolution of SMS to SCCM (System Center Configuration Manager), has produced both a simpler and more complex toolset at the same time.  Confusing.  Yes.  I’ve been told that I am, many times.  Among the things that have become both at once are the parts under the hood.  While the UI has been streamlined, it incurs some re-wiring of the engine while not breaking wires that support some legacy parts that are still relied upon.

I spend roughly 80% of a typical work day inside the database environment of Configuration Manager.  Digging around in the tables, views and whatnot, like the cast of Walking Dead when they discover an abandoned house.  Occasionally popping my head up with some odd chunk of something and proclaiming “a-ha!!!”, which pisses off my coworkers.

I often mumble things like “oooh!  I can link this, and this, and that, and subquery on that, and get a list of all the HP laptops that had their RAM increased on a Tuesday within AD sites that begin with ‘EasternRegion’, which are 64-bit and running Windows 8.1 with IE11 installed.  Cool!”  It’s semi-retarded I know.

Back to the discussion… During this evolution, new things have been added.  Some old things have been removed.  And some have been modified.  Two quick examples being the views “v_Collection” and “v_Collections“.  (rubs chin, squints eyes, grunts, and drinks more coffee).

Whether you like building new reports within the constructs (SSRS, etc.) or from outside (Visual Studio, scripting, etc.), you’ve probably encountered some frustration weaving together “just the right” mix of tables and views to get the items you want to pull into your results.  It can be a lot of fun.  (diabolical laughter…).  Some examples include finding derived attributes, such as specific Distribution Point servers for a given resource (device), especially when they roam around a lot.

There are some really, and I mean REALLY, useful views that come built-in, which will suffice for most general needs.  But when you need to pull together a lot of pieces to describe and reveal a particular device, it is often helpful to create a new view.  Doing that in an external app “query builder” may feel comfortable, but it’s kind of like using Scotch tape to fix a house roof.

Caveats

I don’t profess to be a DBA, nor a database “expert”, but I have been known to play the part when surrounded by people that don’t speak English.

If you are a rock-solid DBA, I can’t tell you anything new.  In fact, you’re probably already done with this article by now and falling asleep.  If you’re not an uber-DBA, this would be a great time to become friends with someone who is.

On a serious note, whenever you create a new View, there are performance and security aspects to consider.  So talk to your doctor and follow the directions before adding water and bringing to a boil.  Uh, I mean talk to your DBA team.  There are trade-offs to adding a view to the source database, as well as creating one in a peer database, or from a cached replica, and so on. That part is up to you to research, scratch, poke and punch until you’re satisfied you have the best approach.  All I’m attempting to do is provide a simple piece in a bigger puzzle:  a consolidated SQL view.

You may ask: Why bother?  Why not just join the data in an application or script during the app-layer grind?

Answer:  The more you can do within the database environment, the better.

That’s right.  A properly designed, built and tuned SQL process will yield results way more efficiently than any app-layer churning.  This goes back to my trusty old “wrench and hammer” analogy: use the proper tool for each job.  Manipulating and moving data is best performed by a database.  You can trust me, because I’m an app developer and I drink lots of cold, stale coffee.

Enough blabber… clap! clap!

The Code

CREATE VIEW v_AA_ClientSummary
AS

SELECT DISTINCT 
dbo.v_R_System.Name0 AS ComputerName, 
dbo.v_R_System.ResourceID, 
dbo.v_R_System.Client0 AS ClientInstalled, 
dbo.v_R_System.Client_Version0 AS ClientVersion, 
dbo.v_R_System.AD_Site_Name0 AS ADSiteName, 
dbo.v_R_System.Creation_Date0 AS ClientDate, 
dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0 AS Manufacturer, 
dbo.v_GS_COMPUTER_SYSTEM.Model0 AS ModelName, 
dbo.v_GS_COMPUTER_SYSTEM.SystemType0 AS CPUType, 
dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 AS ChassisType, 
dbo.v_GS_SYSTEM_ENCLOSURE.SerialNumber0 AS SerialNumber, 
dbo.v_GS_OPERATING_SYSTEM.Caption0 AS OSName, 
dbo.v_GS_OPERATING_SYSTEM.CSDVersion0 AS ServicePack, 
dbo.v_GS_PC_BIOS.Manufacturer0 AS BIOSvendor, 
dbo.v_GS_PC_BIOS.ReleaseDate0 AS BIOSdate, 
dbo.v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS Memory, 
dbo.v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0 AS SiteCode, 
dbo.DPInfo.ServerName AS DPServerName
FROM dbo.v_R_System INNER JOIN
dbo.v_GS_COMPUTER_SYSTEM ON 
dbo.v_R_System.ResourceID = 
dbo.v_GS_COMPUTER_SYSTEM.ResourceID 
INNER JOIN
dbo.v_GS_SYSTEM_ENCLOSURE ON 
dbo.v_R_System.ResourceID = 
dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID 
INNER JOIN
dbo.v_GS_OPERATING_SYSTEM ON 
dbo.v_R_System.ResourceID = 
dbo.v_GS_OPERATING_SYSTEM.ResourceID 
INNER JOIN
dbo.v_GS_PC_BIOS ON dbo.v_R_System.ResourceID = 
dbo.v_GS_PC_BIOS.ResourceID 
INNER JOIN
dbo.v_GS_X86_PC_MEMORY ON 
dbo.v_R_System.ResourceID = 
dbo.v_GS_X86_PC_MEMORY.ResourceID 
INNER JOIN
dbo.v_RA_System_SMSAssignedSites ON 
dbo.v_R_System.ResourceID = 
dbo.v_RA_System_SMSAssignedSites.ResourceID 
INNER JOIN
dbo.DPInfo ON dbo.v_R_System.AD_Site_Name0 = 
dbo.DPInfo.ADSiteName

GO

Epilogue

Yawning?  That’s okay, so am I.

You may notice a lot of INNER JOIN stuff going on.  This constrains the results to only those resources which have inventory data.  So items which have been discovered (v_R_SYSTEM), but which do not have any related inventory data (e.g. v_GS_COMPUTER_SYSTEM), will not be returned.

There may also be duplicate rows in terms of Name0 (ComputerName) values, due to unique AD SiteName and DP server name values.  This is especially common with mobile devices, but you can subquery this into yet another view to remove that and return truly distinct rows.

I used “v_AA…” for the prefix so that it sorts alphanumerically to the top of the list.  You can call it “kitty-doo-doo” if you prefer.

Finally, be sure to apply appropriate permissions for what you intend to access this new object.  And listen to your grandmother: don’t forget indexing!

Blah blah blah and yap yap yap.  Cheers!

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s