cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
marda
Engaged Sweeper II
Any help would be greatly appreciated. I'm trying to figure out how to take the Switch: Ports report and make it show all assets regardless of whether or not a device is connected to a switch. Right now the report only shows assets that are connected to a switch in the "Deviceassetname" column. Ultimately, I'm looking to create a report similar to the all assets page but showing a column with the switch name the asset is connected to and if it hasn't been scanned or can't associate the asset with a switch, just fill it in with a null value. I feel I'm really close, but again only showing assets connected (in the column with the heading "Connected To" to a switch when I need to see all. Here's what I have so far

Select Top 1000000 tblAssets.AssetID,
tsysAssetTypes.AssetTypename As AssetType,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Mac As MACAddress,
tblAssetCustom.Custom4 As [Function],
tblAssetCustom.Custom3 As [Building Number],
tblAssetCustom.Custom1 As [Virtual Asset],
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
Coalesce(tsysOS.OSname, tblLinuxSystem.OSRelease, tblMacOSInfo.SystemVersion,
tblAssets.Description) As OS,
tblAssetCustom.Location,
tblAssetCustom.Custom2 As [Critical System],
tblAssets_1.AssetName As [Connected To],
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
Inner Join tblAssetMacAddress On
tblAssets.AssetID = tblAssetMacAddress.AssetID
Inner Join tblSNMPAssetMac On tblAssetMacAddress.Mac =
tblSNMPAssetMac.AssetMacAddress
Inner Join tblSNMPInfo On tblSNMPAssetMac.IfIndex = tblSNMPInfo.IfIndex And
tblSNMPAssetMac.AssetID = tblSNMPInfo.AssetID
Inner Join tblAssets tblAssets_1 On tblSNMPInfo.AssetID = tblAssets_1.AssetID
Where tblAssets.Assettype <> 66
Order By AssetType
4 REPLIES 4
rader
Champion Sweeper III
Lansweeper put a default one in called "Switch: Ports" that might help.

Here's what I have created as well. I also created one for a pie chart to be displayed next to Switch Ports Query.

1) Switch Ports Query:

Select Top 1000000 Switch.AssetID,
Switch.icon,
Switch.AssetName,
Switch.[If],
Switch.ifName,
Switch.ifAlias,
Switch.IfDescription,
Switch.Type,
Switch.Admin,
Switch.MTU,
Switch.Speed,
Switch.[IP Address],
Switch.Mask,
Switch.Portname,
Switch.Vlan,
Switch.MacAddress,
tsysAssetTypes1.AssetTypeIcon16 As icon2,
Switch.deviceassetid,
tblAssets.AssetName As deviceassetname,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
Switch.AssetMacAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Firstseen,
tblAssets.Lastseen
From (Select Top 1000000 tblAssets1.AssetID,
tsysAssetTypes.AssetTypeIcon16 As icon,
tblAssets1.AssetName,
tblSNMPInfo.IfIndex As [If],
tblSNMPInfo.ifName,
tblSNMPInfo.ifAlias,
tblSNMPInfo.IfDescription,
tblSNMPIfTypes.IfTypename As Type,
Case
When tblSNMPInfo.IfAdminstatus = 1 Then 'Up'
When tblSNMPInfo.IfAdminstatus = 2 Then 'Down'
Else 'Testing'
End As Admin,
tblSNMPInfo.IfMTU As MTU,
Case
When (tblSNMPInfo.IfSpeed Is Null Or tblSNMPInfo.IfSpeed = 0) Then ''
When tblSNMPInfo.IfSpeed > 999999999 Then
Convert(nvarchar(255),Cast(tblSNMPInfo.IfSpeed / 1000000000 As
float)) + 'Gbit'
When tblSNMPInfo.IfSpeed > 999999 Then
Convert(nvarchar(255),Cast(tblSNMPInfo.IfSpeed / 1000000 As float))
+ 'Mbit'
Else Convert(nvarchar(255),Cast(tblSNMPInfo.IfSpeed / 1000 As float))
+ 'Kbit'
End As Speed,
tblSNMPInfo.IfIPAddress As [IP Address],
tblSNMPInfo.IfMask As Mask,
tblSNMPInfo.IfMacaddress As MacAddress,
tblSNMPAssetMac.AssetMacAddress,
tblSNMPInfo.Portname,
tblSNMPInfo.Vlan,
tblAssetMacAddress.AssetID As deviceassetid
From tblSNMPInfo
Left Join tblAssets As tblAssets1 On tblSNMPInfo.AssetID =
tblAssets1.AssetID
Left Join tsysAssetTypes On tblAssets1.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 tsysAssetTypes.AssetTypename = 'Switch'
Order By tblAssets1.AssetName,
[If]) As Switch
Left Outer Join tblAssets On Switch.deviceassetid = tblAssets.AssetID
Left Outer Join tsysAssetTypes As tsysAssetTypes1 On tblAssets.Assettype =
tsysAssetTypes1.AssetType
Left Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
Left Outer Join tsysIPLocations On tblAssets.LocationID =
tsysIPLocations.LocationID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode


2) Switch Connections:

Select Distinct Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename As AssetType,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblSNMPInfo.IfIndex As Interface,
tblSNMPInfo.IfDescription As Name,
tblSNMPIfTypes.IfTypename As Type,
tblSNMPInfo.IfAdminstatus As Admin,
tblSNMPInfo.IfMTU As MTU,
Ceiling(tblSNMPInfo.IfSpeed / 1000 / 1000) As Speed,
tblSNMPInfo.IfIPAddress As IP,
tblSNMPInfo.IfMask As Mask,
tblSNMPInfo.IfMacaddress As MAC,
tblAssets1.AssetName As Asset
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSNMPInfo On tblAssets.AssetID = tblSNMPInfo.AssetID
Inner Join tblSNMPIfTypes On tblSNMPIfTypes.IfType = tblSNMPInfo.IfType
Left Join tblSNMPAssetMac On tblSNMPAssetMac.AssetID = tblSNMPInfo.AssetID And
tblSNMPAssetMac.IfIndex = tblSNMPInfo.IfIndex
Left Join tblAssetMacAddress On tblAssetMacAddress.Mac =
tblSNMPAssetMac.AssetMacAddress
Left Join tblAssets tblAssets1 On
tblAssets1.AssetID = tblAssetMacAddress.AssetID
Where tsysAssetTypes.AssetTypename = 'switch'
Order By tblAssets.AssetName,
Interface


3) Chart: Switch Ports in Use

Select Top 1000000 Case
When tblSNMPInfo.IfOperstatus = 1 Then 'Up'
When tblSNMPInfo.IfOperstatus = 2 Then 'Down'
When tblSNMPInfo.IfOperstatus = 3 Then 'Testing'
When tblSNMPInfo.IfOperstatus = 4 Then 'Unknown'
When tblSNMPInfo.IfOperstatus = 5 Then 'Dormant'
When tblSNMPInfo.IfOperstatus = 6 Then 'NotPresent'
When tblSNMPInfo.IfOperstatus = 7 Then 'LowLayerDown'
Else 'Other'
End As 'Interface Status',
count(*) As 'Number of ports'
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSNMPInfo On tblAssets.AssetID = tblSNMPInfo.AssetID
Inner Join tblSNMPIfTypes On tblSNMPIfTypes.IfType = tblSNMPInfo.IfType
Left Join tblSNMPAssetMac On tblSNMPAssetMac.AssetID = tblSNMPInfo.AssetID And
tblSNMPAssetMac.IfIndex = tblSNMPInfo.IfIndex
Left Join tblAssetMacAddress On tblAssetMacAddress.Mac =
tblSNMPAssetMac.AssetMacAddress
Left Join tblAssets tblAssets1 On
tblAssets1.AssetID = tblAssetMacAddress.AssetID
Left Outer Join tsysAssetTypes As tsysAssetTypes_1 On tblAssets1.Assettype =
tsysAssetTypes_1.AssetType
Where (tsysAssetTypes.AssetTypename = 'switch' Or tsysAssetTypes.AssetTypename =
'router') And tblSNMPIfTypes.IfTypename Like '%Ethernet%'
Group By Case
When tblSNMPInfo.IfOperstatus = 1 Then 'Up'
When tblSNMPInfo.IfOperstatus = 2 Then 'Down'
When tblSNMPInfo.IfOperstatus = 3 Then 'Testing'
When tblSNMPInfo.IfOperstatus = 4 Then 'Unknown'
When tblSNMPInfo.IfOperstatus = 5 Then 'Dormant'
When tblSNMPInfo.IfOperstatus = 6 Then 'NotPresent'
When tblSNMPInfo.IfOperstatus = 7 Then 'LowLayerDown'
Else 'Other'
End


Hopefully you can find what you need in here. Good luck.
Randy
marda
Engaged Sweeper II
rader wrote:
Lansweeper put a default one in called "Switch: Ports" that might help.

Here's what I have created as well. I also created one for a pie chart to be displayed next to Switch Ports Query.

1) Switch Ports Query:

Select Top 1000000 Switch.AssetID,
Switch.icon,
Switch.AssetName,
Switch.[If],
Switch.ifName,
Switch.ifAlias,
Switch.IfDescription,
Switch.Type,
Switch.Admin,
Switch.MTU,
Switch.Speed,
Switch.[IP Address],
Switch.Mask,
Switch.Portname,
Switch.Vlan,
Switch.MacAddress,
tsysAssetTypes1.AssetTypeIcon16 As icon2,
Switch.deviceassetid,
tblAssets.AssetName As deviceassetname,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
Switch.AssetMacAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Firstseen,
tblAssets.Lastseen
From (Select Top 1000000 tblAssets1.AssetID,
tsysAssetTypes.AssetTypeIcon16 As icon,
tblAssets1.AssetName,
tblSNMPInfo.IfIndex As [If],
tblSNMPInfo.ifName,
tblSNMPInfo.ifAlias,
tblSNMPInfo.IfDescription,
tblSNMPIfTypes.IfTypename As Type,
Case
When tblSNMPInfo.IfAdminstatus = 1 Then 'Up'
When tblSNMPInfo.IfAdminstatus = 2 Then 'Down'
Else 'Testing'
End As Admin,
tblSNMPInfo.IfMTU As MTU,
Case
When (tblSNMPInfo.IfSpeed Is Null Or tblSNMPInfo.IfSpeed = 0) Then ''
When tblSNMPInfo.IfSpeed > 999999999 Then
Convert(nvarchar(255),Cast(tblSNMPInfo.IfSpeed / 1000000000 As
float)) + 'Gbit'
When tblSNMPInfo.IfSpeed > 999999 Then
Convert(nvarchar(255),Cast(tblSNMPInfo.IfSpeed / 1000000 As float))
+ 'Mbit'
Else Convert(nvarchar(255),Cast(tblSNMPInfo.IfSpeed / 1000 As float))
+ 'Kbit'
End As Speed,
tblSNMPInfo.IfIPAddress As [IP Address],
tblSNMPInfo.IfMask As Mask,
tblSNMPInfo.IfMacaddress As MacAddress,
tblSNMPAssetMac.AssetMacAddress,
tblSNMPInfo.Portname,
tblSNMPInfo.Vlan,
tblAssetMacAddress.AssetID As deviceassetid
From tblSNMPInfo
Left Join tblAssets As tblAssets1 On tblSNMPInfo.AssetID =
tblAssets1.AssetID
Left Join tsysAssetTypes On tblAssets1.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 tsysAssetTypes.AssetTypename = 'Switch'
Order By tblAssets1.AssetName,
[If]) As Switch
Left Outer Join tblAssets On Switch.deviceassetid = tblAssets.AssetID
Left Outer Join tsysAssetTypes As tsysAssetTypes1 On tblAssets.Assettype =
tsysAssetTypes1.AssetType
Left Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
Left Outer Join tsysIPLocations On tblAssets.LocationID =
tsysIPLocations.LocationID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode


2) Switch Connections:

Select Distinct Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename As AssetType,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblSNMPInfo.IfIndex As Interface,
tblSNMPInfo.IfDescription As Name,
tblSNMPIfTypes.IfTypename As Type,
tblSNMPInfo.IfAdminstatus As Admin,
tblSNMPInfo.IfMTU As MTU,
Ceiling(tblSNMPInfo.IfSpeed / 1000 / 1000) As Speed,
tblSNMPInfo.IfIPAddress As IP,
tblSNMPInfo.IfMask As Mask,
tblSNMPInfo.IfMacaddress As MAC,
tblAssets1.AssetName As Asset
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSNMPInfo On tblAssets.AssetID = tblSNMPInfo.AssetID
Inner Join tblSNMPIfTypes On tblSNMPIfTypes.IfType = tblSNMPInfo.IfType
Left Join tblSNMPAssetMac On tblSNMPAssetMac.AssetID = tblSNMPInfo.AssetID And
tblSNMPAssetMac.IfIndex = tblSNMPInfo.IfIndex
Left Join tblAssetMacAddress On tblAssetMacAddress.Mac =
tblSNMPAssetMac.AssetMacAddress
Left Join tblAssets tblAssets1 On
tblAssets1.AssetID = tblAssetMacAddress.AssetID
Where tsysAssetTypes.AssetTypename = 'switch'
Order By tblAssets.AssetName,
Interface


3) Chart: Switch Ports in Use

Select Top 1000000 Case
When tblSNMPInfo.IfOperstatus = 1 Then 'Up'
When tblSNMPInfo.IfOperstatus = 2 Then 'Down'
When tblSNMPInfo.IfOperstatus = 3 Then 'Testing'
When tblSNMPInfo.IfOperstatus = 4 Then 'Unknown'
When tblSNMPInfo.IfOperstatus = 5 Then 'Dormant'
When tblSNMPInfo.IfOperstatus = 6 Then 'NotPresent'
When tblSNMPInfo.IfOperstatus = 7 Then 'LowLayerDown'
Else 'Other'
End As 'Interface Status',
count(*) As 'Number of ports'
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSNMPInfo On tblAssets.AssetID = tblSNMPInfo.AssetID
Inner Join tblSNMPIfTypes On tblSNMPIfTypes.IfType = tblSNMPInfo.IfType
Left Join tblSNMPAssetMac On tblSNMPAssetMac.AssetID = tblSNMPInfo.AssetID And
tblSNMPAssetMac.IfIndex = tblSNMPInfo.IfIndex
Left Join tblAssetMacAddress On tblAssetMacAddress.Mac =
tblSNMPAssetMac.AssetMacAddress
Left Join tblAssets tblAssets1 On
tblAssets1.AssetID = tblAssetMacAddress.AssetID
Left Outer Join tsysAssetTypes As tsysAssetTypes_1 On tblAssets1.Assettype =
tsysAssetTypes_1.AssetType
Where (tsysAssetTypes.AssetTypename = 'switch' Or tsysAssetTypes.AssetTypename =
'router') And tblSNMPIfTypes.IfTypename Like '%Ethernet%'
Group By Case
When tblSNMPInfo.IfOperstatus = 1 Then 'Up'
When tblSNMPInfo.IfOperstatus = 2 Then 'Down'
When tblSNMPInfo.IfOperstatus = 3 Then 'Testing'
When tblSNMPInfo.IfOperstatus = 4 Then 'Unknown'
When tblSNMPInfo.IfOperstatus = 5 Then 'Dormant'
When tblSNMPInfo.IfOperstatus = 6 Then 'NotPresent'
When tblSNMPInfo.IfOperstatus = 7 Then 'LowLayerDown'
Else 'Other'
End


Hopefully you can find what you need in here. Good luck.
Randy


Thanks Randy, your reports are very helpful and I'm going to use them for other efforts. They don't quite get me to where I need to be with this report though. The "Switch: Ports" report you are talking about is actually the one I'm referring to in the thread title. I feel like I'm right there, but I'm missing something with one of my joins.
rader
Champion Sweeper III
Thanks Randy, your reports are very helpful and I'm going to use them for other efforts. They don't quite get me to where I need to be with this report though. The "Switch: Ports" report you are talking about is actually the one I'm referring to in the thread title. I feel like I'm right there, but I'm missing something with one of my joins.


That's were I started as well to create these. Good luck.
marda
Engaged Sweeper II
Is there anybody out there?