Filter report for Server 2003 and 2008 not seen in the last 180 Days

[Daithi77]

Hi Folks, I'm new to Lansweeper (Lansweeper has been running in this company that I'm currently working in for a while) and I'm trying to build a report that hasn't seen assets with OS 2003 and 2008 for 180 days. I can gather all the assets that haven't been seen but don't know how to filter them by Windows 2003 and 2008. Below is the code I'm using. Any help would be appreciated. 

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysAssetTypes.AssetTypename As AssetType,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
Coalesce(tsysOS.OSname, tblMacOSInfo.SystemVersion, tblLinuxSystem.OSRelease)
As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Where tblAssets.Lastseen < GetDate() - 180 And tblState.Statename = 'Active'
Order By tblAssets.IPNumeric,
tblAssets.Domain,
tblAssets.AssetName

[Daithi77]

Thanks Hendrik_VE..... That seems to have done the trick hopefully.

[Hendrik_VE · Accepted solution]

Hi, 

Please try this report:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysAssetTypes.AssetTypename As AssetType,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssets.Lastseen,
  tsysOS.Image As icon,
  tsysOS.OSname
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
Where tblAssets.Lastseen < GetDate() - 180 And (tsysOS.OSname Like '%2003%' Or
    tsysOS.OSname Like '%2008%') And tblAssetCustom.State = 1
Order By tblAssets.AssetName