Security

I've an Authentication source that returns the number of Clearpass Guest devices that have been registered by a particular sponsor name

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

I'd like to add a condition that only counts the number of devices that have airgroup_enable set to 1.

Tried adding an additional AND to the above

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

But clearpas complains saying column airgroup_renable doesn't exist in that table. Anyone know what the correct SQL might be?

Rgds

Alex

We dont have any such 'airgroup_enable' colum in clearpass database, thats why it is returing doesn't exist in that table.

:-) sort of figured that out.

So If I wanted to cound the number of clearpass guest devices registered by a given sponsor name that had airgroup_enable = 1 

Is it doable ?

Rgds

Alex

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

Many thanks Tim, you're a star!

Rgds

Alex

Ermmm inserting that into my Auth source comes back with an SQL error