cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
MikeInLa
Champion Sweeper
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.
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
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

View solution in original post

7 REPLIES 7
GMFDE
Engaged Sweeper III
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
Champion Sweeper
Thank you, that is exactly what I was looking for.
Hemoco
Lansweeper Alumni
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
Champion Sweeper
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
Champion Sweeper
GMFDE,
Thanks for your reply, but your code gives me errors. I am using the report builder in Lansweeper.
GMFDE
Engaged Sweeper III
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
Engaged Sweeper III
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.