Notification

Icon
Error

Windows 7 EOL - Windows 7 EOL report with UPN

Posted: Thursday, September 19, 2019 2:31:26 AM(UTC)
Mark Groveunder

Mark Groveunder

Member Original PosterPosts: 2
0
Like
I created a report using the SQL from the Lansweeper site:

https://www.lansweeper.c...ows-7-end-of-life-audit/

But, I need the UPN field included in this report. I've tried to add the table tblADusers and the UPN field, but can't get it to work. Any suggestions?
Esben.D
#1Esben.D Member Administration Posts: 1,982  
posted: 9/19/2019 12:22:43 PM(UTC)
I think your on the right track. Most likely you didn't join the AD table with the other correctly, since it isn't done automatically in the report builder.

You'll need to manually connect tbladusers to tblassets on both username and userdomain. Then you can enable the UPN and it should work.

This should be your result:
Code:
Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
   tblADusers.UPN,
  tblAssets.IPAddress,
  tblAssets.Description,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblOperatingsystem.Caption As [Operating System],
  tblAssets.SP As SP,
  Case
    When tsysOS.OSname = 'Win 7' And tblAssets.SP = 1 Then DateDiff(dd,
      GetDate(), '01/14/2020')
  End As DaysRemaining,
  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
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
  Inner Join tblADusers On tblADusers.Username = tblAssets.Username And
    tblADusers.Userdomain = tblAssets.Userdomain
Where tblComputersystem.Domainrole < 2 And tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName
Mark Groveunder
#2Mark Groveunder Member Original PosterPosts: 2  
posted: 9/19/2019 3:50:50 PM(UTC)
That worked great! Thanks for the help!
RC62N
#3RC62N Member Posts: 433  
posted: 9/19/2019 4:42:11 PM(UTC)
Just a heads-up, in case you weren't aware: if you've got a Windows 10 Enterprise Agreement or Enterprise Agreement Subscription, it looks like you get a bonus year of Windows 7 support.

Computerworld article: Microsoft offers free post-2020 Windows 7 support for Win 10 Enterprise subscribers
TechCrunch article: Microsoft will let some Windows 7 customers get free security updates for an extra year

Active Discussions

Lansweeper local admin users of a specific device
by  kdunnett   Go to last post Go to first unread
Last post: 5/25/2020 9:30:26 PM(UTC)
Lansweeper How to get total disk usage of all VM assets
by  Erik.T  
Go to last post Go to first unread
Last post: 5/25/2020 4:49:58 PM(UTC)
Lansweeper Windows Server 2016 & Patch Tuesday May 2020
by  Hendrik.VE   Go to last post Go to first unread
Last post: 5/22/2020 8:20:05 PM(UTC)
Lansweeper Users mapped "shared" printers
by  Andy.S  
Go to last post Go to first unread
Last post: 5/22/2020 4:16:23 PM(UTC)
Lansweeper Report on Assets in a Static Group
by  Andy.S   Go to last post Go to first unread
Last post: 5/22/2020 2:55:03 PM(UTC)
Lansweeper Windows 10 Activation
by  TruSynergy  
Go to last post Go to first unread
Last post: 5/21/2020 7:54:25 PM(UTC)
Lansweeper Filtering Report Based On Active Status
by  CyberCitizen   Go to last post Go to first unread
Last post: 5/21/2020 4:04:33 AM(UTC)
Lansweeper Windows: Unauthorized Administrators (Built-in)
by  Jackie.L  
Go to last post Go to first unread
Last post: 5/20/2020 8:01:17 PM(UTC)