Asset out of warranty

[MR1371]

I was messing around with the asset out of warranty report and screwed something up. Now I keep getting the error "error while getting report Data conversion failed. [ OLE DB status value (if known) = 2 ]"

Here is my code

Select Top 1000000 tblAssetCustom.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tblAssetCustom.Model,
tblAssetCustom.Manufacturer,
tblAssetCustom.Warrantydate As [Warranty Expiration],
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Username,
tblOperatingsystem.Caption,
tblADusers.Department,
tsysIPLocations.IPLocation,
tblAssets.IPAddress
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
And tsysAssetTypes.AssetTypeIcon48 = tblAssets.Assettype And
tsysAssetTypes.AssetTypeIcon10 = tblAssetCustom.FTPheader
Inner Join tblADusers On tblAssets.Username = tblADusers.Username And
tsysAssetTypes.AssetTypeIcon48 = tblADusers.IpPhone
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Where tblAssetCustom.Warrantydate < GetDate()
Order By [Warranty Expiration] Desc

[Susan_A]

MR1371 wrote:
I was messing around with the asset out of warranty report and screwed something up. Now I keep getting the error "error while getting report Data conversion failed. [ OLE DB status value (if known) = 2 ]"

Your report contains several incorrect Joins. TsysAssetTypes.AssetTypeIcon48 and tblADusers.IpPhone are linked for instance, but these fields have nothing to do with each other. We advise anyone to read up on SQL before attempting to build or modify reports, as the Lansweeper report builder is a standard SQL editor. Understanding Joins and other SQL principles is a must. All built-in SQL reports can be found in our report center.

rhodges wrote:
Unfortunately, I don't know what IPLocations are and how to link the tsysIPLocations to the working tables.

IP locations are groups of IP addresses you can define in the Configuration\Asset Groups section of the console. To add this information to a report, follow the instructions in this forum topic.

[rhodges]

I created a report with the information that I thought you were looking for. Unfortunately, I don't know what IPLocations are and how to link the tsysIPLocations to the working tables. This is what I have so far:

Select Top 1000000 tblAssetCustom.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tblAssetCustom.Model,
tblAssetCustom.Manufacturer,
tblAssetCustom.Warrantydate As [Warranty Expiration],
tblADusers.Username,
tblOperatingsystem.Caption,
tblADusers.Department,
tblAssets.IPAddress,
tblAssetCustom.Serialnumber,
tblAssetCustom.PurchaseDate As [Purchase Date],
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.OScode
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblADusers On tblAssets.Username = tblADusers.Username
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblAssetCustom.Warrantydate < GetDate()
Order By [Warranty Expiration] Desc

[rhodges · Accepted solution]

Here is the original report if you need to have that to start from:

Select Top 1000000 tblAssetCustom.AssetID,
tblAssets.AssetName,
tblAssets.IPAddress,
tsysAssetTypes.AssetTypename,
tblAssetCustom.Model,
tblAssetCustom.Manufacturer,
tblAssetCustom.Serialnumber,
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Expiration],
tsysAssetTypes.AssetTypeIcon10 As icon
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssetCustom.Warrantydate < GetDate() And tblAssetCustom.State = 1
Order By [Warranty Expiration] Desc