Close Menu
    Facebook X (Twitter) Instagram
    Monday, May 12
    X (Twitter) LinkedIn
    All about Endpoint Management
    • Home
    All about Endpoint Management
    Home»SCCM 2007»sccm report computers with heartbeat time stamp

    sccm report computers with heartbeat time stamp

    Eswar KonetiBy Eswar KonetiNovember 09, 2:01 pm1 Min Read SCCM 2007 15,267 Views
    Share
    Facebook Twitter LinkedIn Reddit

    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 .

    Computers Configmgr 2007 configmgr report Heartbeat discovery heartbeat time stamp Microsoft SCCM report SCCM SCCM 2007 sccm report computers with heartbeat time stamp SCCM Reports SQL System center configuration Manager
    Share. Twitter LinkedIn Email Facebook Reddit

    Related Posts

    Optimize Your Intune Workflow with a Powerful Browser Extension

    March 22, 10:39 am

    Migrate Microsoft 365 Updates from SCCM/MECM to Intune for Co-Managed Devices

    February 11, 9:50 pm

    Investigating Co-Management Issues with Windows Endpoints in SCCM/Intune

    October 26, 10:45 pm

    7 Comments

    1. benedict_uk@yahoo.com on March 3, 2022 7:47 AM

      Great thanks !

      Reply
    2. Gunnar Andre on July 11, 2012 6:45 PM

      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
      • Eswar Koneti on July 12, 2012 6:28 AM

        it is updated now with group by clause.

        Reply
    3. Gunnar Andre on July 11, 2012 1:45 PM

      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
      • Eswar Koneti on July 11, 2012 3:56 PM

        Hi Gunnar,
        you can try with max(A.AgentTime) as 'Time Stamp'

        Reply
    4. Dave Todd on January 5, 2012 1:38 AM

      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
      • Eswar Koneti on January 11, 2012 1:28 PM

        dont think i can go with other query but i will findout if i can get better one than this soon!

        Reply

    Leave a ReplyCancel reply

    This site uses Akismet to reduce spam. Learn how your comment data is processed.

    Sign Up

    Get email notifications for new posts.

    Author

    I’m Eswar Koneti ,a tech enthusiast, security advocate, and your guide to Microsoft Intune and Modern Device Management. My goal? To turn complex tech into actionable insights for a streamlined management experience. Let’s navigate this journey together!

    Support

    Awards

    Archives

    © Copyright 2009-2024 Eswar Koneti, All rights reserved.

    Type above and press Enter to search. Press Esc to cancel.