ITMS Administrator Group

 View Only
  • 1.  OS Version and OS Build data needed

    Posted Oct 11, 2016 11:42 AM
      |   view attached

    Hello all,

    I've been trying to locate a table that specifically references the Windows Version and OS Build information (see attached).  This is not the OS name and edition; but version and OS Build.

    I've been unsuccessful so far.

    Does anyone know which table/tables has that information?

    Thanks for any assistance.

    -David



  • 2.  RE: OS Version and OS Build data needed
    Best Answer

    Posted Oct 12, 2016 03:37 AM

    Does Inv_AeX_AC_Identification not contain what you need?

     

    SELECT 
    	--*
    	Name
    	,[System Type]
    	,[OS Name]
    	,[OS Type]
    	,[OS Version]
    	,[OS Revision]
    	,[OS Major Version]
    	,[OS Minor Version]
    	,[OS Build Number]
    FROM Inv_AeX_AC_Identification

     



  • 3.  RE: OS Version and OS Build data needed

    Broadcom Partner
    Posted Oct 12, 2016 02:18 PM

    Hi DHeinz57,

    The requested information is not in the database because inventory solution or basic inventory does not collect the exact build number.
    If you want to get the buildnumber you have to create a custom inventory. Hopefully Symantec will extend inventoy or basic inventory to collect the exact build number...

    To create the Custom Inventory create a customdataclass with the follwinging information:custominv_osbuild.png

    After creation of the custom dataclass create a new Task (Powershell) and paste in the following Powershell script:

    #************************DO NOT EDIT********************************
    $nse = new-object -comobject Altiris.AeXNSEvent
    $nse.priority = 1
    $nse.To = "{1592B913-72F3-4C36-91D2-D4EDA21D2F96}"
    #************************DO NOT EDIT********************************
    
    #Modify this varaible with the custom data class guid
    $objDCInstance = $nse.AddDataClass("Windows Version")
    
    $objDataClass = $nse.AddDataBlock($objDCInstance)
    
    $Build = $(Get-ItemProperty -Path 'Registry::HKEY_LOCAL_MACHINE\Software\Microsoft\Windows NT\CurrentVersion' CurrentBuild).CurrentBuild
    $Revision = $(Get-ItemProperty -Path 'Registry::HKEY_LOCAL_MACHINE\Software\Microsoft\Windows NT\CurrentVersion' UBR).UBR
    $WinVer = $Build + "." + $Revision
    
    ####Add new row of data
    $objDataRow = $objDataClass.AddRow()
    
    $objDataRow.SetField(0, $WinVer)
    
    #Send the data
    $nse.sendqueued()
    

    It should look like....
    customInventory.png

    You could create a Report based on the data in the table...

    Network23



  • 4.  RE: OS Version and OS Build data needed

    Posted Oct 13, 2016 11:59 AM

    Thanks guys for the quick response!

    I used AlexHedley's suggestion as the Inv_AeX_AC_Identification.[OS Build Number] was close to what I needed; not the full build number, but enough for me to write a SQL CASE statement in a report similiar to below:

            CASE
                WHEN os.[OS Build Number] LIKE '9600%' THEN 'Possible Win8 or Win8 Upgrade'
                WHEN os.[OS Build Number] LIKE '10240%' THEN 'Initial Release'
                WHEN os.[OS Build Number] LIKE '10586%' THEN '1511'
                WHEN os.[OS Build Number] LIKE '14393%' THEN '1607'
                ELSE 'Not Win10'
                END AS 'OS Version',

    I will look into the custom inventory option to pull the OS version as well as this will be very helpful especially with the "Anniversary Update".  I'm sure this is just the first of many version changing updates.

    -David



  • 5.  RE: OS Version and OS Build data needed

    Posted Nov 03, 2021 09:48 AM
    Edited by WDRAIN1 Nov 03, 2021 09:49 AM

    Here's another version of what's been shared to get the info you are looking for....


    Here's the SQL query with the Case Statement for the Windows 10 Version Number for the output above

    select
    id.[OS Build Number] [OS Build]
    ,CASE
    WHEN [OS Build Number] = 10240 THEN '1507'
    WHEN [OS Build Number] = 10586 THEN '1511'
    WHEN [OS Build Number] = 14393 THEN '1607'
    WHEN [OS Build Number] = 15063 THEN '1703'
    WHEN [OS Build Number] = 16299 THEN '1709'
    WHEN [OS Build Number] in (17134,17133) THEN '1803'
    WHEN [OS Build Number] in (17661,17692,17763) THEN '1809'
    WHEN [OS Build Number] in (18282,18362) THEN '1903'
    WHEN [OS Build Number] in (18363) THEN '1909'
    WHEN [OS Build Number] in (19041) THEN '2004'
    WHEN [OS Build Number] in (19042) THEN '20H2'
    WHEN [OS Build Number] in (19043) THEN '21H1'
    WHEN [OS Build Number] in (20215) THEN '21H2'
    END [Version]
    ,count(*) Installs
    from vcomputer vc
    join Inv_AeX_AC_Identification id on id._resourceguid = vc.guid
    where vc.[OS Name] like '%Windows 10%' and id.[OS Build Number] is not null
    group by id.[OS Build Number]




  • 6.  RE: OS Version and OS Build data needed

    Posted Nov 03, 2021 09:50 AM

    Here's another version of what's been shared to get the info you are looking for....


    Here's the SQL query with the Case Statement for the Windows 10 Version Number for the output above

    select
    id.[OS Build Number] [OS Build]
    ,CASE
    WHEN [OS Build Number] = 10240 THEN '1507'
    WHEN [OS Build Number] = 10586 THEN '1511'
    WHEN [OS Build Number] = 14393 THEN '1607'
    WHEN [OS Build Number] = 15063 THEN '1703'
    WHEN [OS Build Number] = 16299 THEN '1709'
    WHEN [OS Build Number] in (17134,17133) THEN '1803'
    WHEN [OS Build Number] in (17661,17692,17763) THEN '1809'
    WHEN [OS Build Number] in (18282,18362) THEN '1903'
    WHEN [OS Build Number] in (18363) THEN '1909'
    WHEN [OS Build Number] in (19041) THEN '2004'
    WHEN [OS Build Number] in (19042) THEN '20H2'
    WHEN [OS Build Number] in (19043) THEN '21H1'
    WHEN [OS Build Number] in (20215) THEN '21H2'
    END [Version]
    ,count(*) Installs
    from vcomputer vc
    join Inv_AeX_AC_Identification id on id._resourceguid = vc.guid
    where vc.[OS Name] like '%Windows 10%' and id.[OS Build Number] is not null
    group by id.[OS Build Number]