SCCM: Machines that have failed an advertisement

I get tired of sifting through the standard reports that restrict you to a particular deployment, a particular advertisement, a particular collection, a particular state, etc. I often like to see details at a more global level, even if those details run to hundreds or thousands of lines, so I often write my own reports in WQL.

I decided it would be a good idea today if I could see all machines that have failed an advertisement... or more particularly, failed any advertisement. As usual, the standard report requires you to specify an advertisement and a state.

So I spent a half hour or so working out how I could get a report of any machines that had failed any advertisement. I decided it would be a good idea to optionally filter the results using an Advertisement ID in case I wanted to narrow it down. Here's the result:

The Prompt Query for the Variable @AdvertID (defaults to %) is:

begin
if (@__filterwildcard = '')
select AdvertisementID, AdvertisementName, Comment from v_Advertisement order by AdvertisementName
else
select AdvertisementID, AdvertisementName, Comment from v_Advertisement
WHERE AdvertisementID like @__filterwildcard
order by AdvertisementName
end

The main SQL Statement for the report is:

select sys.Netbios_Name0, sys.User_Domain0, sys.User_Name0,site.SMS_Installed_Sites0, Client_Type0, ainfo.AdvertisementName, LastStatusMessageID, LastStatusMessageIDName, DATEADD(ss,@__timezoneoffset,LastStatusTime) as LastStatusTime, stat.AdvertisementID, LastExecutionResult, LastExecutionContext
from v_ClientAdvertisementStatus stat
join v_AdvertisementInfo ainfo on stat.AdvertisementID=ainfo.AdvertisementID
join v_R_System sys on stat.ResourceID=sys.ResourceID
left join v_RA_System_SMSInstalledSites site on stat.ResourceID=site.ResourceID
where stat.LastState='11' /* 11 = failed */
and stat.AdvertisementID LIKE @AdvertID

I hope someone else finds this useful.

Cheers,
Sean

PS: I've extended this concept into a collection, The WQL Query for the collection is:

select sys.ResourceID, sys.ResourceType, sys.Name,
sys.SMSUniqueIdentifier, sys.ResourceDomainORWorkgroup, sys.Client
from SMS_R_System as sys
join SMS_ClientAdvertisementStatus as adstat
on adstat.ResourceID=sys.ResourceID
where adstat.LastState='11'

SCCM: Distribution Points

It seems that if a Distribution Point in the SCCM distribution model exceeds its configured retries, it doesn't appear to run a maintenance task in order to get those packages. The maintenance task essentially checks what packages the server is meant to have against the packages it finds already on the disk. (You can copy the files in there manually and it will be quite happy).

In order to force the Distribution Points to run a maintenance task, I acquired this handy little script from Microsoft and have scheduled it to run nightly against a collection of machines that has the Distribution Point role. Sit back and you can watch the network activity jump at the scheduled time.

' Set required variables.
actionNameToRun = "Peer DP Maintenance Task"

' Create a CPAppletMgr instance.
Set controlPanelAppletManager = CreateObject("CPApplet.CPAppletMgr")

' Get the available ClientActions object.Set availableClientActions = controlPanelAppletManager.GetClientActions()

' Loop through the available client actions. Run matching client action when found.
For Each clientAction In availableClientActions
If clientAction.Name = actionNameToRun Then
clientAction.PerformAction
wscript.echo "Ran: " & clientAction.Name
End If
Next

SCCM: Distribution Error and the Ampersand

I came across an interesting problem in SCCM today. A branch distribution point ( BDP ) wasn't replicating a package and it turns out it was because one of the files in the package had an ampersand ( & ) in the file name.

I was seeing this in the ContentTransferManager.log on the BDP:

Unable to enumerate files in E:\BDPTmpWrkFldr\PDP2B6A.tmp (0x80070003)

I came across a Microsoft Knowledge Base article KB967648 that matches the error:

Consider the following scenario:

• Distribution points and branch distribution points exist in a Microsoft System Center Configuration Manager 2007 Service Pack 1 (SP1) site.
• A package is saved on distribution points and on branch distribution points.
• A file name in this package contains an ampersand (&).
• A property of this package is changed on the distribution points.In this scenario, branch distribution points cannot update this package, and an error is encountered. Additionally, package distribution fails, and the package folder on branch distribution points cannot be accessed because of security permissions issues.

There is a hotfix available from Microsoft that is linked from the KB article.