August 18 2011

SCCM report – count Windows Server versions on physical hardware only

I had a requirement to count the different versions of Windows Server but only on physical hardware. This SCCM report does this by excluding VMWare and Hyper-V machines:

SELECT OPSYS.Caption0 as C054, COUNT(*) AS 'Count'
inner join v_R_System sys on OPSYS.ResourceID=sys.ResourceID
where OPSYS.Caption0 LIKE '%server%'
and (v_GS_COMPUTER_SYSTEM.Manufacturer0 NOT LIKE '%Microsoft Corporation%')
and (v_GS_COMPUTER_SYSTEM.Manufacturer0 NOT LIKE '%VMware, Inc.%')


I use a maximum of one Google Ad per post to help offset some of my blog hosting costs.


August 15 2011

SCCM report for SQL server Developer, Enterprise and Standard version installations

I had a requirement to report on SQL Server installations in our environment as part of our Microsoft EA true-up process. This included SQL 2000, 2005 & 2008 installations. The difficulty was that you can’t run a simple Add / Remove Programs report because it will report all of the SQL client tools installations as well as the Express and Windows Internal Databases version. I needed to actually report on just the SQL Server Developer, Enterprise and Standard version installations.

After using the MOF edit by Sherry Kissinger (, I then create a report that filtered out much of the details and gave me a summary of SQL 2000-2005-2008-2008R2 Server Developer, Enterprise and Standard version installations. The query for the SCCM report is:

sys1.Netbios_name0 as [Machine],
max(Case sql.PropertyName0 when 'SKUName' then
sql.PropertySTRValue0 end) as [SQL 2008 / R2 Edition],
max(Case sql2.PropertyName0 when 'SKUName' then
sql2.PropertySTRValue0 end) as [SQL 2005 Edition],
sys1.ad_site_name0 as [Site Name],
v_R_User.Full_User_Name0 As [Primary Machine User]
from v_r_system sys1
left join v_gs_sql_property0 sql on sys1.resourceid=sql.ResourceID
left join v_gs_sql_property_legacy0 sql2 on sys1.ResourceID=sql2.ResourceID
left Outer Join v_R_User On sys1.User_Name0 = v_R_User.User_Name0
(sql.PropertyName0 in ('SKUNAME','SPLevel','version','fileversion')
sql2.PropertyName0 in ('SKUNAME','SPLevel','version','fileversion'))
sql.PropertyStrValue0 LIKE '%standard%' or
sql2.PropertyStrValue0 LIKE '%standard%' or
sql.PropertyStrValue0 LIKE '%enterprise%' or
sql2.PropertyStrValue0 LIKE '%enterprise%' or
sql.PropertyStrValue0 LIKE '%develop%' or
sql2.PropertyStrValue0 LIKE '%develop%'
group by sys1.Netbios_name0, sys1.User_Name0, sys1.ad_site_name0, v_R_User.Full_User_Name0


August 8 2011

KMS host server won’t activate clients

Our KMS server had been running without issue for months until recently when I tried to activate another Windows Server 2008 R2 client (already 30+ in the environment) and it just wouldn’t activate, giving the error “”Error: 0xC004F074 The Software Licensing Service reported that the computer could not be activated. The Key Management Service (KMS) is unavailable”.”.

I decided to reinstall the KMS host key on the KMS server and tried to re-activate using “slmgr -ato”, however this gave an error also – “The activation server determined that the specified product key could not be used.” After doing some googling and spending hours trying to get some Microsoft help, it appears that there are limits on the number of times a KMS host can be activated (although the error message was completely useless and didn’t even remotely hint that this was the problem).

Anyway, in short – the solution was to email Microsoft via with:

  • KMS host key
  • Organisation name
  • Enrollment number
  • Number of additional activations you would like

Within about 2 business days Microsoft responded with “As requested, the number of KMS activations has been increased. The additional KMS host activations have been added to our production environment and are available for use. We appreciate your patience while we worked to fulfill your request.”

I then ran tried to re-activate using “slmgr -ato” on the KMS server – this time it was successful and the KMS clients could once again activate against the KMS host.


August 4 2011

Automate WebDAV configuration for SCCM site systems

This article here – – outlines how to configure IIS WebDAV for SCCM site systems. This is very fiddly manual process so I’ve created a small batch file to automate this. This is for Windows Server 2008 R2 with the correct IIS components already installed:


REM Enable WebDav
%WinDir%System32InetSrvAppCmd.exe set config "Default Web Site/" /section:system.webServer/webdav/authoring /enabled:true /commit:apphost

REM Set authoring rule to allow all users read access
%WinDir%System32InetSrvAppCmd.exe set config "Default Web Site/" /section:system.webServer/webdav/authoringRules /+[users='*',path='*',access='Read'] /commit:apphost

REM Adjust WebDav settings

REM Hidden files
%WinDir%System32InetSrvAppCmd.exe set config "Default Web Site/" /section:system.webServer/webdav/authoring /fileSystem.allowHiddenFiles:true /commit:apphost

REM Anonymous properties
%WinDir%System32InetSrvAppCmd.exe set config "Default Web Site/" /section:system.webServer/webdav/authoring /properties.allowAnonymousPropfind:true /commit:apphost

REM Custom properties
%WinDir%System32InetSrvAppCmd.exe set config "Default Web Site/" /section:system.webServer/webdav/authoring /properties.allowCustomProperties:false /commit:apphost

REM Infinite depth
%WinDir%System32InetSrvAppCmd.exe set config "Default Web Site/" /section:system.webServer/webdav/authoring /properties.allowInfinitePropfindDepth:true /commit:apphost

August 1 2011

SCCM unattended OSD with bootable USB drive

Here’s a neat trick to created an unattended USB boot disk for deploying SCCM OSD task sequences. The machine that you are going to use for this process needs to have the SCCM console loaded on it. The machine also needs to be a Windows Vista, Windows 7 or Windows Server 2008 or higher Operating System, this process will not work on Windows XP or Windows Server 2003. Also log onto the machine with an account that has administrative privileges.

  1. Attach the USB Flash Drive to a Windows Vista, 7 or Server 2008 machine (won’t work with XP or 2003)
  2. 2. Open an administrative command prompt window (cmd.exe) and start DISKPART
  3. At the DISKPART> prompt type ‘List disk’
  4. Determine which disk number corresponds to the USB flash drive
  5. At the DISKPART> prompt, type ‘Select disk x’ where x is the disk number that the USB flash drive corresponds to
  6. At the DISKPART> prompt, type ‘Clean’
  7. At the DISKPART> prompt, type ‘List Partition’. If there is a partition, at the DISKPART> prompt, type ‘Select Partition 1’ then type ‘Clean’ or even try ‘Clean all’ if the partitions won’t go away
  8. At the DISKPART> prompt, type ‘Create Partition Primary’. If you receive an error at this stage regarding not being able to create a partition, the USB Flash Drive is not capable of being made bootable and will not work as an SCCM 2007 bootable Task Sequence Media. Please restart the process using a different USB Flash Drive.
  9. At the DISKPART> prompt, type ‘Select Partition 1’
  10. At the DISKPART> prompt, type ‘Format FS=FAT32 QUICK’
  11. At the DISKPART> prompt, type ‘Active’
  12. At the DISKPART> prompt, type ‘Assign’
  13. At the DISKPART> prompt, type ‘Exit’
  14. From the SCCM console, right-click on the Task Sequence and select Create Task Sequence Media. Follow the prompts to create a USB boot disk.
  15. Once created, on the USB drive, navigate to SMSDataTSMBOOTSTRAP.INI
  16. Open TSMBOOTSTRAP.INI and change line Unattended=false to Unattended=true
  17. Save the changes to TSMBOOTSTRAP.INI

You can now boot your machine onto the USB drive (BIOS support required of course) and as long as you have a mandatory advertisement for a OS task sequence and the required computer association in SCCM, you should be fine! Generally much faster the PXE or CD / DVD booting!

If you need to make a few and know that USB is disk 1, you could use a diskpart script (diskpart.txt in this case) something like this (check before you use it!):

Select disk 1
Create Partition Primary
Select Partition 1

And then just run diskpart /s diskpart.txt