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'

Sawmill Report Analyser

I have discovered today that Sawmill is a really cool tool for slicing and dicing data to produce useful analytical reporting information. Well worth a look if you want to produce reports from logged data from Proxies, RADIUS servers, Firewalls, Mail Server logs... you name it.

It has a bunch of preconfigured plug-ins for just about everything, will munch just about any log file you throw at it with quite reasonable performance and is very versatile in its configurability. Pretty cheap too!

I've known about the tool for a while, but never had a reall good play with it until today.

Disclaimer: I am in no way affiliated with Sawmill.