IT Management Suite

 View Only
  • 1.  Report to show release version of Windows 10

    Posted May 11, 2020 10:55 AM
    What table or what do I need to hit on to show if windows 10 is 1803, 1809, 1909, etc?

    ------------------------------
    Aspirus
    ------------------------------


  • 2.  RE: Report to show release version of Windows 10

    Posted May 11, 2020 11:16 AM
    Found a post from 2016 with the answer:

    select
        vc.name Computer
        ,VC.[OS Name]
        ,RelVer.ReleaseVer [Win10 Release Verision]
           ,OS.[Type]
        ,OS.Architecture
        ,OS.[Platform]
        ,OS.[OS Name] OperatingSystem
        ,os.[Service Pack]
        ,os.[OS Edition]
        ,os.TagFlags
    from VComputer vc
    JOIN  Inv_AeX_AC_Identification id ON id._ResourceGuid = vc.guid 
    CROSS APPLY dbo.fnSysMask_GetAllSystemMaskAsStringTable( id.[OS System Mask]) OS   -->> the built in fnSysMask_GetAllSystemMaskAsStringTable contains a number of useful filter that can be used for filters
    CROSS APPLY dbo.fnSysMask_GetWindows10_VersionCodeAsTable( id.[OS System Mask]) RelVer  -->> the built in fnSysMask_GetWindows10_VersionCodeAsTable function contains the Windows 10 Release Number
    where vc.[OS Name] like '%Windows 10%'

    ------------------------------
    Aspirus
    ------------------------------



  • 3.  RE: Report to show release version of Windows 10
    Best Answer

    Posted May 11, 2020 12:11 PM
    Edited by Cody Dirrigle May 15, 2020 09:41 AM

    If you're looking for counts by version, this might save you some work. 

    ;with Windows10FriendlyVersions as (
    	SELECT    
    	CASE 
    
    	WHEN
    		id.[OS Build Number] >= 19041
    		and id.[OS Build Number] <  19042
    	THEN 'Windows 10 Version 2004 (Build 119041) (TBA, Codename 20H1)'
    
    	WHEN
    		id.[OS Build Number] >= 18363
    		and id.[OS Build Number] <  18364
    	THEN 'Windows 10 Version 1909 (Build 18363) (November 2019 Update, Codename 19H2)'
    
    	WHEN
    		id.[OS Build Number] >= 18362
    		and id.[OS Build Number] <  18363
    	THEN 'Windows 10 Version 1903 (Build 18362) (May 2019 Update, Codename 19H1)'
    
    	WHEN id.[OS Build Number] >= 17763
    		and id.[OS Build Number] <  17764
    	THEN 'Windows 10 Version 1809 (Build 17763) (October 2018 Update, Codename Redstone 5)'
    
    	WHEN id.[OS Build Number] >= 17134
    		and id.[OS Build Number] <  17135
    	THEN 'Windows 10 Version 1803 (Build 17134) (April 2018 Update, Codename Redstone 4)'
    
    	WHEN id.[OS Build Number] >= 16299
    		and id.[OS Build Number] <  16300
    	THEN 'Windows 10 Version 1709 (Build 16299) (Fall Creators Update, Codename Redstone 3)'
    
    	WHEN id.[OS Build Number] >= 15063
    		and id.[OS Build Number] <  15064
    	THEN 'Windows 10 Version 1703 (Build 15063) (Creators Update, Codename Redstone 2)'
    
    	WHEN id.[OS Build Number] >= 14393
    		and id.[OS Build Number] <  14394
    	THEN 'Windows 10 Version 1607 (Build 14393) (Anniversary Update, Codename Redstone 1)'
    
    	WHEN 
    		id.[OS Build Number] >= 10240
    		and id.[OS Build Number] <  10241
    	THEN 'Windows 10 Version 1507 (Build 10241) (Initial Release, Codename Threshold 1)'
    
    
    
    
    
    	ELSE ('Windows 10 (Unknown) (Build ') + CONVERT(nvarchar(10), id.[OS Build Number]) + ')'
    	END as [Windows 10 Version and Build]
    
    	,id._ResourceGuid
        FROM  Inv_AeX_AC_Identification AS id
        CROSS APPLY dbo.fnSysMask_GetWindowsOSNumberAsTable(id.[OS System Mask]) tt
        WHERE 
        tt.OsNumber = 31  -- Windows 10
    and id.[OS Build Number] is not null
    )
    
    select fv.[Windows 10 Version and Build], count(*) as [Count] from vComputer i 
    join Windows10FriendlyVersions fv on fv._ResourceGuid = i.guid
    
    
    group by fv.[Windows 10 Version and Build]
    order by fv.[Windows 10 Version and Build] 
    
    
    
    
    
    



     



    ------------------------------
    Ben Barker
    Systems Engineer
    ------------------------------



  • 4.  RE: Report to show release version of Windows 10

    Posted May 11, 2020 12:21 PM
    thank you!

    ------------------------------
    Aspirus
    ------------------------------



  • 5.  RE: Report to show release version of Windows 10

    Posted May 27, 2020 09:03 AM
    Edited by Riggzie May 27, 2020 09:05 AM
    I like the one you found from 2016... cuts the work out of editing every time a version comes out.. 
    this is what i was using... 
    you need to edit it each time a new version comes out but works well..

    Select t0.Name, t2.CreatedDate as 'Altiris Created Date', t0.[Client Date], [OS Name], t0.[System Type],
    Case       
    	when t0.[OS Build Number] in('10240') then '1507' 
    	when t0.[OS Build Number] in('10586') then '1511' 
    	when t0.[OS Build Number] in('14393') then '1607' 
    	when t0.[OS Build Number] in('15063','16170','16184','16188','16226') then '1703' 
    	when t0.[OS Build Number] in('16299','17017') then '1709' 
    	when t0.[OS Build Number] in('17134') then '1803' 
    	when t0.[OS Build Number] in('17763') then '1809' 
    	when t0.[OS Build Number] in('18362') then '1903' 
    	when t0.[OS Build Number] in('18363') then '1909' 
    	when t0.[OS Build Number] in('9600') then 'Not Listed' 
    	Else 'Please see LCS to have fixed' 
    	end as 'OS Build',  t0.[OS Build Number]
    
    from inv_aex_ac_identification t0
    	left join vResource t1 on t0._ResourceGuid = t1.Guid
    	left join vItem t2 on t0._ResourceGuid = t2.Guid
    	left join vFixedAssetResourceStatus t4 on t0._ResourceGuid = t4.Guid
    where t0.[OS Name] like '%10%' and t4.Status = 'Active'
    
    order by t0.Name​


    ------------------------------
    Altiris Administrator - Systems Management Engineer
    Bristol Myers Squibb
    ------------------------------



  • 6.  RE: Report to show release version of Windows 10

    Broadcom Employee
    Posted May 28, 2020 06:42 AM
    As far as I remember, starting from 8.5 RU2 release, default reports like "Computers with Agent installed" & "Computer last information update time" already shows these versions (If there is SMA 8.5 RU2 installed on client PC and sent own basic inventory to NS)


    ------------------------------
    Software QA Engineer 3
    Broadcom Inc.
    ------------------------------



  • 7.  RE: Report to show release version of Windows 10

    Posted Jul 08, 2020 09:42 AM
    Cody,

    I know this question has already been answered, but here is another query that I'm sharing that summarizes the Windows 10 Release Version Installs

    select   
    RelVer.ReleaseVer [Win10 Release Version]
    ,Count(*)[Install Count]
    ,format(  Count(*) * 1.0 / t.Win10Installs,'p') [Install %]
    from VComputer vc
    JOIN  Inv_AeX_AC_Identification id ON id._ResourceGuid = vc.guid 
    CROSS APPLY dbo.fnSysMask_GetWindows10_VersionCodeAsTable( id.[OS System Mask]) RelVer  -->> the built in fnSysMask_GetWindows10_VersionCodeAsTable function contains the Windows 10 Release Number
    CROSS JOIN (
    select COUNT(*) [Win10Installs]  from VComputer vc
    JOIN  Inv_AeX_AC_Identification id ON id._ResourceGuid = vc.guid 
    CROSS APPLY dbo.fnSysMask_GetWindows10_VersionCodeAsTable( id.[OS System Mask]) RelVer 
    where vc.[OS Name] like '%Windows 10%'
    )
    where vc.[OS Name] like '%Windows 10%'
    group by  RelVer.ReleaseVer ,t.Win10Installs