Report with all IPs of every asset

[Artur_Mucha]

Hi,

I need to have report which will give me bellow table with AssetID and all IPv4 addresses detected for that Asset in one column.

So it should look like:

AssetID | IPs
server1 | 10.0.0.1, 10.0.0.2
server2 | 10.0.0.3
server3 | 10.0.0.4, 10.0.0.5, 10.0.0.6

Thanks,
Artur Mucha

[Tom_P]

To be able to group/concat the returned lines into one single line you would have to make use of SQL Server specific commands, custom stored procedures and/or functions. Unfortunately, as these are general SQL statements and not specifically related to Lansweeper, we cannot provide these commands or customized procedures. A quick search online returned the following links that might be useful in setting this up:

• http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string
  
• http://blog.shlomoid.com/2008/11/emulating-mysqls-groupconcat-function.html
  
• https://www.mssqltips.com/sqlservertip/2914/rolling-up-multiple-rows-into-a-single-row-and-column-for-sql-server-data/
  
• http://stackoverflow.com/questions/8005846/sql-server-combining-multiple-rows-into-one-row
  

[Artur_Mucha]

Thank you for your help but that kind of report I was able to prepare on my own (maybe not that good looking as yours).

Is there any possibility to group all IPs of asset into one row? That is the main requirement of this topic.

[Nick_VDB]

Different asset types will use different tables. TblNetwork only stores network information for Windows computers, for Linux machines this is stored in tblLinuxNetworkDetection. We added a modified report but do note that you will get multiple rows for non-Windows devices if they have multiple network cards and IP Addresses.

Select Distinct Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  Coalesce(tblNetwork.IPAddress, tblLinuxNetworkDetection.Ipv4,
  tblMacNetwork.Ipv4, tblAssets.IPAddress) As [All IPAddress'],
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
  Left Join tblLinuxNetworkDetection On tblAssets.AssetID =
    tblLinuxNetworkDetection.AssetID
  Left Join tblMacNetwork On tblAssets.AssetID = tblMacNetwork.AssetID
Where Coalesce(tblNetwork.IPAddress, tblLinuxNetworkDetection.Ipv4) != '' And
  Coalesce(tblNetwork.IPAddress, tblLinuxNetworkDetection.Ipv4,
  tblMacNetwork.Ipv4, tblAssets.IPAddress) Is Not Null And
  tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName

[elg0ch0]

Is there any other table keeping IP addresses information for my assets other than tblNetwork and tblLinuxNetworkDetection?
I'm running my query using a SQL UNION, which should be good enough to get all IPs but I'm missing some assets IPs (some of them for sure Linux boxes, firewalls and very likely some windows too)
Am I missing anything? this my current query:

Select Distinct Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblNetwork.IPAddress As ['All IPAddress'],
  tblAssets.Lastseen
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
Where tblNetwork.IPAddress != '' And tblNetwork.IPAddress Is Not Null And
  tblAssetCustom.State = 1
Union
Select Distinct Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblLinuxNetworkDetection.Ipv4 As ['All IPAddress'],
  tblAssets.Lastseen
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
  Left Join tblLinuxNetworkDetection On
    tblAssets.AssetID = tblLinuxNetworkDetection.AssetID
Where tblLinuxNetworkDetection.Ipv4 Is Not Null And
  tblLinuxNetworkDetection.Ipv4 != '' And
  tblLinuxNetworkDetection.Ipv4 != '127.0.0.1' And
  tblAssetCustom.State = 1

Thanks,

[elg0ch0]

Never mind, I just realized out that I was missing the IP contained in tblAssets (tblAssets.IPAddress)

Regards,

RAA wrote:
Is there any other table keeping IP addresses information for my assets other than tblNetwork and tblLinuxNetworkDetection?
I'm running my query using a SQL UNION, which should be good enough to get all IPs but I'm missing some assets IPs (some of them for sure Linux boxes, firewalls and very likely some windows too)
Am I missing anything? this my current query:

Select Distinct Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblNetwork.IPAddress As ['All IPAddress'],
  tblAssets.Lastseen
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
Where tblNetwork.IPAddress != '' And tblNetwork.IPAddress Is Not Null And
  tblAssetCustom.State = 1
Union
Select Distinct Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblLinuxNetworkDetection.Ipv4 As ['All IPAddress'],
  tblAssets.Lastseen
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
  Left Join tblLinuxNetworkDetection On
    tblAssets.AssetID = tblLinuxNetworkDetection.AssetID
Where tblLinuxNetworkDetection.Ipv4 Is Not Null And
  tblLinuxNetworkDetection.Ipv4 != '' And
  tblLinuxNetworkDetection.Ipv4 != '127.0.0.1' And
  tblAssetCustom.State = 1

Thanks,

[Artur_Mucha]

This report which you performed gives only information about Windows hosts.
What about others? I'm interested especially in Linux ones.

[Nick_VDB]

The information that you are after is stored in tblNetwork, however, the field that stores all the IP Addresses contains both the IPV4 and IPV6 addresses. We created a report giving back all the IP addresses of your assets.

Instructions for adding this report to your Lansweeper installation can be found here. If you are interested in building or modifying reports, we do recommend:

• Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial.
  
• Making use of our database dictionary, which explains in great detail what each database table and field stores. More information on the dictionary can be found here.

Select Distinct Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblNetwork.IPAddress As [All IPAddress'],
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
Where tblNetwork.IPAddress != '' And tblNetwork.IPAddress Is Not Null And
  tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName