Quickly find clients not recently online via sms web report

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’

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: