Report for computers marked as custom state but scanned recently

[JacobH]

I mark computers as a certain state that aren't meant to be online, such as STOCK, DECOMMISSIONED, RECYCLED, SOLD, etc... but, in the case of STOCK, they are provisioned and Lansweeper doesn't mark them as active, though it does scan them.

So, I've made a report that you can have email you, if a computer comes online that you aren't expecting (i.e. you marked it as X custom state)... and it's where stuff has been seen in the past 3 days... (you can modify that)


You take the below report and modify the

And tblAssetCustom.StateName in ('Stock','Decommissioned')

criteria to whatever state you're looking for.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tblAssets.IPAddress,
  tsysOS.OSname,
  tsysIPLocations.IPLocation,
  tblComputersystem.Lastchanged,
  tsysOS.Image As icon,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssetCustom.PurchaseDate,
  tblAssetCustom.Warrantydate,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber
From tblComputersystem
  Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
    And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Where tblAssets.Lastseen > GetDate() - 3
  And tblAssetCustom.Statename in ('Stock','Decommissioned')
Order By tblAssets.AssetName