SQL Query to report SCCM client version and install status

Put this together to generate a list of our SCCM clients and report on their install version and status:


select distinct SYS.Name0 as 'Machine Name', SYS.User_Name0 as 'Login ID', SYS.User_Domain0 as 'Domain', USR.Full_User_Name0 as 'Full Name', case when SYS.Client0 = 1 then 'Yes' when SYS.Client0 = 0 then 'No' else convert(varchar(2), SYS.Client0) end as 'Client Installed', SYS.Client_Version0 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 join v_R_User USR on SYS.User_Name0 = USR.User_Name0 where CN.Name = 'All Systems'