→ 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: 
MagnusKnight
Engaged Sweeper II
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.
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
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

View solution in original post

1 REPLY 1
Daniel_B
Lansweeper Alumni
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