We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

Time stamp in Entitlement_usage report

saidnya_naik
New Contributor
New Contributor
Hi,

We have analytics created for SAP endpoint wherein the report which is generated has the startdate and enddate which is taken from the table entitlement_usage.
Currently the report doesn't give us the exact time stamp of the transaction.
For eg: We are able to see the startdate and enddate as 05-Jan-2023 00:00:00
 
Below is the query which we have used to create the report:

select distinct ev.entitlementtypekey, (select a.name from accounts a where a.accountkey=ua.accountkey) as 'SAP USER ID',
eu.id as EID, eu.STARTDATE, eu.ENDDATE,eu.ENTITLEMENT_VALUEKEY,
ev.entitlement_value,eu.USAGEVALUE,
eu.USAGE_PERIOD,
u.username from entitlement_usage eu
join user_accounts ua on ua.accountkey = eu.accountkey
join users u on u.userkey = ua.userkey
join entitlement_values ev on ev.entitlement_valuekey= eu.entitlement_valuekey
join entitlement_types et on et.entitlementtypekey=ev.entitlementtypekey
WHERE eu.startdate>=date_sub(NOW(),interval 1 MONTH)

Please let us know how we can get the exact time stamp in the above analytics report when the transaction had taken place.
6 REPLIES 6

DaanishJawed
Saviynt Employee
Saviynt Employee

Hi @saidnya_naik ,

Can you try the below query?

SELECT DISTINCT
    ev.entitlementtypekey,
    (SELECT 
            a.name
        FROM
            accounts a
        WHERE
            a.accountkey = ua.accountkey) AS 'SAP USER ID',
    eu.id AS EID,
    DATE_FORMAT( eu.STARTDATE, "%Y-%m-%d %h:%m:%s") as 'Start Date',
    DATE_FORMAT( eu.ENDDATE, "%Y-%m-%d %h:%m:%s") as 'End Date',
    eu.ENTITLEMENT_VALUEKEY,
    ev.entitlement_value,
    eu.USAGEVALUE,
    eu.USAGE_PERIOD,
    u.username
FROM
    entitlement_usage eu
        JOIN
    user_accounts ua ON ua.accountkey = eu.accountkey
        JOIN
    users u ON u.userkey = ua.userkey
        JOIN
    entitlement_values ev ON ev.entitlement_valuekey = eu.entitlement_valuekey
        JOIN
    entitlement_types et ON et.entitlementtypekey = ev.entitlementtypekey
WHERE
    eu.startdate >= DATE_SUB(NOW(), INTERVAL 1 MONTH)

Thanks.

saidnya_naik
New Contributor
New Contributor

Hi Daanish,

Tried the above query, it is giving us a time stamp. But all of the rows present in the query have the same time for the startdate and enddate.

Hi @saidnya_naik ,

That can be because of the data that has been imported/present in the system, contains the same start date and end date. Can you validate the data once?

Also, what is the use-case that you are trying to achieve through this report?

saidnya_naik
New Contributor
New Contributor

Hi,

We are trying to get the usage_data of the tcode of all the SAP endpoints into Saviynt wherein we need to get the transaction start time and transaction end time for each of the entitlement.


Hi @saidnya_naik ,

How are you importing the usage_data of all tcode of all SAP endpoints? Is there any JSON that you have configured?

Thanks.

We are importing SWNCMONIINDEX usage table in the SAP connection under TABLES