cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Daithi77
Engaged Sweeper

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

1 ACCEPTED SOLUTION
Hendrik_VE
Champion Sweeper III

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

View solution in original post

2 REPLIES 2
Daithi77
Engaged Sweeper

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

Hendrik_VE
Champion Sweeper III

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