Since I noticed people were asking about how to get a server EOL report. I adapted the Windows 7 EOL report for servers.
Servers that are nearing their EOL (EOL within 1 year) are marked orange. Assets past their EOL are marked red. All other servers are marked green.
For Windows Server 2008/ 2008R2, the amount of days remaining until EOL is also displayed.
The report will only list assets that meet all of the following criteria:
- The asset state is set to active.
- The asset is a Windows Server
Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblOperatingsystem.Caption As [Operating System],
tblAssets.SP As SP,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
Case
When tsysOS.OSname Like '%2003%' Then '#ffadad'
When GetDate() > '01/14/2019' And GetDate() < '01/14/2020' And
tsysOS.OSname Like '%2008%' Then '#ff8b00'
When GetDate() > '01/14/2020' And tsysOS.OSname Like '%2008%' Then '#ffadad'
Else '#d4f4be'
End As backgroundcolor,
Case
When tsysOS.OSname Like '%2008%' Then DateDiff(dd, GetDate(), '01/14/2020')
End As DaysRemaining
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblComputersystem.Domainrole > 1 And tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName
