cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
tcilmo
Engaged Sweeper II
Is it possible to build a report that shows all scanned Linux servers with a count if nic each Linux asset has? I see that the names if the interfaces are in the database for each server. Would it be possible to count them somehow? I have a report started, but it lists each interface individual and the count per asset. I only what the total count of interface names per asset. Anyone know how to accomplish this? Here is my code so far.

Select Top 1000000 tblAssets.AssetID,
tblAssetCustom.Model,
tblAssetCustom.Manufacturer,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblLinuxSystem.OSRelease,
tblAssets.Memory,
tblAssets.NrProcessors,
tblLinuxHardDisks.Size,
tblLinuxNetworkDetection.Name, count (tblLinuxNetworkDetection.Name) as count
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Inner Join tblLinuxHardDisks On tblAssets.AssetID = tblLinuxHardDisks.AssetID
Inner Join tblLinuxNetworkDetection On tblAssets.AssetID =
tblLinuxNetworkDetection.AssetID
Where tsysAssetTypes.AssetTypename <> 'router'
Group By tblAssets.AssetID,
tblAssetCustom.Model,
tblAssetCustom.Manufacturer,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10,
tblLinuxSystem.OSRelease,
tblAssets.Memory,
tblAssets.NrProcessors,
tblLinuxHardDisks.Size,
tblLinuxNetworkDetection.Name
2 REPLIES 2
tcilmo
Engaged Sweeper II
Awesome - thanks for the assist. I appreciate it.
Esben_D
Lansweeper Employee
Lansweeper Employee
Since you want to see the count in a column you'll have to use a subquery to do the count.

You were on the right track with the tables, I just replaced your count line with a subquery that counts the amount of Network IDs per asset.

Select Top 1000000 tblAssets.AssetID,
tblAssetCustom.Model,
tblAssetCustom.Manufacturer,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblLinuxSystem.OSRelease,
tblAssets.Memory,
tblAssets.NrProcessors,
tblLinuxHardDisks.Size,
NetworkCount.NIC
From tblAssets
Left Join (Select Top 1000000 tblAssets.AssetID,
Count(tblLinuxNetworkDetection.NetworkID) As NIC
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType =
tblAssets.Assettype
Inner Join tblLinuxNetworkDetection On tblAssets.AssetID =
tblLinuxNetworkDetection.AssetID
Group By tblAssets.AssetID) As NetworkCount On NetworkCount.AssetID =
tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Inner Join tblLinuxHardDisks On tblAssets.AssetID = tblLinuxHardDisks.AssetID

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now