How to create a report when an Entitlement Enddate is set for more than a certain period of time

[JohnDoe]

Hello.

I have configured the access request to have the applicant specify the Enddate of the Entitlement.
So I want the administrator to be notified when the Enddate is set to 30 days or more from the Startdate.
So I want to output the following parameters as a report.

・Requestor's username
・Requestor's first name
・Requestor's last name
・Approver
・Entitlement name
・Security system
・endpoint
・Entitlement StartDate
・Entitlement EndDate

Conditions
Entitlement Enddate is 30 days or more

Can you tell me the SQL query to get this report?

Thank you and best regards,

Allow Enddate to be specified on request with the following settings:

Entitlement Type > Detail

[sudeshjaiswal · Accepted solution]

Hello @JohnDoe ,

Please use the below query for your use case,

select u.username, u.firstname, u.lastname, a.name as 'Account Name', e.endpointname as 'Application', ev.entitlement_value as 'Entitlement', ae1.startdate as 'Start Date',
ae1.ENDDATE as 'End Date' from
users u, accounts a, user_accounts ua, entitlement_values ev, account_entitlements1 ae1, endpoints e
where u.userkey= ua.userkey and u.STATUSKEY=1
and ua.ACCOUNTKEY = a.ACCOUNTKEY
and a.ACCOUNTKEY = ae1.ACCOUNTKEY
and a.status in ('Manually Provisioned',1,'Active')
and ae1.ENTITLEMENT_VALUEKEY = ev.ENTITLEMENT_VALUEKEY
and a.ENDPOINTKEY = e.ENDPOINTKEY
and e.STATUS = 1
and ev.status = 1
and ae1.enddate is not null
and DATEDIFF(ae1.ENDDATE,ae1.startdate) >= 30

Thanks