List of Devices matching list of MAC addresses

[atkinsjd]

I need to develop a query that lists all assets from a list of selected manufacturers. This must be performed by matching the MAC address of the devices in my inventory against a pre-defined list. Any help would be appreciated.

[atkinsjd]

Thanks for the help. I created a second table called tsysMacVendor2 which contains only the subset of MAC/Vendors I was looking for. I added colons to this table to make match rules a bit easier. I modified the query and it seems to be working.

Select Top (1000000) 
  tsysIPLocations.IPLocation,
  tsysAssetTypes.AssetTypename As Type,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.IPAddress,
  tblAssets.Mac As 'MAC Address',
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssets.Lastseen
From tblAssets
  Left Outer Join tsysIPLocations On dbo.tblAssets.IPNumeric >=
    tsysIPLocations.StartIP And dbo.tblAssets.IPNumeric <= tsysIPLocations.EndIP
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype,
  tsysMacVendor2
Where tblAssets.Mac <> '' And tsysMacVendor2.MAC Like SubString(tblAssets.Mac,
  1, 😎 + '%'
Order By tblAssets.IPNumeric

[RCorbeil]

It looks like there's already an OUI table in the database, tSysMacVendor. The IEEE list changes over time, so not all of yours will necessarily be in there right now, but you should be able to append new entries to the table.

I'm skimming the tables to see if there's a colon-stripped copy of the MAC stored someplace but not having any luck. Assuming that it's not there to be found, adding a link to the tSysMacVendor table should do the trick for you.

Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssets.Mac,
  tsysMacVendor.Vendor
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysMacVendor On tsysMacVendor.MAC = Replace(Left(tblAssets.Mac, 8), ':', '')
Where
  tblAssetCustom.State = 1
  AND tblAssets.Mac <> ''
  AND Replace(Left(tblAssets.Mac, 8), ':', '') In ('00000A','000023','00002B','0000BC','000105','00010D','000123','000158','0001C3','0001EE','00022C','000248','0002A2','0002A3','0002D3','000305','000317','00032C','000358','00037B','00038D','0003AA','0003AD','000417','000463','0004D7','00054B','000594','0005DA','00063D','000660','000677','000746','000768','0007A6','0007BE','000808','00089B','0008CC','00097E','000991','0009F5','000A44','000A66','000AA9','000ADC','000AED','000B17','000B29','000B2D','000BCB','000BF3','000C02','000C1A','000C62','000C87','000CDE','000D1E','000D81','000D98','000DAB','000E13','000EC1','000ECF','000EF0','000F18','000F67','000F69','000F73','000F9C','000F9E','001025','001048','0010C3','001182','0011FC','00120A','00121F','00124F','001293','0012A8','0012C2','0012ED','001324','001345','001384','001386','001411','001415','0014F7','0014FF','001524','00154E','0015A6','0015BA','0015DD','0015FC','00165F','001677','0016BD','002004','002025','002034','00203D','002096','00209D','0020B5','003011','003056','003097','0030A0','0030DE','00401A','0040AE','0040C3','0050A0','0050CB','0050DB','0050FF','006041','0060B2','0060E5','008074','008094','0080A3','00904F','009068','0090DF','0090E8','00A03D','00A045','00A0ED','00C04E','00C0AF','00C0CB','00D024','00D026','00D027','00D0AF','00E067','00E07E','00E090','00E0A1','00E0C4','00E0E4','080017','080070','1000E8')

[atkinsjd]

If I took the data in the list and created "ICS_LIST" with two columns (ICS_MAC and ICS_VENDOR), what would the query look like?

Most of these assets are going to be network devices and I will not be able to get administrative access with lansweeper. Lansweeper should still record the MAC Address for these devices, correct?

[RCorbeil · Accepted solution]

It's not pretty, but it does what you ask:

Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssets.Mac
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where
  tblAssetCustom.State = 1
  AND tblAssets.Mac <> ''
  AND Replace(Left(tblAssets.Mac,8), ':', '') IN ('00000A','000023','00002B','0000BC','000105','00010D','000123','000158','0001C3','0001EE','00022C','000248','0002A2','0002A3','0002D3','000305','000317','00032C','000358','00037B','00038D','0003AA','0003AD','000417','000463','0004D7','00054B','000594','0005DA','00063D','000660','000677','000746','000768','0007A6','0007BE','000808','00089B','0008CC','00097E','000991','0009F5','000A44','000A66','000AA9','000ADC','000AED','000B17','000B29','000B2D','000BCB','000BF3','000C02','000C1A','000C62','000C87','000CDE','000D1E','000D81','000D98','000DAB','000E13','000EC1','000ECF','000EF0','000F18','000F67','000F69','000F73','000F9C','000F9E','001025','001048','0010C3','001182','0011FC','00120A','00121F','00124F','001293','0012A8','0012C2','0012ED','001324','001345','001384','001386','001411','001415','0014F7','0014FF','001524','00154E','0015A6','0015BA','0015DD','0015FC','00165F','001677','0016BD','002004','002025','002034','00203D','002096','00209D','0020B5','003011','003056','003097','0030A0','0030DE','00401A','0040AE','0040C3','0050A0','0050CB','0050DB','0050FF','006041','0060B2','0060E5','008074','008094','0080A3','00904F','009068','0090DF','0090E8','00A03D','00A045','00A0ED','00C04E','00C0AF','00C0CB','00D024','00D026','00D027','00D0AF','00E067','00E07E','00E090','00E0A1','00E0C4','00E0E4','080017','080070','1000E8')

If the list of OUIs you want to look for is much longer than that, you might consider creating a table to do a comparison against.