MAC Addresses for ALL assets

[nickoliver]

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?

[nickoliver]

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_]

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