Configuration Manager Current Branch FastChannel Information via SQL Query

A lot of people use the console--but I don't go in there that much.  I'm more of a query SQL kind of person.  Some of the updates lately for Current Branch have been leveraging the "FastChannel" for communications.  If you don't remember, originally the FastChannel was meant for quick-hit communications, primarily around Endpoint protection.  However, over the last several updates, the product team has been adding more communications over the fast channel.  Most of those communications are to make the console experience feel more "real time"--and I get that.  For people who live in the console.  but I don't... so where is that information and how can I use it... using SQL?

Here's a couple things to have in your SQL query backpocket.

If you are Current Branch 1710 or higher, the 1710 clients will communicate back about if they have 1 or more of 4 specific "reboot pending" reasons.  You can see that in console--but as a SQL report, here's a summary query to show you counts of devices and what reboot pending state (and why) they are in:

select cdr.ClientState [Pending Reboot],
Case when (1 & cdr.ClientState) = 1 then 1 else 0 end as [Reason: ConfigMgr],
Case when (2 & cdr.ClientState) = 2 then 1 else 0 end as [Reason: Pending File Rename],
Case when (4 & cdr.ClientState) = 4 then 1 else 0 end as [Reason: Windows Update],
Case when (8 & cdr.ClientState) = 8 then 1 else 0 end as [Reason: Windows Feature],
Count(*) [Count]
from vSMS_CombinedDeviceResources cdr
where CAST(right(left(cdr.ClientVersion,9),4) as INT) >= 8577 and cdr.clientversion > '1'
Group by cdr.ClientState
order by cdr.clientstate

It'll only tell you about clients which are version 8577 or higher (aka, 1710).  If you are absolutely certain all your clients are 1710 or higher, you can remove that section of the "where" clause.
asking for clientversion > 1 is because you "might" have mobile clients reporting to your CM.  You really only want to know about Windows-based clients.  Essentially, those where clauses are so that you can be a little more accurate about pending reboots.  If you have a lot of clients less than version 1710, they can't communicate their clientState via the FastChannel, so you might think "great, these devices don't have a pending reboot"--when what it really means is "these clients aren't able to tell me if they need a pending reboot, because their client version is not capable of telling me that, via this method".

Another piece of information that can come in via the Fast Channel, if you are using Current Branch 1806 or higher, 1806 clients can tell you about a CURRENTLY logged in user.  This differs from what we as SMS/ConfigMgr admins are used to in the past.  We have for years been able to tell "last logged on user" or "most likely primary user"--based on heartbeat, hardware inventory, or asset intelligence data.  But that could be "old news"--depending upon how frequent your heartbeat or inventory runs, it could be hours to days old information.  Current logged on user should be at worst a few minutes old (depending of course upon your size, and complexity)

select s1.netbios_name0 [ComputerName], cdr.CurrentLogonUser [Current Logged on User According to FastChannel]
from vSMS_CombinedDeviceResources cdr
join v_r_system s1 on s1.resourceid=cdr.machineid
oder by s1.netbios_name0