Close Menu
    Facebook X (Twitter) Instagram
    Tuesday, May 20
    X (Twitter) LinkedIn
    All about Endpoint Management
    • Home
    All about Endpoint Management
    Home»configmgr»1602»How to Query Clients collection or SSRS with Online Status in SCCM Configmgr 1602

    How to Query Clients collection or SSRS with Online Status in SCCM Configmgr 1602

    Eswar KonetiBy Eswar KonetiApril 19, 7:38 pm3 Mins Read 1602 22,837 Views
    Share
    Facebook Twitter LinkedIn Reddit

     

    Microsoft has introduced new feature in System Center Configuration manager Build Version 1602  called Client Online Status .This is really cool and exciting feature which is really needed these days to know if the computer is online or offline (of course ,SCCM agent must be working and healthy which is different story).

    A new status for clients is available for monitoring if a computer is online or not. A computer is considered online if it is connected to it's assigned management point. To indicate that the computer is online, the client sends ping-like messages to the management point. If the management point doesn't receive a message after 5 minutes, the client is considered offline.

    How to Monitor the status of individual clients:

    In the Configuration Manager console, click Assets and Compliance > Devices or choose a collection under Device Collections.

    image

    Beginning in version 1602 of Configuration Manager, the icons at the beginning of each row indicate the online status of the device:

    image

     

    For more detailed online status, add the client online status information to the device view by right-clicking the column header and clicking the online status fields you want to add. The columns you can add are

    • Device Online Status indicates whether the client is currently online or offline. (This is the same information given by the icons).
    • Last Online Time indicates when the client online status changed to online.
    • Last Offline Time indicates when the status changed to offline.

    Now ,coming to the subject line , wouldn’t it be nice to create Collection or SSRS Report for client online Status in Configmgr 1602 ?

    If you want to create collection or SSRS Report  for Clients with online ,offline Status in 1602 and later build versions ,you must know the correct SQL views (SSRS) and wmi instance that store this information . Download the SQL views documentation for Configmgr 1602 from http://eskonr.com/2016/04/download-sccm-configmgr-1602-sql-views-documentation/

    collections Uses WQL and Reports uses SQL .

    For Collection , Client online Status stored in wmi namaspace : SMS_CollectionMemberClientBaselineStatus with CNIsOnline =True or False

    For SSRS Report ,Client Online Status stored in view: v_CollectionMemberClientBaselineStatus with CNIsOnline=1 or 0

    Once we know the wmi instance or SQL view,it is easy to create collection or SSRS Report.

    To create collection ,Use the following WQL Code (subselected) for Online Clients:

    select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,
    SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ResourceId in
    (select resourceid from SMS_CollectionMemberClientBaselineStatus where SMS_CollectionMemberClientBaselineStatus.CNIsOnline = 1)

    image

    image

    To Create SSRS Report ,Use the following SQL Code for Online Clients:

    select Name,sitecode,Clientversion,LastHardwareScan,LastMPServerName,CNIsOnline,max(CNLastOfflineTime) CNLastOfflineTime ,
    max(CNLastOnlineTime) CNLastOnlineTime
    from v_CollectionMemberClientBaselineStatus
    where CNIsOnline=1
    group by Name,sitecode,Clientversion,LastHardwareScan,CNIsOnline,LastMPServerName
    order by Name

    image

    Until Next!

    1602 CNIsOnline Collection CollectionMemberClientBaselineStatus configmgr Featured Online Status Query for Online Clients in 1602 SCCM SQL SSRS Report Subselected
    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

    13 Comments

    1. Brett Dalton on October 24, 2019 8:02 AM

      Hi Eswar,

      Of the CNIsOnline values, 0 is offline and 1 is online. What is the value for Online Status is Unknown? These are the machines I need to filter into this collection.

      Cheers,
      Brett

      Reply
      • Brett Dalton on October 24, 2019 8:04 AM

        A value of 'Unknown' doesn't work either.

        Reply
        • Eswar Koneti on October 29, 2019 8:50 PM

          Hi Brett,
          What is unknown here ? what are you trying to achieve ?

          Thanks,
          Eswar

          Reply
          • Brett Dalton on October 30, 2019 8:17 AM

            Hi Eswar,

            I want to create a filter that only includes computers with "Online status is unknown" so I can do a bulk approval for those computers to be managed by my new config manager hierarchy. Auto approval is turned on but not all computers that have now received the agent are being managed by this instance of SCCM so I want to force these manually in the hope it brings more computers under this CM hierarchy.

            Thanks,
            Brett

            Reply
      • Eswar Koneti on July 8, 2020 12:46 AM

        Hi,
        If the device has client installed, then it is either displayed as online or offline.
        If the device does not have SCCM client installed, it will be unknown or something which you can query the list of clients that doesnt have sccm agent.

        Thanks,
        Eswar

        Reply
    2. Indiana Jones on July 25, 2017 11:25 PM

      This is great! Now if only there is a way to wake-up a computer in every subnet to stay online as a ClientCache source.. or a way to automatically find a computer in each subnet that would be the best candidate and change it's power policy..

      Reply
      • Eswar Koneti on July 28, 2017 10:12 PM

        To wakup computer,you can use broadcast or unicast in SCCM Site properties settings.

        Regards,
        Eswar

        Reply
        • Bill Fry on August 30, 2018 10:30 PM

          I could not start a new question as I could not get the login info below to display.

          I have had an issue with the device status and it still does not work. I did not know about the port number and I did open it and rebooted, but all the icons show unknown. Anything else I can check?

          Reply
          • Eswar Koneti on September 2, 2018 12:15 AM

            For clients online status ,it uses Client notification port TCP 10123 .check if that is opened or not. https://docs.microsoft.com/en-us/sccm/core/plan-design/hierarchy/ports

            Regards,
            Eswar

            Reply
    3. Sarika on September 13, 2016 10:34 PM

      Hi ESWAR,
      I have a list of devices in an excel which have nothing in common.
      I have them to be added in a collection.Can u please advise me in an easier way

      Reply
      • Eswar Koneti on September 14, 2016 7:43 PM

        Hi,
        why dont you use nowmicro right click tools which will help you to add list of clients to collection ? download link from http://www.nowmicro.com/software/recast

        Reply
    4. amoreau on August 25, 2016 4:37 PM

      Hi,
      In 1606 SCCM version CNIsOnline values changed to True / False :

      select Name,sitecode,Clientversion,LastHardwareScan,LastMPServerName,CNIsOnline,max(CNLastOfflineTime) CNLastOfflineTime ,
      max(CNLastOnlineTime) CNLastOnlineTime
      from v_CollectionMemberClientBaselineStatus
      where CNIsOnline='True'
      group by Name,sitecode,Clientversion,LastHardwareScan,CNIsOnline,LastMPServerName
      order by Name

      Reply
      • Eswar Koneti on August 25, 2016 5:32 PM

        Great, thanks for the Update. Haven't looked at it in production version of CB 1606.
        I will update the blog post.
        Thanks,
        Eswar

        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.