Security

Reply
Highlighted
MVP Expert

Counting number of devices registered to a SponsorName

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

 

Guru Elite

Re: Counting number of devices registered to a SponsorName

What are you specifically trying to do workflow-wise?

| Tim Cappalli | Aruba Security | @timcappalli | timcappalli.me |

NOTE: Answers and views expressed by me on this forum are my own and not necessarily the position of Aruba or Hewlett Packard Enterprise.
MVP Expert

Re: Counting number of devices registered to a SponsorName

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

 

Guru Elite

Re: Counting number of devices registered to a SponsorName

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

| Tim Cappalli | Aruba Security | @timcappalli | timcappalli.me |

NOTE: Answers and views expressed by me on this forum are my own and not necessarily the position of Aruba or Hewlett Packard Enterprise.
MVP Expert

Re: Counting number of devices registered to a SponsorName

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

Rgds

Alex

 

MVP Expert

Re: Counting number of devices registered to a SponsorName

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

MVP Expert

Re: Counting number of devices registered to a SponsorName

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

MVP Expert

Re: Counting number of devices registered to a SponsorName

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

a

Search Airheads
cancel
Showing results for 
Search instead for 
Did you mean: