Need to generate report which could give active account not checked-out for last 12 months

[Narendrapranaam]

Hi Team,

Client is looking for active accounts who are not utilizing CPAM license (basically not performing Check-out). To find the same,

1. We ran a report (Detected - Dormant Accounts of active users in the last 6 months) using sql query which could find last login for a user and with last login we tried to convince that the tool was not used for password check-out but it was not reliable data as CPAM unable to detect last login if same session continuously used by individual
2. We ran out-of-box report(Checked-out report with Unique value) that has unique value of check-out with start date and end date but even that doesnt shows last used date(end date/start date)

We would need help in providing data which client is looking for, unique A- account and last used (timestamp)

Thanks!

Regards,
Naren

[This message has been edited by moderator to disable URL hyperlink]

[Nirav]

Hi Naren,

Based on your requirement, you are looking to identify if any request is created for accounts which are of the type "Credential" and perform analysis if any request was raised in last 6 months.

You can try the below query in data analyzer which combines request, task and accounts table to identify if there is any request raised for account of the type credential and will give you the requestID and request date.

You can tailor the query further with date ranges or download report into an excel.

select JBPMPROCESSINSTANCEID,requestdate from ars_requests where requestkey in (select requestkey from arstasks where accountname in (select name from accounts where accountconfig like "%\"pamtype\":\"Credential\"%"))

Regards,

Nirav

[Narendrapranaam]

Hi Nirav,

The requirement is to get list of all users who have not performed password check-out for past 1 year(12 months)

Thanks!

Regards,
Naren

[Narendrapranaam]

Hi @Nirav  , Team,

Any updates?

Thanks!

Regards,

Naren

[This message has been edited by moderator to add @ mention]

[Narendrapranaam]

Hi Team,

Any updates?

Thanks!

Regards,

Naren

[Narendrapranaam]

Hi Team,

Any updates pls?

Thanks!

Regards,

Naren

[Nirav]

Hi Naren,

 As per your forum subject "Need to generate report which could give active account not checked-out for last 12 months" - you need account that is not checked out right? You can just reverse the above query to get a list of all accounts not checked out for 'X' months. Please edit the query as per your requirement. 

select name from accounts where accountconfig like "%\"pamtype\":\"Credential\"%" and name not in (select accountname from arstasks where requestkey not in (select requestkey from ars_requests where requestdate > "Please specify your duration condition here")));

Regards,

Nirav