User Data Bulk Update

Hi colleagues,

If you need to update the User form in bulk mode, you might need to do it on DB level. This is the case especially if number of your users exceeds the value which is configured in Max Entries Returned by GetList parameter in Server Information form.

As an example, you might need to remove duplicate permissions from users. If this is what you need to do, you also need to update the "user_cache" table besides "user_x" (DB table for User form) because ARS will not recognize your update since it is a DB level update. However please be careful when you update esp. user_cache table because ARS uses this table on-line and you can easily have your user data corrupted if you make an incorrect update. Following are very basic queries how you can update a user's permissions:

-- user_x update

update user_x
set group_list = ';15001;'
where username = 'test_user'


-- user_cache update

update user_cache
set shortgroup = ';15001;'
where username = 'test_user'

Please note that "user_cache" and "user_x" has different table structures.

Other than User form, Group form also have this structure on DB, means there is group_cache table.

Have a sunny day!

License Report

Hi folks,

I could not write since long time. Today I would like to help you that you can generate your own license report on database level. ARS keeps licenses in tables "AR System Current License Usage" and "AR System Historical License Usage". According to the Configuration Guide, a license record is inserted into the Current License Usage when a user is logged in and occupy a license and it is archived to Historical License Usage after some time. If you have assigned Fixed licenses to many users and you do not know which accounts are really used, then you can generate a license report by combining these two tables on database. In this way, you can save some Fixed licenses. First you can start by storing the combination on a temporary table:


create table tmp_recent_license_usage as
select user_name, license_type, timeofaction last_used
from ar_system_current_license_usag
where timeofaction > <some_timestamp>
UNION ALL
select user_name, type_of_license license_type, time last_used
from ar_system_historical_license_u lic
where time > <some_timestamp>

Then you can group records by "user_name" and "license_type" because one user might have logged in with different license type in this time period:


create table tmp_recent_license_usage_user as
select user_name, license_type, unixts_to_date(max(last_used)) last_used
from tmp_recent_license_usage lic
group by user_name, license_type


In the last step, you can take the difference between your Fixed licensed users in Technician Profile form and users recently logged in:


select u.technician_login, u.first_name, u.last_name, u.email_address, gid, t.site user_site
from user_x u
left join technician_profile t on (u.technician_login = t.technician_login)
where node_license_type = 1
  and not exists (select * from tmp_recent_license_usage_user where user_name = u.technician_login and license_type = 'Fixed')
order by u.technician_login

Take care till next time.