One CM SQL View to Rule them All

Profile picture for user PaulW
Posted by PaulW on Sat, 09/12/2020 - 12:03am

A few months ago, I learned of a View in the CM database that is basically every column that you can add in the devices node.  Too many times someone wants a report that just lists exactly what they see in the Devices node.  So, this view gives you that without having to do any of those wonderful SQL JOINs we always have so much fun with.

This week in the MMS Tips and Tricks, I decided to show this view, along with using SSMS to filter views.  So lets take a look at the view.

So, all of these columns:
Console Device Node

are contained in this SQL View:

v_CombinedDeviceResources

 

In this view, there are currently 122 columns.  Most of these correspond to columns in the Devices node in the console.  Many are useful, many are not.  These ones I have found to be the most useful.

SQL View Column Console Column Additional Notes
MachineID Resource ID Important internal CM internal ID for joining to other tables to get additional data.
Name Name Computer Name
SiteCode Site Code  
Domain Domain Computer Domain
ClientVersion Client Version The version of the install client.
ClientCertType Client Certificate Type of certificate used for client communications.  (PKI, self-signed)
LastClientCheckTime Client Check Result Change  
ClientCheckPass Client Check Result  
ADSiteName Active Directory Site  
ADLastLogonTime Last Logon Timestamp From Active Directory Computer Account
ClientActiveStatus Client Activity  
LastPolicyRequest Policy Request  
LastDDR Heartbeat DDR  
LastHardwareScan Hardware Scan  
LastMPServerName Management Point Last management point that it connected to for policy, etc.
CP_Status Last Status Last Status from CM Client Push
CP_LatestProcessingAttempt Last Request Attempt Last Date that a client push was performed
CP_LastInstallationError Last Installation Error Client Push installation error, if any
DeviceOS Operating System  
DeviceOSBuild Operating System Build  
EP_*** Endpoint Protection *** Anything EP_ are endpoint protection related fields
CNIsOnline Device Online Status If the device is currently connected to a MP via the fast channel
CNLastOnlineTime Last Online Time Last time it came online via the fast channel
CNLastOfflineTime Last Offline Time Last time it went offline via the fast channel
CNAccessMP Device Online Management Point The Management Point that the device is currently online with via the fast channel
CNIsOnInternet Device Online From Internet If the computer is currently online via the internet for the fast channel
ClientState Pending Restart

This is a bitwise value that dictates the reasons for a reboot.
0 = No reboot Pending
1 = Configuration Manager
2 = File Rename
4 = Windows Update
8 = Add or Remove Feature

These can be combined as well.

PrimaryUser Primary User(s) Comma separated list of primary users on a computer listed as domain/Username.  So, not perfect for reporting, but good enough for many instances.
CurrentLogonUser Currently Logged on User Current logged on user, provided through the fast channel
LastLogonUser Recently Logged on User  
MACAddress MAC Address  
CoManaged Co-managed  
BoundaryGroups Boundary Group(s) Comma separated list of Boundary Groups computer is part of.

 

Filtering Views

And as far as filtering views in SSMS goes, this is easiest to show with a short video.

Filtering Views in SSMS

Related Technology

Add new comment

The content of this field is kept private and will not be shown publicly.
CAPTCHA
Verify you are a human.