→ 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: 
John_H
Engaged Sweeper
Hi All,

I would like to add IP location to the report below. I found this article explaining how to do so

http://m.lansweeper.com/Forum/yaf_postst10743_Adding-IP-locations-to-a-report.aspx#post40242

However, I CANNOT right click on the link created between fields (no idea why not, I can right click and get the menu options for everything else).

Here is the current report I am using for Microsoft Software and I need some help with the SQL query syntax to add IP Location to the output

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tsysOS.OSname,
tblSerialnumber.Product,
tblSerialnumber.ProductID,
tblSerialnumber.ProductKey,
tsysOS.Image As icon
From tblAssets
Inner Join tblSerialnumber On tblAssets.AssetID = tblSerialnumber.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where (tblSerialnumber.Product Like '%windows%' Or
tblSerialnumber.Product Like '%office%') And tblAssetCustom.State = 1
Order By tblAssets.AssetUnique,
tblSerialnumber.Product
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
You could either try another browser or do it directly in the SQL code. Just change the "Inner Join" to tsysiplocations to "Left Join".

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tsysIPLocations.IPLocation,
tsysOS.OSname,
tblSerialnumber.Product,
tblSerialnumber.ProductID,
tblSerialnumber.ProductKey,
tsysOS.Image As icon
From tblAssets
Inner Join tblSerialnumber On tblAssets.AssetID = tblSerialnumber.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tsysIPLocations
On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
tsysIPLocations.EndIP
Where (tblSerialnumber.Product Like '%windows%' Or
tblSerialnumber.Product Like '%office%') And tblAssetCustom.State = 1
Order By tblAssets.AssetUnique,
tblSerialnumber.Product

View solution in original post

1 REPLY 1
Daniel_B
Lansweeper Alumni
You could either try another browser or do it directly in the SQL code. Just change the "Inner Join" to tsysiplocations to "Left Join".

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tsysIPLocations.IPLocation,
tsysOS.OSname,
tblSerialnumber.Product,
tblSerialnumber.ProductID,
tblSerialnumber.ProductKey,
tsysOS.Image As icon
From tblAssets
Inner Join tblSerialnumber On tblAssets.AssetID = tblSerialnumber.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tsysIPLocations
On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
tsysIPLocations.EndIP
Where (tblSerialnumber.Product Like '%windows%' Or
tblSerialnumber.Product Like '%office%') And tblAssetCustom.State = 1
Order By tblAssets.AssetUnique,
tblSerialnumber.Product