→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here
‎01-15-2019 09:53 AM
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
‎01-21-2019 03:57 PM
‎01-15-2019 05:54 PM
‎01-16-2019 08:32 AM
Rob B wrote:
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.
Where tblComputersystem.Domainrole < 2 And tblAssetCustom.State = 1
Where tsysOS.OSname != 'Win 10' And tblComputersystem.Domainrole < 2 And tblAssetCustom.State = 1
‎01-24-2019 05:31 PM
philmcdonnell wrote:Rob B wrote:
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:Where tblComputersystem.Domainrole < 2 And tblAssetCustom.State = 1
- to -Where tsysOS.OSname != 'Win 10' And tblComputersystem.Domainrole < 2 And tblAssetCustom.State = 1
Hope this helps.
Regards,
Phil
‎01-17-2019 07:59 PM
philmcdonnell wrote:Rob B wrote:
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:Where tblComputersystem.Domainrole < 2 And tblAssetCustom.State = 1
- to -Where tsysOS.OSname != 'Win 10' And tblComputersystem.Domainrole < 2 And tblAssetCustom.State = 1
Hope this helps.
Regards,
Phil
‎01-17-2019 09:14 PM
Martiny J wrote:philmcdonnell wrote:Rob B wrote:
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:Where tblComputersystem.Domainrole < 2 And tblAssetCustom.State = 1
- to -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.
Where tsysOS.OSname != 'Win 10' And tsysOS.OSname != 'Win 8.1' And tblComputersystem.Domainrole < 2 And tblAssetCustom.State = 1
And also do you have one for Windows Server 2008 R2.
‎01-15-2019 04:45 PM
‎01-16-2019 02:41 PM
Socal_s197 wrote:
Is there an easy way to add the EOL timer on one of the dashboards?
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now