Find Users Who Have/Have Not Set Challenge Questions in OIM

Recently we had a request to find the number of users who had (and had not) set their challenge questions in OIM 11g. Here’s a quick tip to get the answer: Challenge questions and answers are stored in the PCQ table. Everything is encrypted, but you can still verify their existence by running these SQL queries:

This first query will allow you to see the number of users who have not defined their challenge questions:

select count(*) from usr left outer join pcq on (usr.usr_key = pcq.usr_key)
where pcq_key is null;

This second query allows you to see the number of users who have defined their challenge questions (note the division by 3 because each user has 3 challenge questions):

select count(*)/3 from usr left outer join pcq on (usr.usr_key = pcq.usr_key)
where pcq_key is not null;