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

 

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!

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

  1. 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
        1. 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
    1. 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. 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
      1. 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
  3. 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
  4. 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
    1. 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

Post Comment