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!

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

  1. 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
  2. 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

Leave a Reply