Wednesday, May 27, 2009

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:

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

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.


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'

No comments:

Post a Comment