Adding IP locations to "Out of Warranty" report

[MJC_NHS_NECSU]

Would appreciate some help on adding IP location to the query below. I'll freely admit that I've no clue about reporting, as our resident SQL/report monkey appears to be missing in action right now.... All help gratefully accepted and I'd give whoever helps me some virtual biscuits if possible!

Select Top 1000000 tblAssetCustom.AssetID,
tblAssets.AssetUnique,
tsysOS.OSname,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Expiration],
tsysOS.Image As icon
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssetCustom.Warrantydate < GetDate() And tblComputersystem.Domainrole <
2 And tblAssetCustom.State = 1
Order By tblAssetCustom.Warrantydate Desc

[kcovingt · Accepted solution]

I'm not from lansweeper but I was able to get this working on my box so try this code and see if that works for you.

Select Top 1000000 tblAssetCustom.AssetID,
tblAssets.AssetUnique,
tsysIPLocations.IPLocation,
tsysOS.OSname,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tsysOS.Image As icon,
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Expiration]
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Where tblAssetCustom.Warrantydate < GetDate() And tblAssetCustom.State = 1 And
tblComputersystem.Domainrole < 2
Order By tsysIPLocations.IPLocation