sccm report computers with heartbeat time stamp

Posted on November 9, 2011 by | 7 Comments | 14,442 Views

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 .

Related Posts

7 Responses to "sccm report computers with heartbeat time stamp"

  2. Gunnar Andre · Edit

    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.

    Reply
  3. Gunnar Andre · Edit

    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

    Reply
  4. Dave Todd · Edit

    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'

    -

    Reply

Post Comment