Notification

Icon
Error

Windows 7 EOL Report

Posted: Tuesday, January 15, 2019 9:53:40 AM(UTC)
Esben.D

Esben.D

Member Administration Original PosterPosts: 1,738
7
Like
With Windows 7 EOL coming up in 2020. I've created a report which provides an overview of all workstations and if they are closing on EOL.

Assets that are nearing their EOL (EOL within 1 year) are marked orange. Assets past their EOL are marked red. All other assets are marked green.
For Windows 7 (SP1) assets, 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 workstation
Code:
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 = 'Win XP' Then '#ffadad'
    When tsysOS.OSname = 'Win 7' And tblAssets.SP <> 1 Then '#ffdb8e'
    When tsysOS.OSname = 'Win 7' And tblAssets.SP = 1 Then '#ffbe38'
    When GetDate() > '01/14/2019' And GetDate() < '01/14/2020' And
      tsysOS.OSname = 'Win 7' And tblAssets.SP = 1 Then '#ff8b00'
    When GetDate() > '01/14/2020' And tsysOS.OSname = 'Win 7' And
      tblAssets.SP = 1 Then '#ffadad'
    Else '#d4f4be'
  End As backgroundcolor,
  Case
    When tsysOS.OSname = 'Win 7' And tblAssets.SP = 1 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 < 2 And tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName
Socal_s197
#1Socal_s197 Member Posts: 1  
posted: 1/15/2019 4:45:37 PM(UTC)
Is there an easy way to add the EOL timer on one of the dashboards?
Rob B
#2Rob B Member Posts: 10  
posted: 1/15/2019 5:54:07 PM(UTC)
Thank you. Can we limit it to just client versions prior to Win10? Presently the report shows every client O/S, so the report number in parenthesis is not a true indication of how many systems are affected.
philmcdonnell
#3philmcdonnell Member Posts: 1  
posted: 1/16/2019 8:32:19 AM(UTC)
Originally Posted by: Rob B Go to Quoted Post
Thank you. Can we limit it to just client versions prior to Win10? Presently the report shows every client O/S, so the report number in parenthesis is not a true indication of how many systems are affected.


Hello,

This is my very first post reply here so please don't shoot me if it doesn't work for you. The testing I did on my end seems to work fine, please let me know if it works for you.

Looking at the query it is simple to eliminate all Win 10 out of the list just by changing line 39

from:

Code:
Where tblComputersystem.Domainrole < 2 And tblAssetCustom.State = 1


- to -

Code:
Where tsysOS.OSname != 'Win 10' And tblComputersystem.Domainrole < 2 And tblAssetCustom.State = 1


Hope this helps.

Regards,
Phil

Esben.D
#4Esben.D Member Administration Original PosterPosts: 1,738  
posted: 1/16/2019 2:41:43 PM(UTC)
What Phil suggested above should indeed work if you want to limit it to everything but Windows 10.

Originally Posted by: Socal_s197 Go to Quoted Post
Is there an easy way to add the EOL timer on one of the dashboards?


The easiest way would be to just use the iframe widget to iframe a countdown timer from somewhere on your dashboard which counts down to 14 January 2020.

Did you have something else in mind?
Martiny J
#5Martiny J Member Posts: 1  
posted: 1/17/2019 7:59:48 PM(UTC)
Originally Posted by: philmcdonnell Go to Quoted Post
Originally Posted by: Rob B Go to Quoted Post
Thank you. Can we limit it to just client versions prior to Win10? Presently the report shows every client O/S, so the report number in parenthesis is not a true indication of how many systems are affected.


Hello,

This is my very first post reply here so please don't shoot me if it doesn't work for you. The testing I did on my end seems to work fine, please let me know if it works for you.

Looking at the query it is simple to eliminate all Win 10 out of the list just by changing line 39

from:

Code:
Where tblComputersystem.Domainrole < 2 And tblAssetCustom.State = 1


- to -

Code:
Where tsysOS.OSname != 'Win 10' And tblComputersystem.Domainrole < 2 And tblAssetCustom.State = 1


Hope this helps.

Regards,
Phil




It's still showing Windows 8 machines in the report. And also do you have one for Windows Server 2008 R2.
KevinA-REJIS
#6KevinA-REJIS Member Posts: 17  
posted: 1/17/2019 9:14:20 PM(UTC)
Originally Posted by: Martiny J Go to Quoted Post
Originally Posted by: philmcdonnell Go to Quoted Post
Originally Posted by: Rob B Go to Quoted Post
Thank you. Can we limit it to just client versions prior to Win10? Presently the report shows every client O/S, so the report number in parenthesis is not a true indication of how many systems are affected.


Hello,

This is my very first post reply here so please don't shoot me if it doesn't work for you. The testing I did on my end seems to work fine, please let me know if it works for you.

Looking at the query it is simple to eliminate all Win 10 out of the list just by changing line 39

from:

Code:
Where tblComputersystem.Domainrole < 2 And tblAssetCustom.State = 1


- to -

Code:
Where tsysOS.OSname != 'Win 10' And tblComputersystem.Domainrole < 2 And tblAssetCustom.State = 1


Hope this helps.

Regards,
Phil




It's still showing Windows 8 machines in the report.


Try this:

Code:
Where tsysOS.OSname != 'Win 10' And tsysOS.OSname != 'Win 8.1' And tblComputersystem.Domainrole < 2 And tblAssetCustom.State = 1


Quote:
And also do you have one for Windows Server 2008 R2.


+1 for a 2008/2008 R2 report. I tried adding them to this report but can't get them to show up.

Esben.D
#7Esben.D Member Administration Original PosterPosts: 1,738  
posted: 1/21/2019 3:57:26 PM(UTC)
I've posted a 2008/2008R2 report here: https://www.lansweeper.c...r-2008--2008-R2-EOL.aspx
Rob B
#8Rob B Member Posts: 10  
posted: 1/24/2019 5:31:09 PM(UTC)
Thank you, Phil. That worked.

Originally Posted by: philmcdonnell Go to Quoted Post
Originally Posted by: Rob B Go to Quoted Post
Thank you. Can we limit it to just client versions prior to Win10? Presently the report shows every client O/S, so the report number in parenthesis is not a true indication of how many systems are affected.


Hello,

This is my very first post reply here so please don't shoot me if it doesn't work for you. The testing I did on my end seems to work fine, please let me know if it works for you.

Looking at the query it is simple to eliminate all Win 10 out of the list just by changing line 39

from:

Code:
Where tblComputersystem.Domainrole < 2 And tblAssetCustom.State = 1


- to -

Code:
Where tsysOS.OSname != 'Win 10' And tblComputersystem.Domainrole < 2 And tblAssetCustom.State = 1


Hope this helps.

Regards,
Phil



Active Discussions

Lansweeper non-active computer report
by  Apaulcolypse   Go to last post Go to first unread
Last post: Yesterday at 5:16:21 PM(UTC)
Lansweeper Report for showing CPUs below or above
by  GBA Craig  
Go to last post Go to first unread
Last post: 7/18/2019 3:38:41 PM(UTC)
Lansweeper Hardware list
by  GBA Craig   Go to last post Go to first unread
Last post: 7/18/2019 3:34:43 PM(UTC)
Lansweeper Top 10 users submitting tickets
by  LGuth  
Go to last post Go to first unread
Last post: 7/17/2019 9:29:44 PM(UTC)
Lansweeper Microsoft Patch Tuesday Report - July 2019
by  Noobmode   Go to last post Go to first unread
Last post: 7/17/2019 8:48:08 PM(UTC)
Lansweeper Custom Scanning - file not existing
by  Bruce Garoutte  
Go to last post Go to first unread
Last post: 7/17/2019 8:19:50 PM(UTC)
Lansweeper MouseJack vulnerability affected devices report
by  Viper   Go to last post Go to first unread
Last post: 7/16/2019 5:19:14 PM(UTC)
Lansweeper TPM support
by  Sander Eerdekens  
Go to last post Go to first unread
Last post: 7/16/2019 1:38:17 PM(UTC)