Find work Item Pending with spadmin

IdentityIQ (IIQ) IIQ Discussion and Questions
1

Could someone please assist with this? We have a significant number of work items currently pending with the spadmin account.

Is there a way to identify:

  • Which users these work items are associated with,
  • Which roles they pertain to, and
  • Which applications they are related to?

We’re open to using either SQL queries on the IdentityIQ database or creating a custom report to extract this information.

2

Hi @poison001 ,

You can retrieve all pending work items assigned to spadmin using the following query. Make sure to replace 'id' with the Identity ID of spadmin:

SELECT * FROM spt_work_item WHERE owner = 'spadmin_identity_id';

This query will return all the work items, including details such as the target_name (i.e., the requestee) and attributes.

3

I tried this but i can’t find app details and role details

4

Hi @poison001 ,

The Attributes column contains the ApprovalSet details, where you can find the associated role name.

5

Hello Deepak,

You should start looking for the type of opened work items and then act accordingly. It could be a case that most of them be either from irrelevant certifications, or unactioned remediation, or approvals for terminated identities etc.

select type,COUNT(type) as type_count from IDENTITYIQ.spt_work_item where state is NULL group by type;

Based on the result, analyse each type -

Let’s say for Certification

select FROM_UNIXTIME(wi.created/1000,'%Y-%m-%d') as created,FROM_UNIXTIME(wi.modified/1000,'%Y-%m-%d') as modified,wi.name,wi.description,wi.type,wi.state,wc.name workflow_case_name,wi.attributes,id.name owner
 from IDENTITYIQ.spt_work_item as wi,IDENTITYIQ.spt_identity as id,IDENTITYIQ.spt_workflow_case as wc 
 where wi.state is NULL and wi.owner=id.id and id.name='spadmin' and certification is NULL and wi.workflow_case=wc.id;

For Items not related with certs-

select FROM_UNIXTIME(wi.created/1000,'%Y-%m-%d') as created,FROM_UNIXTIME(wi.modified/1000,'%Y-%m-%d') as modified,wi.name,wi.description,wi.type,wi.state,cert.name cert_name,wi.certification,wi.attributes,id.name owner
 from IDENTITYIQ.spt_work_item as wi,IDENTITYIQ.spt_identity as id,IDENTITYIQ.spt_certification as cert 
 where wi.state is NULL and wi.owner=id.id and id.name='spadmin' and certification is NOT NULL and wi.certification=cert.id

Now extract the required value from attributes column

Assuming MySQL as database, if any other, please review and modify the query.