Is your SCCM SQL stuck in Evaluation mode? Don’t despair!

Have you ever wondered what happens when you install SCCM 2012 or 2007 on top of SQL and choose ‘Evaluation mode’, then forget to enter the SQL key?

SQL will turn off the SQL Database Service on day 180, and never turn back on until you license it

How’s that for getting your attention?

Recently at a client, my contact was searching for their install keys, and promised to provide them later. No problem, I said, and proceeded to install both ConfigMgr 2012 R2 and SQL 2012 SP1 in evaluation mode. Typically what would happen here is that after a week or so, the VAR would get back to us with some keys…This time however…

The big rub was that this was an educational institute. They are sold licenses in different quanities and at different rates than your typical enterprise or SMB, the sorts of customers I deal with much more often. Those larger firms often buy a bundle of SQL licenses, and I’d just use one of them, which would get re-added / comped to their license pool the next time they re-upped their licenses. Schools don’t typically need SQL Server, relying instead on Postgresql or MySQL (shudder) or even worse, Oracle.

I contacted Microsoft about my dilemma and posted online, I was told that System Center does include a special SQL installer that won’t prompt you for a key, meaning that there are no install keys for me to find in my Volume License account. What should happen is that if you use the right iso, it will contain your own license key for SQL pre-embedded, and you can actually see it when you run the installer.  However, this installer will detect pre-existing SQL features, and will dump/not display the license info if it detects any.

When I posted online about it people told me to backup my SQL Server DBs, delete my SQL install, then reinstall using the special installer ISO (and hope that SCCM’s accounts restore without any pain).

Thanks to the help of my friend, MCT and SQL Badass Sassan Karai, we found a better way.

Continue reading

SCCM SQL Query for SSD Drives

Recently at a client, a need arose to provide a report listing whether a workstation had an SSD or a spinning disk.

Unfortunately, Windows does not concern itself with noting whether or not a disk is spinning or solid state, and thus the information is not in the registry or WMI for us to collect with SCCM.

However, most manufacturers stick to a naming convention, and with a little bit of elbow grease, you can expand this to work in your environment too.  In this format, you’ll get ‘Known SSD Drive’ if the drive model or naming is captured in the CASE filter of query, or the drive model if not.

Jump here for the code : Detect SSD Drive

You can modify the code to list whatever you like in the 'SSD Column'
You can modify the code to list whatever you like in the ‘SSD Column’

SCCM SQL Reporting code “Computers with one of many installed apps”

Today at a Client, the need arose to create a report which would list computers that have any of two applications installed.  The principles used could easily be modified to fit a much larger list of applications, if the need arises.

I started by modifying the canned “Search for Installed software” by stripping a number of the default prompts, and then altering the comparison line of the query to include two or comparisons, one for Mozilla Firefox and another for Google Chrome.  The result was this report, showing a count of how many instances of each version of the app was found, which was pretty neat.

02 - need to edit

It was desired to know beyond this which computers actually have a particular application installed. This was done by using the SCCM reports linking function. However, out of the box we didn’t have any good reports available, so we created one.

As I normally do, I began in the SQL Management Studio. I was able to identify the ProductName0 Column of the v_GS_INSTALLED_SOFTWARE_CATEGORIZED Table as a good identifier to locate the software we needed.  For the actual code, see the link below.

After creating our new report which asks for the input of a Product name and then shows you all of the juicy details including computers which have that product name installed, I went back to the original report in the ConfigMgr console and added a link to the newly created report. In order to link reports, you need to specify one of the columns on the output rows of one report as an input for a prompt on the receiving report. An easy tip to determine which column number is which is to mouse over the header of each line, and note the code tip in your status bar (for most browsers). We’ll mouse over the Product Name field and see what happens.

03 - need to edit

Note the ‘Col=’ bit of the url, highlighted in Yellow. This is our clue which column to select in the linked field, as depicted below.

04 - need to edit

And, now, in the final product, clicking the Linked icon in the front of any row will launch another report, listing all of the computers with this software installed.

02 - need to edit

05 - need to edit

Sorry for the somewhat gratuitous redacting of information.  Some of my clients are a bit sensitive when it comes to serial numbers and collection names, etc.

I hope that this demonstration was helpful! As always, please let me know if you see any room for improvement or if you have any questions.

Edit:  I now have a better way of doing this, which joins all product versions together.  

Thanks go out to Simon Cartin for helping me figure out how to do the IF Then and SubString commands!

Here is a screen shot of this output.

6 - new version

The code for this is also included in the below link.

All source code is available here –