New connected Ports on a Switch

[FischbachM]

Hello everyone,

i haven´t found it in the custom reports so far, and i´m really interested in a simple report, wich shows me the new connected assets to my switches.

I need a list of devices wich are new connected (e.g. within the last 2 days) to a Port on the switch.

Thanks in Advance.

Michael

[Hendrik_VE]

In case you scan the connected asset as well, you can use the report below.
For devices that aren't picked up by Lansweeper yet, I don't believe there is a 'first seen' record in the database to detect on newly discovered devices. But you can omit the 'tblAssets1.Firstseen > GetDate() - 7' where clause and find out yourself.

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 [If],
  tblSNMPInfo.IfDescription As Name,
  tblSNMPIfTypes.IfTypename As Type,
  Case
    When tblSNMPInfo.IfAdminstatus = 1 Then 'Up'
	When tblSNMPInfo.IfAdminstatus = 2 Then 'Down'
	When tblSNMPInfo.IfAdminstatus = 3 Then 'Testing'
    Else 'Other'
  End As 'Admin Status',
  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 'Operational Status',
  tblsnmpassetmac.lastseen as 'Last Detected',
  tblsnmpassetmac.lastchanged as 'Last Changed',
  tblassets1.firstseen as 'Asset First Seen',
  tblSNMPInfo.IfMTU As MTU,
  Ceiling(tblSNMPInfo.IfSpeed / 1000 / 1000) As Speed,
  tblSNMPInfo.IfIPAddress As IP,
  tblSNMPInfo.IfMask As Mask,
  tblSNMPInfo.IfMacaddress As SwitchportMAC,
  tblSNMPAssetMac.AssetMacAddress As AssetMAC,
  Case
    When tblSNMPAssetMac.AssetMacAddress Is Null Then 'No MAC detected'
    When tsysMacVendor.Vendor Is Null Then 'MAC detected but Vendor not found'
    Else tsysMacVendor.Vendor
  End As AssetVendor,
  tsysAssetTypes_1.AssetTypeIcon16 As icon2,
  tblAssets1.AssetName As deviceassetname,
  tblAssets1.Username,
  tblAssets1.Userdomain,
  tblSNMPInfo.Vlan,
  Case
    When tblSNMPInfo.IfOperstatus = 1 and tblsnmpinfo.ifadminstatus = 1 Then '#d4f4be'
	When tblSNMPInfo.IfOperstatus = 2 and tblsnmpinfo.ifadminstatus = 1 Then '#ffff00'
	When tblSNMPInfo.IfOperstatus = 2 and tblsnmpinfo.ifadminstatus = 2 Then '#ffadad'
    Else '#00ffff'
  End As backgroundcolor
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 tsysMacVendor On
    tsysMacVendor.MAC = SubString(tblSNMPAssetMac.AssetMacAddress, 1, 2) +
    SubString(tblSNMPAssetMac.AssetMacAddress, 4, 2) +
    SubString(tblSNMPAssetMac.AssetMacAddress, 7, 2)
  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  tblAssets1.Firstseen > GetDate() - 7
Order By tblAssets.AssetName,
  [If]