Need Help with Warranty Report

[MagnusKnight]

I am trying to run a report that shows all of my servers, and when their Warranty End Date will be. I have been trying to piece together existing queries, but I have not had any real success.

I'm trying to start with the Asset: Warranty Status report as my base, but I want to restrict it to only Physical Servers.

I found a post here, http://www.lansweeper.com/forum/yaf_postst9064_I-just-need-a-report-to-show-all-server.aspx#post36086 that restricts output to servers, however it only shows Windows servers, not VMware ESXi servers.

I would only like to include the fields from this post, Computer Name, Manufacturer, Model, and only list Physical servers, since virtual ones have no hardware and will not have a warranty. I would like this added to my modified Asset: Warranty Status report.

[Daniel_B · Accepted solution]

Filtering on servers only works for Windows assets, because we can see from tblComputersystem.Domainrole if a computer is a server or not. For Linux and Unix computers there is no secure way to do this. You can use a query like the following example which filters on Windows servers and all other VMware, Linux and Unix assets. Listing only physical machines can be done by not allowing "%virtual%" in the model and serialnumber fields.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssetCustom.Warrantydate,
  tblAssetCustom.PurchaseDate
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where ((tsysAssetTypes.AssetTypename = 'windows' And
      tblComputersystem.Domainrole > 1) Or tsysAssetTypes.AssetTypename In
    ('Linux', 'Unix', 'VMware server')) And tblAssetCustom.State = 1 And
  tblAssetCustom.Model Not Like '%virtual%' And tblAssetCustom.Serialnumber Not
  Like '%virtual%'
Order By tblAssets.AssetName