cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
mark_groveunder
Engaged Sweeper
I created a report using the SQL from the Lansweeper site:

https://www.lansweeper.com/report/windows-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?
3 REPLIES 3
RCorbeil
Honored Sweeper II
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
mark_groveunder
Engaged Sweeper
That worked great! Thanks for the help!
Esben_D
Lansweeper Employee
Lansweeper Employee
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:
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