Linux Asset Network Interface Count

[tcilmo]

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

[tcilmo]

Awesome - thanks for the assist. I appreciate it.

[Esben_D]

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