entering multiple AVP into database

[Alan DeKok]

On Feb 5, 2021, at 4:50 PM, Matt Zagrabelny via Freeradius-Users <freeradius-users at lists.freeradius.org> 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.