SCCM Client Status Reporting issue with multiple sites

Agent health is one of our top priorities in every SCCM deployment, we know if agents are good we can achieve a three nines success in software distributions or software updates. This is the reason we developed a combined strategy for agent health, on one side we use the SCCM Client Status Reporting tool, on the other we developed our complementary solution. Once the data from both systems flows in, reporting helps us to keep under control the agents.

Working on this topic we hit a known bug supposed to be fixed in SCCM Service Pack 2 ( When multiple sites are present the Client Status Reporting Tool doesn’t return the correct last DDR time, this means the agent is marked as unhealthy even if it is not.

After applying SP2 we found the issue is still there, so with a little reverse engineering I tracked the guilty stored procedure and modified it so that it would return the latest DDR received for any site the agent is reporting to. ** totally unsupported mod, as always **

ALTER PROCEDURE dbo.CH_SyncClientSummary
   declare @UTCdiff int
   set @UTCdiff = DateDiff(mi, GetDate(), GetUTCDate())
   declare @currentsite varchar(3) 
   select @currentsite = ThisSiteCode from SMSData 
   — Add a new record for every machine if it doesn’t already exist
   Insert into CH_ClientSummary(MachineID)
   Select MachineID
   From MachineIdGroupXRef
   Where not exists (select * from CH_ClientSummary
                     Where CH_ClientSummary.MachineID=MachineIdGroupXRef.MachineID)
   And ArchitectureKey=5
   — Update other data
   Update CH_ClientSummary
   set NetBiosName = ( Select System_DISC.Netbios_Name0 from System_DISC
                       Where CH_ClientSummary.MachineID = System_DISC.ItemKey ),
       SiteCode = ( Select System_SMS_Instal_ARR.SMS_Installed_Sites0 from System_SMS_Instal_ARR
                    Where CH_ClientSummary.MachineID = System_SMS_Instal_ARR.ItemKey ),
       Version = ( Select System_DISC.Client_Version0 from System_DISC
                   Where CH_ClientSummary.MachineID = System_DISC.ItemKey ),
       LastDDR = DateAdd( mi, isnull(-wsd.TimezoneOffset, @UTCdiff), ( Select Max (DiscItemAgents.AgentTime) from DiscItemAgents
                                                                       inner join Agents as ag on DiscItemAgents.AgentID = ag.AgentID
                                                                       Where CH_ClientSummary.MachineID = DiscItemAgents.ItemKey and
                                                                       ag.AgentName = ‘Heartbeat Discovery’)
       LastHW = DateAdd( mi, isnull(-wsd.TimezoneOffset, @UTCdiff), wsd.LastHWScan),
       LastSW = DateAdd( mi, isnull(-wsd.TimezoneOffset, @UTCdiff), ( Select SoftwareInventoryStatus.LastUpdateDate from SoftwareInventoryStatus
                                                    Where CH_ClientSummary.MachineID = SoftwareInventoryStatus.ClientId )
       LastStatusMessage = ( Select max(sm.Time) from StatusMessages as sm
                             inner join StatusMessageAttributes as sma on
                             sma.RecordID = sm.RecordID
                             inner join MachineIdGroupXRef as mgx on
                             sma.AttributeValue = mgx.GUID
                             where sma.AttributeID=408 and
                             CH_ClientSummary.MachineID=mgx.MachineID ),
       Obsolete = (Select System_DISC.Obsolete0 from System_DISC
                   where CH_ClientSummary.MachineID = System_DISC.ItemKey )
   from CH_ClientSummary
   left outer join WorkstationStatus_DATA wsd on CH_ClientSummary.MachineID = wsd.MachineID
   — Update with Client Deployment FSP data
   Update CH_ClientSummary
   set ClientFrameworkHealthy = h.IsHealthy,
       ClientDeployed = (Case When h.det is null then 0
                              Else 1
       ClientAssigned = (Case When h.aet is null then 0
                              Else 1
   FROM CH_ClientSummary cs
   inner join MachineIdGroupXRef mgx on cs.MachineID = mgx.MachineID
   inner join
      Select cds.SMSID,
             MIN(Case when chs.HealthState > 1 then 0
                      else 1 end) as IsHealthy,
             MAX(cds.DeploymentEndTime) as det,
             MAX(cds.AssignmentEndTime) as aet
      From ClientDeploymentState as cds
      left outer join ClientHealthState as chs on
      cds.RecordID = chs.RecordID
      Group By cds.SMSID
   ) as h on mgx.GUID = h.SMSID

– Daniele

This posting is provided "AS IS" with no warranties, and confers no rights.

  1. Leave a comment

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: