cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
FischbachM
Engaged Sweeper II
Hello everyone,
i tried to change the default switch report, that it shows me assets, wich have multiple ports. (e.g. if you change the Lan port on your computer, it shows the new port and the old one in the switches)
At the moment my report shows all Assets conneted to switches, with their amount of connected ports. I stuck at how i can only Show the assets with 2 or more connections.

Thanks in Advance.


Select Top 1000000 tblAssets.AssetName,
Count(tblAssets.AssetName) As Anzahl,
tblAssets.AssetID
From (Select Top 1000000 tblAssets_1.AssetID,
tblAssets_1.AssetName,
tblSNMPInfo.IfIndex As [If],
tblSNMPInfo.Vlan,
tblAssetMacAddress.AssetID As deviceassetid
From tblSNMPInfo
Inner Join tblAssets As tblAssets_1 On tblSNMPInfo.AssetID =
tblAssets_1.AssetID
Inner Join tsysAssetTypes On tblAssets_1.Assettype =
tsysAssetTypes.AssetType
Left Outer Join tblSNMPIfTypes On tblSNMPInfo.IfType =
tblSNMPIfTypes.IfType
Left Outer Join (tblAssetMacAddress
Right Outer Join tblSNMPAssetMac On tblAssetMacAddress.Mac =
tblSNMPAssetMac.AssetMacAddress) On tblSNMPInfo.IfIndex =
tblSNMPAssetMac.IfIndex And tblSNMPInfo.AssetID =
tblSNMPAssetMac.AssetID
Where tblAssets_1.AssetID Is Not Null And tblAssets_1.Assettype = 6
Order By tblAssets_1.AssetName,
[If]) As aaa
Left Outer Join tblAssets On aaa.deviceassetid = tblAssets.AssetID
Left Outer Join tsysAssetTypes As tsysAssetTypes_1 On tblAssets.Assettype =
tsysAssetTypes_1.AssetType
Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
Left Outer Join tsysIPLocations On tblAssets.LocationID =
tsysIPLocations.LocationID
Where tsysAssetTypes_1.AssetTypename = 'windows'
Group By tblAssets.AssetName,
tblAssets.AssetID
1 REPLY 1
FischbachM
Engaged Sweeper II
i figured it out by myself.

I used the Having clause and it works fine for my needs


Select Top 1000000 tblAssets.AssetName,
Count(tblAssets.AssetName) As Anzahl,
tblAssets.AssetID
From (Select Top 1000000 tblAssets_1.AssetID,
tblAssets_1.AssetName,
tblAssetMacAddress.AssetID As deviceassetid
From tblSNMPInfo
Inner Join tblAssets As tblAssets_1 On tblSNMPInfo.AssetID =
tblAssets_1.AssetID
Left Outer Join (tblAssetMacAddress
Right Outer Join tblSNMPAssetMac On tblAssetMacAddress.Mac =
tblSNMPAssetMac.AssetMacAddress) On tblSNMPInfo.IfIndex =
tblSNMPAssetMac.IfIndex And tblSNMPInfo.AssetID =
tblSNMPAssetMac.AssetID
Where tblAssets_1.AssetID Is Not Null And tblAssets_1.Assettype =
6) As aaa
Left Outer Join tblAssets On aaa.deviceassetid = tblAssets.AssetID
Left Outer Join tsysAssetTypes As tsysAssetTypes_1 On tblAssets.Assettype =
tsysAssetTypes_1.AssetType
Where tsysAssetTypes_1.AssetTypename = 'windows'
Group By tblAssets.AssetName,
tblAssets.AssetID
Having Count(tblAssets.AssetName) > 1