SQL Query to report SCCM client version and install status (extended)

I needed to extend the SQL query to report SCCM client version and install status that I wrote on July 22 to include systems where the client was Null. This is what I came up with:


select distinct SYS.Name0 as 'Machine Name', ISNULL(SYS.User_Name0, 'None') as 'Login ID', ISNULL(SYS.User_Domain0, 'None') as 'Domain', ISNULL(USR.Full_User_Name0, 'None') as 'Full Name', case when SYS.Client0 = 1 then 'Yes' when SYS.Client0 = 0 then 'No (or Inactive)' when SYS.Client0 is null then 'None' else convert(varchar(2), SYS.Client0) end as 'Client Installed', ISNULL(SYS.Client_Version0, 'None') as 'Client Version' from v_R_System SYS join v_FullCollectionMembership FCM on SYS.ResourceID = FCM.ResourceID join v_Collection CN on FCM.CollectionID = CN.CollectionID left outer join v_R_User USR on SYS.User_Name0 = USR.User_Name0 where CN.Name = 'All Systems' and ( SYS.Client0 = 0 or SYS.Client0 is null )

This is useful for tracking down those stale AD records.