cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
atkinsjd
Engaged Sweeper II
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.
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
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.

View solution in original post

4 REPLIES 4
atkinsjd
Engaged Sweeper II
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
Honored Sweeper II
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
Engaged Sweeper II
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
Honored Sweeper II
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.

New to Lansweeper?

Try Lansweeper For Free

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

Try Now