Find Windows 10 systems and versions

This SQL script returns the Windows 10 systems including Username, last logon, Activity, Last login, OS version, branch and Code Name.

[SQL Code]
select 
 Coalesce(User_domain0            ,'') as 'User Domain',
 Coalesce(v_r_system.user_name0   ,'') as 'User',
 Coalesce(Unique_User_name0       ,'') as 'Logon Name',
 Coalesce(V_R_User.Full_User_Name0,'') as 'Full Name', 
 Coalesce(last_Logon_timestamp0   ,'') as 'Last logon',
v_GS_COMPUTER_SYSTEM.Name0 as 'System Name', 

case 
	when LastEvaluationHealthy = 1 then 'Pass'
	when LastEvaluationHealthy = 2 then 'Fail'
	when LastEvaluationHealthy = 3 then 'No Results'
end as 'Client check result',

Case
	when ClientActiveStatus = 0 then 'Inactive'
	when ClientActiveStatus = 1 then 'Active'
else 'Unknown'
end as 'Client Activity' ,

V_R_SYSTEM.Operating_System_Name_and0 as 'Operating System Name',

Case
	when V_GS_OPERATING_SYSTEM.BuildNumber0 = 10240 then '1507'
	when V_GS_OPERATING_SYSTEM.BuildNumber0 = 10586 then '1511'
	when V_GS_OPERATING_SYSTEM.BuildNumber0 = 14393 then '1607'
	when V_GS_OPERATING_SYSTEM.BuildNumber0 = 15063 then '1703'
	when V_GS_OPERATING_SYSTEM.BuildNumber0 = 16299 then '1709'
else 'Unknown' end as 'Version',

Case
	when V_GS_OPERATING_SYSTEM.BuildNumber0 = 10240 then 'Threshold 1'
	when V_GS_OPERATING_SYSTEM.BuildNumber0 = 10586 then 'Threshold 2'
	when V_GS_OPERATING_SYSTEM.BuildNumber0 = 14393 then 'November - Threshold 2'
	when V_GS_OPERATING_SYSTEM.BuildNumber0 = 15063 then 'Anniversary - Redstone 1'
	when V_GS_OPERATING_SYSTEM.BuildNumber0 = 16299 then 'Creators – Redstone 2'
	--when V_GS_OPERATING_SYSTEM.BuildNumber0 = 10240 then 'Fall Creators Update - Redstone 3'
else 'Unknown' end as 'Code name',
 
case 
	when V_R_SYSTEM.OSBranch01 = -1 then '(WIPB) Pre-release'
	when V_R_SYSTEM.OSBranch01 = 0 then '(CB) Release-Ready'
	when V_R_SYSTEM.OSBranch01 = 1 then '(CBB) Business-Ready'
	when V_R_SYSTEM.OSBranch01 = 2 then '(LTSB) Mission critical'
Else 'Unknown' end as 'OS Branch', 
V_GS_OPERATING_SYSTEM.BuildNumber0 as 'Build Number',
Client_version0 as 'Client Version'

from  V_R_SYSTEM 
inner join V_GS_OPERATING_SYSTEM on V_GS_OPERATING_SYSTEM.ResourceID = V_R_SYSTEM.ResourceId 
inner join v_CH_ClientSummary on v_CH_ClientSummary.ResourceID = V_R_SYSTEM.ResourceId 
inner join v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID = V_R_SYSTEM.ResourceId 

left outer join V_GS_SYSTEM_CONSOLE_USAGE on V_GS_SYSTEM_CONSOLE_USAGE.ResourceId = V_R_System.ResourceId
left outer JOIN V_R_User ON V_GS_SYSTEM_CONSOLE_USAGE.TopConsoleUser0 = V_R_User.Unique_User_Name0


where V_GS_OPERATING_SYSTEM.Name0 like '%windows 10%'

order by v_GS_COMPUTER_SYSTEM.Name0


Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s