Security

I'm trying to write an SQL statement  to count the number of GlearPass Guest devices registered by a particular SponsorName

I've add the follpowing filter to Authentication:Source [Guest Device Repository]  ( attached)

But basically I'm doing

Select Count(*) from tips_guest_users AS Sponsor_Count  from tips_guest_users where Sponsor_Name = '%{Authentication:Username}'

Sponsor_Count is an integer with an alias of SponsorCount.

When I add it to a service I get a "Failed to get value for attributes=[SponsorName]."  error

Goes back to my previous post re airgroups. wpa2-psk devices have their mac addresses regiatered in clearpass guest and if appropriate airgroup_enable = 1 and shared airgroup =personal

Because we have a whole batch of ACLs applied to wifi devices that include blocking client to client connectivity, for airgroups I need to apply another set of ACLS that allow airgroup functuonality to work. This is done by default for clearpass guest registered devices 

For 802.1x devices I have 2 choices 

1). Register the mac address of the airgroup client device in clearpass guest and apply the same ACLs  - this works just fine, but means the user has to register every device they've got.

2). Try to do something automagically. 

As we are only providing personal airgroup functionality at the moment,  If a user has registered a number of airgroup devices ( chromecast apple tv etc) then we want any 802.1x devices authenticating onto our wpa2-enterprise network with a userid that = the sponsor name of the registered devices to have the same ACL set as the wpa2-psk devices and so I can set the appropriate Aruba-Airgroup-userid attribute.

If I can count the number of devices registered by a SponsorName = Authentication:Username if its not 0 I can  apply appropriate set of ACLs to enable the client device to see the registered airgroup devices without registering anything.

I know I can put my Username into the airgroup_shared_user attribute but that doesn't get me the correct ACL set for the 802.1x device  hencde the above

Will happily talk to you at Atmposphere about this but at the moment just need to get a count of devies registered by a particular user

Rgds

Alex

select COUNT(*) FROM tips_guest_users WHERE ((guest_type = 'DEVICE') AND (sponsor_name = '%{Authentication:Username}'))

Many thanks, that worked just fine. Its opened another can of worms ... but . is what I want at the moment

Rgds

Alex

o.k. So got everything working as I wanted ... and of course then wanted to change something. I started off by trying to add another AND clause for airgroup_enable = 1 and the sql failed with "column airgroup_enable" does not exist in the specified table.

o.k. so I then took a backup from policy manager, extracted its contents and ran pg_retore on the 2 .pgdump files to get a basic set of database tabels on my iMac. .... The unpacks worked but generated some errors as user postgres didn't exist on my mac. However the point to note is that database table tips_guest_users wasn't restored from the backup.

We also run the Aruba cppmbackup VM to automagically backup all our servers overnight ... and an extract/restore from its tar.gz file also didn't show tips_guest_users.being present either.

This is a bit worrying .. can anyone confirm that table tips_guest_users is created when running the normal backup options for clearpass ... or is it really that you have to go into clearpass guest and manually create a backup file there ?

Just going to try unpacking the .pgdump files onto a linux box that does have user postgres on it

Rgds

Alex

Tim,

Thanks for the "tweaked" SQL

select COUNT(*) AS sponsor_count FROM tips_guest_users WHERE ((guest_type = 'DEVICE') AND (sponsor_name = '%{Authentication:Username}') AND (attributes@>'{"airgroup_enable": "1"}'))

but if I put it into clearpass it flags an sql error.

A

an actually its clearpass complaining. The SQL actually works just fine

a