This website uses cookies. By clicking Accept, you consent to the use of cookies. Click Here to learn more about how we use cookies.
Accept
Reject
Community FAQ
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Switch: Ports Report All Assets

marda
Engaged Sweeper II

‎03-09-2021 05:02 PM

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
Labels:
0 Kudos
4 REPLIES 4
rader
Champion Sweeper III

‎03-10-2021 09:06 PM

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
0 Kudos
marda
Engaged Sweeper II
In response to rader

‎03-11-2021 01:54 PM

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.
0 Kudos
rader
Champion Sweeper III
In response to marda

‎03-11-2021 06:24 PM

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.
0 Kudos
marda
Engaged Sweeper II

‎03-10-2021 06:09 PM

Is there anybody out there?
0 Kudos

New to Lansweeper?

Try Lansweeper For Free

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

Try Now