Quick Tip – SCCM SQL Secret

Have you ever wanted some information from the SCCM SQL DB, which you can access in the console but can’t find the data easily in the DB? Helpfully, all the data you access in the console is retrieved from the site server – which keeps a record of the queries.

Open the SCCM Console and access the data in the console, this can be loading a list of devices or even a status message query.

Look at the log file “SMSProv.log” located in %Program Files%\Microsoft Configuration Manager\Logs. You should be able to find the query that the site server ran to get the data.

\\SCCMSITESERVER\drive$\%Program Files%\Microsoft Configuration Manager\Logs\SMSProv.log

SCCM OSD – Failed to Run Task Sequence (0X80004005)

I had a system delivered recently with a NIC which was dead on arrival. (Dell quality is going up and up!!)

An engineer attended site and replaced the motherboard. Next time I attempted to run OSD from PXE boot I encountered an error :

Failed to run Task Sequence (0X80004005)

So I looked in X:\Windows\temp\smsts.log and noticed :

SyncTimeWithMP() failed. 80004005.

The genius engineer had set the time wrong, or not at all – so when the machine was trying to make a secure connection with the management point it was failing!!

Setting the time correctly in the BIOS / firmeware resolved the issue.

Downloading SCCM / ConfigMgr Install Prerequisites

Before you can install the 800MB file containing the ConfigMgr install, you have to download more files. Microsoft likes making installation difficult!

This is fine on most systems with internet access – but when installing isolated labs, it can be a pain in the ass.

Luckily you can download the Prerequisites on another machine and copy them into the Lab environment.

<ConfigMgr Extracted Source> \SMSSETUP\BIN\X64\SetupDL.exe “C:\Path”

Understanding the SCCM Database (using the Console)

I planned on publishing an article on how to use a log stored on the primary server to turn data you have queried in the SCCM console into an SQL query which can be used in Report Builder etc…

I drafted the beginning of this article in the middle of last year and didn’t get around to reviewing and finishing it until today. I had forgotten the location of the log required for this so googled it – and came across a blog post with all the information I planned to share – but with lots of screenshots and even instructions on using Report Builder.

So instead of re-inventing the wheel, I’ll link you to that excellent post instead!

http://www.scconfigmgr.com/2014/01/24/create-custom-reports-for-configmgr-2012-with-report-builder/

Collecting Custom Properties / Fields Using the Registry and Hardware Inventory

I recently implemented a mechanism for collecting warranty end dates using a script which added the date to a registry key, then using hardware inventory to collect the key into SCCM SQL DB.

This is useful if there is information about a client machine which is not stored in WMI.

It worked great and proved a good method of collecting data, In this post i will cover the whole process in detail :

Step 1 – The Script

The first stage is creating a powershell, vbscript or bat file to grab the custom property you are looking for and write it to a registry key. I won’t go into the detail of doing that in this post. I am using a script which reads the setupact.log file in C:\Windows\Panther and returns whether the machine firmware is BIOS or EFI (UEFI) – then writes the info to a reg key. I couldn’t find a field in the registry or in WMI on Windows 7 which stores this information. (Hence the script and this post!)

Step 2 – The Deployment

The script needs to be deployed and run on every machines we want to collect information from. I find it easiest to create a source folder, place the powershell script in there and create a bat file ‘wrapper’ to call the powershell script and temporarily change the execution policy.

powershell.exe -ExecutionPolicy Bypass -NoLogo -NonInteractive -NoProfile -WindowStyle Hidden -File "script.ps1" >> "%temp%\script.log"

For the detection method, use the following :

  • Setting Type: Registry
  • Hive: HKEY_LOCAL_MACHINE (or other if you are using different hive)
  • Key: SOFTWARE\CompanyName\YourKey (this is the key you have written your info too)
  • Value: NameOfKey
  • Data Type: String (or other if you are using different data type)
  • I used “This registry setting must satisfy the following rule to indicate the presence of this application”
  • Operator “Not equal to”
  • Value: <blank>

Sometimes my script would fail to gather the info required but would create the key with null value, i didnt want my detection rule to be satisified by this. (I know i was lazy and should have added more error checking the script! But this had the same outcome….)

Step 3 – The Mof File

Great! We have the info in the registry, but now we want to add it to the SCCM DB – for ease of querying and reporting etc…

This method was inspired by this brilliant post – https://sccmguru.wordpress.com/2014/04/24/hp-and-lenovo-warranty-information-in-configuration-manager-2012-r2/

I suggest using the methods described in the above post to create your MOF file and import the changes into ConfigMgr.

Step 4 – The Result

Once you have deployed the hardware inventory custom settings, the deployment has run on some machines and hardware inv has run you should have some results! If not, something is wrong, retrace your steps and check for mistakes…

ConfigMgr Distribution Point Priority

Using System Center – Configuration Manager 2012 R2 we recently had some issues with routing, iscsi interfaces and content distribution. We needed to speed up distribution to a specific distribution point for some corrupt packages in order to get software installed at an Office in the Middle East.

So we needed the content to ‘jump’ the queue. There are no options in the SCCM Console to change DP priority order (that I have found), but luckily Microsoft have provided a way of doing it.

Note : As noticed by flatfour67 in the comments this may not work for DP’s assigned to secondary sites.

The following PoSH (Powershell) will list your Distribution Points and the Priority Assigned to them.

$dpinfo = Get-WmiObject -Query "SELECT NALPath, Priority, SiteCode, TransferRate, Description FROM SMS_DistributionPointInfo" -Namespace "ROOT\SMS\site_SITECODE" -ComputerName "MP_ServerName" | select NALPath, Priority, SiteCode, TransferRate
$dpinfo | Out-GridView
## Yes it could be done on one line, but I like doing it this way. After i have closed the GridView window i am still able to access the data without having to query WMI on the server again...

Default priority is 200, anything lower has a higher priority (content will be sent here first) and vice versa. Available values are 1 -> 300.

So How Do I Change the Priority?

Continue reading “ConfigMgr Distribution Point Priority”

Status Messages For an OSD Deployment

Troubleshooting OSD remotely can be difficult if the person initiating the build does not know how to get the logs from a failed machine.

This is a useful status message query to show all messages from the OSD process. It requires a Deployment ID, which is from the deployment of the task sequence you have a problem with.

select
SMS_StatusMessage.*,
SMS_StatMsgInsStrings.*,
SMS_StatMsgAttributes.*,
SMS_StatMsgAttributes.AttributeTime
from SMS_StatusMessage
left join SMS_StatMsgInsStrings
on SMS_StatMsgInsStrings.RecordID = SMS_StatusMessage.RecordID
left join SMS_StatMsgAttributes
on SMS_StatMsgAttributes.RecordID = SMS_StatusMessage.RecordID
where SMS_StatMsgAttributes.AttributeID = 401 and SMS_StatMsgAttributes.AttributeValue = "!!DEPLOYMENTID!!"
and SMS_StatMsgAttributes.AttributeTime >= ##PRM:SMS_StatMsgAttributes.AttributeTime## order by SMS_StatMsgAttributes.AttributeTime DESC

Replace !!DEPLOYMENTID!! with the deployment ID of the TS.