What query do i need to generate access report in Saviynt intelligence tab?

[wizzy]

What query do i need to generate access report in Saviynt intelligence tab for the following field out put:

EMPLOYEE ID      

LOGIN    

FULL NAME         

DEPT NAME         

ENT ASSIGN PROV MECHANISM  

ENT ASSIGN CREATE         

ENVIRONMENT  

PROVISIONED BY               

ENT DISPLAY NAME          

ENT DESCRIPTION             

ENTITY TYPE        

APPLICATION     

CORP REQUESTABLE        

FIELD REQUESTABLE 

[This post has been edited by a Moderator to move to its own thread.]

[rushikeshvartak · Accepted solution]

SELECT U.EMPLOYEEID,
       AT.ACCOUNTNAME LOGIN,
       U.DISPLAYNAME  FULLNAME,
       U.DEPARTMENTNAME,
       AT.SOURCE      AS 'ENT ASSIGN PROV MECHANISM',
       AT.STARTDATE   AS 'ENT ASSIGN CREATE',
       E.DISPLAYNAME  AS 'ENVIRONMENT',
       AT.UPADTEUSER  AS 'PROVISIONED BY',
       EV.DISPLAYNAME AS 'ENT DISPLAY NAME',
       EV.DESCRIPTION AS 'ENT DESCRIPTION',
       ET.DISPLAYNAME AS 'ENTITY TYPE',
       E.DISPLAYNAME  AS 'APPLICATION',
       ''             AS 'CORP REQUESTABLE',
       ''             AS 'FIELD REQUESTABLE'
FROM   USERS U
       INNER JOIN ARSTASKS AT
               ON AT.USERKEY = U.USERKEY
       INNER JOIN ENDPOINTS E
               ON E.ENDPOINTKEY = AT.ENDPOINT
       LEFT JOIN ENTITLEMENT_VALUES EV
              ON AT.ENTITLEMENT_VALUEKEY = EV.ENTITLEMENT_VALUEKEY
       LEFT JOIN ENTITLEMENT_TYPES ET
              ON ET.ENTITLEMENTTYPEKEY = EV.ENTITLEMENTTYPEKEY
WHERE  U.USERNAME = 137658 

[wizzy]

Hello @rushikeshvartak 

That query worked. My follow up question is how do I set this to generate the report automatically? For say daily, Weekly or monthly? Als what would the query looks like if I do not need these two columns:

CORP REQUESTABLE        

FIELD REQUESTABLE

Or if I need to add more columns?

Thank you

[sk]

@wizzy : You can simply click on schedule against respective analytics and schedule as per your requirement. For details refer: Scheduling Analytics . Also if you want to automatically send report in email you can attached email template to the analytics for details refer: Sending-Analytics-Reports-Via-Emails 

If you don't need any of the columns then simply remove them

[rushikeshvartak]

You can add more columns before FROM 

You can schedule report from analytics config List -- > Schedule -->

refer: Scheduling Analytics 

SELECT U.EMPLOYEEID,
       AT.ACCOUNTNAME LOGIN,
       U.DISPLAYNAME  FULLNAME,
       U.DEPARTMENTNAME,
       AT.SOURCE      AS 'ENT ASSIGN PROV MECHANISM',
       AT.STARTDATE   AS 'ENT ASSIGN CREATE',
       E.DISPLAYNAME  AS 'ENVIRONMENT',
       AT.UPADTEUSER  AS 'PROVISIONED BY',
       EV.DISPLAYNAME AS 'ENT DISPLAY NAME',
       EV.DESCRIPTION AS 'ENT DESCRIPTION',
       ET.DISPLAYNAME AS 'ENTITY TYPE',
       E.DISPLAYNAME  AS 'APPLICATION' 

FROM   USERS U
       INNER JOIN ARSTASKS AT
               ON AT.USERKEY = U.USERKEY
       INNER JOIN ENDPOINTS E
               ON E.ENDPOINTKEY = AT.ENDPOINT
       LEFT JOIN ENTITLEMENT_VALUES EV
              ON AT.ENTITLEMENT_VALUEKEY = EV.ENTITLEMENT_VALUEKEY
       LEFT JOIN ENTITLEMENT_TYPES ET
              ON ET.ENTITLEMENTTYPEKEY = EV.ENTITLEMENTTYPEKEY
WHERE  U.USERNAME = 137658 

[wizzy]

thank you @rushikeshvartak  I have other SQL queries that need to be converted to Saviynt compatible queries. Here is one of them:

SELECT UNIQUE UGP_NAME AS "Role Name",  

  POL_DESCRIPTION      AS "Application",  

  CASE  

    WHEN ent_list.ent_code IS NULL  

    THEN '$0BLANK POLICY'  

      || ' '  

      || POC.POC_FIELD_VALUE  

    ELSE Ent_list.ent_display_name  

  END AS "Entitlement",  

  Ugp.Ugp_Description      AS "Role Description",  

  Ent_List.Ent_Description AS "Entitlement Description",  

  (SELECT usr_display_name FROM usr WHERE ugp_role_owner_key = usr.usr_key  

  )            AS "Role Owner",  

  POL_NAME     AS "Policy Name",  

  Obj.Obj_Name AS "Endpoint"  

FROM UGP  

INNER JOIN pog  

ON ugp.ugp_key = pog.ugp_key  

INNER JOIN pol  

ON pog.pol_key = pol.pol_key  

INNER JOIN POC  

ON poc.pol_key = pol.pol_key  

INNER JOIN OBJ  

ON poc.obj_key = obj.obj_key  

LEFT JOIN ENT_LIST  

ON Lower(POC.POC_FIELD_VALUE) = Lower(ENT_CODE)  

AND ent_list.obj_key          = poc.obj_key  

WHERE POC_FIELD_VALUE NOT    IN ('READ', 'TP1TRAN','MP1TRAN','TT1TRAN','ALLOW', 'CPC', 'CKC','ADP', '350','150','101','A','U','0PSo0000000RMR7GAO')  

ORDER BY ugp_name,  

  pol_description; 

 Can I share the other one with you via email? Thank you

[rushikeshvartak]

You need to convert to SQL in case if you face issue create new thread

Please click the 'Accept As Solution' button on the reply (or replies) that best answered your original question.

[wizzy]

thank you @rushikeshvartak  I have other SQL queries that need to be converted to Saviynt compatible queries. Here is one of them:

SELECT UNIQUE UGP_NAME AS "Role Name",  

  POL_DESCRIPTION      AS "Application",  

  CASE  

    WHEN ent_list.ent_code IS NULL  

    THEN '$0BLANK POLICY'  

      || ' '  

      || POC.POC_FIELD_VALUE  

    ELSE Ent_list.ent_display_name  

  END AS "Entitlement",  

  Ugp.Ugp_Description      AS "Role Description",  

  Ent_List.Ent_Description AS "Entitlement Description",  

  (SELECT usr_display_name FROM usr WHERE ugp_role_owner_key = usr.usr_key  

  )            AS "Role Owner",  

  POL_NAME     AS "Policy Name",  

  Obj.Obj_Name AS "Endpoint"  

FROM UGP  

INNER JOIN pog  

ON ugp.ugp_key = pog.ugp_key  

INNER JOIN pol  

ON pog.pol_key = pol.pol_key  

INNER JOIN POC  

ON poc.pol_key = pol.pol_key  

INNER JOIN OBJ  

ON poc.obj_key = obj.obj_key  

LEFT JOIN ENT_LIST  

ON Lower(POC.POC_FIELD_VALUE) = Lower(ENT_CODE)  

AND ent_list.obj_key          = poc.obj_key  

WHERE POC_FIELD_VALUE NOT    IN ('READ', 'TP1TRAN','MP1TRAN','TT1TRAN','ALLOW', 'CPC', 'CKC','ADP', '350','150','101','A','U','0PSo0000000RMR7GAO')  

ORDER BY ugp_name,  

  pol_description; 

 Can I share the other one with you via email? Thank you

[wizzy]

Hello @rushikeshvartak  can you help me with this? I need the above OIG SQL query translated to Saviynt. Thank You