Troubleshooting advertisement status for clients can take some time. Which systems are active vs. not active. I modified the original SMS Report # 110 Titled “All system resources for a specific advertisement in a specific state” to include the last HINV and the last Hearbeat DDR for the systems. Gives a bit better view of how the advertisement is really doing.
declare @State int
select @State=MessageState
from v_AdvertisementStatusInformation
where MessageStateName=@StateName and MessageState < 100
if @State in (0,1,2,3) /* no status, accepted, rejected, expired */
select sys.Netbios_Name0, sys.User_Domain0, sys.User_Name0, site.SMS_Installed_Sites0, Client_Type0,
LastAcceptanceMessageID, LastAcceptanceMessageIDName,
DATEADD(ss,@__timezoneoffset,LastAcceptanceStatusTime) as LastAcceptanceStatusTime,
AdvertisementID, GSYS.agenttime as ‘Last Heartbeat’, gstatus.lasthwscan as ‘Last HINV’
from v_ClientAdvertisementStatus stat
join v_R_System sys on stat.ResourceID=sys.ResourceID
join v_AgentDiscoveries gsys on sys.ResourceID=gsys.ResourceID
join v_gs_workstation_status gstatus on sys.ResourceID=gstatus.ResourceID
left join v_RA_System_SMSInstalledSites site on stat.ResourceID=site.ResourceID
where stat.LastAcceptanceState=@State and stat.AdvertisementID=@AdvertID and GSYS.agentname = ‘Heartbeat Discovery’
else if @State = 11 /* failed */
select sys.Netbios_Name0, sys.User_Domain0, sys.User_Name0,site.SMS_Installed_Sites0, Client_Type0,
LastStatusMessageID, LastStatusMessageIDName,
DATEADD(ss,@__timezoneoffset,LastStatusTime) as LastStatusTime,
AdvertisementID, LastExecutionResult, LastExecutionContext, GSYS.agenttime as ‘Last Heartbeat’, gstatus.lasthwscan as ‘Last HINV’
from v_ClientAdvertisementStatus stat
join v_R_System sys on stat.ResourceID=sys.ResourceID
join v_AgentDiscoveries gsys on sys.ResourceID=gsys.ResourceID
join v_gs_workstation_status gstatus on sys.ResourceID=gstatus.ResourceID
left join v_RA_System_SMSInstalledSites site on stat.ResourceID=site.ResourceID
where stat.LastState=@State and stat.AdvertisementID=@AdvertID and GSYS.agentname = ‘Heartbeat Discovery’
else if @State in (9,12,13) /* running, reboot pending, suceeded */
select sys.Netbios_Name0, sys.User_Domain0, sys.User_Name0, site.SMS_Installed_Sites0, Client_Type0,
LastStatusMessageID, LastStatusMessageIDName,
DATEADD(ss,@__timezoneoffset,LastStatusTime) as LastStatusTime,
AdvertisementID, LastExecutionContext, GSYS.agenttime as ‘Last Heartbeat’, gstatus.lasthwscan as ‘Last HINV’
from v_ClientAdvertisementStatus stat
join v_R_System sys on stat.ResourceID=sys.ResourceID
join v_AgentDiscoveries gsys on sys.ResourceID=gsys.ResourceID
join v_gs_workstation_status gstatus on sys.ResourceID=gstatus.ResourceID
left join v_RA_System_SMSInstalledSites site on stat.ResourceID=site.ResourceID
where stat.LastState=@State and stat.AdvertisementID=@AdvertID and GSYS.agentname = ‘Heartbeat Discovery’
else
select sys.Netbios_Name0, sys.User_Domain0, sys.User_Name0, site.SMS_Installed_Sites0, Client_Type0,
CASE LastStatusMessageID WHEN 65535 THEN 10002 ELSE LastStatusMessageID END as LastStatusMessageID,
LastStatusMessageIDName,
DATEADD(ss,@__timezoneoffset,LastStatusTime) as LastStatusTime,
AdvertisementID, GSYS.agenttime as ‘Last Heartbeat’, gstatus.lasthwscan as ‘Last HINV’
from v_ClientAdvertisementStatus stat
join v_R_System sys on stat.ResourceID=sys.ResourceID
join v_AgentDiscoveries gsys on sys.ResourceID=gsys.ResourceID
left join v_RA_System_SMSInstalledSites site on stat.ResourceID=site.ResourceID
join v_gs_workstation_status gstatus on sys.ResourceID=gstatus.ResourceID
where stat.LastState=@State and stat.AdvertisementID=@AdvertID and GSYS.agentname = ‘Heartbeat Discovery’
