Which table to find IPV6 address?

[MikeInLa]

I want to list computers in a report including any IPV6 addresses but I can't figure out which table/entry to add to my report.

[GMFDE]

MikeInLa wrote:
Yeah, I want to setup a report within Lansweeper.

Sorry for the misunderstanding. But, your original request was how to find tables that contain information. If you use the code I provided from within SQL Manager Studio, it helps you find tables.

MikeInLa wrote:
...but I can't figure out which table/entry to add to my report.

[MikeInLa]

Thank you, that is exactly what I was looking for.

[Hemoco · Accepted solution]

Please use the following report:

Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblNetwork.IPAddress,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where ((tblNetwork.IPAddress Is Not Null) Or (tblNetwork.IPAddress <> '')) And
tblAssetCustom.State = 1

[MikeInLa]

Yeah, I want to setup a report within Lansweeper.

Under each asset, under Config>Network, next to the adapter in the "IP Address" column, it shows the IPV4 Address and the IPV6 Address (on machines where IPV6 is enabled). I can create a generic list of all assets (computers) that will show the IPV4 address (easy) but I want to add their IPV6 Address to that list, so that we can easily run it at any time. One of the tables in the report viewer may have exactly what I need, I just can't find it (I've checked all of the tblNetwork and tblAsset. Perhaps there is some command I need to manually add to my script?

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssetCustom.State = 1

[MikeInLa]

GMFDE,
Thanks for your reply, but your code gives me errors. I am using the report builder in Lansweeper.

[GMFDE]

MikeInLa wrote:
GMFDE,
Thanks for your reply, but your code gives me errors. I am using the report builder in Lansweeper.

Yeah, this has to be run from within Microsoft SQL Management Studio. If you have MS SQL deployed, you already have access to MSMS. Or you can download it from MS.

[GMFDE]

This query might help, run this from your SQL manager console...

GO

 SELECT t.name AS table_name, SCHEMA_NAME(schema_id) AS schema_name,c.name AS column_name
 FROM sys.tables AS t
 INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
 WHERE c.name LIKE '%address%' /* CHANGE NAME BETWEEN THE % */
 ORDER BY schema_name, table_name;

make sure you are connected to your lansweeperdb database in sql.

you can put whatever colomn name you want where it it states %address%. We use this query frequently to find specific tables.

Hope this helps.