DHCP. Several dhcp attributes from the sql table

24 Dec 2021

      Hi,
...
...
/How can I select several dhcp attributes from the sql table and send />>/them to DHCP-ACK? /
 Read the rlm_sql documentation and follow it's examples.
...
Oh, and post some sample *contents* of the SQL table you're looking at.  And maybe even *debug output*.
...
If your question contains as little detail as possible, then the answers will have as little detail as possible
More detailed information

Sites DHCP
--------------------------------------------------------------------------------------------------------------
dhcp DHCP-Discover {
update control {
        Prefix := 'Request'
}
         dhcplog {
     fail = 1
}
update request {
        User-Name = 'dhcp'
}
dhcpstaticnd {
     fail = 1
}
if (notfound || noop) {
  dhcpdynnd {
     fail = 1
  }
}
if (ok) {
       dhcproute {
       fail = 1
     }
}
if (ok) {
     update reply {
         DHCP-Message-Type = DHCP-Offer
         DHCP-Domain-Name-Server = 195.98.64.65
         DHCP-Domain-Name-Server = 195.98.64.66
DHCP-NTP-Servers = 195.98.64.84
DHCP-Relay-Remote-Id = "%{request:DHCP-Relay-Remote-Id}"
DHCP-Relay-Circuit-Id = "%{DHCP-Relay-Circuit-Id}"
DHCP-DHCP-Server-Identifier = "%{Packet-Dst-IP-Address}"
     }
#<----->    if (request:DHCP-Parameter-Request-List:DHCP-Classless-Static-Route && (request:DHCP-Parameter-Request-List:DHCP-Router-Address || request:DHCP-Parameter-Request-List:DHCP-Static-Routes)) {
#<----->update reply {
#<----->    DHCP-Router-Address !* 192.168.1.1
#<----->}
#<----->    }
     update control {
        Prefix := 'Answer'
     }
     <-->    dhcplog {
fail = 1
     }
}
else {
     if (DHCP-Hop-Count > 1 && notfound) {
update reply {
     DHCP-Message-Type := DHCP-Nak
}
     }
     else {
update reply {
     DHCP-Message-Type !* DHCP-Nak
}
     }
     reject
}

}
---------------------------------------------------------------------------------------
Mods sql

sql dhcpstaticnd {
database = "mysql"
driver = "rlm_sql_${database}"

server = "localhost"
#port = 3306
login = "raduser"
password = "*********"
read_groups = no
read_profiles = yes
read_clients = no
do_clients = no
query_user="dhcp"

radius_db = "raddbdhcp"

authreply_table = "dhcpip"

# Remove stale session if checkrad does not see a double login
delete_stale_sessions = no

# Print all SQL statements when in debug mode (-x)
sqltrace = no
sqltracefile = ${logdir}/sqltrace.sql

# number of sql connections to make to server
num_sql_socks = 50

connect_failure_retry_delay = 60

# closed "lifetime" seconds after they were first opened.

lifetime = 0

max_queries = 0

safe_characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /"

sql_user_name = "dhcp"

# Use these for case sensitive usernames.
         authorize_check_query = "SELECT id, 'dhcp' as UserName, 'Connect-Rate', IPint, ':=' \
          FROM ${authreply_table} dh \
          WHERE RemoteID = BINARY '%{DHCP-Relay-Remote-Id}' \
  and upper(CircuitID) = upper('%{DHCP-Relay-Circuit-Id}') \
  and MacAddr = BINARY '%{DHCP-Client-Hardware-Address}' \
  ORDER BY LeaseDate desc"

authorize_reply_query = "SELECT id,'dhcp' as UserName, attribute, value, op \
          FROM ${authreply_table} dh \
          WHERE RemoteID = BINARY '%{DHCP-Relay-Remote-Id}' \
  and upper(CircuitID) =  upper('%{DHCP-Relay-Circuit-Id}') \
  and MacAddr = BINARY '%{DHCP-Client-Hardware-Address}' \
          ORDER BY LeaseDate desc"

}
----------------------------------------------------------------------------------
DEBUG

Received DHCP-Discover of Id 8fd33637 from 10.255.116.231:68 to 10.255.249.194:67
         DHCP-Opcode = Client-Message
         DHCP-Hardware-Type = Ethernet
         DHCP-Hardware-Address-Length = 6
         DHCP-Hop-Count = 1
         DHCP-Transaction-Id = 2412983863
         DHCP-Number-of-Seconds = 0
         DHCP-Flags = 0
         DHCP-Client-IP-Address = 0.0.0.0
         DHCP-Your-IP-Address = 0.0.0.0
         DHCP-Server-IP-Address = 0.0.0.0
         DHCP-Gateway-IP-Address = 10.255.116.231
         DHCP-Client-Hardware-Address = 04:bf:6d:97:dd:ed
         DHCP-Message-Type = DHCP-Discover
         DHCP-DHCP-Maximum-Msg-Size = 1500
         DHCP-Client-Identifier = 0x0104bf6d97dded
         DHCP-Vendor-Class-Identifier = 0x6e6468637063
         DHCP-Hostname = "Keenetic_Start"
         DHCP-Parameter-Request-List = DHCP-Subnet-Mask
         DHCP-Parameter-Request-List = DHCP-Router-Address
         DHCP-Parameter-Request-List = DHCP-Domain-Name-Server
         DHCP-Parameter-Request-List = DHCP-Domain-Name
         DHCP-Parameter-Request-List = DHCP-Broadcast-Address
         DHCP-Parameter-Request-List = DHCP-Static-Routes
         DHCP-Parameter-Request-List = DHCP-NTP-Servers
         DHCP-Parameter-Request-List = DHCP-Vendor
         DHCP-Parameter-Request-List = DHCP-NETBIOS-Name-Servers
         DHCP-Parameter-Request-List = DHCP-Classless-Static-Route
         DHCP-Parameter-Request-List = 249
         DHCP-Relay-Circuit-Id = 0x000400010002
         DHCP-Relay-Remote-Id = 0x0113686e2d6368656c313031762d737730362d3137
(5) Received code 1025 Id 2412983863 from 10.255.116.231:68 to 10.255.249.194:67 length 327
(5)   DHCP-Opcode = Client-Message
(5)   DHCP-Hardware-Type = Ethernet
(5)   DHCP-Hardware-Address-Length = 6
(5)   DHCP-Hop-Count = 1
(5)   DHCP-Transaction-Id = 2412983863
(5)   DHCP-Number-of-Seconds = 0
(5)   DHCP-Flags = 0
(5)   DHCP-Client-IP-Address = 0.0.0.0
(5)   DHCP-Your-IP-Address = 0.0.0.0
(5)   DHCP-Server-IP-Address = 0.0.0.0
(5)   DHCP-Gateway-IP-Address = 10.255.116.231
(5)   DHCP-Client-Hardware-Address = 04:bf:6d:97:dd:ed
(5)   DHCP-Message-Type = DHCP-Discover
(5)   DHCP-DHCP-Maximum-Msg-Size = 1500
(5)   DHCP-Client-Identifier = 0x0104bf6d97dded
(5)   DHCP-Vendor-Class-Identifier = 0x6e6468637063
(5)   DHCP-Hostname = "Keenetic_Start"
(5)   DHCP-Parameter-Request-List = DHCP-Subnet-Mask
(5)   DHCP-Parameter-Request-List = DHCP-Router-Address
(5)   DHCP-Parameter-Request-List = DHCP-Domain-Name-Server
(5)   DHCP-Parameter-Request-List = DHCP-Domain-Name
(5)   DHCP-Parameter-Request-List = DHCP-Broadcast-Address
(5)   DHCP-Parameter-Request-List = DHCP-Static-Routes
(5)   DHCP-Parameter-Request-List = DHCP-NTP-Servers
(5)   DHCP-Parameter-Request-List = DHCP-Vendor
(5)   DHCP-Parameter-Request-List = DHCP-NETBIOS-Name-Servers
(5)   DHCP-Parameter-Request-List = DHCP-Classless-Static-Route
(5)   DHCP-Parameter-Request-List = 249
(5)   DHCP-Relay-Circuit-Id = 0x000400010002
(5)   DHCP-Relay-Remote-Id = 0x0113686e2d6368656c313031762d737730362d3137
Trying sub-section dhcp DHCP-Discover {...}
(5)   dhcp DHCP-Discover {
(5)     update control {
(5)       Prefix := 'Request'
(5)     } # update control = noop
(5) dhcplog: EXPAND .query
(5) dhcplog:    --> .query
(5) dhcplog: Using query template 'query'
rlm_sql (dhcplog): Reserved connection (4)
(5) dhcplog: EXPAND %{User-Name}
(5) dhcplog:    -->
(5) dhcplog: SQL-User-Name set to ''
(5) dhcplog: EXPAND INSERT INTO dhcplog (LogDate,Seq,DHCPTypeIn,DHCPTypeOut,DHCPGateway,RemoteID,CircuitID,MacAddr,IpAddr, GwAddr)       VALUES (sysdate(),      '%{control:Prefix}',    '%{request:DHCP-Message-Type}',         '%{reply:DHCP-Message-Type}',  '%{request:DHCP-Gateway-IP-Address}',   '%{request:DHCP-Relay-Remote-Id}','%{request:DHCP-Relay-Circuit-Id}',   '%{request:DHCP-Client-Hardware-Address}','%{reply:DHCP-Your-IP-Address}',      '%{reply:DHCP-Router-Address}')
(5) dhcplog:    --> INSERT INTO dhcplog (LogDate,Seq,DHCPTypeIn,DHCPTypeOut,DHCPGateway,RemoteID,CircuitID,MacAddr,IpAddr, GwAddr)       VALUES (sysdate(),      'Request',      'DHCP-Discover',        '',     '10.255.116.231',       '0x0113686e2d6368656c313031762d737730362d3137','0x000400010002',       '04:bf:6d:97:dd:ed','',         '')
(5) dhcplog: Executing query: INSERT INTO dhcplog (LogDate,Seq,DHCPTypeIn,DHCPTypeOut,DHCPGateway,RemoteID,CircuitID,MacAddr,IpAddr, GwAddr)     VALUES (sysdate(),      'Request',      'DHCP-Discover',        '',     '10.255.116.231',       '0x0113686e2d6368656c313031762d737730362d3137','0x000400010002',       '04:bf:6d:97:dd:ed','',         '')
(5) dhcplog: SQL query returned: success
(5) dhcplog: 1 record(s) updated
rlm_sql (dhcplog): Released connection (4)
(5)     [dhcplog] = ok
(5)     update request {
(5)       User-Name = 'dhcp'
(5)     } # update request = noop
(5) dhcpstaticnd: EXPAND .query
(5) dhcpstaticnd:    --> .query
(5) dhcpstaticnd: WARNING: No such configuration item .query
(5)     [dhcpstaticnd] = noop
(5)     if (notfound || noop) {
(5)     if (notfound || noop)  -> TRUE
(5)     if (notfound || noop)  {
(5) dhcpdynnd: EXPAND .query
(5) dhcpdynnd:    --> .query
(5) dhcpdynnd: WARNING: No such configuration item .query
(5)       [dhcpdynnd] = noop
(5)     } # if (notfound || noop)  = noop
(5)     if (ok) {
(5)     if (ok)  -> FALSE
(5)     if (ok) {
(5)     if (ok)  -> FALSE
(5)     else {
(5)       if (DHCP-Hop-Count > 1 && notfound) {
(5)       if (DHCP-Hop-Count > 1 && notfound)  -> FALSE
(5)       else {
(5)         update reply {
(5)           DHCP-Message-Type !* ANY
(5)         } # update reply = noop
(5)       } # else = noop
(5)       [reject] = reject
(5)     } # else = reject
(5)   } # dhcp DHCP-Discover = reject
(5) Not sending reply to client.
(5) Finished request
(5) Cleaning up request packet ID 2412983863 with timestamp +0
Ready to process requests

-- 
Sergey Kodentsev