Security

Hey everyone. Looking to the SQL gurus here for some guidance.

 

Is there any way i can construct an SQL query to the endpoint database to do return Airwatch defined attributes for a device?

 

For example - Take username from RADIUS request {Authentication:Username} which is actully device MAC address or UDID - search endpoint database for either MAC / UDID and then return Airwatch specific attributes. such as Device Owner or Serial Number and send these back in a RADIUS reply.

 

I've done some browsing of the SQL db (tipsdb) and can see all the endpoints recorded in the tips_endpoint_profiles table but none of the columns here contain the data i need which means i can't modify the standard SQL search filters as the columns aren't valid in the tips_endpoint_profiles table. 

 

The columns i'm looking for are device owner and serial number

 

 

It seems the data i need is stored in the tips_tag_values table but i can't work out how to search the endopints from this data as the id field appears to be different to the standard endpoint table. 

 

Scott

 

 

Use this as the value in your enforcement profile: 

%{Endpoint:Owner}
%{Endpoint:Serial Number}


Thanks, 
Tim

Hey Tim,

 

The enforcement profile i've got sorted. It's the actual authorisation of the device by using username against the Endpoint Repository that is the challenge. 

 

From what i can see, when you use role mapping to query endpoint status this seems to happen without the need for any specific filter on the Endpoint Repository source. Most of these queries seem to be based on the Connection:Client-Mac-Address-NoDelim field which is derived from the radius calling station ID. If i send the mac address in the calling station ID then my logic works, i can do a mac authentication based on the username (which is mac address) then do an authorisation / role mapping based on Airwatch attributes (i.e. is it comprimised). and then after that lookup is done, i can then return airwatch variables in the enforcement profile as you suggested. 

 

In my use case the NAS is not able to send the calling station id of the endpoint so all i have to work with is the username.

 

I guess what i need to be able to do is use the Authentication:Username attribute as the search target in the MDM repository. does that make sense?

 

Scott

 

 

 

 

So it looks like the table i need to access is tips_endpoints_attr_view however it appears the appexternal account does not have permission to read this table... GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE tips_endpoints_attr_view TO appadmin;

After much swearing and testing i have discovered the following information which hopefully helps someone else trying to do this. 

 

The attributes that are populated by Airwatch (and presumably any other external context server) are not stored in the standard endpoints table in the tipsdb. 

 

The Airwatch attributes are stored in the SELECT  tips_endpoints_attr_view table which is not accessible using the appexternal user account. 

 

the only way around this is to modify the default Endpoint Repository authentication source and add filters that perform the lookups here. These will work as the Endpoint Repository uses the appuser sql account which has the right permissions. 

 

The next challenge with the variables in this table is that each endpoint attribute is stored as it's own table, not just a column on the existing one. 

 

for example if i search for a mac address in this table it will return a large number of results where each attribute contains a tag_value and a tag_name . These don't have a direct relationship to each other but rather are tied to the mac_address. 

 

So to return the device owner from MAC address you need to run something like this:

 

SELECT tag_value as owner FROM tips_endpoints_attr_view
WHERE tag_name = 'Owner' AND mac_address = '%{Authentication:Username}'

 

If you are using an authentication method such as 802.1x where the Calling Station ID is populated with the MAC address of the client, then this lookup is done automatically using some hidden query filter and the results are returned as "Endpoint:Owner" attributes in the access tracker which are then able to be used for enforcement / role mapping. 

 

If however you are using a method that doesn't send calling station id, these lookups can't be automatically performed as the hidden filters only run based on the device mac address as presented in the calling station id. 

 

Hopefully Aruba can provide a simpler method of retrieving these variables, i have been told by the TAC there is a high priority feature request in place to allow permissions of the appexternal account to be modified to allow direct query of the  tips_endpoints_attr_view table. 

 

Scott