Saviynt Forums

saidnya_naik · ‎03/14/2023

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.

DaanishJawed · ‎03/15/2023

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 · ‎03/22/2023

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.

DaanishJawed · ‎03/23/2023

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 · ‎03/27/2023

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.

DaanishJawed · ‎05/31/2023

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.

saidnya_naik · ‎06/22/2023

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

Saviynt Forums

Time stamp in Entitlement_usage report