Category Archives: SCCM

Why you should not like “like”

So for the past two days I have been checking and triple checking the Configuration Manager environment I support at work. Nothing like a hurricane being labeled “worst case” storm to make you shake the dust of the DR plans. After all the backup checks and distribution point health and content validations, I started looking and the performance of various components. Overall nothing major found but while checking the collection evaluations I did find a few collections that stood out for poor performance. Only one was real nasty and over 2 minutes. The collection is not very large in terms of members but the query populating it needed a little work. So before I dig into the details, you may like to know how to identify the issue. You can find all the info you need in the “colleval.log”.  If you use a little googlefu there are some good tips on how to parse the log with powershell and identify your troublemakers. Or you can use the Collection Evaluation Viewer from the System Center 2012 R2 Toolkit. If you have never used it before The Config Ninja has a great post walking you through it and some reports to display the same info.

With the Collection Evaluation Viewer you can use the run time to identify collections that need some review. When you identify a collection to review open the properties and look at the membership rules.  Here is an example of a collection query that was running longer then it should.

In some environments this may complete in just a few seconds but it was taking over 2 minutes for me. On the whole this is a fairly normal requirement for a deployment. All computers in a location with Software X installed. But the database has to get all of the system records in the location and then check all of the product names it has reported and check to see if the name is “Like” the value in the query. Now there is nothing wrong with Like and there are lots of cases where you must use it. But you have to understand that it is a more expensive cost to SQL queries using them. So to check out what the possible returns were and what the wild cards were allowing the query to collect I queried the view in sql.

And the query returned a single product name of  SAP GUI for Windows

So to solve this query’s performance issue, I just switched to = and the collection evaluation run time went down to 3.5 seconds

Now that the longest running evaluation was resolved there where a couple of collections that where taking 15 – 20 seconds to complete. Not terrible but not good either, as I looked through them I found a couple of things to share. First up is another collection for computers with a specific type of software installed.

So I go to SQL and check how many Display names are returned by the wild card query and get back two. So this time changing to a “in list” query reduced collection evaluation time.

One thing to make note of is you need to consider is if the values being returned are going to change often and will you know about the changes. But in general I would use the original query with an ad-hoc query or a report. The explicit values for the collection membership query are appropriate because of the impact to the collection evaluation process.

For the last example I am going to use a query that need to use like.  This query is evaluating the computer name and the author needed to include systems with a specific range of ending values and a specific character starting the computer name. Along with a few exclusions.

Right away we know the original author did not understand WQL operators.  By using the correct operators when you must use like, the query is simplified and performs much better.

Evaluating for single characters with and underscore “_” is quicker then using the percent “%” for any and all character combinations. If you need to query for a specific number of any characters use multiple underscores. Specifying the range allows the query to be much shorter and simpler.


Troubleshooting Config Manager Content Distribution with SQL

I have been spending a good portion of my time troubleshooting Content Distribution issue for my distribution points lately and wanted to share my process. The distmgr.log is the starting point for identifying issues. I deal with a large number of distribution points with very active distributions and deployments and sometimes it is helpful to have a way to cut through the noise and focus on distributions that are having issues. I use the following SQL query to start that process

For this query a state of 0 indicates the content distribution to that distribution point was successful.  But just because the state is not 0 does not indicate that there is a issue. This query just gives an indication of what is in progress and should be active in the distribution logs. I created a report that will give me a summary of the distributions in progress and how many distribution points are remaining.


The report shows a normal day for me. If I refresh the report  after a few minutes and the overall count goes down for each package then everything is progressing and I will have a good day. But if the counts do not go down I have some checking to do. I can check the Content Status for packages that I need more details on or run the SQL Query to get more detailed information. From there the details drive the next steps. Re-sending the  content, removing and re-sending, adding disk space, resolving network issues, etc.

Happy Troubleshooting

There are no task sequences available for this computer

Occasionally I will see the error message “There are no task sequences available for this computer” in my work Config Manager environment. If after checking the simple things like ensuring the  Task sequence is deployed to a collection and deployed correctly. I am forces to move on to the hard stuff like reading the log files. I had a case like this that was a bit of a stumbling block for a while, but thanks to the fine folks a Microsoft Premier Support we were able to resolve everything.

Some background  info: I have a large test environment that we use to test all installs and OSD Builds before moving them to our production environment. Last Thursday I get a report from a team working on a new OSD task sequence that they stopped seeing all of their task sequences in the selection menu. Then on last Friday another team reported the same issue. So I had them collect smsts.log files and send me the info.  So just for fun our patching team started prepping for the release of the next round of patches and started cleanup and creating new software deployment packages. This along with normal deployment activity ran our secondary site out of space twice. So fast forward to this Thursday and we have managed to get everything else working but no OSD builds will start. They are all failing with the error “No assigned task sequence”. When reviewing the smsts.log the error occurs right after making the request for policy assignments

I have seen similar behavior with corrupt policy and checked for that with this SQL query

If you get any rows returned you can remove the bad policy records with this SQL statement

But in this case there were no rows returned so in lieu pulling the remaining two strands of hair off my head , A case was opened with premier support. After several hours of checking and rechecking settings and trying various things we where still in the same boat, then the support engineer said try this SQL query

While checking the return for the row with our machineID, we noticed that the ArchitectureKey value was not set correctly. In this case the ArchitectureKey  value was the negative value of the machineID. Because we were working with the unknown computer records we set ArchitectureKey  for those entries to 2. If you are working with an existing client record the correct value would be 5.  After correcting those values the OSD build began to pull policy assignments normally.

*** 10/4/16 Updated SQL statements to only Identify unknown computer records. Deletes of regular computer records will not cause the issue and the fix will partially restore the deleted object.

If you are in the same boat use this SQL query to identify the issue

And this SQL Statement to correct the issue

We are still doing some post issue research for root cause but it appears that a admin user deleted all members of a collection rather then removing the membership rule on the collection. This appeared to cause the  negative values and opened the door to OSD Hell week.


Content Location Requests

So things have been busy recently but I would like to share something that I have found to be helpful. I have been troubleshooting instances of installs via SCCM failing due to content not being available to download. However when checking the status of the content it reports that is was successfully distributed to the distribution point. There is a long story about why that I will share later. But in the process of trying to proactively find remote locations and packages that would experience this issue I needed a way to mimic the content location request of the client. The post by Noah Swanson was very helpful. For reference here is Noah’s powershell :

First run through I update the path to the location of the Microsoft.ConfigurationManagement.Messaging.dll on my system and set the site code, Management Point, package id, and package version to valid values for my environment. Everything works, kudos to Noah.  Next I set the AD Site to a value for one of the remote sites. Once again everything works but the response has distribution points for my local client’s ip and the remote AD Site.  So it looks as though this code was developed to run on a local client. I needed to check numerous packages at numerous locations.  A little experimentation and I found that you do not need all of the information populated by the discover method. Just setting the AD Site was sufficient complete the request. Now I have a quick function to start testing with.

Nothing fancy but if the request has distribution points in the return I get the list and if there are none I get a message with the package id and the AD Site that need to be checked. While this is great for one off checks and how do I check lots of packages? Simple, feed a list of packages to the Config Manager Powershell Cmdlets to get the relevant info and you are ready to go.




Client install tip

It has been a busy couple of weeks at work, I spent a couple of weeks travelling to remote sites for some project work. When I got back I had to on on-call week to keep me busy right before the Thanksgiving holiday. It was one of those weeks that would make you pull your hair out, I don’t have any so i survived. Nothing complicated or real interesting but I just spent the week doing the same things over and over. Most of the time it was repairing the SCCM clients so the helpdesk could install code or remote control the system.

Tip – it pays to read the logs
I had numerous clients failing to install or repair due to a error 1603 being returned from the client.msi

So this is not uncommon and is sometimes caused by wmi or pending actions from other installs. So I start with the standard steps – reboot and try again. Same result, so lets check wim with  winmgmt /verifyrepository  everything reports ok aka “WMI repository is consistent”. Ok, lets re-register the msiexec service: remove with msiexec /unregister  install with msiexec /regserver . Done now lets try again; Dang, same error.  Ok, now I have to read the log file. The client.msi.log has the error “CustomAction CcmRegisterWinTask returned actual error code 1603”. Client_msi_log

A search for the error leads me to which shows the resolution is to reset the task scheduler service to automatic and start it before installing the client. I added that to my standard checks for when the client install fails and found several more with the same issue. At least, I can quickly fix the client now to find out what is changing the service configuration.


The Evolution of a Script or why I am constantly refactoring my code.

I have a confession to make, I hate reinventing the wheel when it comes to scripting. Most of the time, there are functioning examples of what I need to do readily available. All I need to do is just ask Bing or Google. Sometimes I piece together functionality from numerous scripts to make a new script that fills the need for the task at hand. This is the story of one such script.

Below you will find the gist of the Check-TSDependencies script in all its ugly glory.  As you can plainly see no though of reusability. Definitely not a tool script. If you stretch your imagination perhaps it could be called a controller script. But to be this was a get the job done; save MrBoDean some time; throw away script.

Well as I mentioned in a previous post  I have been working on improving the processes we use at work to manage SCCM and this ugly little script ended up filling a real need. SCCM has great reporting in the console, if you want to see the status of all distribution points for a single package or the status of all packages on a single distribution point. But trying to determine the status of 30-40 packages quickly does not happen very quickly.  That is until I started running this script.

Then the need for improvements came a long. “This is great for one distribution point but I keep needing to 2 or 3 or 10 …”; “This is great for troubleshooting after we have a issue, how can it be used proactively?”; “Displaying the status on the screen is ok but can you send a email of the status? Oh and I only want the email if there are failures.” and so on …

It quickly became apparent that this needed to evolve. No problem this script is so useful all it needs is parameters for prompts and hard coded packages and SCCM config. Maybe pretty it up and have it use some best practices. But when I take some time and really analyze the code that would not be very effective. The core functionality of the script is the wmi query and translating the results into easy to understand messages. The interactive prompts for the distribution point and the task sequence name are defiantly candidates for parameters. But the interactive portion would have to go or be placed in a controller script. But a little work with the SCCM comandlets and the PowerShell pipeline and the controller may not be needed.

The end result

Also available and


Lazy is as Lazy does

I was happily working away on a couple of scripts for Configuration Manager today, when a case of the monday’s hit. We are prepping to change a little over a 1000 distribution points rate limits. In testing the scripts to set the rate limit, a wmi query that returned some useful info so I tucked it away to come back and take a look at later. After getting the functional work done, I started working on documenting the state before and after the changes. That useful info earlier was the array that stores the rate limit. But in testing the documentation script, the value was null. So after comparing the console to the wmi results and a bit of head scratching, I broke down and started reading the documentation. Turns out that SCCM has lazy properties.

Some Configuration Manager object properties are relatively inefficient to retrieve. If these properties were retrieved for many instances in a class (as might be done in a query), the response would be considerably delayed. Such properties are considered lazy properties and are not usually retrieved during query operations. However, if these properties are retrieved during a query, they have null or zero values, which might not be the actual value of the property for every instance. Therefore, if you want to get the correct value for lazy properties, you must get each instance individually.

So powershell to the rescue. Run the Query for the bulk info

Then a foreach to drill in

Once we get to what we want a Get call will populate the Lazy Properties

see the script at