FreeRADIUS PostgreSQL

Double Column 'order by':

select id,groupname,attribute,op,value from radgroupreply where groupname like '%21CN%' order by groupname asc, attribute asc;

Existing 21CN group settings:

select * from radgroupreply where groupname = 'BT21CN-WBMC' order by id;

Select all usersnames in BT21CN-WBMC group:

SELECT COUNT(DISTINCT(rr.username)) FROM radreply rr
INNER JOIN radusergroup rug
ON (rr.username=rug.username)
WHERE rug.groupname = 'BT21CN-WBMC';

SELECT DISTINCT(rr.username) FROM radreply rr
INNER JOIN radusergroup rug
ON (rr.username=rug.username)
WHERE rug.groupname = 'BT21CN-WBMC' ORDER BY username;

sudo -u postgres psql radius_test -q -c "SELECT DISTINCT(rr.username) FROM radreply rr INNER JOIN radusergroup rug ON (rr.username=rug.username) WHERE rug.groupname = 'BT21CN-WBMC';" | head -n -2 | tail -n +3 | sed 's/ //g' > All-BT21CN-WMBC-Users.txt

Select all usernames in BT21CN-WBMC group with the Test VRF defined (but not with a static route in the Test VRF):

SELECT DISTINCT(rr.username), rug.groupname, rr.attribute, rr.op, rr.value FROM radreply rr
INNER JOIN radusergroup rug
ON (rr.username=rug.username)
WHERE rug.groupname = 'BT21CN-WBMC'
AND rr.attribute = 'Cisco-AVPair'
AND rr.value ~* '.*test.*'
AND rr.value !~* '.*route.*'
ORDER BY rr.username;

Select all usernames in BT21CN-WBMC group with any VRF defined:

SELECT COUNT(DISTINCT(rr.username)) FROM radreply rr
INNER JOIN radusergroup rug
ON (rr.username=rug.username)
WHERE rug.groupname = 'BT21CN-WBMC'
AND rr.attribute = 'Cisco-AVPair'
AND rr.value ~* '.*vrf.*'
AND rr.value !~* '.*route.*vrf.*';

SELECT DISTINCT(rr.username) FROM radreply rr
INNER JOIN radusergroup rug
ON (rr.username=rug.username)
WHERE rug.groupname = 'BT21CN-WBMC'
AND rr.attribute = 'Cisco-AVPair'
AND rr.value ~* '.*vrf.*'
AND rr.value !~* '.*route.*vrf.*'
ORDER BY rr.username;

Select all usernames in BT21CN-WBMC group without any VRF defined:

SELECT COUNT(DISTINCT(username)) FROM (

SELECT rra.username
 FROM radreply rra
 INNER JOIN radusergroup rga
 ON (rra.username=rga.username)
 WHERE rga.groupname = 'BT21CN-WBMC'

EXCEPT SELECT rrb.username
 FROM radreply rrb
 INNER JOIN radusergroup rgb
 ON (rrb.username=rgb.username)
 WHERE rgb.groupname = 'BT21CN-WBMC'
 AND rrb.attribute = 'Cisco-AVPair'
 AND (rrb.value ~* 'ip:vrf-id=.*' OR rrb.value ~* '.*vrf forwarding.*')

 ) AS username_count;


SELECT rra.username
 FROM radreply rra
 INNER JOIN radusergroup rga
 ON (rra.username=rga.username)
 WHERE rga.groupname = 'BT21CN-WBMC'

EXCEPT SELECT rrb.username
 FROM radreply rrb
 INNER JOIN radusergroup rgb
 ON (rrb.username=rgb.username)
 WHERE rgb.groupname = 'BT21CN-WBMC'
 AND rrb.attribute = 'Cisco-AVPair'
 AND (rrb.value ~* 'ip:vrf-id=.*' OR rrb.value ~* '.*vrf forwarding.*')

ORDER BY username;

Select full profiles for users in BT21CN-WBMC group with no VRF defined:

SELECT rra.username, rra.attribute, rra.op, rra.value
 FROM radreply rra
 INNER JOIN radusergroup rga
 ON (rra.username=rga.username)
 WHERE rga.groupname = 'BT21CN-WBMC'
 AND rra.username IN

  (

  SELECT rra.username
   FROM radreply rra
   INNER JOIN radusergroup rga
   ON (rra.username=rga.username)
   WHERE rga.groupname = 'BT21CN-WBMC'
   AND rra.username IS NOT NULL

  EXCEPT SELECT rrb.username
   FROM radreply rrb
   INNER JOIN radusergroup rgb
   ON (rrb.username=rgb.username)
   WHERE rgb.groupname = 'BT21CN-WBMC'
   AND rrb.attribute = 'Cisco-AVPair'
   AND (rrb.value ~* 'ip:vrf-id=.*' OR rrb.value ~* '.*vrf forwarding.*')
   AND rrb.username IS NOT NULL

 )

ORDER BY username;



! Select full profiles for 21CN users with no VRF defined and not using PBR

SELECT rra.username, rra.attribute, rra.op, rra.value
 FROM radreply rra
 INNER JOIN radusergroup rga
 ON (rra.username=rga.username)
 WHERE rga.groupname = 'BT21CN-WBMC'
 AND rra.username IN

  (

  SELECT rra.username
   FROM radreply rra
   INNER JOIN radusergroup rga
   ON (rra.username=rga.username)
   WHERE rga.groupname = 'BT21CN-WBMC'
   AND rra.username IS NOT NULL

  EXCEPT SELECT rrb.username
   FROM radreply rrb
   INNER JOIN radusergroup rgb
   ON (rrb.username=rgb.username)
   WHERE rgb.groupname = 'BT21CN-WBMC'
   AND rrb.attribute = 'Cisco-AVPair'
   AND (rrb.value ~* 'ip:vrf-id=.*'
        OR rrb.value ~* '.*vrf forwarding.*'
        OR rrb.value ~* '.*ip vrf receive.*')
   AND rrb.username NOT LIKE '%test%'
   AND rrb.username IS NOT NULL

 )

ORDER BY username;

Insert into all users in the BT21CN-WMBC group which have no VRF defined, a default VRF and loopback:

INSERT INTO radreply (attribute, op, value, username)

SELECT 'Cisco-AVPair', '+=', 'ip:ip-unnumbered=loopback10', username FROM (

(  SELECT DISTINCT(rra.username)
    FROM radreply rra
    INNER JOIN radusergroup rga
    ON (rra.username=rga.username)
    WHERE rga.groupname = 'BT21CN-WBMC'

   EXCEPT SELECT rrb.username
    FROM radreply rrb
    INNER JOIN radusergroup rgb
    ON (rrb.username=rgb.username)
    WHERE rgb.groupname = 'BT21CN-WBMC'
    AND rrb.attribute = 'Cisco-AVPair'
    AND (rrb.value ~* 'ip:vrf-id=.*' OR rrb.value ~* '.*vrf forwarding.*')
 )


) AS user_inserts;



INSERT INTO radreply (attribute, op, value, username)

SELECT 'Cisco-AVPair', '+=', 'ip:vrf-id=WBMC', username FROM (

(  SELECT DISTINCT(rra.username)
    FROM radreply rra
    INNER JOIN radusergroup rga
    ON (rra.username=rga.username)
    WHERE rga.groupname = 'BT21CN-WBMC'

   EXCEPT SELECT rrb.username
    FROM radreply rrb
    INNER JOIN radusergroup rgb
    ON (rrb.username=rgb.username)
    WHERE rgb.groupname = 'BT21CN-WBMC'
    AND rrb.attribute = 'Cisco-AVPair'
    AND (rrb.value ~* 'ip:vrf-id=.*' OR rrb.value ~* '.*vrf forwarding.*')
 )


) AS user_inserts;

---