→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
QWERTY2022
Engaged Sweeper
I would like to know if I can Filter out one of my domains 'XXXDEV' to not show on my WIN Server Report
or by an IP range. I wish we did just not scan this group but we do and would not like these systems to show in the report.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblOperatingsystem.Caption As OS,
tblAssets.Version,
Case
When (tblOperatingsystem.Caption Like '%2000%' Or
tblOperatingsystem.Caption Like '%2003%' Or
tblOperatingsystem.Caption Like '%2008%') Then 'EOL'
When tblAssets.Version In ('1709', '1803', '1903') Then 'EOL'
When GetDate() >= subquery1.[EOL Date] And tblOperatingsystem.Caption Like
'%2012%' Then 'EOL'
When GetDate() >= subquery1.[EOL Date] And (tblAssets.Version = '1607' Or
tblOperatingsystem.Caption Like '%2016%') Then 'EOL'
When GetDate() >= subquery1.[EOL Date] And (tblAssets.Version = '1809' Or
tblOperatingsystem.Caption Like '%2019%') Then 'EOL'
When GetDate() >= subquery1.[EOL Date] And tblAssets.Version = '1909' Then
'EOL'
When GetDate() >= subquery1.[EOL Date] And tblAssets.Version = '2004' Then
'EOL'
When GetDate() >= subquery1.[EOL Date] And tblAssets.Version = '2009' Then
'EOL'
When GetDate() >= subquery1.[EOL Date] And tblAssets.Version = '20H2' Then
'EOL'
When GetDate() >= subquery1.[EOL Date] And tblAssets.Version = '21H2' Then
'EOL'
When GetDate() < subquery1.[EOL Date] And tblOperatingsystem.Caption Like
'%2012%' Then Cast(DateDiff(DAY, GetDate(), subquery1.[EOL Date]) As
nvarchar) + ' days remaining'
When GetDate() < subquery1.[EOL Date] And (tblAssets.Version = '1607' Or
tblOperatingsystem.Caption Like '%2016%') Then Cast(DateDiff(DAY,
GetDate(), subquery1.[EOL Date]) As nvarchar) + ' days remaining'
When GetDate() < subquery1.[EOL Date] And (tblAssets.Version = '1809' Or
tblOperatingsystem.Caption Like '%2019%') Then Cast(DateDiff(DAY,
GetDate(), subquery1.[EOL Date]) As nvarchar) + ' days remaining'
When GetDate() < subquery1.[EOL Date] And tblAssets.Version = '1909' Then
Cast(DateDiff(DAY, GetDate(), subquery1.[EOL Date]) As nvarchar) +
' days remaining'
When GetDate() < subquery1.[EOL Date] And tblAssets.Version = '2004' Then
Cast(DateDiff(DAY, GetDate(), subquery1.[EOL Date]) As nvarchar) +
' days remaining'
When GetDate() < subquery1.[EOL Date] And tblAssets.Version = '2009' Then
Cast(DateDiff(DAY, GetDate(), subquery1.[EOL Date]) As nvarchar) +
' days remaining'
When GetDate() < subquery1.[EOL Date] And tblAssets.Version = '20H2' Then
Cast(DateDiff(DAY, GetDate(), subquery1.[EOL Date]) As nvarchar) +
' days remaining'
When GetDate() < subquery1.[EOL Date] And tblAssets.Version = '21H2' Then
Cast(DateDiff(DAY, GetDate(), subquery1.[EOL Date]) As nvarchar) +
' days remaining'
Else 'Supported'
End As Status,
subquery1.[EOL Date],
tblAssets.Lastseen,
tblAssets.Lasttried,
Case
When GetDate() >= subquery1.[EOL Date] Then '#ffadad'
When GetDate() >= subquery1.[Mainstream Support End Date] Then '#ffd152'
When GetDate() >= DateAdd(month, -12, subquery1.[EOL Date]) Then '#ffd152'
Else '#d4f4be'
End As backgroundcolor
From tblAssets
Left Join (Select tblOperatingsystem.AssetID,
Case
When tblOperatingsystem.Caption Like '%2000%' Then '2010-07-13'
When tblOperatingsystem.Caption Like '%2003%' Then '2015-07-14'
When tblOperatingsystem.Caption Like '%2008%' Then '2020-01-14'
When tblOperatingsystem.Caption Like '%2012%' Then '2023-10-10'
When tblassets.Version = '1607' Or
tblOperatingsystem.Caption Like '%2016%' Then '2027-01-12'
When tblassets.Version = '1809' Or
tblOperatingsystem.Caption Like '%2019%' Then '2029-01-09'
When tblassets.Version = '1903' Then '2020-12-08'
When tblassets.Version = '1909' Then '2021-05-11'
When tblassets.Version = '2004' Then '2021-12-14'
When tblassets.Version = '2009' Then '2022-05-10'
When tblassets.Version = '21H2' Then '2031-10-14'
End As [EOL Date],
Case
When tblassets.Version = '1607' Then '2022-01-11'
When tblassets.Version = '1809' Then '2024-01-09'
When tblassets.Version = '21H2' Then '2026-10-13'
End As [Mainstream Support End Date]
From tblOperatingsystem
Inner Join tblassets On tblassets.AssetID = tblOperatingsystem.AssetID) As
subquery1 On subquery1.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblOperatingsystem On tblOperatingsystem.AssetID =
tblAssets.AssetID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblComputersystem On tblComputersystem.AssetID = tblAssets.AssetID
Where tblState.Statename = 'Active' And tblComputersystem.Domainrole > 1
Order By tblAssets.Domain,
tblAssets.AssetName
2 REPLIES 2
QWERTY2022
Engaged Sweeper
Thank you!
rom
Champion Sweeper III
should be


WHERE tblassets.domain <> 'XXXDEV'