showing empty entries on reports

[manuelP]

hi

for certain reasons we need to know which switch port is in use by which device, admin state etc.

so i created a report that displays all switch ports with the informations i need.
now i want to link the assets to the switch ports, but in doing so all ports with no device connected are not shown on the report anymore.

i posted my script below.

after adding tblsnmpassetmac and linking assetdit + ifindex to tbllsnmpinfo the devices are shown to the correct switch port, but as mentioned above empty switch ports are not shown anymore.

i tried to set up a condition for tblsnmpassetmac.assetmacaddress like:

Case When tblSNMPAssetMac.AssetMacAddress is NULL Then '1' Else '2' End

but i guess this isnt working because there is no entry in the table for this switch port at all, so i need a way to tell the system if you don't find anything just send back blank.

br

manuel

REPORT WITH ONLY SWITCH PORTS

Select Top 1000000 tblSNMPInfo.AssetID,
tblAssets.AssetName,
tblSNMPInfo.IfIndex,
Case
When tblSNMPInfo.IfAdminstatus = 1 Then 'Up'
When tblSNMPInfo.IfAdminstatus = 2 Then 'Down'
Else 'Testing'
End As [Port-Adminstatus],
Case
When tblSNMPInfo.IfOperstatus = 1 Then 'Active'
When tblSNMPInfo.IfOperstatus = 2 Then 'NotActive'
Else 'Testing'
End As [Port-Status],
tblSNMPIfTypes.IfTypename
From tblSNMPInfo
Inner Join tblSNMPIfTypes On tblSNMPInfo.IfType = tblSNMPIfTypes.IfType
Inner Join tblAssets On tblAssets.AssetID = tblSNMPInfo.AssetID
Inner Join tblDeviceRecognition On tblAssets.Assettype =
tblDeviceRecognition.Type
Where (tblAssets.AssetName Like 'MAYSW%' Or tblAssets.AssetName Like 'VMASW%')
And tblSNMPIfTypes.IfTypename Not Like 'loopback' And
tblSNMPIfTypes.IfTypename Not Like 'propVirtual' And
tblSNMPIfTypes.IfTypename Not Like 'other' And tblDeviceRecognition.TypeName
Like 'switch'
Group By tblSNMPInfo.AssetID,
tblAssets.AssetName,
tblSNMPInfo.IfIndex,
Case
When tblSNMPInfo.IfAdminstatus = 1 Then 'Up'
When tblSNMPInfo.IfAdminstatus = 2 Then 'Down'
Else 'Testing'
End,
Case
When tblSNMPInfo.IfOperstatus = 1 Then 'Active'
When tblSNMPInfo.IfOperstatus = 2 Then 'NotActive'
Else 'Testing'
End,
tblSNMPIfTypes.IfTypename,
tblDeviceRecognition.TypeName
Order By tblAssets.AssetName,
tblSNMPInfo.IfIndex

after adding tblsnmpassetmac

Select Top 1000000 tblSNMPInfo.AssetID,
tblAssets.AssetName,
tblSNMPInfo.IfIndex,
Case
When tblSNMPInfo.IfAdminstatus = 1 Then 'Up'
When tblSNMPInfo.IfAdminstatus = 2 Then 'Down'
Else 'Testing'
End As [Port-Adminstatus],
Case
When tblSNMPInfo.IfOperstatus = 1 Then 'Active'
When tblSNMPInfo.IfOperstatus = 2 Then 'NotActive'
Else 'Testing'
End As [Port-Status],
tblSNMPIfTypes.IfTypename,
tblSNMPAssetMac.AssetMacAddress
From tblSNMPInfo
Inner Join tblSNMPIfTypes On tblSNMPInfo.IfType = tblSNMPIfTypes.IfType
Inner Join tblAssets On tblAssets.AssetID = tblSNMPInfo.AssetID
Inner Join tblDeviceRecognition On tblAssets.Assettype =
tblDeviceRecognition.Type
Inner Join tblSNMPAssetMac On tblSNMPAssetMac.AssetID = tblSNMPInfo.AssetID
And tblSNMPAssetMac.IfIndex = tblSNMPInfo.IfIndex
Where (tblAssets.AssetName Like 'MAYSW%' Or tblAssets.AssetName Like 'VMASW%')
And tblSNMPIfTypes.IfTypename Not Like 'loopback' And
tblSNMPIfTypes.IfTypename Not Like 'propVirtual' And
tblSNMPIfTypes.IfTypename Not Like 'other' And tblDeviceRecognition.TypeName
Like 'switch'
Group By tblSNMPInfo.AssetID,
tblAssets.AssetName,
tblSNMPInfo.IfIndex,
Case
When tblSNMPInfo.IfAdminstatus = 1 Then 'Up'
When tblSNMPInfo.IfAdminstatus = 2 Then 'Down'
Else 'Testing'
End,
Case
When tblSNMPInfo.IfOperstatus = 1 Then 'Active'
When tblSNMPInfo.IfOperstatus = 2 Then 'NotActive'
Else 'Testing'
End,
tblSNMPIfTypes.IfTypename,
tblDeviceRecognition.TypeName,
tblSNMPAssetMac.AssetMacAddress
Order By tblAssets.AssetName,
tblSNMPInfo.IfIndex

[ErikT]

Manuel,

To include switch ports with no device connected in your report, you should use a LEFT JOIN instead of an INNER JOIN when adding the tblSNMPAssetMac table. This will ensure that all switch ports are returned in the result set, even if there is no matching entry in the tblSNMPAssetMac table.

Here's how you can modify your query:

Select
  Top 1000000 tblSNMPInfo.AssetID,
  tblAssets.AssetName,
  tblSNMPInfo.IfIndex,
  Case
    When tblSNMPInfo.IfAdminstatus = 1 Then 'Up'
    When tblSNMPInfo.IfAdminstatus = 2 Then 'Down'
    Else 'Testing'
  End As [Port-Adminstatus],
  Case
    When tblSNMPInfo.IfOperstatus = 1 Then 'Active'
    When tblSNMPInfo.IfOperstatus = 2 Then 'NotActive'
    Else 'Testing'
  End As [Port-Status],
  tblSNMPIfTypes.IfTypename,
  tblSNMPAssetMac.AssetMacAddress
From
  tblSNMPInfo
  Inner Join tblSNMPIfTypes On tblSNMPInfo.IfType = tblSNMPIfTypes.IfType
  Inner Join tblAssets On tblAssets.AssetID = tblSNMPInfo.AssetID
  Inner Join tblDeviceRecognition On tblAssets.Assettype = tblDeviceRecognition.Type
  Left Join tblSNMPAssetMac On tblSNMPAssetMac.AssetID = tblSNMPInfo.AssetID
  And tblSNMPAssetMac.IfIndex = tblSNMPInfo.IfIndex
Where
  (
    tblAssets.AssetName Like 'MAYSW%'
    Or tblAssets.AssetName Like 'VMASW%'
  )
  And tblSNMPIfTypes.IfTypename Not Like 'loopback'
  And tblSNMPIfTypes.IfTypename Not Like 'propVirtual'
  And tblSNMPIfTypes.IfTypename Not Like 'other'
  And tblDeviceRecognition.TypeName Like 'switch'
Group By
  tblSNMPInfo.AssetID,
  tblAssets.AssetName,
  tblSNMPInfo.IfIndex,
  Case
    When tblSNMPInfo.IfAdminstatus = 1 Then 'Up'
    When tblSNMPInfo.IfAdminstatus = 2 Then 'Down'
    Else 'Testing'
  End,
  Case
    When tblSNMPInfo.IfOperstatus = 1 Then 'Active'
    When tblSNMPInfo.IfOperstatus = 2 Then 'NotActive'
    Else 'Testing'
  End,
  tblSNMPIfTypes.IfTypename,
  tblDeviceRecognition.TypeName,
  tblSNMPAssetMac.AssetMacAddress
Order By
  tblAssets.AssetName,
  tblSNMPInfo.IfIndex

By using a LEFT JOIN, the query will include all switch ports, and where there is no device connected, the AssetMacAddress will be NULL. You can then handle these NULL values in your report as needed to indicate empty ports.