Windows 7 devices ESU Licensed SCCM Report

Author Nawaz and Mamata

Greetings,

Organizations around the globe have either got Machines migrated from windows 7 to windows 10 or they have purchased Windows 7 extended Licenses.

The article has been updated in references to Obtaining Extended Security Updates for eligible Windows devices 

Licenses have been applied using VAMT and KMS and frankly speaking i am unaware of how good they can report about applied licenses.

I thought to get some SCCM SQL query which can be used further to differentiate between Licensed and Non-Licensed Machines.

Twitter community plays the best role when it comes to sharing knowledge. The Idea of this blog is originated from the tweet by Paul Merrilees where he has mentioned how can we use WQL query to get windows 7  Extended license status.

I have brought up SQL query which will help you to seize data directly from the  SCCM SQL database.

Hardware Inventory Class to be enabled: Software Licensing Product

Table referenced : v_GS_SOFTWARE_LICENSING_PRODUCT

SQL Query For windows 7 :

select
vs.Name0 as HostName
,vs.AD_Site_Name0 as ‘ADSite’
,os.Caption0 as ‘Operating System’
,Lp.Name0 as ‘Licensed Name’
,lp.LicenseStatus0,lp.ID0
,CASE
When lp.LicenseStatus0 = 0 and lp.name0 like ‘Windows(R) 7, Client-ESU-Year%’ then ‘ESU-Year1 Licesnsed but hardware Inventory Pending’ else ‘Licensed’ end as ‘License status’
from v_GS_SOFTWARE_LICENSING_PRODUCT LP
join v_R_System vs on vs.ResourceID = lp.ResourceID
Join v_GS_OPERATING_SYSTEM OS on OS.ResourceID = vs.resourceid
WHERE LP.ID0=’77db037b-95c3-48d7-a3ab-a9c6d41093e0′

IDO needs to be changed from table below as required.

Its been observed once licensed is applied License name like “Windows(R) 7, Client-ESU-Year1 add-on for Enterprise,EnterpriseE,EnterpriseN,Professional,ProfessionalE,ProfessionalN,Ultimate,UltimateE,UltimateN” is added from WMI to SQL database but not the license status as “1” and partialProdukey as “DPCMB”(this may vary). On another Hardware inventory refresh this details are added in to database and query shows details as well.

ESU Program ESU SKU (or Activation) ID
Windows 7 SP1 (Client)
Year 1 77db037b-95c3-48d7-a3ab-a9c6d41093e0
Year 2 0e00c25d-8795-4fb7-9572-3803d91b6880
Year 3 4220f546-f522-46df-8202-4d07afd26454

SQL Query for Windows 2008 Operating Sytem :

select
vs.Name0 as HostName
,vs.AD_Site_Name0 as ‘ADSite’
,os.Caption0 as ‘Operating System’
,Lp.Name0 as ‘Licensed Name’
,lp.LicenseStatus0,lp.ID0
,CASE
When lp.LicenseStatus0 = 0 and lp.name0 like ‘%Server-ESU-Year%’ then ‘ESU-Year1 Licesnsed but hardware Inventory Pending’ else ‘Licensed’ end as ‘License status’
from v_GS_SOFTWARE_LICENSING_PRODUCT LP
join v_R_System vs on vs.ResourceID = lp.ResourceID
Join v_GS_OPERATING_SYSTEM OS on OS.ResourceID = vs.resourceid
WHERE LP.ID0=’553673ed-6ddf-419c-a153-b760283472fd’

IDO needs to be changed from table below as required.

ESU Program ESU SKU (or Activation) ID
Windows Server 2008 R2 SP1 and Windows Server 2008 (Server)
Year 1 553673ed-6ddf-419c-a153-b760283472fd
Year 2 04fa0286-fa74-401e-bbe9-fbfbb158010d
Year 3 16c08c85-0c8b-4009-9b2b-f1f7319e45f9

Happy Sharing.

2 thoughts on “Windows 7 devices ESU Licensed SCCM Report”

  1. Shailendra Kumar

    how same SQL query can be used to check ESU license status of Windows server 2008 R2 SP1 Server OS? is there any changes to SQL query ? if yes can you please share query for server 2008 R2.

Leave a Comment

Your email address will not be published. Required fields are marked *