→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
MJC_NHS_NECSU
Engaged Sweeper
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
1 ACCEPTED SOLUTION
kcovingt
Engaged Sweeper III
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

View solution in original post

1 REPLY 1
kcovingt
Engaged Sweeper III
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