Even if this title doesn’t mean anything to you, just bear with me for a moment. I’m about to show you a nice and easy way to query a database (the SMS/SCCM Database in this case) and this even for all records which have specific bits in a bitmask set as it happens quite often within SCCM.

Recently I had to query some information from SCCM again I wanted to use within my MDT Web FrontEnd. I wanted to have a list of all available packages and be able to search within this list. Additionally return a list of all programs assigned to a specific package. This shouldn’t be a problem as I already had a full bunch of existing functions for this used within some webservices but one issue hit me hard into the face.

Accessing SCCM using the WMI or the SDK for SCCM 2007 was incredibly slow. It took me more then 13 seconds to just establish the connection and another couple of seconds to return the result. This is ok for a webservice during the Gather process as a couple of seconds more in an automatic process doesn’t really matter (at least from my point of view). But this is simply unacceptable for a website. Everybody would assume an error before the result arrives. OK, I`m pretty sure that I probably wrote it in a very inefficient way and that there are a couple of ways to improve the response time but even if I could drop the time it took by more then 90% it would still be slow compared to all other requests.

So I thought why not directly accessing the database as this is probably the fastest way of getting the necessary information? As long as we just query and don’t need to make any changes this is acceptable. It’s adding some complexity if you need to get information from different servers but I was lucky, all my packages are hosted on the central server.

As mentioned in an earlier post, I used the .Net Entity Framework to access the MDT Database. This way you can program against a Model and use LINQ to query the database instead of writing all the database related stuff yourself (opening the connection, handling the connection, creating the SQL Query, executing the query, processing the resultset, etc). Instead you are working with Classes, not with Tables which makes it easier to code and you have IntelliSense (hooray) ;-)

And now we will use the same for accessing the SCCM Database.

What do we need to have?

First you need to have some kind of Development Environment. I`ve used Visual Studio 2008 for this and the free Express Editions should do it (No, I don’t have tested it).

Second you need to have the .Net Framework 3.5 SP1 installed which will also add the Entity Framework and the necessary templates to Visual Studio.

OK, let’s start before you get bored by to much text.

Start Visual Studio and create a new Project. As this is just a demonstration we simply use the “Console Application” Project template.

Now we add a new item to our Project. Namely the “ADO.NET Entity Data Model”

This will open up a wizard. To ease our life we choose to generate the model from the database

Next we need to create the Database connection. The exact settings depend on your local environment, but I’m sure you are able to figure this out ;-)

You might want to save the connection string for future usage so just check the appropriate checkbox and give it a proper name e.g. SCCMEntities.

Now the wizard will connect to the SCCM Database and retrieve a list of all Tables, Views and Stored Procedures. As this will be a couple hundred items it might take a moment to finish. In the next wizard pane, just expand the “Views” Node and choose the “v_Package” and “v_Program” Views.

Now we “Finish” the Wizard. The wizard now creates a model based on the supplied information. After this process has finished you will see a graphical presentation of the Entity Types which have been abstracted from the Views

I recommend to apply some minor changes to this automatically created Classes. Just right-click one of them and choose “Properties”. In the Properties windows (mostly in the lower right corner of Visual Studio) change the Entity Set Name to “v_Packages” and “v_Programs” accordingly. This will make it easier to identify if you are dealing with a single object or several of them. If you want you can also rename them to something you prefer. For this demo I will leave it as it is.

The next thing we should add is an association between those Entities. If you look into the list of properties of these Entity Types you will see that the “v_Package” Entity has a Property called “PackageID”. This is a reference from each Program to it’s corresponding Package. And we want to use this in our Model. So just click on the “Association” item of the Toolbox (typically on the left side)

Then click on the “PackageID” property of “v_Package” and once again on the “PackageID” property of “v_Program”. This will create a new association and you should also see a graphical representation of this association in the model

It might happen that it doesn’t create the Association properly. To verify just right-click on the Association (the dotted line shown above) and choose “Table Mapping”. It should look like this

If it is empty, just choose “v_Program” from the dropdown box shown and it will fill in the rest automatically.

Additionally you will see that it added so called “Navigation Properties” to the Entity Types.

To keep it simple, you can use these Navigation properties later to move from the Package to all assigned programs or the other way round.

OK, now you can close the model to save the changes. All preparations have been finished. Let’s do some coding:

Open the “Module1” (Hey, it`s just a demo :-) )

The first thing we need to have is an instance of the Model we have just prepared using the wizard. To do this we create a private variable and call it context:

Now we want to see some data. To query our Model we use a new language called LINQ or “Language-INtegrated Query”. LINQ is a standardized language to query “something”. I said something because it doesn’t really matter what you query. It might be a database, an Entity Model, XML Files, even Active Directory. All it needs is a so called Provider which will be able to translate this LINQ queries into something which can be used on the targeted objects. But enough about this. Just search on the Internet for this Topic and you will find tons of useful information about it. All you need to know for now is that you can use LINQ out-of-the-box as it is integrated into VB.Net and C#. There are even different syntaxes you can use. I personally prefer the method based syntax and all my examples will base on this. OK, Let’s have a look on a very simple query to get a list of all Packages and just write the package id and name to the console:

If you now run the project the output should look similar to this

Let`s use a more complex example to describe what we actually did. We now want to have a list of all Packages which contain the word “Office” and order it by the name of the Package. A possible solution could look like this

OK, the syntax might look a bit strange, especially if you have used SQL before. Some call this Yoda SQL and I think this really hits the mark. It looks quite similar to SQL just a bit “mixed up” ;-)

But let’s get into the details. First we define a new Variable called “Apps” to store the result. You might notice that we don’t define the type of the variable. It will be set implicitly as the compiler “knows” the type by the value we assign to it (This is a new feature since version 3 of the .Net Framework). Then we use the private variable “context” we have defined before. This is the instance of our SCCM Model. It has a property called “v_Packages” which is more or less the “Table/View” from the Database. So it contains a list of “v_Package” objects (or the rows from the Table/View). Now we can use a lot of expressions (functions) on this list. In our case it’s the “Where” and the “OrderBy” Expression (Quite similar to the Where and OrderBy clause of SQL). There are a lot more you can use and combine and IntelliSense make it easy to use them. In this Expressions you see something which gave me a hard time at the beginning. It’s a so called “Lambda Expression”. I`m not going into the very detail now as this would probably confuse you even more (I`m already confused by just writing it). It’s easier to start playing around with it as you will very soon grasp what it does and how to use it. You mainly define a generic function with a parameter (“a” and “b” in our case). The name of the parameter isn’t really important. The important thing is, that this parameter will give you access to the actual object which is used to be filtered, ordered, etc.

If you now run your project, you will get a list of all Packages which contain the word “Office” and it will be ordered by the name of the package.

OK, another example to see how powerful this can be. If you ever used a Package within a Task Sequence like during the Deployment process you probably know that the Program which will be executed has some requirements. One of them is, that the following checkbox need to be checked

This one can be found on the Advanced Tab of the properties of the program. So let’s say we are only interested in a list of all programs with this checkbox checked. How do we do this? And here comes the hard part. It is part of a Bitmask stored as an Integer in the “ProgramFlags” Property of the “v_Program”. According to the SCCM 2007 SDK Documentation, it is set in Bit 1 of this Bitmask (It’s called AUTHORIZED_DYNAMIC_INSTALL in the Documentation). And for the fun of it we also want to have Bit 13 (UNATTENDED) set. This is a one-liner in LINQ (OK, 4 lines in the example but these are just to ease reading):

So what did we do here? First we added an “Include” Expression. This will just make sure, that the query not only gets a list of Programs, it will also load all assigned Packages. The “v_Package” is the Navigation Property I mentioned already. This is the beauty of having this object oriented. We can get the information of different tables in one single call and one single hierarchy/tree of objects and move between all associated entries. But first to the Where Expression. Here we first (Bitwise) OR 1 and 4096 (4096 is the decimal representation of Bit 13) and then we (bitwise) AND it with the Bitmask stored in the property ProgramFlags. As you are probably not keen on getting to deep into Bitwise operations (it has been a long article already), it gives a good idea on how powerful and easy it can be.

Just one more thing to mention. We order the result by the Name of the Package(!) and we also write out the Package ID to the console. This shall just show how we can move between associated objects. Isn’t that kewl? ;-)

Ok, one final example. It`s nice to have a list of Packages which can run from a Task Sequence but we wanted to have a list of Packages. How do we do this?

This is actually a very good example why this is sometimes called Yoda SQL. If you are experienced in SQL, you will have a hard time putting things like Select or Distinct at the end ;-)

OK, even if this has been a rather long (yes, very long) article, it touched only the surface of possibilities. I personally think this is a great and easy way to access some information from a database. It’s not made for every database and has it’s limitations (the MDT Database gave me a hard time, even if it looks simple), but I really don’t want to miss all the features it’s been giving me. So just give it a try, play around with it. As always, Feel free to contact me or leave a comment on this Blog (you need to log on first due to the high amount of spam).

Also find a download with this example project HERE. Just open the app.config file within the project and change the Connection String to your environment. And then have a lot of fun playing around. As we are using Views in this examples we can’t really hurt anything. It`s provided “AS IS” without any express or implied warranty of any kind. And never ever use it to make any changes to the SCCM Tables! It`s nice for reading. To make changes you have to use WMI or the Management provider from the SDK (which basically also uses WMI).