entering multiple AVP into database

classic Classic list List threaded Threaded
4 messages Options
| Threaded
Open this post in threaded view
|

entering multiple AVP into database

Users mailing list
Greetings FR-users,

I am seeking some advice for getting a list of AVPs into a database.

Our Cisco ASA VPN sends the following vendor attributes to FR:

(2)   Cisco-AVPair = "mdm-tlv=device-platform=win"
(2)   Cisco-AVPair = "mdm-tlv=device-mac=5c-3a-45-aa-ff-ee"
(2)   Cisco-AVPair = "mdm-tlv=device-type=HP HP ProBook 445R G6"
(2)   Cisco-AVPair = "mdm-tlv=device-platform-version=10.0.18363 "
(2)   Cisco-AVPair = "mdm-tlv=device-public-mac=5c-3a-45-aa-ff-ee"
(2)   Cisco-AVPair = "mdm-tlv=ac-user-agent=AnyConnect Windows 4.9.04043"
(2)   Cisco-AVPair =
"mdm-tlv=device-uid-global=8F31836F07DA5CE40D0207A1AB4DFA8200048FC3"
(2)   Cisco-AVPair =
"mdm-tlv=device-uid=9A920780AEEAD2B5496D09DAA6DDCCDD159619CD117D8D000780F6DF3B083C14"

After stripping off the leading "mdm-tlv=" of the string I am left with a
key, value pair that I'd like to put into the accounting database.

I see three database possibilities:

1. Add new fields to the radacct table for the keys above. New fields would
be "device-platform", "device-mac", etc. I do not know if the set of fields
is fixed or if new ones would be sent along from the NAS. This is one of
the downsides of adding new fields to the radacct table.

2. Create a new one to many table that would contain the key, value pairs
and a foreign key back to the radacct table.

3. Add a JSONB field to the radacct table and put all of the key, value
pairs into a JSONB data structure.

Does anyone have any advice as to a sensible path forward for storing this
AVP data?

Alternatively, does anyone have any success stories of storing AVP data
when the NAS gives multiple entries of the same attribute back to FR?

Thanks for any dialog.

Cheers!

-m
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
| Threaded
Open this post in threaded view
|

Re: entering multiple AVP into database

Alan DeKok-2
On Feb 5, 2021, at 4:50 PM, Matt Zagrabelny via Freeradius-Users <[hidden email]> wrote:

> I am seeking some advice for getting a list of AVPs into a database.
>
> Our Cisco ASA VPN sends the following vendor attributes to FR:
>
> (2)   Cisco-AVPair = "mdm-tlv=device-platform=win"
> (2)   Cisco-AVPair = "mdm-tlv=device-mac=5c-3a-45-aa-ff-ee"
> (2)   Cisco-AVPair = "mdm-tlv=device-type=HP HP ProBook 445R G6"
> (2)   Cisco-AVPair = "mdm-tlv=device-platform-version=10.0.18363 "
> (2)   Cisco-AVPair = "mdm-tlv=device-public-mac=5c-3a-45-aa-ff-ee"
> (2)   Cisco-AVPair = "mdm-tlv=ac-user-agent=AnyConnect Windows 4.9.04043"
> (2)   Cisco-AVPair =
> "mdm-tlv=device-uid-global=8F31836F07DA5CE40D0207A1AB4DFA8200048FC3"
> (2)   Cisco-AVPair =
> "mdm-tlv=device-uid=9A920780AEEAD2B5496D09DAA6DDCCDD159619CD117D8D000780F6DF3B083C14"
>
> After stripping off the leading "mdm-tlv=" of the string I am left with a
> key, value pair that I'd like to put into the accounting database.
>
> I see three database possibilities:
>
> 1. Add new fields to the radacct table for the keys above. New fields would
> be "device-platform", "device-mac", etc. I do not know if the set of fields
> is fixed or if new ones would be sent along from the NAS. This is one of
> the downsides of adding new fields to the radacct table.

  You can update the SQL schemas if you want.  You then also need to update the queries, to insert the new information.

  As to which ones are fixed, and which ones are new, well... ask Cisco.  One thing for sure, you can't update the schemas when the server receives a new packet.

> 2. Create a new one to many table that would contain the key, value pairs
> and a foreign key back to the radacct table.
>
> 3. Add a JSONB field to the radacct table and put all of the key, value
> pairs into a JSONB data structure.
>
> Does anyone have any advice as to a sensible path forward for storing this
> AVP data?
>
> Alternatively, does anyone have any success stories of storing AVP data
> when the NAS gives multiple entries of the same attribute back to FR?

  Convert them to ??? format, and dump that into the DB.  Flat-text or jsonb is fine.

  Or, insert multiple rows, each with the same foreign key.

  Alan DeKok.


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
| Threaded
Open this post in threaded view
|

Re: entering multiple AVP into database

Users mailing list
Hi Alan,

Thank you for the reply!

On Fri, Feb 5, 2021 at 4:14 PM Alan DeKok <[hidden email]> wrote:

> On Feb 5, 2021, at 4:50 PM, Matt Zagrabelny via Freeradius-Users <
> [hidden email]> wrote:
>
> > Alternatively, does anyone have any success stories of storing AVP data
> > when the NAS gives multiple entries of the same attribute back to FR?
>
>   Convert them to ??? format, and dump that into the DB.  Flat-text or
> jsonb is fine.
>

To get the ball rolling I decided for this option. As expected, the AVP is
going into the database.

Here is one of the lines from queries.conf:

NULLIF('%{Cisco-AVPair[*]}', '')

Looking at the database I see what looks to be quoted-printable [0] strings:

 mdm-tlv=3Ddevice-platform=3Dlinux-64=2Caudit-session-id=3Dc0a87311021f3000601dda71=2Cmdm-tlv=3Dac-user-agent=3DOpenConnect
VPN Agent =28NetworkManager=29 v8.10-1

The =3D is an encoded "=", the =2C is an encoded ",".

Is FR encoding the data in quoted-printable format?

If so, is there a way to decode it?

Thanks for any help or pointers.

-m

[0] https://en.wikipedia.org/wiki/Quoted-printable
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
| Threaded
Open this post in threaded view
|

Re: entering multiple AVP into database

Alan DeKok-2
On Feb 5, 2021, at 7:24 PM, Matt Zagrabelny via Freeradius-Users <[hidden email]> wrote:
> To get the ball rolling I decided for this option. As expected, the AVP is
> going into the database.
>
> Here is one of the lines from queries.conf:
>
> NULLIF('%{Cisco-AVPair[*]}', '')

 Yes.  The docs say that doing a [*] expansion prints all of the attributes, separated by commas.

> Looking at the database I see what looks to be quoted-printable [0] strings:
>
> mdm-tlv=3Ddevice-platform=3Dlinux-64=2Caudit-session-id=3Dc0a87311021f3000601dda71=2Cmdm-tlv=3Dac-user-agent=3DOpenConnect
> VPN Agent =28NetworkManager=29 v8.10-1
>
> The =3D is an encoded "=", the =2C is an encoded ",".
>
> Is FR encoding the data in quoted-printable format?

  The file containing the SQL module queries describe which characters are escaped, and why.

> If so, is there a way to decode it?

  You can decode is quoted-printable.  You do NOT want to allow random things from the network to change your SQL queries.  So quoting them is entirely the correct thing to do.

  One plan for v4 is to allow for stored procedures, and have the strings passed as parameters rather than in-line.  This would remove the need for quoting strings from the network.

  Alan DeKok.


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html