cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
nickoliver
Engaged Sweeper II
I am trying to get a complete list of mac addresses of assets on the network so that we can implement a whitelist inside our network. I have looking around and trying different items in the report builder but ive only been able to get a list of PC MAC addresses (BUT its not a complete list of PC MAC addresses).

This is what im currently using:

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblNetwork.MACaddress,
tblNetwork.Description
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblAssetCustom.State = 1 And tblNetwork.IPEnabled = 1
Order By tblAssets.AssetName



When I go onto an asset which hasnt appeared in this list, I can see if i go to config - > network that there is a MAC address for the asset but its not included in the list.

What Im trying to achieve is a report which will tell me the asset name, the mac address, and what the adaptor is (aka to see if they are virtual ports). Theres alot to tables to go through and when I add another table in to get a bit more information the results seemed to get messed up.

Can someone help/point me in the right direction?
2 REPLIES 2
nickoliver
Engaged Sweeper II
Thanks, It now shows all of the assets but some of the windows machines dont show a MAC address. I know they have one as if I click on one of the windows assets and browse to config and network, it shows me the mac address next to an adaptor. This may be how Lansweeper store the data but its confusing me.

Also With your code, how can I get it to show multiple MAC addresses if they exist on an asset? some PC's have 4 mac addresses
Łukasz_B_
Engaged Sweeper
Try this one 🙂

You probably will need to modyfiy it but im sure this will help, this here gives you report of all the assets and their MAC, so it includes printers, network devices, video recordes etc.
Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Mac,
Case
When tsysAssetTypes.AssetTypename = 'Windows' Then
SubString(tblOperatingsystem.Caption, CharIndex(' ',
tblOperatingsystem.Caption, 1), Len(tblOperatingsystem.Caption))
Else tsysAssetTypes.AssetTypename End As System
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblAssets.IPAddress Is Not Null And tsysAssetTypes.AssetType != 208 And
tblState.State = 1
Order By tblAssets.IPAddress Desc

New to Lansweeper?

Try Lansweeper For Free

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

Try Now