In this post,we will see how to get heartbeat discovery (DDR) time for the clients from specific collection using Reports.For troubleshooting client health issues ,we need to know when was the client last reported its DDR .
If you need to create collection to list the clients that are not sending heartbeat DDR for past X days ,please refer http://eskonr.com/2011/11/sccm-collection-based-on-heartbeat-agent/
Report:
SELECT Sys.Netbios_Name0 AS Name,
case when Sys.Client0='1' then 'Yes' else 'No' end as 'Client Installed?',
ad.AgentName,
Min(ad.AgentTime) as 'Time Stamp',
os.caption0 [OS]
FROM v_r_system Sys INNER JOIN
v_FullCollectionMembership fcm ON fcm.ResourceID =Sys.ResourceID
inner join v_AgentDiscoveries ad ON ad.ResourceId=Sys.ResourceID
inner join v_GS_OPERATING_SYSTEM OS on os.resourceid=sys.resourceid
WHERE (fcm.CollectionID = 'PS1002AE') and ad.AgentName like 'Heartbeat Discovery'
group by Netbios_Name0,Client0,AgentTime,os.Caption0,ad.AgentName
order by Netbios_Name0 desc
You need to replace the collection ID .
7 Comments
Great thanks !
Hi !
Look like it does not work. It return this:
Column 'v_R_System.Netbios_Name0' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
it is updated now with group by clause.
Hi !
Thanks for the script, but it has one problem. If the computer have two heartbeat-dates the script returns both. It should only return the last one. It looks like meny computers is not sending heartbeat to the server.
guNNar
Hi Gunnar,
you can try with max(A.AgentTime) as 'Time Stamp'
Great Query Eswar, Its nice to find someone out there that has such knowledge
I am trying to do a report "computers in a specific state for a deployment, the query is large so i added CollectionID from v_FullCollectionMembership table
can you look at my query to see if I got it, or would you do something different,
select AssignmentName,sn.StateName as DeploymentStateName
from (select * from v_CIAssignment where Assignment_UniqueID=@DEPLOYMENTID) ass
cross join (select * from v_StateNames where StateID=@STATUS%10000 and TopicType=@STATUS / 10000) sn
if isNumeric(@STATUS)> 0
if cast(@STATUS as int) < 3020000
select m.Name0 as ComputerName0,
m.User_Name0 as LastLoggedOnUser,
asite.SMS_Assigned_Sites0 as AssignedSite,
m.Client_Version0 as ClientVersion,
DATEADD(ss,@__timezoneoffset,s.StateTime) as DeploymentStateTime,
(s.LastStatusMessageID&0x0000FFFF) as ErrorStatusID,
sn.StateName as Status,
@DEPLOYMENTID as DeploymentID,
@collectionID as CollectionID-- ADDED BY DAVE---
from v_CIAssignment a
join v_AssignmentState_Combined s
on s.AssignmentID=a.AssignmentID and s.StateType in (300,301)
left outer join v_StateNames sn
on sn.StateID=isnull(s.StateID,0) and sn.TopicType=s.StateType
join v_R_System m
on m.ResourceType=5 and m.ResourceID=s.ResourceID and isnull(m.Obsolete0,0)1
JOIN v_FullCollectionMembership AS P1 ON P1.ResourceID = m.ResourceID ------ADDED BY DAVE--
left join v_RA_System_SMSAssignedSites asite
on m.ResourceID = asite.ResourceID
where (@DEPLOYMENTID = '' or a.Assignment_UniqueID = @DEPLOYMENTID)
and ((sn.TopicType=@STATUS / 10000) and (sn.StateID = @STATUS%10000))
order by m.Name0
else
select m.Name0 as ComputerName0,
m.User_Name0 as LastLoggedOnUser,
asite.SMS_Assigned_Sites0 as AssignedSite,
m.Client_Version0 as ClientVersion,
DATEADD(ss,@__timezoneoffset,s.StateTime) as DeploymentStateTime,
(s.LastStatusMessageID&0x0000FFFF) as ErrorStatusID,
sn.StateName as Status,
@DEPLOYMENTID as DeploymentID,
@collectionID as CollectionID --ADDED BY DAVE---
from v_CIAssignment a
join v_AssignmentStatePerTopic s
on s.AssignmentID=a.AssignmentID and s.TopicType=302
left outer join v_StateNames sn
on sn.StateID=isnull(s.StateID,0) and sn.TopicType=s.TopicType
join v_R_System m
on m.ResourceType=5 and m.ResourceID=s.ResourceID and isnull(m.Obsolete0,0)1
JOIN v_FullCollectionMembership AS P1 ON P1.ResourceID = m.ResourceID ------ADDED BY DAVE--
left join v_RA_System_SMSAssignedSites asite
on m.ResourceID = asite.ResourceID
where (@DEPLOYMENTID = '' or a.Assignment_UniqueID = @DEPLOYMENTID)
and ((sn.TopicType=@STATUS / 10000) and (sn.StateID = @STATUS%10000))
order by m.Name0
else
select 'Deployment Status ID'='Error: Deployment Status ID must be an integer value'
-
dont think i can go with other query but i will findout if i can get better one than this soon!