June 19, 2018 at 5:02 am
Hi
I'm really hoping someone can help me as I'm underexperienced and struggling
I have an SQL server which I use for radius accounting.
I have 2 tables:
Table 1 contains all the user names and client MAC addresses they have authenticated with (internetradiusaccounting.username and internetradiusaccounting.macaddress)
Table 2 contains the manufacturer name of that MAC address (macaddresses.companyname)
All I want to do is run an SQL query against all customer in table 1 (internetradiusaccounting.username) to see if they have a particular manufacturer of equipment based on MAC address they authenticated with in table 1 (internetradiusaccounting.macaddress) but using the manufacturer name in table 2 (macaddresses.companyname) and output those usernames so I can see who for example is using "Asus Tek" equipment
So I tried myself (not an SQL experienced person) and the server stopped responding for about an hour at which point it locked me out but self recovered so if it did produce a result I never saw it:
SELECT internetradiusaccounting.username, macaddresses.companyname, internetradiusaccounting.macaddress
FROM internetradiusaccounting INNER JOIN macaddresses ON internetradiusaccounting.macaddress = macaddresses.companyname
WHERE macaddresses.companyname = 'Asus Tek"
If anybody could help I would be very grateful!
Thank you
June 19, 2018 at 7:43 am
james.wilson 80895 - Tuesday, June 19, 2018 5:02 AMHi
I'm really hoping someone can help me as I'm underexperienced and strugglingI have an SQL server which I use for radius accounting.
I have 2 tables:
Table 1 contains all the user names and client MAC addresses they have authenticated with (internetradiusaccounting.username and internetradiusaccounting.macaddress)
Table 2 contains the manufacturer name of that MAC address (macaddresses.companyname)
All I want to do is run an SQL query against all customer in table 1 (internetradiusaccounting.username) to see if they have a particular manufacturer of equipment based on MAC address they authenticated with in table 1 (internetradiusaccounting.macaddress) but using the manufacturer name in table 2 (macaddresses.companyname) and output those usernames so I can see who for example is using "Asus Tek" equipment
So I tried myself (not an SQL experienced person) and the server stopped responding for about an hour at which point it locked me out but self recovered so if it did produce a result I never saw it:
SELECT internetradiusaccounting.username, macaddresses.companyname, internetradiusaccounting.macaddress
FROM internetradiusaccounting INNER JOIN macaddresses ON internetradiusaccounting.macaddress = macaddresses.companyname
WHERE macaddresses.companyname = 'Asus Tek"If anybody could help I would be very grateful!
Thank you
That doesn't sound right. You're comparing apples to oranges. The macaddress shouldn't have a company name.
I don't know the structure of your tables, but I'm guessing there's a macaddress column in your macaddresses table and your query could look like this:
SELECT i.username,
m.companyname,
i.macaddress
FROM internetradiusaccounting AS i
INNER JOIN macaddresses AS m ON i.macaddress = m.macaddress
WHERE m.companyname = 'Asus Tek';
June 19, 2018 at 9:12 am
Thank you for your help on this I will try it later tonight
The 'macaddresses' table contains the all various MAC's that are assigned to manufacturers and also the Manufacturers name
eg:
macaddresses table:
macaddress companyname
64:D1:54:CE:BD:88 Mikrotik
So my internetradiusaccounting table contains the MAC address of the clients when they connected but doesn't tell me who manufactured the equipment.
internetradiusaccounting:
username macaddress
john watts = 64:D1:54:CE;BD:88
So what I'm trying to achieve is to search the manufacturer name of the MAC address and output all client username in the internetaccountingradius table with a particular brand which is held in the 'macaddresses' table under column 'companyname' it would make more sense if 'companyname' read 'manufacturer'
Thank you
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply