Reporting on RSA Authentication Manager 8.x users with On-Demand Token, a fixed passcode or a hardware/software token assigned and when they last authenticated

[#1]

Article Number

000016912

Applies To

RSA Product Set:  SecurID
RSA Product/Service Type:  Authentication Manager
RSA Version/Condition:  8.x

Issue

The license status page on the Security Console under Setup > Licenses > Status shows the number of active users in the database and the total number of active users allowed on the license.  This solution provides information on how to determine which users are contributing to the active user count.

Resolution

Steps to Run the Active User Report

1. Download the query script using the link in the Notes section of this article.
2. Copy the SQL script attached to this article to the  Authentication Manager 8.x primary's /home/rsaadmin directory (using WinSCP for example).
3. Set the file owner for the UserLicenseReportv8.sql file to rsaadmin

chown rsaadmin UserLicenseReportv8.sql

4. Set file permissions on the SQL script to be executable, using one of the two commands below:

chmod +x UserLicenseReportv8.sql

chmod 0755 UserLicenseReportv8.sql

5. Confirm that SSH is enabled via the Operations Console under Administration > Operating System Access.
6. Logon to the Authentication Manager 8.x primary via SSH as the rsaadmin user.
7. Determine the Authentication Manager 8.x database password as shown in following example.  Note that the OC administrator name and output password will be different than the example values shown here:

rsaadmin@am8p:~> cd /opt/rsa/am/utils
rsaadmin@am8p:/opt/rsa/am/utils> ./rsautil manage-secrets -a get com.rsa.db.dba.password
Please enter OC Administrator username: <enter name of the Operations Console administrator>
Please enter OC Administrator password: <enter password for the Operations Console administrator>
com.rsa.db.dba.password: <rsa.db.dba.password is returned>

For example, 

rsaadmin@am8p:~> cd /opt/rsa/am/utils
rsaadmin@am8p:/opt/rsa/am/utils> ./rsautil manage-secrets -a get com.rsa.db.dba.password
Please enter OC Administrator username: ocadmin
Please enter OC Administrator password: ********
com.rsa.db.dba.password: GrcvNN2FUAsWjyPfhaIsSWvjvZhvtN

8. Execute the query.  You will be prompted for the com.rsa.db.dba.password output from step 7.

cd ../pgsql/bin
rsaadmin@am8p:/opt/rsa/am/pgsql/bin> ./psql -h localhost -p 7050 -d db -U rsa_dba -f ~/UserLicenseReportv8.sql > ~/output.html
Password for user rsa_dba: <enter com.rsa.db.dba.password captured above>

9. The output.html is written to /home/rsaadmin.  Using WinSCP, or another file transfer tool, copy the output.html file off the Authentication Manager server for review.

Sample Output

The tables below show sample output from running this report

Users with a Token

tokenid	serial	token_type	username	userid
dbd546b20b8d940a01e21d006c577c83	XXXXXX7768	SOFTID	user.subdomain2	1cbb255d0b8d940a01b685408c546b54
c732dffc0b8d940a01b4dbdc7cb27983	XXXXXX4021	SID800	on.demand	4753bdb00b8d940a0206fc869db5d862
dbd546930b8d940a01e596c7960ed372	XXXXXX7761	SOFTID	on.demand	4753bdb00b8d940a0206fc869db5d862
dbd546a30b8d940a01da848ff692edae	XXXXXX7765	SOFTID	smithj	69d617890b8d940a01ea754e2e5a4735
dbd546a30b8d940a01e0db9875cfe7a6	XXXXXX7764	SOFTID	smithj	69d617890b8d940a01ea754e2e5a4735
c252948a0b8d940a01f5126d581b06e4	XXXXXX1332	SID800	token.user	7d4186760b8d940a025f5f29d52711a2
c732de660b8d940a01a2b7a9d4d2ed4c	XXXXXX3997	SID800	doem	bd7166410b8d940a01b837590a350b6c
761f49910b8d940a02003f7e7d5b53af	XXXXXX2256	KEYFOB	doem	bd7166410b8d940a01b837590a350b6c
dbd546640b8d940a01c4fe7db09a19dc	XXXXXX7755	SOFTID	soft.token	c42380de0b8d940a01f2e5ffb619483c
dbd546930b8d940a01e4bec42a031ab8	XXXXXX7760	SOFTID	soft.token	c42380de0b8d940a01f2e5ffb619483c
c2560e9d0b8d940a01d7f48adeb14e53	XXXXXX4670	KEYFOB	rsatest	c713094a0b8d940a034c88b30bb9106c
dbd546930b8d940a01c0f3509c5e1e62	XXXXXX7759	SOFTID	rsatest	c713094a0b8d940a034c88b30bb9106c
dbd546930b8d940a01c76ca09a2f3692	XXXXXX7758	SOFTID	rsatest	c713094a0b8d940a034c88b30bb9106c
485742750b8d940a01f6ffe2eefdc0a5	XXXXXX7530	SID800	selfenrol1	f07063e30b8d940a018e8da09016da50
dbd546840b8d940a01c6cba4d6c49998	XXXXXX7757	SOFTID	selfenrol1	f07063e30b8d940a018e8da09016da50

(15 rows)

Total Tokens Assigned

tokens_assigned
15

(1 row)

Total Unique Users with Tokens

Note that a user can have up to three tokens assigned to them.

total_unique
8

(1 row)

Users Enabled for On Demand Authentication

tokenid	username	userid
937fd36fdd8e940a1a845a4e89110aa5	risk.based	6818ab470b8d940a01e5323de4e7a166
59571bc30b8d940a0246d45829470b7c	rsatest	c713094a0b8d940a034c88b30bb9106c
6adc132e0b8d940a02e3e748b24a8180	on.demand	4753bdb00b8d940a0206fc869db5d862
f0875fec0b8d940a0271cf7d22174dd9	selfenroll1	f0875b480b8d940a01c3f4d2c4aa3c73

(4 rows)

Total Users Enabled for On Demand Authentication

total
4

(1 row)

Users Enabled for Risk Based Authentication

username
risk.based
self.service
admin
rsatest
on.demand
m??ller??
smithj

(7 rows)

Users with Fixed Passcode

loginuid
jdoe
rsatest
m??ller??
ip.user
Challenge.Me
site1admin
site2admin
Administrator
radiustest
locked.out
special.user
tacplus
win71

(13 rows)

Total Users with a Fixed Passcode

total
13

(1 row)

All Active Users

username	identity_source	fixed_passcode	token	on_demand_enabled	last_login	rba_enabled
admin	Internal Database	NO		NO		YES
Administrator	SMITH.LOCAL	YES		NO	2012-08-09 11:29:14	NO
blah	Internal Database	NO	000216702256	NO		NO
blah	Internal Database	NO	000209513997	NO		NO
Challenge.Me	SMITH.LOCAL	YES		NO	2012-10-05 20:32:53	NO
ip.user	SMITH.LOCAL	YES		NO	2012-09-18 14:30:49	NO
jdoe	Internal Database	YES		NO		NO
locked.out	SMITH.LOCAL	YES		NO		NO
m??ller??	Internal Database	YES		NO	2013-07-12 15:06:40.174	YES
on.demand	SMITH.LOCAL	NO	000209514021	YES	2013-05-24 09:37:38.285	YES
on.demand	SMITH.LOCAL	NO	000205167761	YES	2013-05-24 09:37:38.285	YES
radiustest	Internal Database	YES		NO	2012-06-19 09:11:05	NO
risk.based	SMITH.LOCAL	NO		YES	2013-04-24 10:33:48.73	YES
rsatestSMITH	.LOCAL	YES	000075884670	YES	2013-07-16 12:50:59.676	YES
rsatest	SMITH.LOCAL	YES	000205167759	YES	2013-07-16 12:50:59.676	YES
rsatest	SMITH.LOCAL	YES	000205167758	YES	2013-07-16 12:50:59.676	YES
selfenrol1	Internal Database	NO	000079277530	NO		NO
selfenrol1	Internal Database	NO	000205167757	NO		NO
selfenroll1	SMITH.LOCAL	NO		YES	2012-06-18 14:36:04	NO
self.service	SMITH.LOCAL	NO		NO		YES
site1admin	Internal Database	YES		NO		NO
site2admin	Internal Database	YES		NO		NO
soft.token	SMITH.LOCAL	NO	000205167755	NO		NO
soft.token	SMITH.LOCAL	NO	000205167760	NO		NO
special.user	Special Characters	YES		NO	2013-03-18 16:15:20	NO
tacplus	Internal Database	YES		NO	2013-03-21 08:53:00	NO
token.user	SMITH.LOCAL	NO	000215691332	NO	2013-03-18 12:09:12	NO
user.subdomain2	Internal Database	NO	000205167768	NO		NO
smithj	SMITH.LOCAL	NO	000205167764	NO	2013-05-03 13:08:57.071	YES
smithj	SMITH.LOCAL	NO	000205167765	NO	2013-05-03 13:08:57.071	YES
win71	Internal Database	YES		NO	2013-01-09 14:11:18	NO

(31 rows)

Count of Unique Active Users

A user is active if they have a token and/or a fixed password, and/or an on-demand authenticator, and/or are enabled for Risk Based Authentication

active_users
24

(1 row)

Notes

Note:  Unresolvable users will also count towards the total number of users. The report generated by this solution will not detect unresolvable users, so please ensure that you run a cleanup identity source job to check for unresolvable users before report execution.  To run the cleanup,

1. Login to the Security Console and select Setup > Identity Sources > Cleanup Unresolvable Users.
2. Select the identity source in question.
3. Define a grace period.
4. Click Next.
5. Review the list of unresolveable users.  
6. Click Clean Up Now.

This solution is applicable only to RSA Authentication Manager 8.x.  

A SQL query script can determine the active user count details and is attached below or available for download here.