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:
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 (Netbios Name) |
| SiteCode | Site Code | |
| Domain | Domain | Computer Domain (NT 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 | |
| UserDomainName | User's Domain | (NT Domain) |
| 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 | As reported in Active Directory (Microsoft Windows NT Server 6.3) |
| DeviceOSBuild | Operating System Build | (like 6.3.9600.20174) |
| 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. 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. (DOMAIN\username1) |
| LastLogonUser | Recently Logged on User | (username1) |
| 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.
Add new comment