cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Artur_Mucha
Engaged Sweeper II
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
7 REPLIES 7
Tom_P
Lansweeper Employee
Lansweeper Employee
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
Engaged Sweeper II
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
Champion Sweeper III
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
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,
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
Engaged Sweeper II
This report which you performed gives only information about Windows hosts.
What about others? I'm interested especially in Linux ones.
Nick_VDB
Champion Sweeper III
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