Hello Community,
There is still time to submit entries to our Spring challenge (view more details in my post below) Asset Discovery Challenge - Lansweeper Community Remember to Like or Comment on your favourite entry: The entry with the most votes (likes) wins!
The contest closes on 20th April.
Good luck!
Mercedes
Original message
Hi All,
Are you ready for our next community challenge? I am! Recently a colleague discovered a new device on the network - an airfryer! This got us thinking about random, unique assets our users may have come across spontaneously.
So we invite you to uncover unique and interesting devices in your network and share these with the rest of the community - the more random the better really.
🔎 What gems will you discover? From vintage medical devices to cutting-edge culinary technology, the possibilities are endless!
But that's not all! Participants will have the chance to win exclusive superhero-themed community badges and Lansweeper swag.
[Participate now: Asset Discovery Challenge - Lansweeper Community!] 🚀
Hint: Get started with this raw query from our team
Select * from ( Select Case When tblAssets.AssetName Is Not Null Then tblAssets.AssetName When tblAssetRadarDevice.DeviceName Is Not Null Then tblAssetRadarDevice.DeviceName When tblAssetRadarDevice.MacAddress Is Not Null Then tblAssetRadarDevice.MacAddress Else '' End As AssetName, tblAssetRadarDevice.MacAddress, Coalesce(tblAssetRadarDevice.Manufacturer, tblDeviceRecognition.MacVendor) As 'Mac vendor', tblDeviceRecognition.Brand As 'Device brand', Coalesce(NullIf(tsysAssetTypes.AssetTypename, 'Unknown'), tsysAssetTypes2.AssetTypename, 'Unknown') As 'Type', Coalesce(NullIf(tblAssetCustom.Model, ''), tblDeviceRecognition.Model) As 'DeviceModel', Case When tblLinuxSystem.OSRelease Is Null Then Case When tblDeviceRecognition.OsName Is Null And tblDeviceRecognition.OsBuild Is Null Then Coalesce(NullIf(tsysAssetTypes.AssetTypename, 'Unknown'), tsysAssetTypes2.AssetTypename, 'Unknown') Else Coalesce(tblMacOSInfo.SystemVersion, tblChromeOS.OsVersion, tsysOS.Osname, tblDeviceRecognition.OsName, '') + ' ' + IsNull(tblDeviceRecognition.OsVersion, '') + ' ' + Coalesce(tblAssets.BuildNumber, tblDeviceRecognition.OsBuild, tblDeviceRecognition.OsVersion, '') End Else tblLinuxSystem.OSRelease End As 'Operating system', Case Convert(FLOAT,tblAssetRadarDevice.Protocols) When '1' Then 'DHCP' When '2' Then 'ARP' When '3' Then 'DHCP, ARP' When '4' Then 'UDP' When '5' Then 'DHCP, UDP' When '6' Then 'ARP, UDP' When '7' Then 'DHCP, ARP, UDP' When '8' Then 'UDPv6' When '9' Then 'DHCP, UDPv6' When '10' Then 'ARP, UDPv6' When '11' Then 'DHCP, ARP, UDPv6' When '12' Then 'UDP, UDPv6' When '13' Then 'DHCP, UDP, UDPv6' When '14' Then 'ARP, UDP, UDPv6' When '15' Then 'DHCP, ARP, UDP, UDPv6' Else 'None' End As 'Protocols' From tblAssetRadarDevice Left Join tblDeviceRecognition On tblAssetRadarDevice.MacAddress = tblDeviceRecognition.MacAddress Left Join tblAssetMacAddress On tblAssetMacAddress.Mac = tblAssetRadarDevice.MacAddress And tblAssetMacAddress.AssetID In (Select Top (1) tblAssets.AssetID From tblAssets Inner Join tblAssetRadarDevice On tblAssetRadarDevice.MacAddress = tblAssets.Mac Where tblAssets.Mac = tblAssetMacAddress.Mac Order By tblAssets.Lastseen Desc) And tblAssetMacAddress.AssetID In (Select Top (1) tblAssets.AssetID From tblAssets Where tblAssets.IPAddress Is Not Null And tblAssets.IPAddress = tblAssetRadarDevice.Ipv4Address And tblAssets.Mac Is Not Null And tblAssets.Mac = tblAssetRadarDevice.MacAddress Order By tblAssets.Lastseen Desc) Left Outer Join tblAssets On tblAssets.AssetID = tblAssetMacAddress.AssetID Left Outer Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID Left Outer Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype Left Outer Join tsysAssetTypes As tsysAssetTypes2 On tsysAssetTypes2.AssetType = tblDeviceRecognition.Type Left Outer Join tsysOS On tblAssets.OScode = tsysOS.OScode And (tblAssets.Assettype = -1 Or tblDeviceRecognition.Type = -1) Left Outer Join tblLinuxSystem On tblLinuxSystem.AssetID = tblAssets.AssetID Left Outer Join tblMacOSInfo On tblMacOSInfo.AssetID = tblAssets.AssetID Left Outer Join tblChromeOS On tblChromeOS.AssetID = tblAssets.AssetID union Select tblAssets.AssetName, tblAssets.Mac As 'MacAddress', '' As 'Mac vendor', tblAssetCustom.Manufacturer As 'Device brand', tsysAssetTypes.AssetTypename As 'Type', tblAssetCustom.Model As 'DeviceModel', Case When tblLinuxSystem.OSRelease Is Null Then Coalesce(tblMacOSInfo.SystemVersion, tblChromeOS.OsVersion, tsysOS.Osname, '') + ' ' + Coalesce(tblAssets.BuildNumber, '') Else tblLinuxSystem.OSRelease End As 'Operating system', '' As 'Protocols' From tblAssets Left Outer Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID Left Outer Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype Left Outer Join tsysOS On tblAssets.OScode = tsysOS.OScode And tblAssets.Assettype = -1 Left Outer Join tblLinuxSystem On tblLinuxSystem.AssetID = tblAssets.AssetID Left Outer Join tblMacOSInfo On tblMacOSInfo.AssetID = tblAssets.AssetID Left Outer Join tblChromeOS On tblChromeOS.AssetID = tblAssets.AssetID ) As result Where DeviceModel is not NULL and DeviceModel <> '' Order By AssetName asc GO
See you on the quest 💪
