Changelog:
05/03/2018:
  • Added support for Windows 10 April 2018 Update (1803)
  • You can download a ready to use KACE package here.
02/05/2018:
  • Updated to fit the new release policy published by Microsoft
    • All fields are now checking if its an Enterprise or Education release (+6 Month Support)
    • Added the newest End of Support Dates for 1709
  • Added a Common Name Field to Display the Name of the release.
10/18/2017: 
  • Modified Query to have to EOL sections:
    • Calculated EOL Date based on MS 18 month support policy in the Semi-Anual-Channel. 
    • Fixed MS EOL Date
  • Added Windows 10 1709 into the Query.

Note: This query will not work with the Long-Term Servicing Channel

Hi Guys, 

ever wanted to quickly get an overview on which managed Windows 10 device still has support? 
Note: this is not of interest for companies who are running the LTSB Version of Windows 10!
Based on that article i wrote an easy to extend report for you: Windows 10 lifecycle
If you need to know the current build numbers have a look here: Windows 10 Builds

The query will find all machines in the inventory of the current org with operating system name starts with "Microsoft Windows 10".
It will then use the build number to calculate different thinks like a readable OS Version everybody knows (like 1511, 1607 or 1703). 
Also it will check if we gave the build build number an EOL date. If not the query will use the Microsoft default of 18 month (starting with the release date) for a windows 10 build.


Here is the query:

SELECT MACHINE.NAME AS 'Machine',
       MACHINE.OS_NAME AS 'OS Name',
       MACHINE.OS_BUILD AS 'OS Build',
       CASE MACHINE.OS_BUILD
          WHEN '10240' THEN '1507 (RTM)'
          WHEN '10586' THEN '1511'
          WHEN '14393' THEN '1607'
          WHEN '15063' THEN '1703'
          WHEN '16299' THEN '1709'
          WHEN '17134' THEN '1803'
          ELSE 'Unknown OS Build'
       END
          AS 'OS Version',
      CASE MACHINE.OS_BUILD
          WHEN '10240' THEN 'RTM Version'
          WHEN '10586' THEN 'November Update'
          WHEN '14393' THEN 'Anniversary Update'
          WHEN '15063' THEN 'Creators Update'
          WHEN '16299' THEN 'Fall Creators Update'
          WHEN '17134' THEN 'April 2018 Update'
          ELSE 'Unknown OS Build'
       END
          AS 'OS Common Name',
      CASE MACHINE.OS_BUILD
          WHEN '10240' THEN #1507
            Date_Add(Date('2015-07-29'),INTERVAL 18 MONTH)
          WHEN '10586' THEN #1511
            (CASE WHEN (MACHINE.OS_NAME LIKE '%Enterprise%' OR MACHINE.OS_NAME LIKE '%Education%') THEN Date_Add(Date('2015-11-10'),INTERVAL 24 MONTH)
            ELSE Date_Add(Date('2015-11-10'),INTERVAL 18 MONTH)
            END)
          WHEN '14393' THEN #1607
            (CASE WHEN (MACHINE.OS_NAME LIKE '%Enterprise%' OR MACHINE.OS_NAME LIKE '%Education%') THEN Date_Add(Date('2016-08-02'),INTERVAL 24 MONTH)
            ELSE Date_Add(Date('2016-08-02'),INTERVAL 18 MONTH)
            END)
          WHEN '15063' THEN #1703
            (CASE WHEN (MACHINE.OS_NAME LIKE '%Enterprise%' OR MACHINE.OS_NAME LIKE '%Education%') THEN Date_Add(Date('2017-04-05'),INTERVAL 24 MONTH)
            ELSE Date_Add(Date('2017-04-05'),INTERVAL 18 MONTH)
            END)
          WHEN '16299' THEN #1709
            (CASE WHEN (MACHINE.OS_NAME LIKE '%Enterprise%' OR MACHINE.OS_NAME LIKE '%Education%') THEN Date_Add(Date('2017-10-17'),INTERVAL 24 MONTH)
            ELSE Date_Add(Date('2017-10-17'),INTERVAL 18 MONTH)
            END)
          WHEN '17134' THEN #1803
           Date_Add(Date('2018-04-30'),INTERVAL 18 MONTH)
          ELSE 'NO EoS DATE'
      END
          AS 'Calculated EoS Date',
      CASE MACHINE.OS_BUILD
          WHEN '10240' THEN #1507
            Date('2017-05-09')
          WHEN '10586' THEN #1511
            (CASE WHEN (MACHINE.OS_NAME LIKE '%Enterprise%' OR MACHINE.OS_NAME LIKE '%Education%') THEN Date('2018-04-10')
            ELSE Date('2017-10-10')
            END)
          WHEN '14393' THEN #1607
            (CASE WHEN (MACHINE.OS_NAME LIKE '%Enterprise%' OR MACHINE.OS_NAME LIKE '%Education%') THEN Date('2018-10-09')
            ELSE Date('2018-04-10')
            END)
          WHEN '15063' THEN #1703
            (CASE WHEN (MACHINE.OS_NAME LIKE '%Enterprise%' OR MACHINE.OS_NAME LIKE '%Education%') THEN Date('2019-04-09')
            ELSE Date('2018-10-09')
            END)
          WHEN '16299' THEN #1709
            (CASE WHEN (MACHINE.OS_NAME LIKE '%Enterprise%' OR MACHINE.OS_NAME LIKE '%Education%') THEN Date('2019-10-08')
            ELSE Date('2019-04-09')
            END)
          WHEN '17134' THEN #1803
            Date('2019-11-12')
          ELSE 'NO MS EoS DATE'
      END
          AS 'MS EoS Date',
      CASE MACHINE.OS_BUILD
          WHEN '10240' THEN #1507
            DATEDIFF(DATE('2017-05-09'), NOW())
          WHEN '10586' THEN #1511
            (CASE WHEN (MACHINE.OS_NAME LIKE '%Enterprise%' OR MACHINE.OS_NAME LIKE '%Education%') THEN DATEDIFF(DATE('2018-04-10'), NOW())
            ELSE DATEDIFF(DATE('2017-10-10'), NOW())
            END)
          WHEN '14393' THEN #1607
            (CASE WHEN (MACHINE.OS_NAME LIKE '%Enterprise%' OR MACHINE.OS_NAME LIKE '%Education%') THEN DATEDIFF(DATE('2018-10-09'), NOW())
            ELSE DATEDIFF(DATE('2018-04-10'), NOW())
            END)
          WHEN '15063' THEN #1703
            (CASE WHEN (MACHINE.OS_NAME LIKE '%Enterprise%' OR MACHINE.OS_NAME LIKE '%Education%') THEN DATEDIFF(DATE('2019-04-09'), NOW())
            ELSE DATEDIFF(DATE('2018-10-09'), NOW())
            END)
          WHEN '16299' THEN #1709
            (CASE WHEN (MACHINE.OS_NAME LIKE '%Enterprise%' OR MACHINE.OS_NAME LIKE '%Education%') THEN DATEDIFF(DATE('2019-10-08'), NOW())
            ELSE DATEDIFF(DATE('2019-04-09'), NOW())
            END)
          WHEN '17134' THEN #1803
            DATEDIFF(DATE('2019-11-12'), NOW())
          ELSE 'NO FIX EoS DATE'
       END
          AS 'Days till EoS'
  FROM MACHINE MACHINE
 WHERE MACHINE.OS_NAME LIKE 'Microsoft Windows 10%'
 ORDER BY MACHINE.OS_BUILD ASC

You can copy it to a custom report in SMA:
Update: I recommend to use "OS Common Name" as linebreak.


And you will get this beautiful report:


Cheers 
Timo